Lessons learnt while using the Cloud Adapter

During the last week of August, I had blogged about how to get your on-premise database to your SQL Server instance running on an Azure virtual machine. I had run into a few issues while trying to run the wizard provided by Management Studio.

The First Stumble

This error is easy to circumvent and pretty much mentioned in the online documentation. The error message would read as:

Failed to locate a SQL Server of version 12.0.2000 or later installed on the remote machine. Please verify that a SQL Server of the same or higher version than the source SQL Server is installed on the remote machine.

The above error is self-explanatory. There is a requirement that the source database engine version be lower or equal to the version of the SQL Server instance running on Azure. Eg. You cannot deploy a database from a SQL Server 2014 instance to a SQL Server 2012 instance running on an Azure VM.

The Second Stumble

The second common error that you might run into is:

The Cloud Adapter port configuration is not valid. Verify the virtual machine endpoint configurations.

The above error will be encountered when the endpoint is not configured for the Azure virtual to accept connections from the outer realm! This can be easily rectified by adding a TCP endpoint to your Azure virtual machine for 11435 which is the port that the SQL Server Cloud Adapter Service is listening on. This is also mentioned in the online documentation. Once you have created the endpoint for your Azure virtual for your on-premise server to connect with the Cloud Adapter service, your endpoint configuration should look like the one in the screenshot below:

image

The Third Stumble

The next issue could be with permissions/authentication or it might not be as easy as it seems.

Cloud Adapter operation failed due to invalid authentication. Verify the virtual machine name, user name, and password.

So the first thing to check if you have the correct account name and password. If it is due to an authentication error, then the application event log of the Azure Virtual Machine will show the following error with the source as SQL Server Cloud Adapter service as shown in Screenshot 2. The text of the error message is mentioned below.

Access denied for user <user name>

image

The other error that you might encounter is when the SQL Server Cloud Adapter service tries to enumerate the database engines installed on the virtual machine. The error would still be talking about the authentication which is reported by the management studio wizard but a little investigation into the application event logs of the virtual machine will show the following error:

[Error] <ip address> Exception in GetSqlInstances(): SQL Server WMI provider is not available on <machine name>.. Stack trace:    at Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer.TryConnect()
   at Microsoft.SqlServer.Management.Smo.Wmi.WmiSmoObject.get_Proxy()
at Microsoft.SqlServer.Management.Smo.Wmi.WmiSmoObject.EnumChildren(String childTypeName, WmiCollectionBase coll)
at Microsoft.SqlServer.Management.Smo.Wmi.ServerInstanceCollection.InitializeChildCollection()
at Microsoft.SqlServer.Management.CloudAdapter.Tasks.GetSqlInstances()
at Microsoft.SqlServer.Management.CloudAdapter.CloudAdapter.GetSqlInstances(String username, String password). Inner Exception: Invalid namespace .

The above error clearly states that the GetSqlInstances() method failed. Microsoft.SqlServer.Management.Smo.Wmi namespace contains classes that provide programmatic access to the Windows Management Instrumentation (WMI) from an SMO application. I had talked about needing the shared management objects in an earlier post. The SQL Server 2014 WMI provider is also required which is available by installing the client connectivity components from any SQL Server 2014 setup including SQL Server Express. The components that I had installed were:

a. Client Tools Connectivity

b. Client Tools Backwards Compatibility

If you are not sure if you have the WMI provider, then look for the file “C:\Program Files (x86)\Microsoft SQL Server\120\Shared\sqlmgmproviderxpsp2up.mof“. This is the SQL Server 2014 MOF file. Another way to test if the WMI provider is working without running the wizard every time and have it fail is to run the PowerShell commands below on your Azure Virtual Machine. This script will tell you where the instance enumeration being performed by the deployment wizard will work or fail.

