Database restore failure when restoring from URL

SQL Server 2014 and later includes a feature named Managed Backup, which automates scheduled database backup tasks. Managed Backup backs up databases to an Azure storage account using Backup to URL. As a part of its functionality, Managed Backup attempts to automatically resolve operational errors such as broken backup chains. Specifically, if an out of…


How to view query plans from Query Store in Management Studio

Query Store is a great new feature in SQL Server 2016 and SQL Azure Database. Among other things, it captures and stores query plans for queries that execute on a SQL Server instance, so that they can be reviewed later, i.e. for performance troubleshooting. The plans are exposed in the sys.query_store_plan view, in the query_plan…


Collecting performance counter values from a SQL Azure database

Performance Monitor is a well-known tool for SQL Server diagnostics and troubleshooting. However, when working with SQL Azure, it is obviously not available. That said, in the latest SQL Azure update (V12), the sys.dm_os_performance_counters DMV is now available. This DMV provides the values for all SQL Server (but not Windows) performance counters, and can replace…


Database migration to SQL Azure using a bacpac – a blocker and a workaround

Update 2016-09-30: With recent versions of SqlPackage, the workaround for non-PRIMARY filegroups described below is no longer needed. When validating database during export, SqlPackage will no longer complain about non-PRIMARY filegroups. When importing a bacpac with such filegroups into Azure SQL Database, all references to them will be automatically replaced with references to the PRIMARY filegroup. There…


Using Storage Spaces on an Azure VM cluster for SQL Server storage

Storage Spaces is a feature of Windows Server that lets you create a virtual disk (also known as a storage space) out of a number of underlying physical disks. The Performance Guidance for SQL Server in Azure VMs whitepaper mentions (on pages 15 and 26) that in Azure VMs, Storage Spaces can be used to…


Row count discrepancy between statement level and batch level events

Both XEvents and SQL Trace include events that fire at the completion of a statement and at the completion of a batch. For XEvents, they are sqlserver.sql_statement_completed and sqlserver.sql_batch_completed, and for SQL Trace they are SQL:StmtCompleted and SQL:BatchCompleted. When troubleshooting a DML query recently, I noticed that the row count reported in the sql_batch_completed event…


Marked transactions

A little known and perhaps underappreciated feature of SQL Server is the ability to assign a name and a description to a transaction. The name of the transaction is recorded in the transaction log, and the transaction is called a marked transaction. The syntax is BEGIN TRANSACTION TransactionName WITH MARK ‘TransactionDescription’ (the description is optional)….


SQL Server 2012 CDC for Oracle – a Review of One Implementation

SQL Server 2012 shipped with a new feature named SQL Server 2012 Change Data Capture for Oracle By Attunity (shortened to SQL 2012 Oracle CDC in this blog). This feature allows using Change Data Capture in a SQL Server database, with an Oracle database as the source of data. One notable advantage of this feature…


Getting out of single user mode

Sometimes, brute force is the best way to get something done. Imagine a busy production server with about thirty databases. Somehow, during an administrative task, one of the databases ended up in single user mode. The application using the database is multithreaded and normally opens and closes multiple connections to the database at a high…


Yet another cause of Kerberos authentication failure connecting to SQL Server

Yesterday I was troubleshooting a rather common problem. A query running on an instance of SQL Server (INST1) was using the OPENROWSET function to retrieve a rowset from another instance (INST2). This is known as “double-hop authentication”, and Kerberos authentication is required for this to work. When executed from a particular client machine, the query…