Planning to set up an Always On Availability Group configuration to move data data from a 2012 primary replica into a 2014 “readable” secondary replica? Think it twice!

I had a customer who wanted to use AOAG, not as a HA/DR solution but as one to replicate data to another instance to which they planned to offload all read-only workload. The instance hosting the secondary replica of the database was expected to run a new set of business processes which consumed data from…

0

How is @@DBTS expected to behave on an Always On Availability Group secondary replica, a Database Mirroring mirror, or a database which is being created out of a restored backup and is in standby or in-recovery state?

A coworker recently posted the following to an internal forum: “We are redirecting read load of our SSIS Packages from Primary to Secondary, and our delta logic is dependent of @@dbts values. On primary, the value of @@dbts always changes. But the same on secondary doesn’t change. Any experience around this.” And this is what…

0

SQL Server 2005 upgrade to SQL Server 2014 and compatibility levels

A database currently attached to an instance of SQL Server 2005 could be either backed up (or detached), and later restored (or attached) on top of an instance of SQL Server 2014, and SQL Server 2014 won’t complain as it typically did in the past when you tried to restore (or attach) databases across database…

2

How to identify whether a database has participated in a cross-database transaction or in a distributed transaction

When customers evaluate using Database Mirroring or AlwaysOn Availability Groups as a HA/DR solution to protect one or more of their databases, there comes a point where they come to know there exists one documented limitation with the use of those technologies because of which it is not supported to protect databases that participate in…

1

Why is everyone classified in the internal workgroup?

A PFE colleague recently posted a question describing what seemed like a weird and unexpected situation he observed while he was onsite in one customer. From SQL Server Management Studio, every single entry in the Processes section of the Activity Monitor showed as being classified as the “internal” in the “Workload Group” column, just like…

0

sys.dm_exec_requests showing negative values for total_elapsed_time, wait_time, or any other column it exposes as an integer (int) data type

In the case of that DMV (and probably in many other) the problem is that the internal value which populates the column (total_elapsed_time, or open_transaction_count, or open_resultset_count for that matter) is returned as a ULONG (32bit unsigned integer covering a range of values spanning 0 to 4294967295 decimal.) However, the GetRow method of the internal…

3

Microsoft SQL Server 2008 R2 Best Practice Analyzer fails with “Engine – Login does not exist or is not a member of the System Administrator role”

If the Windows account you are using for SQL BPA to connect and analyze an instance of SQL Server Database Engine doesn’t have an individual login created in the instance and that login is a member of the System Administrator server role, you will get that error, even if the login has been made a…

0

Do the 10GB DB size limit in SQL Server Express SKUs also apply to TempDB?

No, it doesn’t. The CheckSize function from the file manager class, which is called when a file is grown or shrinked skips checking for the limits imposed by the SKU when the file correspond to the master, tempdb, or model system databases. For further information about the “Maximum Relational Database Size” limit imposed by every…

0

Got a read only database and the contents of sys.dm_db_index_usage_stats.last_user_update contains today’s date. How’s that possible?

Any attempt to update the index (via any DML statement) updates that index usage statistics as part of the query scan setup/initialization, even before the iterator’s GetRow method is invoked (i.e. before the iterator attempts to actually insert, delete or update a single row.) In the case of a R/O database, it could happen that…

0

Standard edition of SQL Server supports only a 2 node cluster, but will it support multi-subnet clustering or do we need Enterprise edition?

Even though the Features Supported by the Editions of SQL Server 2014 doesn’t have any indication on which SKUs the multi-subnet FCIs improvement is available, after having confirmed with the program manager who owned the stretch cluster improvement back in 2008, the answer to the question is: multi-subnet failover clustered instances is available and supported…

0