[System.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")
$m = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') '.'
foreach ($svi in $m.ServerInstances)
{
	$svi.Name;
}

This post was intended to document that common issues that you might run into while deploying a database from an on-premise SQL Server instance to a SQL Server instance running on an Azure Virtual Machine.

Installing the SQL Server Cloud Adapter Service

The Cloud Adapter is a stateless, synchronous service that receives messages from the on-premise instance of SQL Server. This service is required when you are deploying a database from your on-premise SQL Server instance to a SQL Server deployed on an Azure Virtual Machine.

Cloud Adapter is supported with SQL Server 2012 and higher. On SQL Server 2012, the Cloud Adapter for SQL Server requires SQL Management Objects.

For your SQL Server 2012 installation, you will need the SQL Server Cloud Adapter to be installed. This is available for download from the SQL Server 2014 Feature Pack. The filename that you need to download is SqlCloudAdapter.msi.

When you try to install this on your Azure VM, you might end up with the error message below:

Service cannot be started. System.IO.FileNotFoundException: Could not load file or assembly ‘Microsoft.SqlServer.SqlEnum, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.
File name: ‘Microsoft.SqlServer.SqlEnum, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91′
   at Microsoft.SqlServer.Management.CloudAdapter.Service.CloudAdapterService.ReadConfigurationParameters()
   at Microsoft.SqlServer.Management.CloudAdapter.Service.CloudAdapterService.OnStart(String[] args)
   at System.ServiceProcess.ServiceBase.ServiceQueuedMainCallback(Object state)

The above error message clearly states that the version number that the installer is looking for is SQL Server 2014 i.e. version = 12.0.0.0. You can install this assembly when Microsoft® SQL Server® 2014 Shared Management Objects (SharedManagementObjects.msi) is installed from SQL Server 2014 Feature Pack.

Reference:
Cloud Adapter for SQL Server
http://msdn.microsoft.com/en-us/library/dn169301.aspx

Chasing the Ghost Cleanup in an Availability Group

Because read operations are mapped to snapshot isolation transaction level, the cleanup of ghost records on the primary replica can be blocked by transactions on one or more secondary replicas. The ghost record cleanup task will automatically clean up the ghost records for disk-based tables on the primary replica when they are no longer needed by any secondary replica. This is similar to what is done when you run transaction(s) on the primary replica. In the extreme case on the secondary database, you will need to kill a long running read-query that is blocking the ghost cleanup. Note, the ghost clean can be blocked if the secondary replica gets disconnected or when data movement is suspended on the secondary database. This state also prevents log truncation, so if this state persists, we recommend that you remove this secondary database from the availability group.

The above is a snippet from the official Microsoft documentation for Availability Group Secondary Replicas under the limitations and restrictions section.

So a transaction on a secondary replica can block an operation on a primary replica… Hmm.. Now that smells like a mystery!

Before I go further, let me explain what Ghost Cleanup does. Let me give you the official text from the Books Online.

Deletes operations from a table or update operations that cause a row to move can immediately free up space on a page by removing references to the row. However, under certain circumstances, the row can physically remain on the data page as a ghost record. Ghost records are periodically removed by a background process. This residual data is not returned by the Database Engine in response to queries.

I had some free time a while back and decided to actually track this down to show how the Ghost Cleanup actually works in an availability group replica. My availability group setup was a simple one which had two SQL Server instances sitting across two different subnets as shown in Screenshot 1.

image

So, let’s get the show on the road and let me walk you through walk you the through the ghost cleanup behavior on the secondary replica.

The DML

On my existing Availability Group setup, I inserted a single row in a table of a primary replica database with the value of 3. The logged operations show up as follows in the SQL Server transaction log.. Psst.. Don’t tell anyone that I was reading the log file Winking smile

image

As you can see from the green highlight above the Transaction ID is 11899 (0x2e7b) which inserted a row on Page ID 315 (ox13b).

What was on the page

I verified that the page on the primary replica database had the new entry that I had added into the heap using the Page ID retrieved from the transaction log. Note that we are keeping track of the oldest active transaction as well. The record shows the transaction timestamp which was responsible for the DML operation 11899 (0x2e7b). From Screenshot 2 below, you can see that the version information is maintained and the transaction timestamp shows up correctly (green highlight). The value also shows up correctly (pink highlight).

image

What happened after that?

Then I started a transaction on the secondary replica and executed a SELECT query on the same table with a HOLDLOCK hint to keep the row lock. Then I proceeded to delete both the rows with value 3 in the primary replica. I verified that the rows were not retrieved by a SELECT query on the primary and secondary replica. The transaction log dump from secondary replica shows that the changes were replayed.

Oops! I had to read the transaction log again.

From the green highlights in Screenshot 4, you can see that the GhostCleanupTask transaction ran on the secondary replica. The pink highlights shows that the transaction ID 11900 (0x2e7c) deleted two rows from the Page ID 315 (ox13b). So all is good now.

image

Curiosity killed the cat!

imageWell curiosity got the better of me and I decided to check if the same the story was being told inside the transaction log of the primary replica database. And this is where David Duchovny’s a.k.a. Agent Moulder and Gillian Anderson a.k.a. Agent Scully’s faces from the X-Files will be an apt representation of what I present next.

Screenshot 5 will show that the Ghost Cleanup Task continues to execute on the Primary Replica Database! What now executed? Did we not delete the rows and verify everything was alright…

The first observation is that the transaction log is being replayed to the letter on the secondary replica. Notice that the transaction IDs of the Ghost Cleanup Task correspond with the transaction IDs of the Ghost Cleanup Task found on the secondary replica instance database. It wasn’t a joke when the documentation said that transactions are replayed on the secondary replica!

The yellow highlights show that the rows were deleted from the table that I had performed the delete on. The pink highlights confirm that the same transaction was associated with both the deletes.

I had verified that there were no ghost records in the database when I started the repro. So, the important question was:

Why Ghost Cleanup Task was running repeatedly on the primary replica database?

image

What was really happening: THE EXPLANATION

Since I had an open transaction on the secondary replica database, I had an active version store! Screenshot 6 shows that my active version store on the secondary replica with a transaction sequence number of 11900 (0x2e7c) which matches the transaction ID of the delete operation. This active version stored was created on the delete operation because I had performed a SELECT with a HOLDLOCK earlier as mentioned in this post on the table from the secondary replica.

Then I created a table on the primary replica database and inserted a row in it. I checked if this data was available on the secondary replica and it was!

image

I found that on the primary replica, the database page shows ghost version records (Screenshot 7). The transaction timestamp matches the transaction ID which performed the delete operation i.e. transaction ID 11900 (0x2e7c).

image

Light at the end of the tunnel

Once transaction which I had stated on the secondary replica with the HOLDLOCK hint was committed, the ghost cleanup task was able to perform the cleanup on the primary replica’s page. Once this was completed successfully, the ghost records on the secondary replica were cleaned up promptly as well.

The above behavior is true for both synchronous and asynchronous modes of operation.

I hope this was a fun Friday read! Have a good weekend!

SQL Server 2012: Trace Flags

This is something that I have been planning to release for some time but just have not been able to get to mainly due to procrastination! So this new year, I decided to get down to it! A list of all the publicly documented SQL Server 2012 Trace Flags.

The list below shows the trace flags available in SQL Server 2012 along with whether the trace flag can be enabled as a startup parameter or using DBCC TRACEON command or both.

SQL Server exposes multiple trace flags which are required to set specific server characteristics or to switch off a particular behavior. Some trace flags are required to enable the fix post the installation of the update.

This is currently a work-in-progress. The trace flags mentioned in the table below have the associated public article links mentioned which provide more information about the trace flag.

If you want a trace flag to be mentioned in the list below, then feel free to leave a comment. Note that only publicly documented trace flags will appear in this list.

The list of trace flags available for SQL Server 2005 are documented here.

The list of trace flags available for SQL Server 2008 are documented here.

Disclaimer
1. Data mentioned below is as of 14th January, 2014.
2. Trace flags should be used under the guidance of Microsoft SQL Server support.  They are used in this post for discussion purposes only and may not be supported in future versions.
3. Trace flags for hotfixes should be only enabled if the fix is applicable to the SQL Server instance that you are enabling the trace flag on.

Legend
Startup: If Y,Trace flag can be enabled by adding the trace flag number as a startup parameter using –T<trace flag number>
DBCC: If Y, Trace flag can be enabled using DBCC TRACEON(<trace flag number>,-1)

Trace Flag Description KB Startup DBCC
205 Report when a stored procedure is being recompiled. This trace flag will write the a corresponding message to the SQL Server errorlog. 195565 Y Y
260 Prints versioning information about extended stored procedure dynamic-link libraries (DLLs). For more information about __GetXpVersion(). BOL Link Y Y
610 Minimally logged inserts for indexed tables for fast load scenarios. Read article for limitations. Article Y Y
652 Disable pre-fetch scans 920093 Y Y
661 Disable ghost removal process 920093 Y Y
806 Audit failure during a read operation is reported in the errorlog as a 823 message on enabling this trace flag 841776 Y Y
815 Detect unwanted changes to in-memory SQL Server data pages, latch enforcement is enhanced with this trace flag enabled Article Y Y
818 Detect stale reads. Very helpful in analyzing recurring data corruption issues. Article Y Y
830 Suppress stuck-stalled I/O warnings Article Y Y
834 Use Microsoft Windows large-page allocations for the buffer pool. Only applies to 64-bit instances. 920093 Y N
836 Use the max server memory option for the buffer pool. Only applicable for 32-bit SQL Server 920093 Y N
840 On Standard edition, allows larger I/O extent reads to populate the buffer pool when SQL Server starts 912322 Y Y
1106 Enable tempdb allocation trace. Not suggested for production use. 947204 Y Y
1117 Even growth of all files in a file group Whitepaper Y Y
1118 Prevents allocation of pages from mixed pages for all databases 328551 Y Y
1204 Returns the resources and types of locks participating in a deadlock and also the current command affected. Useful for SQL Server 2000. BOL Link Y Y
1205 Prints information about each deadlock search 832524 Y Y
1211 Disabled lock escalation BOL Link Y Y
1222 Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema. To be used for SQL Server 2005 and above. BOL Link Y Y
1224 Disables lock escalation based on the number of locks BOL Link Y Y
1260 Disabled mini-dump for non-yield conditions Whitepaper Y Y
1262 Dump everytime a non-yielding scheduler condition is detected. Article Y Y
1439 Trace database restart and failover messages to SQL Errorlog for mirrored databases 983500 Y Y
1448 Prevent replication latency with database mirroring enabled. Apply KB 983480 to prevent issues after enabling -T1448. 937041 Y Y
2301 Enable advanced decision support optimizations 920093 Y Y
2340 Disable batch sort to reduce CPU consumption Blog Y Y
2371 Use a modified threshold for auto update statistics 2754171 Y Y
2389 Automatically generated quick statistics for known ascending keys 922063 N Y
2390 Automatically generated quick statistics for all columns that include known ascending keys or unknown ascending keys 922063 N Y
2528 Disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE. Article Y Y
2544 Put maximum information in a dump file (all memory in the process). Can lead to a very large dump file size for 64-bit and 32-bit systems with AWE enabled. Article Y Y
2546 Dump all threads in a process. Can lead to a very large dump file size for 64-bit and 32-bit systems with AWE enabled. Article Y Y
2551 Generate a filtered dump of the SQL Server process. Reference Y Y
3004 Write extended information about backup/restore to Errorlog Blog N Y
3014 Write extended information about backup/restore to Errorlog Blog N Y
3023 Automatically enable CHECKSUM for BACKUP command 2656988 N Y
3042 Bypasses the default backup compression pre-allocation algorithm to allow the backup file to grow only as needed to reach its final size Article Y Y
3205 Disable hardware compression for tape drives. Article N Y
3213 Display backup/restore configuration parameters Blog N Y
3226 Suppress successful backup messages in the logs Article N Y
3502 Displays start and end of checkpoint. Can be very verbose and increase your transaction log size very quickly 815436 Y Y
3504 Print Checkpoint summary Blog Y Y
3505 Disable Checkpoint 815436 Y Y
3604 Send trace output to client Y Y
3605 Send trace output to SQL Errorlog Y Y
3608 Prevents SQL Server from automatically starting and recovering any database except the master database Article Y N
3625 Limits the amount of information returned to users who are not members of the sysadmin fixed server role, by masking the parameters of some error messages. Article Y N
3688 Needed to enable fix for issue mentioned in KB Article. 922578 Y N
4010 Allows only shared memory connections to the SQL Server Blog Y Y
4022 Skips launching automatic stored procedures when SQL Server starts. This is equivalent to setting “scan for startup procs” configuration option to 0. Article Y Y
4029 Push more verbose errors to the errorlog when read+write errors occur in the network layer on the server side. Blog Y Y
4199 Controls multiple query optimizer changes previously made under multiple trace flags. Refer article for more details. 974006 Y Y
4606 Disables password policy check during server startup Blog Y Y
4610 Increases the size of the hash table that stores the cache entries by a factor of 8. 959823 Y Y
4616 Makes server-level metadata visible to application roles Article Y Y
4618 Limit the number of entries in the TokenAndPermUserStore store 959823 Y Y
4621 Control TokenAndPermUserStore store size using registry value (Applies only to SQL Server 2005. For SQL Server 2008 and above, please refer 959823) 959823 Y Y
6527 Disables generation of a memory dump on the first occurrence of an out-of-memory exception in CLR integration Article Y Y
7806 Enables a dedicated administrator connection (DAC) on SQL Server Express Article Y N
8002 Treat affinity mask like processor affinity 818765 Y Y
8004 Create memory dump for the first occurrence of out-of-memory condition Article Y Y
8011 Disable the ring buffer for Resource Monitor 920093 Y Y
8012  Disable the ring buffer for schedulers 920093 Y Y
8018 Disable the exception ring buffer 920093 Y N
8019 Disable stack collection for the exception ring buffer 920093 Y N
8020 Disable working set monitoring 920093 Y N
8024 Performs additional checks before generating non-yielding condition dump Whitepaper Y Y
8032 Reverts the cache limit parameters to the SQL Server 2005 RTM setting which in general allows caches to be larger Article Y N
8048 Forces CPU based partitioning Blog Y N
8602 Ignore all index hints Whitepaper Y Y
8721 Will dump information into the error log when AutoStat has been run. 195565 Y Y
8744 Disable pre-fetching for ranges 920093 Y Y

Another day at the SQL Bangalore UG Meeting

Another Saturday and another Bangalore SQL UG meeting completed! The turnout on a Saturday morning was really good. This time around the event was a bit different as we had a session on Photography by Vijay [T: @msigeek | B: iClickD]. This week’s User Group meeting was dedicated to SQL Server Tips and Tricks with Balmukund Lakhani [T: @blakhani | B: sqlserver-help] and me presenting on Management Studio and Power Pivot and Power View in Excel 2013.

Balmukund’s session as always was a superb hit where he demonstrated more than a fair share of SSMS tips and tricks. I am sure every one at the event learnt atleast a new thing about SSMS. There are multiple hidden options under the Tools->Options view in SSMS.

Vijay delivered a great session on photography which was very well appreciated by the audience!!

My session was around the the usage of Power View and Power Pivot in Excel 2013. I demonstrated how easy it was to build visualizations with Excel 2013 for administrators. I also demoed the Excel sheet that I had created using the System Health Session data. All my blog posts on this topic are available below.

PowerView and System Health Session–CPU health

PowerView and System Health Session–Scheduler Health

PowerView and System Health Session–SQL Memory Health

PowerView and System Health Session– IO Health

PowerView and System Health Session– System

The Excel file can be downloaded from Sky Drive using the link: http://sdrv.ms/10O0udO The presentation that I used for my session is available at SlideShare and also embedded below.