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

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

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

What events and columns are analyzed by SQL Server Upgrade Advisor from a SQL Trace .trc file?

  Went to a customer this week to assist them with the risk assessment and work planning to accomplish the upgrade of several business applications’ databases. Question was, what events and what columns should a .trc trace file include that will be analyzed by the Upgrade Advisor? Couldn’t find the answer to that question documented,…

0

RESTORE VERIFYONLY: How does it check for available space on destination devices?

  “How does RESTORE VERIFYONLY checks for space on destination drives when a drive isn’t specified as part of the statement?” was the question being asked this time. And this was my answer to that one: This is what SQL does: In order to complete the space check, it iterates over all data files, transaction…

0

When doing a database restore, what actions is the engine taking after it gets past the 100%?

This was a question my admired Greg Low posted on the SQL Server MCMs discussion group a few days back, while he was restoring a 2TB database and noticed that the percent_complete column from sys.dm_exec_requests for the session from which the RESTORE statement was running, reached 100% and thirty minutes later, the restore hadn’t finished…

1