SQL Performance issue after migration from SQL Server 2005 to SQL Server 2012

There can be many things to address. Below is an outline of things to check before jumping on any conclusions :

First, your sequence of POST restore steps will mess up all the work you did. Never shrink your database and that too especially after doing Index maintenance.

Read up - Why you should not shrink your data files by Paul Randal.

Below are my common post restore steps for any lower version to higher version migration. In your case SQL Server 2005 to 2012.

change compatibility level on the restored databases to 110 (with the caveat that it will require a full regression test)
run DBCC UPDATEUSAGE on the restored databases DBCC UPDATEUSAGE('database_name') WITH COUNT_ROWS
Update statistics on all tables with full scan : Update Statistics table_name with FULLSCAN
Check the fragmentation levels and depending on the fragmentation level, run a reorg/rebuild of all Indexes. You can use Ola's Index and Stats maintenance scripts.
Recompile all SP's using sp_recompile 'procedureName'
Refresh your views SP_REFRESHVIEW view_name

----------

As a side note:

turn on Instant File Initialization on the new server.
Have multiple tempdb data files with equal size.
Enable Trace Flag 1118
Configure max and min memory correctly. Especially Max memory away from default.
Properly adjust the MAXDOP settings. Refer to https://dba.stackexchange.com/a/36578/8783 for more details.
Best is to install sp_Blitz from Brent Ozar.  Run it and address the critical and high priority issues reported by it.
You can even use SQL Power Doc from kendalvandyke - SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2012, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 and Windows 8. Also useful for Planning upgrades - see what hidden features are in use on an instance.
Enable Optimize for ad-hoc workloads and Default backup compression options.

Refer to UPGRADE and POST UPGRADE steps for more details.


SSAS Date Dimension Relationship In Data Source View

This relation is not necessarily expressed in the data source view, but in the dimension usage tab, but you can define it in the dsv if you want.

The implications of setting it incorrectly in the DSV are that the dimension usage tab will propose the wrong default and you have to change it manually, but whatever you put in the dimension usage tab on your measure group is what is actually defining the relation.

Without knowing your exact data model I would think your relation has to be Fact.MonthDateKey -> Date.MonthDateKey in your case.

There is an example of how this is done in the AdventureWorks Analysis services project where the date dimension is linked to the Sales Target measure group on Calendar Quarter:

You basically just pick another Granularity Attribute from your dimension and link it to the correct column in your fact table like this:

The dsv relation for this setup looks like this (which would be monthdatekey in your scenario)

The caveat is displayed as a warning when you don't select the key attribute as your granularity attribute:

  If you select a non-key granularity attribute, the server will not be
  able to aggregate data properly unless you make sure all other
  attributes are directly or indirectly related to it by specifying them
  as related attributes

Which is basically saying you need to define attribute relationships.

This is also explained in more detail in this walkthrough Defining Dimension Granularity within a Measure Group

  To specify a grain for a cube dimension other than the default grain,
  you modify the granularity attribute for a cube dimension as used
  within a particular measure group on the Dimension Usage tab of Cube
  Designer. When you change the grain of a dimension within a specific
  measure group to an attribute other than the key attribute for that
  dimension, you must guarantee that all other attributes in the measure
  group are directly or indirectly related to new granularity attribute.
  You do this by specifying attribute relationships between all other
  attributes and the attribute that is specified as the granularity
  attribute in the measure group. In this case, you define additional
  attribute relationships rather than move attribute relationships. The
  attribute that is specified as the granularity attribute effectively
  becomes the key attribute within the measure group for the remaining
  attributes in the dimension. If you do not specify attribute
  relationships appropriately, Analysis Services will not be able to
  aggregate values correctly, as you will see in the tasks in this
  topic.


Restoring RMAN backup in Oracle 9i to another server

As I remember, in 9i when rman is doing backup "include current controlfile" then this controlfile does not contain info about currently processing backup. You can check it by restore this controlfile and issue command:

list backup summary;

You need to backup your controlfile after you do backup of datafiles.

Add backup current controlfile;  command before release channel t1;
Or configure autobackup of controlfile - I like this option more.

CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/Backups/%F';


Restoring ANY database to Azure MI fails

Just got feedback from Microsoft, this is a known issue which is currently being fixed.

As per their recommendation creating the backups with the option CHECKSUM leads to restorable backups.

BACKUP DATABASE [DBNAME] TO URL = N'https://<storage_account>.blob.core.windows.net/<backup_container>/on_prem_db_full.bak'
WITH CHECKSUM


How do databases deal with file system fragmentation?

Depending on the operating system in use, and the DBMS in use, there are varying degrees of cooperation between them.

For instance, using SyBase ASE on AIX, you can tell the DBMS to allocate an entire file system as a single monolithic structure for use ONLY by the DBMS.  In this instance, the database is the file system, and file-system-level fragmentation is a non-issue.

For SQL Server, and probably most other DBMS's, database files are stored on a pre-designed file system that may or may not contain other files.  If the filesystem containing these database files does not contain files from any other system, the database files may still become fragmented due to growth of the files themselves.

In a filesystem that contains both databases and other files, filegrowth operations on the database files will almost certainly result in their becoming physically fragmented.  As far as I am aware there are no DBMS's that integrate directly with the operating system to prevent physical file fragmentation.

Many larger production database systems run on highly complex systems including virtualization of the physical machines, and storage-area-networks, or SANs.  These systems are designed to share resources across the machines using them.  In the case of databases stored on a SAN, there is a very good chance the physical blocks on the SAN that contain the database files will in fact be spread across a large number of physical drives, and are unlikely to be contiguously allocated.  Brent Ozar says "If you’re using shared storage like EMC, NetApp, or Dell gear that shares drives between a bunch of different servers, then all of your drive access will be random anyway.  Your hard drives are shared with other servers that are also making drive requests at the same time, so the drives will always be jumping all over the place to get data."1


How can I output more than 256 characters to a file?

While trying out the various suggestions in the comments I have stumbled across a solution.

I replaced:

'"' + Column4 + '"' as ReallyLongString,

with:

'"' + REPLICATE('O', 4000) + '"' AS ReallyLongString,

and all of the text was output.

However, using a variable:

DECLARE @longText NVARCHAR(MAX)
SET @longText = REPLICATE('O', 4000);

'"' + @longText + '"' AS ReallyLongString,

didn't.

What did work was declaring the variable with a specific length:

DECLARE @longText NVARCHAR(4000)

So, as the text in this case is never going to be longer than 2000 characters (the device reading the file can't cope with text that long) I convert all the text to 2000 character long strings:

SELECT
    'A' as First,
    Column1 as ABC,
    REPLACE(FORMAT(Column2, 'N2', 'en-GB'), ',', '') as Number, -- to get the format correct
    '"' + CAST(Column3 as NVARCHAR(2000)) + '"' as String,
    '"' + CAST(Column4 as NVARCHAR(2000)) + '"' as ReallyLongString,
    ....
FROM Table
WHERE <condition>

This is the how. I still don't really know the why.


Select distinct multiple columns with one result column

SELECT DISTINCT value FROM (
SELECT col_a as value
  FROM aschema.atable  WHERE col_a IS NOT NULL
UNION
SELECT col_b as value
  FROM aschema.atable  WHERE col_b IS NOT NULL
UNION
SELECT col_c as value
  FROM aschema.atable  WHERE col_c IS NOT NULL
)

The UNION there is UNION [DISTINCT]. You don't need to wrap it in SELECT DISTINCT. Though the previous pattern may be faster if it matters if you do UNION ALL, and wrap that in one SELECT DISTINCT FROM ()

SELECT col_a as value
  FROM aschema.atable  WHERE col_a IS NOT NULL
UNION
SELECT col_b as value
  FROM aschema.atable  WHERE col_b IS NOT NULL
UNION
SELECT col_c as value
  FROM aschema.atable  WHERE col_c IS NOT NULL

Why doesn't that work to do what you want?

If you ever find yourself writing a query like this though, I would think the problem would be in the schema itself.


A few things to check:

Have you restarted the MySQL instance after setting the server-id variable in my.cnf? (Obvious, I know, but I have to ask!)
In which section of the my.cnf file have you added this variable? The [mysqld] section would be an appropriate place for it, I think.
Do you have any other .cnf files that might overwrite your setting? Assuming Linux, a command such as mysqld --help --verbose | head -n 20 should give you some output which includes the files where mysqld is looking for default settings.


SQL Server 2008 R2 reindex job reorganise getting stuck

Resolved

The problem was we had a rogue cron job (unix job) related to our web servers that was using an incorrect login,

eventually it took up all 8 CPU’s so the reindex job was simply waiting for some CPU to become available before it could continue (this is why the job failed to timeout)


What are the differences between Mirroring and Failover Clustering and when is each one recommended over the other?

Mirroring is a technology that increases the high availability for user databases and creates a copy of the source db that is maintained in real time. This is done by delivering all the active transaction log records to the mirror server.

From MSDN:

"Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Database mirroring is supported in SQL Server Standard and Enterprise. "

Clustering is a technology that provides high availability for the entire SQL Server instance. This means that it's possible to have two servers with identical structure (OS, applications, services, service packs, patches...etc) and with shared storage (the data itself).

From MSDN:

"SQL Server failover clustering provides high-availability support for an entire SQL Server instance.  SQL Server failover clusters are built on top of Windows Server failover clusters. To create a SQL Server failover cluster, you need to first create the underlying Windows Server failover cluster."

Here's a course from the Microsoft Virtual Academy about High Availability as offered today by MS: Understanding Microsoft’s High-Availability Solutions.


How can I reduce the Tuple Mover's disk I/O impact for mergeouts?

What you need to do is make sense of what is your usual load size and set the size of your TM resource pool accordingly.
  Changing the  ActivePartitions to 2 will help you because will stop looking for older partitions to Consolidate.

Answer me this questions :

What is the size of your TM pool ?
How do you do your Trickle loads ? (they go to WOS or ROS)
How many partitions do you have in the table ?
How many projections do you have for that table ? (do you use them
all)
Do you run delete on this data ?(check for replay delete)
Your projection have the default sort ? (all columns or specific
columns ?)

What is the value of MaxMrgOutROSSizeMB and MoveOutSizePct,PurgeMergeoutPercent.

Better off can you post the definition of your TM ?


How to get back value passed to HASHBYTES()?

This is not impossible but you should consider that the original data is lost and you cannot easily get the original data back from the hash value.

Once you get the hash H1 for a value V1, you only work with the hash value.
Ie. knowing hash value H1 for value V1, you can match V1 with a second value V2 by computing H2 using the same hash algorithm. When V1 = V2 then H1 = H2. (see collision below)

From Hash function (Wikipedia):

  A hash function is any function that can be used to map data of arbitrary size to data of fixed size. The values returned by a hash function are called hash values, hash codes, hash sums, or simply hashes. One use is a data structure called a hash table, widely used in computer software for rapid data lookup. Hash functions accelerate table or database lookup by detecting duplicated records in a large file. An example is finding similar stretches in DNA sequences. They are also useful in cryptography. A cryptographic hash function allows one to easily verify that some input data maps to a given hash value, but if the input data is unknown, it is deliberately difficult to reconstruct it (or equivalent alternatives) by knowing the stored hash value. This is used for assuring integrity of transmitted data, and is the building block for HMACs, which provide message authentication.

In theory, it may be possible to revert some mathematical functions used to compute some hashes. It is also possible to use dictionary and brute force with some tools.
But in most cases, it would be too difficult or it would take too long to get them back.

There is also no guarantee that you will get the same value back. It may also get a value with the same hash because hash are fixed length values and input data is of infinite length. See Hash Collision (Wikipedia):

  a collision or clash is a situation that occurs when two distinct pieces of data have the same hash value, checksum, fingerprint, or cryptographic digest


mysql Relay log file not found error while changing mysql data directory

From the manual:

  An absolute path remains unchanged; in such a case, the index must be edited manually to enable the new path or paths to be used. Previous to MySQL 5.6.5, manual intervention was required whenever relocating the binary log or relay log files. (Bug #11745230, Bug #12133)

So if your binlog index was written with the full paths, you need to update it manually or using the mysqlbinlogmove utility.


Checkpoints are occurring too frequently during pg_restore

Not uncommon during a whole-DB restore because that's an exceptionally huge operation. If you see this during normal operation, consider raising your setting for checkpoint_segments permanently, just like the error message hints.

You might go to the trouble of setting checkpoint_segments higher just before the restore and then lower it again. This is even what the manual suggests (including an explanation):

  Temporarily increasing the checkpoint_segments configuration variable
  can also make large data loads faster. This is because loading a large
  amount of data into PostgreSQL will cause checkpoints to occur more
  often than the normal checkpoint frequency (specified by the
  checkpoint_timeout configuration variable). Whenever a checkpoint
  occurs, all dirty pages must be flushed to disk. By increasing
  checkpoint_segments temporarily during bulk data loads, the number of
  checkpoints that are required can be reduced.

Related answer with more details:

Configuring PostgreSQL for write performance

Postgres 9.5

The upcoming new release has a smarter approach. Quoting the beta release notes:

  Replace configuration parameter checkpoint_segments with min_wal_size
  and max_wal_size (Heikki Linnakangas)

  This allows the allocation of a large number of WAL files without
  keeping them if they are not needed. Thus the default for max_wal_size
  has been increased to 1GB.

Aside: the number of views is barely relevant, those do not contain any data, just the "recipe", i.e.: the query and some attributes of the view. For the question at hand, basically only the total size of the backup file matters.


Schedule replication of SQL Server views to MySQL remote database

You'll want to look at SQL Server Replication.  If you just want a one time nightly feed then you'll probably want Snapshot Replication.  If you want a more real time feed then you'll want to look at Transactional Replication.

Now replication doesn't support replicating the views, so you'll need to replicate the base tables, removing any columns which aren't needed by the website then create the views on the MySQL database.


What data is causing an NVARCHAR/DATETIME conversion error?

If this results from you passing data INTO SQL Server, log your parameters before you issue the query.

If this results from you getting data OUT of SQL Server, remove your cast/convert functions and look at the data. Odds are you're casting/converting a field that isn't guaranteed to be a date.


Search, filter and group across all columns in a tall and wide dataset quickly

Even if we generate a precomputed table with all of the columns in it (example: 80 columns, 300K rows), performing analysis on this is slow [...] Since we do not know ahead of time which fields would be queried on the most, we cannot put indexes on specific columns. On the other hand, (correct me) I don't believe it's best to place 80 indexes on a table.

From a SQL Server point of view, the most obviously applicable technology solution is to create a clustered or nonclustered columnstore index (on all columns) on the 'precomputed' table.

Nonclustered columnstore indexes are available from SQL Server 2012 onward, though the table is then read-only. Given the 'precomputed' word, I assume this would be acceptable. Updatable and clustered columnstore indexes were added in SQL Server 2014. Both require Enterprise Edition.

Assuming your table data types are compatible, and enough memory is available, a columnstore solution ought to provide you with very fast results for the unpredictable select, project, and group by queries you describe.

For further details, see the excellent SQL Server Columnstore Index FAQ and Columnstore Indexes Described on MSDN.


How to run sql agent jobs in a sequence order.with T-sql

Have the last step of job 1 be to fire job 2, use this code:

EXEC dbo.sp_start_job N'MyJob 2'

Then, the last step of job 2 be to fire job 3, so on and so forth.


Does Change Data Capture (CDC) work in a SQL Server 2012 AlwaysOn Failover Cluster Instances setup?

Refer to Replication, Change Tracking, Change Data Capture, and AlwaysOn Availability Groups (SQL Server)
From the reference :

Change Data Capture:
Databases enabled for change data capture (CDC) are able to leverage AlwaysOn Availability Groups in order to insure not only that the database remains available in the event of failure, but that changes to the database tables continue to be monitored and deposited in the CDC change tables.
The order in which CDC and AlwaysOn Availability Groups are configured is not important. CDC enabled databases can be added to AlwaysOn Availability Groups, and databases that are members of an AlwaysOn availability group can be enabled for CDC. In both cases, however, CDC configuration is always performed on the current or intended primary replica.
Harvesting Changes for Change Data Capture Without Replication
If CDC is enabled for a database, but replication is not, the capture process used to harvest changes from the log and deposit them in CDC change tables runs at the CDC host as its own SQL Agent job.
In order to resume the harvesting of changes after failover, the stored procedure sp_cdc_add_job must be run at the new primary to create the local capture job.


mysql - large table - index performance and query issue

ASPECT #1: Indexing
There is no need to add the PRIMARY KEY value id to the index. Why ?
According to the MySQL Documentation of Clustered and Secondary Indexes, Paragraphs 1,2 under the Subheading How Secondary Indexes Relate to the Clustered Index say the following:

All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.
If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.

Thus, the primary key is automatically added to a secondary index.
The proof of this is in the XML output
  "used_key_parts": [
    "loc_country",
    "job",
    "loc_city",
    "id"
  ],

and
  "used_columns": [
    "id",
    "loc_country",
    "job"
  ],

ASPECT #2: Table Compression
Compression might be greatly contributing to the slowness. After all, InnoDB must decompress data coming from that table for it to reside in the InnoDB Buffer Pool. In addition, the InnoDB Buffer Pool holds both compressed and uncompressed data and index pages. How so ???
According to the MySQL Documentation on How Compression Works for InnoDB Tables
, Paragraphs 1,2 under the Subheading Compression and the InnoDB Buffer Pool say the following:

In a compressed InnoDB table, every compressed page (whether 1K, 2K, 4K or 8K) corresponds to an uncompressed page of 16K bytes (or a smaller size if innodb_page_size is set). To access the data in a page, MySQL reads the compressed page from disk if it is not already in the buffer pool, then uncompresses the page to its original form. This section describes how InnoDB manages the buffer pool with respect to pages of compressed tables.
To minimize I/O and to reduce the need to uncompress a page, at times the buffer pool contains both the compressed and uncompressed form of a database page. To make room for other required database pages, MySQL can evict from the buffer pool an uncompressed page, while leaving the compressed page in memory. Or, if a page has not been accessed in a while, the compressed form of the page might be written to disk, to free space for other data. Thus, at any given time, the buffer pool might contain both the compressed and uncompressed forms of the page, or only the compressed form of the page, or neither.

SUGGESTION #1
Since the Buffer Pool holds both compressed and uncompressed pages, maybe increasing the InnoDB Buffer Pool size might slightly improve things. How ?
InnoDB has to evict unused pages from the Buffer Pool. A larger buffer pool reduces the number of times the Buffer Pool hasd to be cleared to make waves for new pages.
SUGGESTION #2
Perhaps changing the size of compressed pages might slightly improve things. This would require reloading the table with the new compression size. Please see my old post innodb_file_format Barracuda on changing the KEY_BLOCK_SIZE would be needed.
UPDATE 2020-12-12 19:52
Here is a crazy trick to try
Instead of your query
SELECT id FROM core
WHERE id!=518601449
AND loc_country='Mongolia'
AND id < 518601449 AND job='a'  LIMIT 151\G

Try refactoring as follows
SELECT id FROM (SELECT id FROM core WHERE loc_country='Mongolia' AND job='a') A
WHERE id < 518601449 LIMIT 151\G

This will force the loc_country_2 index to gather ids in the subqueries first. Then, all IDs < 518601449 are dismissed. Finally, the LIMIT 151 is imposed.
I do not guarantee the result will be better. It could be worse.
You won't know until you try it out and see or at least look at the EXPLAIN plan !!!


How to implement a low resource privileged user on Oracle 10g using Resource Manager?

This is fairly easy.

create resource consumer groups
create a resource manager plan using the created groups
somehow arrange that sessions are mapped to a resource consumer group
in the resource directives specify if a request should be estimated first or not
make sure users have switch privileges to the required resource consumer groups

Point three is a bit vague, there are many ways to have a session map itself to a resource consumer group. One is by user definition, an other is by client machine name, yet an other is by module ....

If estimates are in place and the estimates guesses that more resources could be needed than allowed, the action is greeted with an error message explaining so.

It does need some careful design and mutual agreements between the admins and the users. Oracle Resource Management is very powerful.

Check the docs. Managing Resources with Oracle Database Resource Manager


As far as I know there are no "nosql" databases that promise ACID transactions, so for banking purposes they are a non starter. Referential consistency support is not usually in their key feature sets either.

mySQL claims ACID transactions when using innodb tables, but I believe there are some caveats around that which may be show stoppers (any mix of other table types, including in-memory tables which are sometimes used for intermediate results in complex logic, will break ACID compliance for instance).

If you are looking at these options because they are free, then consider postgres which does offer ACID transactions, even including transactions that make schema changes. Please consider your support SLAs though: if this is a system that needs high availability (and the words "realtime" and "banking" suggest it is) then I recommend having a support contract in case something beyond your understanding or access to fix goes wrong. This will be rather expensive and once you are talking about that sort of money you might as well at least consider commercial database servers too.

One final point not relating to any particular database: make sure you have the right options selected for full ACID behaviour. A lot of systems default to settings that bend the isolation part, because guaranteeing complete isolation often requires serialisation which can significantly impact the performance of concurrent operations. Whatever DBMS you chose make sure that you understand its options for isolation/serialisation (like "SET TRANSACTION ISOLATION LEVEL" and related directives in MSSQL) and how they interact with each other.


How do I temporarily disable sp_send_dbmail?

USE msdb
GO
EXECUTE dbo.sysmail_stop_sp
GO

Will stop the server from sending mail.  EXECUTE dbo.sysmail_start_sp  will start up the service again.


Create relationship between two tables with composite primary key

A multiple-column foreign key constraint must be declared at the table level, not on each column:

CREATE TABLE dbo.t1
(
    id int NOT NULL,
    name nchar(10) NOT NULL,

    CONSTRAINT PK_dbo_t1_id_name
    PRIMARY KEY CLUSTERED (id,name)
);

CREATE TABLE dbo.t2
(
    id int NOT NULL,
    name nchar(10) NOT NULL,

    CONSTRAINT PK_dbo_t2_id_name
    PRIMARY KEY CLUSTERED (id,name),

    CONSTRAINT FK_dbo_t2_t1_id_name
    FOREIGN KEY (id, name)
    REFERENCES dbo.t1 (id, name)
);


Moving Partitioning Files To Another Disk Drive In SQL Server

To move a data file, if you don't have a maintain window, you should perform several steps:

Add a new file to the filegroup (Add Data or Log Files to a
   Database):

ALTER DATABASE [YourDatabaseName]
ADD FILE (
      NAME = N'NewFileLogicalName'
    , FILENAME = N'C:\Data\NewFileName.ndf'
    , SIZE = 8MB
    , FILEGROWTH = 128MB
) TO FILEGROUP [YourFilegroupName];

Empty the old file (DBCC SHRINKFILE):

DBCC SHRINKFILE (N'OldFileLogicalName' , EMPTYFILE);

Remove the old file (Delete Data or Log Files from a Database):

ALTER DATABASE [YourDatabaseName]
REMOVE FILE OldFileLogicalName;

If a logical name matters (ALTER DATABASE File and Filegroup Options):

ALTER DATABASE [YourDatabaseName]
MODIFY FILE (
    NAME = N'NewFileLogicalName'
    , NEWNAME = N'OldFileLogicalName'
);


MySQL provides the ability to set application-specific locking using the GET_LOCK() function. You can check if the mutex has been taken by using the IS_FREE_LOCK() function.

You can refer this thread for detail:
http://forums.mysql.com/read.php?22,45679,45679#msg-45679

Also,
Non-blocking queries are not supported by MySQL:

http://lists.mysql.com/mysql/219041


Amazon Aurora CREATE_TIME in INFORMATION_SCHEMA.TABLES is NULL

This was the response from AWS - they agree using the TABLE_COMMENT column is the way forward. I can also confirm we have successfully implemented this solution.

  Hello,

  Thank you for contacting AWS premium support. My name is Vidyadhar and
  I am glad to assist you on this case.

  From the case description, I understand that you noticed only null
  values in create_time column in information_schema.tables. You were
  removing old tables based on the values inside this column in MySQL.
  Now, you would like to know reason for not showing up creation time in
  Aurora and alternatives to achieve your requirement.

  Just to let you know, there is a known bug in MySQL related to the
  same where create_time will be null for partitioned columns. This has
  been fixed in MySQL 5.6.25 version. Please look into below link for
  reference.

  https://bugs.mysql.com/bug.php?id=69990

  In aurora this column will be NULL for all tables including
  partitioned tables. This has been already notified to the service
  team. Also, there is no workaround to this available in Aurora for
  now.

  It is best option to utilize the table comment session as a work
  around to note the time of table creation and proceed accordingly with
  table deletion. You can look into TABLE_COMMENT column in
  information_schema.tables. Please refer to below example which might
  be helpful.

MySQL [test]> create table S1(id int) comment='2017-09-21 15:53:51';
Query OK, 0 rows affected (0.02 sec)

MySQL [test]>  show create table S1\G
*************************** 1. row ***************************
       Table: S1 Create Table: CREATE TABLE `S1` (   `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1
COMMENT='2017-09-21 15:53:51' 1 row in set (0.00 sec)

MySQL [test]> select TABLE_SCHEMA,TABLE_NAME,TABLE_COMMENT from
information_schema.tables where table_schema='test' and
table_name='S1';
+--------------+------------+---------------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_COMMENT       |
+--------------+------------+---------------------+
| test         | S1         | 2017-09-21 15:53:51 |
+--------------+------------+---------------------+
1 row in set (0.01 sec)

  I truly apologize for the inconvenience caused. Aurora service team is
  working hard to add features and fixing bugs. As of now, I am unable
  to provide any ETA but definitely this will be fixed in future
  versions.

  I hope this information is helpful. Please feel free to reach me if
  you have any further questions or concerns.

  Have a great day!


Ensure you've enabled Mail Session in SQL Server Agent.

SQL Server Agent > Properties > Alert System: Check Enable mail profile.

This link should help:
Avoiding failed notified operator email errors


Help wanted: Special case of changing columns and rows MySQL

You could make it with CASE and UNION, but only if your Field column has fixed values:

SELECT
    T2.Field,
    T2.S,
    T2.N,
    T2.NA,
    T2.P
FROM (SELECT
            'C1' AS Field,
            SUM(CASE WHEN T.C1='S' THEN 1 ELSE 0 END) AS S,
            SUM(CASE WHEN T.C1='N' THEN 1 ELSE 0 END) AS N,
            SUM(CASE WHEN T.C1='NA' THEN 1 ELSE 0 END) AS NA,
            SUM(CASE WHEN T.C1='P' THEN 1 ELSE 0 END) AS P
        FROM Table1 AS T
        UNION
        SELECT
            'C2' AS Field,
            SUM(CASE WHEN T.C2='S' THEN 1 ELSE 0 END) AS S,
            SUM(CASE WHEN T.C2='N' THEN 1 ELSE 0 END) AS N,
            SUM(CASE WHEN T.C2='NA' THEN 1 ELSE 0 END) AS NA,
            SUM(CASE WHEN T.C2='P' THEN 1 ELSE 0 END) AS P
        FROM Table1 AS T
        UNION
        SELECT
            'C3' AS Field,
            SUM(CASE WHEN T.C3='S' THEN 1 ELSE 0 END) AS S,
            SUM(CASE WHEN T.C3='N' THEN 1 ELSE 0 END) AS N,
            SUM(CASE WHEN T.C3='NA' THEN 1 ELSE 0 END) AS NA,
            SUM(CASE WHEN T.C3='P' THEN 1 ELSE 0 END) AS P
        FROM Table1 AS T
        UNION
        SELECT
            'Cn' AS Field,
            SUM(CASE WHEN T.Cn='S' THEN 1 ELSE 0 END) AS S,
            SUM(CASE WHEN T.Cn='N' THEN 1 ELSE 0 END) AS N,
            SUM(CASE WHEN T.Cn='NA' THEN 1 ELSE 0 END) AS NA,
            SUM(CASE WHEN T.Cn='P' THEN 1 ELSE 0 END) AS P
        FROM Table1 AS T) AS T2;

Test:

mysql> SELECT  T2.Field,         T2.S,         T2.N,         T2.NA,         T2.P      FROM (SELECT 'C1' AS Field, SUM(CASE WHEN T.C1='S' THEN 1 ELSE 0 END) AS S, SUM(CASE WHEN T.C1='N' THEN 1 ELSE 0 END) AS N, SUM(CASE WHEN T.C1='NA' THEN 1 ELSE 0 END) AS NA, SUM(CASE WHEN T.C1='P' THEN 1 ELSE 0 END) AS P FROM Table1 AS T UNION SELECT 'C2' AS Field, SUM(CASE WHEN T.C2='S' THEN 1 ELSE 0 END) AS S, SUM(CASE WHEN T.C2='N' THEN 1 ELSE 0 END) AS N, SUM(CASE WHEN T.C2='NA' THEN 1 ELSE 0 END) AS NA, SUM(CASE WHEN T.C2='P' THEN 1 ELSE 0 END) AS P FROM Table1 AS T UNION SELECT 'C3' AS Field, SUM(CASE WHEN T.C3='S' THEN 1 ELSE 0 END) AS S, SUM(CASE WHEN T.C3='N' THEN 1 ELSE 0 END) AS N, SUM(CASE WHEN T.C3='NA' THEN 1 ELSE 0 END) AS NA, SUM(CASE WHEN T.C3='P' THEN 1 ELSE 0 END) AS P FROM Table1 AS T UNION SELECT 'Cn' AS Field, SUM(CASE WHEN T.Cn='S' THEN 1 ELSE 0 END) AS S, SUM(CASE WHEN T.Cn='N' THEN 1 ELSE 0 END) AS N, SUM(CASE WHEN T.Cn='NA' THEN 1 ELSE 0 END) AS NA, SUM(CASE WHEN T.Cn='P' THEN 1 ELSE 0 END) AS P FROM Table1 AS T) AS T2;
+-------+------+------+------+------+
| Field | S    | N    | NA   | P    |
+-------+------+------+------+------+
| C1    |    1 |    1 |    1 |    1 |
| C2    |    2 |    1 |    0 |    1 |
| C3    |    3 |    1 |    0 |    0 |
| Cn    |    0 |    3 |    1 |    0 |
+-------+------+------+------+------+
4 rows in set (0.00 sec)

mysql>


Synchronize creation of rows between tables

Let's say you have this:

CREATE TABLE posts
(
    id int not null auto_increment,
    title varchar(100),
    primary key (id)
) ENGINE=InnoDB;
CREATE TABLE posts_content
(
    post_id int not null,
    blobdata blob,
    primary key (post_id),
    CONSTRAINT `FK_posts` FOREIGN KEY (`post_id`) REFERENCES posts (`id`)
) ENGINE=InnoDB;

You could perhaps run the two INSERTs like this:

START TRANSACTION;
INSERT INTO posts (title) VALUES ('some-title');
SET @GivenPostID = LAST_INSERT_ID();
INSERT INTO posts_content (post_id,blobdata)
VALUES (@GivenPostID,'oxbdsixds');
COMMIT;

Give it a Try !!!


When you start mysql with --skip-grant-tables, you cannot use GRANT/REVOKE commands. You can INSERT directly into the mysql.user table.

Whereas this command cannot work using --skip-grant-tables:

GRANT CREATE, DROP ON *.* TO root@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;

This command should be run instead:

REPLACE INTO mysql.user SET
user='root',
host='localhost',
password=PASSWORD('123456'),
create_priv='Y',
drop_priv='Y',
grant_priv='Y';

and restart mysql.

You may also want to backup your grants from the previous mysql installation as SQL commands.


Why does InnoDB corrupt tables upon recovery?

innodb_fast_shutdown controls the change buffer, not the redo log. No matter what value of innodb_fast_shutdown is, if a transaction wasn't committed at the time of shutdown it won't survive the restart.

If a transaction was committed then whether it's going to persist depends on  innodb_flush_log_at_trx_commit. If it's the default 1 the transaction will persist. If != 1, may not persist.

Now, innodb_force_recovery. Here's what possible. When you run an UPDATE in a transaction, the new record is written to a page (in the buffer pool, the page becomes dirty). Later on, the data page can be flushed to disk even if the transaction isn't committed yet. When you have to use innodb_force_recovery that means you are ready to sacrifice data consistency (is it what you call a damaged table?) just to get the some data back. With innodb_force_recovery=6 InnoDB tries to salvage as much data as possible. It cannot complete the crash recovery process, but the new record is already written to the page - why not make it available to a user?


What are the limitations of Toad for SQL Server freeware?

The Toad site has documentation on the limitations of Toad Data Modeler but not for Toad for SQL Server or Toad for Oracle. I found a blog post "I love the new Toad freeware!" on their site as well claiming that the new freeware is no longer "crippleware" the way it used to be.

I've used the tool for a few days now, and there are lots of menu options disabled, so no, the product is nowhere close to fully functional.


You can import the old DTS packages into SQL Server 2008 using the DTS Migration Wizard. You can find it on the SQL 2008 Management Studio at the following folder: Server -> Management -> Legacy -> Data Transformation Services - Migration Wizard. Point to the current source of the packages and this should help you do your job.

You should be able to find more information here: Support for SQL 2000 DTS packages in SQL 2008 on MSDN.


OK, found it after sifting through the intellisense and trying everything that looked right
FOR DDL_SERVER_SECURITY_EVENTS AS


Since you are using 2012, you can use TRY_CONVERT as Lamak alluded to if you can't change the street number data type. Note, if you have something other an an INT in this column, then your results will not contain these rows

SELECT
   zone_number,
   min(try_convert(int,street_number)) as street_number
FROM
   A
GROUP by ZONE_NUMBER


Prevent Modification of dbo objects while allowing other schemas to be modified

We have full control of this server and have always allowed sysadmin
  rights to any developer.

The sysadmin privilege effectively short-circuits any other permissions. If a user is a member of the sysadmin fixed server role, then any DENY permissions will be ignored.

You can work-around this by revoking sysadmin and instead using the CONTROL SERVER permission. CONTROL SERVER is similar to the sysadmin fixed server role, except that CONTROL SERVER will obey DENY permissions.

GRANT CONTROL SERVER TO dev
EXEC sp_dropsrvrolemember 'dev', 'sysadmin'
DENY ALTER ON SCHEMA::dbo TO dev

If you want to completely undo the developers' need to have high-level access on the server (which is a pretty good idea), then you could take two approaches:

Grant wide access to the database, then explicitly deny permissions on dbo.

USE MyDB
GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE,ALTER ON DATABASE::MyDB TO dev
GRANT VIEW DEFINITION TO dev
DENY ALTER ON SCHEMA::dbo TO dev
EXEC sp_dropsrvrolemember 'dev', 'sysadmin'

Explicitly grant just the permissions the users require; do not grant ALTER permission in dbo.

USE MyDB
GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE,ALTER ON SCHEMA::custom TO dev
GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE ON SCHEMA::dbo TO dev
GRANT VIEW DEFINITION TO dev
EXEC sp_dropsrvrolemember 'dev', 'sysadmin'

Ultimately, you want to follow (or be close to) the principal of least privilege. But you also have to balance complexity and manageability.

I'd suggest you create a few test scenarios to test your permissions when running as dev, so that you can make sure the user has the right permissions. You can do this using EXECUTE AS LOGIN:

EXECUTE AS LOGIN='SomeDeveloper';
CREATE TABLE custom.AM2 (foo BIT);
GO
CREATE PROCEDURE custom.GetAM2 AS SELECT * FROM custom.AM2;
GO
DROP TABLE custom.AM2;
DROP PROCEDURE custom.GetAM2;
GO
REVERT;


How to create unique index for month and year column?

Assuming you have a column of type DATE or DATETIME2 (or DATETIME), you can define two computed, persisted columns for year and month and put an unique index on those:

ALTER TABLE dbo.YourTable
ADD DateMonth AS MONTH(YourDateColumn) PERSISTED

ALTER TABLE dbo.YourTable
ADD DateYear AS YEAR(YourDateColumn) PERSISTED

CREATE UNIQUE INDEX UIX_YourTable_MonthYear ON dbo.YourTable(DateMonth, DateYear)


Installing Oracle 19c server on Windows 7

Windows 7 is not a supported operating system for Oracle 19c.
The documentation states that it is only supported for these versions of Windows:

Windows 8.1 x64 - Pro and Enterprise editions
Windows 10 x64 - Pro, Enterprise, and Education editions
Windows Server 2012 R2 x64 - Standard, Datacenter, Essentials, and
Foundation editions
Windows Server 2016 x64 - Standard, Datacenter, and Essentials
editions
Windows Server 2019 x64 - Standard, Datacenter, and Essentials
editions

Even if you are able to get the listener functioning, you will likely recieve other errors with normal database usage that will just cause more headaches in the future which is why it is very strongly NOT RECOMMENDED to install Oracle on an unsupported operating system.


Optimal way to ignore duplicate inserts?

As the answers on the other question (of which this one is considered a duplicate) mention, there is (since version 9.5) a native UPSERT functionality.  For older versions, keep reading :)

I have set up a test for checking the options.  I'll include the code below, which can be run in psql on a linux/Unix box (simply because for the sake of clarity in the results, I piped the output of the setup commands to /dev/null - on a Windows box one could choose a log file instead).

I tried to make different results comparable by using more than one (that is, 100) INSERT per type, run from a loop inside a plpgsql stored procedure.  Additionally, before each run, the table is reset by truncating and reinserting the original data.

Checking a few test runs, it looks like that using the rule and explicitly adding WHERE NOT EXISTS ot the INSERT statement spend similar time, while cathcing an exception takes significantlly more time to complete.

The latter isn't that surprising:

  Tip: A block containing an EXCEPTION clause is significantly more
  expensive to enter and exit than a block without one. Therefore, don't
  use EXCEPTION without need.

Personally, because of readability and maintainability, I prefer adding the WHERE NOT EXISTS bit to the INSERTs themselves.  Just like with triggers (which could be tested here as well), debugging (or simply tracing INSERT behaviour) is more complicated with rules present.

And the code I used (feel free to point misconceptions or other problems):

\o /dev/null
\timing off

-- set up data
DROP TABLE IF EXISTS insert_test;

CREATE TABLE insert_test_base_data (
    id integer PRIMARY KEY,
    col1 double precision,
    col2 text
);

CREATE TABLE insert_test (
    id integer PRIMARY KEY,
    col1 double precision,
    col2 text
);

INSERT INTO insert_test_base_data
SELECT i, (SELECT random() AS r WHERE s.i = s.i)
FROM
    generate_series(2, 200, 2) s(i)
;

UPDATE insert_test_base_data
SET col2 = md5(col1::text)
;

INSERT INTO insert_test
SELECT *
FROM insert_test_base_data
;

-- function with exception block to be called later
CREATE OR REPLACE FUNCTION f_insert_test_insert(
    id integer,
    col1 double precision,
    col2 text
)
RETURNS void AS
$body$
BEGIN
    INSERT INTO insert_test
    VALUES ($1, $2, $3)
    ;
EXCEPTION
    WHEN unique_violation
    THEN NULL;
END;
$body$
LANGUAGE plpgsql;

-- function running plain SQL ... WHERE NOT EXISTS ...
CREATE OR REPLACE FUNCTION insert_test_where_not_exists()
RETURNS void AS
$body$
BEGIN
    FOR i IN 1 .. 100
    LOOP
        INSERT INTO insert_test
        SELECT i, rnd, md5(rnd::text)
        FROM (SELECT random() AS rnd) r
        WHERE NOT EXISTS (
            SELECT 1
            FROM insert_test
            WHERE id = i
        )
        ;
    END LOOP;
END;
$body$
LANGUAGE plpgsql;

-- call a function with exception block
CREATE OR REPLACE FUNCTION insert_test_function_with_exception_block()
RETURNS void AS
$body$
BEGIN
    FOR i IN 1 .. 100
    LOOP
        PERFORM f_insert_test_insert(i, rnd, md5(rnd::text))
        FROM (SELECT random() AS rnd) r
        ;
    END LOOP;
END;
$body$
LANGUAGE plpgsql;

-- leave checking existence to a rule
CREATE OR REPLACE FUNCTION insert_test_rule()
RETURNS void AS
$body$
BEGIN
    FOR i IN 1 .. 100
    LOOP
        INSERT INTO insert_test
        SELECT i, rnd, md5(rnd::text)
        FROM (SELECT random() AS rnd) r
        ;
    END LOOP;
END;
$body$
LANGUAGE plpgsql;

\o
\timing on

\echo
\echo 'check before INSERT'

SELECT insert_test_where_not_exists();

\echo

\o /dev/null

\timing off

TRUNCATE insert_test;

INSERT INTO insert_test
SELECT *
FROM insert_test_base_data
;

\timing on

\o

\echo 'catch unique-violation'

SELECT insert_test_function_with_exception_block();

\echo
\echo 'implementing a RULE'

\o /dev/null
\timing off

TRUNCATE insert_test;

INSERT INTO insert_test
SELECT *
FROM insert_test_base_data
;

CREATE OR REPLACE RULE db_table_ignore_duplicate_inserts AS
    ON INSERT TO insert_test
    WHERE EXISTS (
        SELECT 1
        FROM insert_test
        WHERE id = NEW.id
    )
    DO INSTEAD NOTHING;

\o
\timing on

SELECT insert_test_rule();


Is it possible to recover deleted tables?

According to Microsoft's Support website:

  To restore a database, you must already have a backup copy of your database.

This is true of essentially all data-loss recovery situations. You should consider your data utterly lost unless you have a backup from which you can restore.

Having said that, there may be options for you to recover, but every single action you take on the computer affected may make each recovery option less likely to succeed. Depending on how critical this data is, you may want to simply remove power from the computer now and image the disk drive to preserve possible forensic recovery. Other lower-impact but correspondingly-less-likely-to-succeed options may include duplicating the MDB file onto external storage.

This is only "Step 1". The next steps would involve analyzing the data you've preserved and trying to extract the data you need. With a disk image safely made, you may be able to boot the computer back up and check Volume Shadow Copy to see if there are any older revisions of the MDB file.

You need to weigh the time investment you're willing to make against the possibility that your data is simply gone. No recovery option is a guarantee and you could spend hours, days, or weeks on recovering the data that was deleted.

And having said all of that, not to needle, but having a backup routine could have avoided all of this.


A MERGE would be more efficient since you're only executing the query once.  Even better would be to do a set-based MERGE rather than doing a bunch of single-row MERGE statements which is what I'm assuming you'd be implementing given the alternative of doing a SELECT to see if the row exists.

Rather than doing a SELECT and then determining whether to do an INSERT or an UPDATE, you would be better off doing the UPDATE, checking to see if you updated 0 rows or not, and then doing the INSERT if your UPDATE didn't affect any rows.  That way, source rows that require an UPDATE would be a bit more efficient and rows that require an INSERT would be essentially just as fast as if you had done a SELECT and INSERT.  This approach is still going to be slower than a MERGE, but it's better than doing the extra SELECT.


Compile TSQL stored procedures into assembly

No, there is no way to do this. And there is not much purpose in doing so anyway as you can easily decompile Assemblies with tools such as ILSpy.

The two ways to protect your intellectual property are:

Proper license agreement. Consult with a lawyer to see what is best for you. There are many options and laws vary by state / region / country / etc so advice you get online might not be correct for you.
Innovation. Even if you protect the code from being seen, it can still be reversed engineered by observation / testing. Keep evolving and improving.
Yes, this is now more than 2, but this one isn't exactly protecting the intellectual property, BUT... Support: provide great support and that will help in some cases. Provide good, thorough documentation and fix things as quickly as possible when someone identifies a bug. This is harder to duplicate than code ;-).


Understanding BinLogDiskUsage Metric behavior

(Long-winded, but incomplete, answer)

The graph moves up and down rather smoothly.  This puzzled me.  With your latest numbers, I can explain some of what is going on.

Data is written to the binlog until it exceeds max_binlog_size (128M, in your case).  At that point, a new binlog file is created.

Slave(s) pull the data from the binlogs continually, and usually keep up.  There is, however, no feedback to let the Master know when all the Slaves are finished with a binlog file.  So, instead, there is some other mechanism for deleting old binlogs.

With expire_logs_days (3 in your case), binlog files older than that many days will be deleted.

Normally your 2GB (1447*1396626) would occupy about 16 binlog files (that /134217728).  And the graph would show distinct drops of 128MB every so often.

The only thing I can think of for leading to 1447 binlogs instead of 16 is spurious PURGE commands.  Please run these:

 SHOW GLOBAL STATUS LIKE '%purge%';
 SHOW GLOBAL STATUS LIKE 'Uptime';

The quotient will say how many purges per second.  Normally, this will be close to zero.  But your system seems to have several per hour.  So, I have converted the mysterious graph into "Why is PURGE being performed so often."

(See http://mysql.rjweb.org/doc.php/mysql_analysis#tuning if you would like deeper analysis of STATUS and VARIABLES.)

More

1447 binlogs with purge_logs_days_seconds = 500 (5 minutes).  This says there are usually 1 or 2 binlogs being purged each time.  The graph shows a weekly pattern.  One part of the week is quite busy -- adding a lot to the binlogs while purging 3-day-old files that have much less.  This makes the graph go up.  Ditto for down.  Flat says that you are adding at the same rate you are deleting.

I would guess there was a lull in traffic, or a national holiday, during the last week of May?

There are still contradictions in what little data you have provided.  1447 and purge after 3 days would say 3 minute interval, not 12/hour.  Or 5 days, not 3.  Also, max binlog size of 70MB (out of 128MB max) would imply that a purge cycle never purges more than 1 file.

The system has a rhythm; nothing seems terribly wrong.


Although replication may not be High Availability in design, it depends on your definition of HA.  Certainly it has been used for HA by many people.

If replication is down long enough it can be marked as Inactive.  To automatically reactivate a replication, you could try using Kin's response: SQL Server replication subscriptions marked as inactive

This runs a job that periodically checks the status of replication and reactivates it.


Order By with duration strings having time units

If (and it's a big if) the format remains constant with a number and a unit, you should be able to create a case statement that will get the total of the smallest unit. In your example, let's use days as the smallest unit:

CASE
    WHEN timeToMaturity LIKE '%Days' THEN cast(rtrim(timeToMaturity,'abcdefghijklmnopqrstuvwxyz')as INT) * 1
    WHEN timeToMaturity LIKE '%Weeks' THEN cast(rtrim(InterestRate.timeToMat,'abcdefghijklmnopqrstuvwxyz')as INT) * 7
    WHEN timeToMaturity LIKE '%Months' THEN cast(rtrim(timeToMaturity,'abcdefghijklmnopqrstuvwxyz')as INT) * 30
    WHEN timeToMaturity LIKE '%Years' THEN cast(rtrim(timeToMaturity,'abcdefghijklmnopqrstuvwxyz')as INT) * 365
        ELSE 0
    END AS TotalDaysToMaturity

Now, I realize that you may need to do something different when it comes to months as all months are not equal, but it may fit your needs. Perhaps someone would come by to figure that out dynamically.


Mongos version 3.0.1 Split Chunk Error with Sharding

According to what you found out yourself, I think I can provide a detailed explanation.

How chunk migration works (simplified)

Here is how chunk migration works (simplified for the sake of shortness):

When chunk exceeds the configured chunkSize (64MB by default), the mongos which caused the size increase will split the chunk.
That chunk is split on the shard the original chunk resided on.  Since the config servers do not form a replica set, it has to update all three config servers.
If the difference in the number of chunks reaches the chunk migration threshold, the cluster balancer will initiate the migration process.
The balancer will try to acquire a balancing lock. Note that because of this there can be only one chunk migration at any given point in time. (This will become important in the explanation what happened.)
In order to acquire the balancing lock, the balancer needs to update the config on all three config servers.
If the balancing lock is acquired, the data will be copied over to the shard with the least chunks.
After the data is successfully copied to the destination, the balancer updates the key range to shard mapping on all three config servers
As a last step, the balancer notifies the source shard to delete the migrated chunk.

What happened in your case

Your data was increasing to a point where chunks had to be split. At this point, all three of your config servers were available and the metadata was updated accordingly. Chunks splitting is a relatively cheap operation in comparison to chunk migration and may happen often, so under load, you usually have a lot more chunk splits than migrations. As said before, only one chunk migration can happen at any given point in time.

Due to whatever problems, one or more of your config servers became unreachable after the chunks were split, but before enough chunks were migrated to balance them out below the chunk migration threshold, since there can only be one running chunk migration at any given point in time. Bottom line: Some time before every chunk that needed to be migrated was actually migrated, one or more of your config servers became unavailable.

Now the balancer wanted to migrate a chunk, but could not reach all config servers to acquire the global migration lock. Hence the error message.

How to deal with such situation

It is very likely that your config server were out of sync. In order to deal with this situation, please read Adam Comerfords answer to mongodb config servers not in sync. Follow it to the letter.

How to prevent

Plain and simple: use MMS. It is free, gives a lot of information about health and performance and using the automation agent, administration of a MongoDB cluster can be done a lot faster.

I tend to suggest to install at least 3 monitoring agents, so you can have a scheduled downtime on one while maintaining redundancy with the others.

MMS has alerting capabilities, so you will be notified if one of your config servers becomes unavailable, which is a serious situation.


Copy a SQL Server 2000 database to another box without interrupting the backup chain

Taking a full backup will invalidate the subsequent differential backups, but it won't break the log chain. So you'd restore with your last full backup taken by the tool - and then all the subsequent log backups. Not ideal, but....

Can you take your full backup just before the tool takes its full backup?


postgres standby - what not WAL records can be replayed?

There are lots of transaction log records other than commits.

Transaction ID allocations (roughly like a BEGIN delayed until first actual write). Inserts, updates, deletes, vacuum, lots of internal operations.

Synchronous streaming replication  only waits for confirmation that the replica has replayed commit transaction log records. Not any of the others.

Some things, like the UNLOGGED tables you refer to, do not generate any transaction logs at all. So they cannot affect replicas. But that is not what that passage in the docs is talking about.


How to set up login/guest and checkout/add to cart

Why not have account_id always populated? When anyone who creates a shopping bag, they get an account_id auto assigned, but there's another field on the account table with a boolean is_guest flag set true by default. You could keep things like customer details in a separate table with a 1:1 map to the account table, for customers who do create accounts.

Your challenge then is that you might have repeat customers with different account_ids, but might be worth it depending on how likely customers will create accounts.


You need to create an external job for that.

Techniques for External Jobs

First, you need to create a credential, with the OS user and password in whose name the job will run:

begin
  dbms_scheduler.create_credential
  (
    credential_name => 'my_credential',
    username        => 'oracle',
    password        => 'Oracle123'
  );
end;
/

After that you can use this credential when creating an external job. For example calling your convert script with 2 arguments (input and output file)

begin
  dbms_scheduler.create_job
  (
    job_name             => 'convert_job',
    job_type             => 'executable',
    number_of_arguments  => 2,
    job_action           => '/usr/bin/bash/convert.sh',
    auto_drop            => true,
    credential_name      => 'my_credential'
  );

  dbms_scheduler.set_job_argument_value('convert_job', 1, '/home/oracle/file.in');
  dbms_scheduler.set_job_argument_value('convert_job', 2, '/home/oracle/file.out');

  dbms_scheduler.enable('convert_job');
end;
/


You can use the exists condition to check for the existence of a record through a subquery. This is often preferable to an anti join (a left join where we make sure the right table is null) as the engine stops as soon as it finds a record.

Sample code:

select
    category._id,
    category.cat_name
from
    category
where
    not exists(
        select
            *
        from
            deptcat
            inner join department on(
                department._id = deptcat.dept_id
            )
        where
            deptcat.cat_id = category._id
            and department._id = 1
    )


If you're looking for an algorithm:

Add 1 to previous number;
if (current number + 100000) % 1000000 = 0, add 100001 to current number.

So:

899998 + 1 = 899999; (100000 + 899999) % 1000000 = 999999; return 899999.
899999 + 1 = 900000; (100000 + 900000) % 1000000 = 0; 900000 + 100001 = 1000001; return 1000001
1000001 + 1 = 1000002; (100000 = 1000002) % 1000000 = 100002; return 1000002
...
1899998 + 1 = 1899999; (100000 + 1899999) % 1000000 = 999999; return 1899999
1899999 + 1 = 1900000; (100000 + 1900000) % 1000000 = 0; 1900000 + 100001 = 2000001; return 2000001
and so on.

One implementation:

CREATE TABLE myNumSeq (lastNum int); INSERT INTO myNumSeq (0);
Create a function, fn_GetNextNum(), that works something like this:

DECLARE @theNum int;
BEGIN TRANSACTION;
SELECT TOP 1 @theNum = lastNum + 1 FROM myNumSeq FOR UPDATE;
IF (@theNum + 100000) % 1000000 = 0
BEGIN
    SET @theNum = @theNum + 100001;
END;
UPDATE myNumSeq SET lastNum = @theNum;
COMMIT TRANSACTION;
RETURN @theNum;

Add error handling as appropriate. I haven't used Oracle regularly in about 18 years, so please consider the above pseudocode. I leave the Oracle-specific details (can't recall if it has both procedures and functions, or the return mechanism used for something like this if it only has procedures) to someone familiar with that.

I'm sure this could be written more concisely, but for single-record additions it should work fine. If you're adding records in bulk, using a variation on a number table would probably be quicker than a function.


Mariadb error 1071: Specified key was too long; max key length is 1000 bytes

Use ENGINE=InnoDB, not MyISAM.  Your attempts at changing settings were useless because you are not using InnoDB for the table in question.
That can be specified as the default in my.cnf before creating tables.  Else do it explicitly on CREATE TABLE.
Also, don't blindly use 255, instead use sensible limits.


You may need to follow the Network Access steps from the DB2 on Mac OS X install guide:

  Network access

  The instructions below assume that you installed DB2 with your current
  user account. If you manually created a different account such as
  "db2inst1", replace $(whoami) with db2inst1

  If you need to access this DB2 instance remotely, you should configure
  the service:

db2 update dbm cfg using svcename db2c_$(whoami)

sudo su -
echo "db2c_$(whoami) 50000/tcp" >> /etc/services
exit

db2set -all db2set DB2COMM=TCPIP
db2stop force
db2start

Alternately, instead of keeping the local DB2 Express-C install, you can run DB2 in a Docker container.


Finding changes for table-valued function definition on SQL Server

The answer is unfortunately no. Without some form of source control or a functional back-up to go to you are kind of out of luck. As soon as you push execute on that alter statement you have changed that table/function/view/StoredProc. For future precautions you should probably have nightly back-ups make sure only the appropriate people have the user permissions required to make database changes. Also you want to make sure you have source control and are following a proper procedure for testing and deployment to make sure this does not happen in the future.


Need to generate PK value programatically in multi-threaded application

You could try this technique will single thread the inserts into this table, but should guarantee uniqueness.  Your C# code would need to do the following in a transaction.  Select the current max Id and hold the lock so other users will wait.  Add +1 to the Id and insert that row.  Commit the transaction

BEGIN TRANSACTION
select @Id = max(Id) from TestIdentity WITH (TABLOCKX,HOLDLOCK)
set @Id += 1
insert into TestIdentity... new row...
COMMIT


Can I time out my scripted "FLUSH TABLES WITH READ LOCK;"?

There is no reliable way of interrupting FLUSH TABLES WITH READ LOCK. I disagree with the previous answer. You can try KILLing the FLUSH query all you want. Typically this will just hang till the original command completes.

However, some good news for you. You should be aware that FLUSH TABLES WITH READ LOCK is not strictly necessary in order to take LVM snapshot.

It is required if:

You are interested in point in time recovery (applying incremental restore via binlogs)
You have lots of non-transactional (ie MyISAM) tables being written to
You are running the snapshot against the master and want to create a slave based on the snapshot.

If your database is InnoDB oriented, and nothing interesting is hapenning with your MyISAM system tables (ie no one creating a procedure, GRANTing privileges etc.) then it's enough that you FLUSH only those tables.

All things InnoDB, you can just take the snapshot. There is no benefit in FLUSHing. InnoDB will make proper recovery anyhow.

The need for FLUSH TABLE comes from the requirement of getting the binary log file and position when you want to be able to rebuild a slave or otherwise use binary logs.


Can't list users - "select * from user" returns only one row with one column

Ok this is kind of a stupid question, but I'll leave it in case someone else makes a similarly dumb mistake.

For the record, I'm not the one who created a set of models including user instead of users.

Postgres has its own table user which it holds postgres users in. When you select * from user what you're getting is the result of the postgres user table. What you have to do instead is select * from "user" where user is in double quotes.


What format is this export of election data in?

I believe this is Political Disclosure Standard Electronic Reporting Format (PDSERF) a format used by [SDR Technologies] which later went off and was acquired by National
Information Consortium Technologies, Inc. in 2000

As for the file format, I found this about it

  This format utilizes EDI, the Electronic Data Interchange, standards developed and maintained by the Accredited Standards Committee X.12 of the American National Standards Institute."

You can tie this into Texas in this proposal by the Texas Ethics Commission which was later subsequently rewarded

  On January 11, 2000 I attended a public briefing in the Capitol Extension held by the Texas Ethics Commission and SDR Technologies, Inc. to discuss specifications for a standard file format for filing reports electronically with the Ethics Commission. This file format will be available to commercial vendors so they may generate campaign finance reports for filing electronically from their software. Additionally, the briefing demonstrated how filers will be able to convert data from spreadsheets such as Excel or Access for filing campaign finance reports electronically. On February 16, 2000 I attended another similar public briefing, which included a filer software demo.

You can also see them talk about it here,

  The document produced by SDR Technologies describing the filing format used for electronically submitting disclosure and registration documents to the Texas Ethics Commission (i.e., containing the technical specifications) is available upon request. It is located in the appraisal files of the Archives and Information Services Division of the Texas State Library and Archives Commission, and is titled "Texas PDSERF/Plus (.PDP) File Layouts."

I think the PDSERF/Plus is an SDR Technologies thing. They also seem to service the Federal Election Commission. You can see them pitch their product to the State of California here, and Michagan, and Louisiana also seem to use it (or have used it).


In my personal preference, I would advice you not to add company_id in item table, as this will reduce the purpose of user_company table and both the Id will be repeated in Item table, of no use.

For retrieving  item records from the company e.g. 'A'. Simple join can be used.


Rank students based on their average in MySQL

I can't test this right now, but you probably want something like this:

SELECT student_id, avg, (@rn := @rn + 1) AS rank
FROM
 (SELECT student_id, AVG(grade) as avg
  FROM grades
  GROUP BY student_id ORDER BY AVG(grade) DESC) agg
CROSS JOIN (SELECT @rn := 0) CONST
ORDER BY avg DESC


MySQL Create Index Semantics: Clustered vs. Nonclustered?

(This assumes you are using `ENGINE=InnoDB.)

Your table must have a primary key:  You have 3 choices:

(preferred)  You explicitly provide such.
There is a UNIQUE index with non-null column(s).  (Sloppy, just make it the PK)
(not a good option)  A hidden PK will be provided.

The PK will be clustered and unique.  That is the only choice for a pk in MySQL.

No other index can be clustered.  Again, that is by-definition.

There are 3 choices for structure of an index:

(the most common) B+Tree.  (Everything else in this answer is B+Tree)
FULLTEXT -- for searching for words in text
SPATIAL -- for 2-dimensional searches, such as geographic

  sales(item_id, time, price, location_id)

Perhaps you need PRIMARY KEY(item_id, time).  But that assumes you will never have two sales for the same item at exactly the same time.

So, it might be safer to have a 5th column:

id INT UNSIGNED AUTO_INCREMENT NOT NULL,
PRIMARY KEY(id)

Let's see the SELECTs; from them we can decide what secondary index(es) you need.  Or do it yourself here .

  My questions are: is this correct? If so how can I create a clustered index on a non-unique column in MySQL?

That is a trick that very few people have discovered.  Once you have something that is unique (such as the 5th column, above), do this instead of the PK above:

PRIMARY KEY(time, id),
INDEX(id)

Now, even it time is not unique, the PK is clustered and unique because of id tacked on.  AUTO_INCREMENT does not require more than "being the first column in some index".


In the practice of Kimball's Dimensional data warehouse design model, do lookup tables (not dimensions) contain a sid column?

If your natural key can only ever be associated with one description, and your lookup table only contains static reference data, then I see no need for a surrogate key.

In the example you gave, your drop down list would contain countries. From my perspective, if the dimensional model is based upon the star-schema then country would not become an independent dimension, but would instead be an attribute on another dimension (such as geography or customer etc). This would not require the surrogate key to be included even if you were to add one and therefore would be a waste of time, effort and storage.

If your dimensional model is based upon the snowflake or your key can be associated with more than one description (for example if the description can change over time) then yes I would include a surrogate key. I would not put this on the reference table though, the surrogate key should belong to the dimension that owns the data.


How can I use package classes and call packages from Oracle11gr2 stored procedure in certain schema?

Try granting directly execute to utl_file to user0:

grant execute on util_file to user0;

Then try to recompile the code.


RMAN-06054: media recovery requesting unknown archived log, Where is SCN kept?

Each redo log file (and archived redo log file) contains starting SCN and ending SCN. In case it is a last redo, the ending SCN is 0xffffffffffff.

nap01:~/oradata/jt10g$ strings redo01.log|head -3
z{|}
JT10G
Thread 0001, Seq# 0000000004, SCN 0x0000000b05b5-0x0000000bd34f

nap01:~/oradata/jt10g$ strings redo02.log|head -3
z{|}
JT10G
Thread 0001, Seq# 0000000005, SCN 0x0000000bd34f-0x0000000bf612

nap01:~/oradata/jt10g$ strings redo03.log|head -3
z{|}
JT10G
Thread 0001, Seq# 0000000006, SCN 0x0000000bf612-0xffffffffffff

Until database sees 0xffffffffffff it keeps asking for further logs. But it is not a problem, you can specify UNTIL SCN or UNTIL CANCEL for your recovery (this means you don't want a complete recovery, in other words you want to lose some recent data).


This is as you say a very simple query, so the optimal index here is very clear. Thanks for showing the whole table structure, indexes and full query, not all people do that.

You indexes are in the good direction, but you must know that (in general, there are other query plans like index_merge) a table scan can only use one index. In this case, as you are filtering by two columns, you have to add the multi-column index:

ALTER TABLE data_dylos ADD INDEX (node_id, added_on);

You have to create it on that order (node_id, added_on) and not in reverse (added_on, node_id), as that would be a different index. The difference is that in a BTREE index, after a range join type is used, the rest of the index is useless.

That is the optimal index query-wise. If you continue to have performance problems you may want to review your configuration (specially innodb_buffer_pool_size).

There are additional techniques that may help you, like caching or partitioning, but those are more advanced, or may require more structure changes, so try this first.


How SQL determine page exists in Buffer pool (internal)

How, in future, SQL determine if page, which Storage Engine asks, is already in memory?

To balance CPU and I/O throughput to achieve good performance and maximize hardware utilization, SQL Server includes 2 asynchronous I/O mechanisms - sequential read ahead and random prefetching

When SQL Server has to scan a large table, the storage engine will initiate the read ahead mechanism to ensure that pages are in memory and they are ready to scan before they are needed by the Query Optimizer.

If you are using Enterprise edition, then there is a mechanism called advanced scanning or merry-go-round scanning that will allow multiple tasks to share full table scans. This advance scanning avoids users to compete for buffer space and taxing other resources on the server.

  And which algorithm it uses to keep some free space for new pages?

SQL Server (and Sybase as well :-) ) uses LRU (Least Recently Used) algorithm to keep track of pages that are aged in the buffer pool. It increments a counter every time a page is referenced and decrements the counter every time the lazy writer process sweeps the page.

Any worker thread will check the memory status of Buffer Pool to make sure that there are healthy number of Free pages to honor incoming new requests.

Note that : SQL Server will always keep a minimum number of free pages on the free list, so that it can serve incoming requests without any significant delay. The amount of free space is calculated based on Buffer Pool and amount of incoming requests.

IF there are no free buffers or very little left then :

If Max Memory is reached then

the lazy writer process will sweep parts of buffer pool and based on when the page was last accessed - either leave it or flush it to disk and thereby put the page on the FREE list. A dirty page will not be flushed to disk until the corresponding log records have been written to disk.

If Max Memory is not reached then

SQL Server will commit more reserved pages into the buffer pool.

You can use DMV (in 2005 and up) - sys.dm_os_buffer_descriptors to find the pages in the buffer pool.

Also, there are Memory Manager surface area changes in SQL Server 2012.

KB 2663912 :

  Starting with SQL Server 2012, Multi-Page allocations and CLR allocations are also included in memory limits that are controlled by max server memory (MB) and min server memory (MB). This change provides a more accurate sizing ability for all memory requirements that go through the SQL Server memory manager. Carefully review your current max server memory (MB) and min server memory (MB) values after you upgrade to SQL Server 2012.

Highly recommend to read :

Inside the Storage Engine: What’s in the buffer pool? by Paul Randal
Performance issues from wasted buffer pool memory by Paul Randal
Sequential Read Ahead
Reading Pages


Allow self-referential foreign keys to be null from an alter-table

If there are no illegal values in C.ParentID, then I am not clear what the problem might be.  Is this Microsoft SQL Server?

I created a test case as follows:

Create Table dbo.Hier
(Id  INT IDENTITY Primary KEy,
 ParID INT NULL,
 Other varchar(10));
GO
 INSERT INTO dbo.Hier (ParID, Other) VALUES (NULL, 'Able');
 INSERT INTO dbo.Hier (ParID, Other) VALUES (1, 'Baker');
 INSERT INTO dbo.Hier (ParID, Other) VALUES (NULL, 'Charlie');
GO
ALTER TABLE dbo.Hier ADD
      CONSTRAINT FK_Parent
      FOREIGN    KEY (ParID)
      REFERENCES Hier(ID);
GO
INSERT INTO dbo.Hier (ParID, Other) VALUES (3, 'Delta');
SELECT * FROM dbo.Hier;
GO
DROP TABLE dbo.Hier;
GO

Seems to work fine. If you are on a different SQL Server it might have different behavior.  Or fluentMigrator may have a problem.


Full-text search migration 2005 -> 2012 (.ndf file?)

There are couple of things to consider :

backup and restore is the safest option that you can use for migration from SQL 2005 to 2012.

Refer here for more details of what you are experiencing.

Full text has undergone major chance in 2008 and up.

from BOL,

Attaching a SQL Server 2005 Database to SQL Server 2012

In SQL Server 2008 and later versions, a full-text catalog is a logical concept that refers to a group of full-text indexes. The full-text catalog is a virtual object that does not belong to any filegroup. However, when you attach a SQL Server 2005 database that contains full-text catalog files onto a SQL Server 2012 server instance, the catalog files are attached from their previous location along with the other database files, the same as in SQL Server 2005.

The state of each attached full-text catalog on SQL Server 2012 is the same as when the database was detached from SQL Server 2005. If any full-text index population was suspended by the detach operation, the population is resumed on SQL Server 2012, and the full-text index becomes available for full-text search.

If SQL Server 2012 cannot find a full-text catalog file or if the full-text file was moved during the attach operation without specifying a new location, the behavior depends on the selected full-text upgrade option. If the full-text upgrade option is Import or Rebuild, the attached full-text catalog is rebuilt. If the full-text upgrade option is Reset, the attached full-text catalog is reset.

EDIT:

You can attach the detached database along with the fulltext catalog using Create database ... FOR ATTACH.

-- Physically move the full text catalog to the new location.
--Attach the AdventureWorks database and specify the new location of the full-text catalog.
CREATE DATABASE AdventureWorks ON
    (FILENAME = 'D:\...\Data\AdventureWorks_Data.mdf'),
    (FILENAME = 'D:\...\log\AdventureWorks_log.ldf'),
    (FILENAME = 'D:\myFTCatalogs\AdvWksFtCat')
FOR ATTACH;
GO


What are the top 5 design decisions which need to be made when implementing a database?

As @SQLChicken states, a good data model is a very important start to have for stable, consistent, extensible and scalable databases.

After you have a logical model, the next step is implementation of the logical model in the target RDBMS. There are many important considerations and actions that are required to do this. For example, depending on the anticipated size of your database, you may decide that certain structures should be de-normalized for performance reasons but this decision should be made carefully, with the implications fully considered. Other considerations:

If you haven't done so already, decide on a naming standard/convention. Name your objects in a consistent way. Take time to define each object such as tables and columns in the database by adding a comment to each.
Define your table primary keys, foreign keys, data constraints, and indexes. Indexes can be added later, so don't worry about getting them all up front. The need for them tends to become apparent over time ;-)
Define views of data that may be useful for reports, ad-hoc queries, etc.
Minimize trigger use - these can be expedient, but have many pitfalls.

By no means is this list exhaustive. Become familiar with the (voluminous) Oracle documentation.


How to Publish Checked-In changes only in Team Foundation Server 2013 to SQL Server?

If you want to publish only checked in changes while you have uncommitted development, don't use your development workspace for publishing.  Instead, create a separate workspace for that purpose.  Perform a get latest in the publishing workspace so that it includes only the latest checked in objects and then publish from there.  A separate build box is often used for this purpose in larger development environments.

EDIT:

A new workspace for publishing can be created using TFS Source Control Explorer. Select "Workspaces..." from the Workspace drop down and click add.  In the working folder mapping dialog, provide a mnemonic workspace name, specify the same source control folder you use for development but specify a new empty local folder.  Click yes when prompted to get the latest from source control.  This new workspace will contain only the latest checked-in objects.

These development and publishing workspaces share the same source control path but are independent due to the different local folders and files.  Be mindful to make changes only in the development workspace (and get latest to see other team members checked-in changes) and use only get latest to update the deployment workspace.


Are autovacuum VACUUMs slower than a manual VACUUM?

"Sort-of".

Automatic VACUUM may run at a lower intensity than manual vacuum, so it pauses longer between each batch. autovacuum_vacuum_cost_delay may be set differently to vacuum_cost_delay; by default there is no delay on manual vacuum, only on autovacuum.

Autovacuum also works harder to avoid blocking concurrent queries and will abort its work if another query tries to take a lock that would conflict with vacuum. Depending on the vacuum phase this may not actually slow it down much, or it might throw away substantial work. The first phase heap sweep marking dead tuples and setting the visibility map doesn't care much if it's aborted part-way, though it might cause a subsequent vacuum to repeat some of the reads. It's much more expensive when we're updating indexes, but that's done in batches to reduce the amount of work lost if a vacuum is canceled.

See autovacuum configuration for the relevant parameters, most notably autovacuum_vacuum_cost_delay. Note that this differs from the vacuum_cost_delay setting used by manual vacuum.


I don't seem to have a script like this in my toolbox, but found some others in my search.

Have you already seen this script?

http://www.sqlservercentral.com/scripts/Indexing/31652/

It uses a cursor, which is probably not strictly necessary.  But I typically can clean them up to avoid a cursor by using the FOR XML and potentially a quirky update to concatenate the strings.

Also found these:

http://www.sqlservercentral.com/Forums/Topic796512-391-1.aspx

http://www.sqlservercentral.com/Forums/Topic401784-562-2.aspx


resolved by upgrading the Data Access Tool (v4) to Data Access Tool (v5)


How am I supposed to update PostgreSQL on Windows, once installed, without manually installing new installers?

You don't want your system to magically upgrade the database software, because that is always downtime (typically short, but still).

You have to take the time to plan and schedule it. It's not all that complicated as you present it.

Also, as detailed in the answer to this question, there are sometimes additional tasks to do that require reading the release notes. These tasks can be automated, but you still have to plan for each minor release.


Impact of Quotes in T-SQL for Column Name

Yes, the first variant will give you the data length of the text 'ColumnName' whereas the second will most likely get you nearer the results you are looking for (which I assume is the total length of all data within the given column).

In SQL Server if you are looking for quoted identifiers you can use the standard " or square brackets. You only need to quote names that are reserved keywords like TABLE, INDEX etc... but as a general code-style tip I recommend picking a quoting scheme and using it consistently within your code. It makes it easier for other developers to read and pick up your scripts.

SQL Server: SET QUOTED_IDENTIFIER


manual transfer fails with table does not exist

You cannot move an InnoDB table in that manner.  The problem is that there is info in ibdata1, even if the table has its own "tablespace" (.ibd).

With 5.6, and if you created the table as "file per table", you can use "transportable tablespaces".  See online docs for details.


String replace using concatenated strings from various columns

Depending on how you would want to deal with possible NULL values, concat_ws() is probably your safest and simplest way to go:

UPDATE tbl
SET filed1 = replace(field1, concat_ws(' ', field2, field3, field4), '')
WHERE filed1 IS DISTINCT FROM replace(field1, concat_ws(' ', field2, field3, field4), '')

concat_ws() ignores NULL values. With plain concatenation (||), one NULL field would make the whole pattern NULL. It was introduced with Postgres 9.1.

The added WHERE clause prevents empty updates. This enhances performance a lot if many rows wouldn't change anyway.


COUNT(condition) counts all rows in the group where condition is not null.

COUNT(*) counts all rows in the group.

COUNT(condition) / COUNT(*) * 100 returns the ratio of the above in percent.


Insert row wise different images in SSRS

Use http:// and join your strings using & not +


How to change only time while keeping the date same in timestamp type

Add the desired INTERVAL

select date + interval '5 hour'

Have a look at: Date/Time Functions and Operators on Postgres documentation.

select a.MyDate + interval '5 hour' as Result_Date
from (select '2017-07-01 00:00:00'::date myDate) a

|result_date        |
|:------------------|
|2017-07-01 05:00:00|

dbfiddle here


MySQL error 1593 on replication between 5.5 on WIndows and 5.6 on CentOS 7

If both the servers showing same server-id, you can set this to some other value and check again.

On Master:

server-id = 1

On Slave:

server-id = 2

Make sure that, while giving change master settings, give the host name as ip address of the master server. This should work, check again and see if the same issue exists. I hope this will help you.


why is my Database Engine Tuning Advisor trying to create statistics, even when I'm using "auto create/update statistics"

Auto Create Statistics will not create multi-column statistics, only single-column. Indexes will create multi-column based on the index automatically, though.

So DTA is simply recommending statistics that don't exist as indexes already.

Most people don't recommend DTA anymore. I don't think it has been maintained for a few years now.  Lots of other tools like sp_blitz will recommend indexes a lot more intelligently.


Connecting a standalone Microsoft R Server to a SQL Server with R Services and a SQL Server without R Services

I can answer some of these questions hopefully:

The standalone machine has the benefit of most of the machine resources at its disposal.  However as far as benefits "while connecting" go, there are none. The point of SQL Server with R services is to keep the analytics close to the data.  This has little or no consequence for external clients connecting to the data, as I see it at least
you can invoke SQL Server stored procedures from R using the RODBC library, for example this worked for me:

install.packages("RODBC")
library(RODBC)
dbhandle <- odbcDriverConnect('driver={SQL Server};server=.\\sql2014;database=tempdb;trusted_connection=true')
data <-sqlQuery(dbhandle, "EXEC sp_who2")
data
odbcCloseAll()

Results:

push compute is a feature of the ScaleR library, eg function RxInSqlServer.  See here for more details.
regarding "anything special that SQL Server with R Services offers for a standalone Microsoft R Server...", you could conceivably call the R stored procedure sp_execute_external_script from R using RODBC, but this seems a bit pointless.  A dedicated SQL Server box (ie without R Services enabled) does not have to share its resources, and this kind of answers your last question I think; the point of having a dedicated box of either kind (MRS only or SQL Server only) would be so that neither has to share its resources.  The point of having a SQL Server with R Services enabled would be so the box could perform dual roles, resources permitting, plus keeping the analytics close to the data.  I hope that makes sense.

Here's a nice article on persisting machine learning models to a table using SQL Server with R Services.  How would you do that in standalone R?

Maybe someone else can chime in because honestly this seems very similar to your other questions.  You might guess not that many people are doing this so one of the best people to do this is you; try these things out, report back : )


For a "cross-server" query you could set up a Central Management Server and add Registered Servers.


Joining Products and Orders so that every product is listed along with the quantity a specific Buyer has ordered

In general, it's best to leave the specifics of how the data is joined together to the DB engine - usually, there is an optimizer that looks at table/index statistics, and figures out the most efficient way to eliminate rows from consideration. If you're having issues with performance on a specific query, you can sometimes force the engine to use a particular method/option on a given query - but this can lead to good performance in specific cases and bad performance in everything else.

That said, the logic of your query is such that rows from your "right" table should be excluded from consideration if they don't belong to the targeted buyer/order, so they do need to be included as a part of the LEFT JOIN clause. I would rework that as follows:

  FROM products p
         LEFT JOIN orders o ON (    p.product_id = o.product_id
                                AND o.buyer_name = 'Joe'
                                AND o.order_no = 123
                               )

If you simply join the two tables, the DB engine will figure out what columns it needs out of those tables from the rest of the query. I can't think of a good reason to use a sub-query that just selects specific columns from a table in a join, rather than using the table itself directly. I won't say there isn't such a reason, just that I have never encountered one.

Some versions of SQL may materialize a subquery - basically, running the subquery before the main query is evaluated, then evaluating the main query from those results. If you do choose to use a subquery, I'd recommend not using SELECT *, but rather specifying just the actual columns you need in the main query (SELECT product_id, order_qty from orders...), to avoid pulling in data you aren't actually going to use.

That same advice holds for the main query; rather that using SELECT *, specify the actual columns you need. As noted before, the DB engine will figure out what columns it needs from each table by looking at the main query's SELECT list, the WHERE clause, the JOIN criteria, and so forth. If you specify more columns in the SELECT list than you really need, then the engine will give them to you. If each row of data in your table has two varchar fields that average 250 characters each, but all you really need is a varchar(25) field and three integers, you'll be returning a result set that's around 10 times as large as it needs to be (which will take about ten times as much time to reach the machine that made the request).

Limiting your columns to those you actually need also will allowing the DB engine's optimizer to apply certain shortcuts that can improve performance. For example, if your table's rows are (on average) 1000 bytes wide, but you have an index that includes just the columns you need, and those columns only average 100 bytes per row, the optimizer can use that index as if it were the table, and thus may be able to reduce the number of database pages it has to read to get your data by a factor of 10. the term for this is a covering index.

It's also a good idea to assign column names to computed columns - it makes it easier to refer to them elsewhere.

So, we'd end up with the following queries (note that I'm assuming you only will be using a column named product_name in your results; if other columns from products apply, add them to the SELECT list (and to the ORDER BY clause):

For a specific order:

SELECT p.product_name
      ,o.buyer_name
      ,COALESCE(o.quantity,0) as Qty_Ordered
  FROM products p
         LEFT JOIN orders o ON (    p.product_id = o.product_id
                                AND o.buyer_name = 'Joe'
                                AND o.order_no = 123
                               )

For a buyer's total activity:

SELECT p.product_name
      ,o.buyer_name
      ,COALESCE(SUM(o.quantity),0) as Qty_Ordered
  FROM products p
         LEFT JOIN orders o ON (    p.product_id = o.product_id
                                AND o.buyer_name = 'Joe'
                               )
 GROUP BY p.product_name, o.buyer_name

A few final notes:

In some SQL variants, every column that is not in an aggregate function in the SELECT list must appear in the GROUP BY clause. Other versions allow you to leave columns out - the value from a "random" row will be returned. If you've got a unique value from a table in the GROUP BY, it would be safe to leave other columns from that table out if using one of these other versions, as all possible values from the rows being aggregated would have to be the same.

Also, your query makes an assumption: any order will only have one line item for a given product. Now, that seems like a reasonable assumption on the surface; however, I have seen systems where someone might place an order for 10 widgets on the 1st of the month, and an additional 10 on the 15th. If there's any chance that a product might show up twice in the same order, you can use the second query to pull the order-specific information as well; you just need to put AND o.order_no = 123 back in, like in the first query. This would probably take slightly longer than the query without the SUM and the ORDER BY - but I would expect, in most cases, the difference would be too small for a human being to tell.


Trigger to prevent column value from A to B, and from B to A

Based on what you want to accomplish logically, shouldn't:
IF OLD.status = 'Cancelled' THEN NEW.status != 'Accomplished';
END IF;
IF OLD.status = 'Accomplished' THEN NEW.status != 'Cancelled';
END IF;

actually say something like this?
IF OLD.status = 'Cancelled' AND NEW.status = 'Accomplished' THEN RETURN OLD; -- Set A = A when trying to set it to B
END IF;
IF OLD.status = 'Accomplished' AND NEW.status = 'Cancelled' THEN RETURN OLD; -- Set B = B when trying to set it to A
END IF;

My syntax might be slightly off for PostgreSQL, but my point is isn't this the kind of logical expression you're looking for? (You shouldn't be doing a boolean comparison in the THEN clause, which I think is why it's mad about the !=.)


Restoring differential backup from SQL Server 2005 to SQL Server 2012

There should be no issue restoring differentials and fulls from SQL 2005 to SQL 2012.  I would validate that your backup files are compatible.  To do this, you'll want to use the RESTORE HEADERONLY command and compare the full backup's FirstLSN value with the differential's DifferentialBaseLSN:

restore headeronly
from disk='X:\BackupFiles\foo.bak'

If these values do not match, then you will need to take an appropriate full backup.


Sql server - The provided statistics stream is corrupt

I am doing data archiving on some tables in my database.
  ...

  Then transfer records i want from original table to the new table.

  ...

  After that, I would add back those
  constraints,triggers,indexes,statistics associated with table A back
  into it. That's where some stats I tried to add, it would throw me the
  error, only happens with some stats i am trying to create.

All this is fine except for transfering old statistics.

Statistics are used to build optimal execution plan, they reflect data distribution in your columns.

When you'll query your new table, all the missing statistics will be recreated unless you disabled AUTO_CREATE_STATISTICS (the option is ON by default).

If you set AUTO_CREATE_STATISTICS to OFF and want to recreate all the statistics that existed on the old table you should just recreate them, all that you need for this is to know on what columns old statistics were created.

This way you'll get fresh statistics on your new table.

The undocumented option that you was trying to use serve another purpose:
it's used with update statistics to recreate the same statistics in another environment. Here "the same" means not to use the same column to create statistics, but set another, not actual statistics, statistics taken from another table with another data distribution, on your table.

I mean if your original table had 1000 rows and half of them had col1 = 0 and another 500 rows had col1 = 1, you could create the same table on another server, put there only one row, and transfer data distribution as 500 rows having 0 + 5000 rows having 1. And this is not what you want. You want real data distribution on your new table, so just create the statistics on col1 that will reflect what is now in this table without using WITH STATS_STREAM.


Can I use SQL Server Authentication for Central Management Servers?

CMS Drawback: Windows Authentication Only, And Only Your Login

Central Management Servers and subordinate registered servers can be registered only by using Windows Authentication.

See:

Create a New Registered Server (SQL Server Management Studio) in the documentation
Central Management Server (CMS) – A Hidden Gem by Dan Taylor.


Insert trigger checking for specific column

I've tried to re-create what you're doing on my local instance, here's what I've got:-

I created two tables, one for the data to be inserted to and one for the trigger to insert to:-

CREATE TABLE dbo.[TriggerTest]
(Source         CHAR(1),
 TestColSource  CHAR(1));
GO

CREATE TABLE dbo.[InsertTest]
(TestColDest CHAR(1));
GO

I then created this trigger (as @Aaron Bertrand says, removing the variable assignment):-

CREATE TRIGGER TRG_TriggerTest
ON dbo.[TriggerTest]
FOR INSERT
AS
BEGIN
    INSERT INTO dbo.[InsertTest]
        (TestColDest)
    SELECT i.TestColSource
    FROM inserted i
    WHERE i.SOURCE <> 'b'
END

I then inserted some data into the initial table:-

INSERT INTO dbo.[TriggerTest]
SELECT 'a', '1'
UNION
SELECT 'b', '2'
UNION
SELECT 'c', '3'

The trigger should only insert the 1 & 3 values from the above statement, but this can be checked by:-

SELECT TestColDest FROM dbo.[InsertTest]

Hopefully that should give you a basis for your trigger. I'm sure the guys on here will correct me if I've got something wrong.


Migrate large postgresql database (windows -> linux)

With the directory format (-F d) of pg_dump, you can parallelize dump and restore, which will help if there are several large tables.


The query must match EXACTLY, including whitespace. I suggest you get the query from the cache, and create it from that rather than entering it any other way.


Grant read access to my SQL Server database to all future SQL users

To add on my comment, you can create a trigger on a database level and add user to desired role automatically when you create a user. You can add a custom one, but for example sake i added db_datareader.

Example :

    create trigger tg_AddUserToRole on database after CREATE_USER
    as
    declare @LoginName nvarchar(30)
    set @LoginName =  (select EVENTDATA().value('(EVENT_INSTANCE/ObjectName)[1]','nvarchar(30)'))
    IF EXISTS(select 1 from sys.sysusers where name = @LoginName and  issqluser = 1)
    exec sp_addrolemember 'db_datareader', @LoginName