Posts Tagged ‘SQL Server’

Azure SQL Managed Instance – An Introduction

October 17, 2018 Leave a comment

Historically there have been two offerings available for running SQL Server in Azure.  The first is Azure SQL Database, released in 2010, which is Platform as a Service (PaaS). The second is SQL Server on an Azure VM, which is Infrastructure as a Service (IaaS), also released in 2010.  The benefits and drawbacks of each offering is listed below.

 BenefitsDrawbacksUse Cases
Azure SQL DatabaseInfrastructure is fully managedLimited to a single databaseNew development in Azure
Cross-database communication is difficultMulti-tenant applications
No SQL Server Agent
Cannot be powered down
SQL Server on an Azure VMFull feature set of SQL Server
Client is responsible for managing infrastructureMigration of existing applications
Can be powered down to reduce costCreation of test environments
Multiple Databases
Cross-database communication
SQL Server Agent

The drawbacks of Azure SQL Database make it difficult to migrate existing applications, because of the number of application changes required.  Azure SQL Database is designed to be used for new development in Azure and for multi-tenant environments, where each tenant requires their own copy of a database.

The benefits of SQL Server on an Azure VM make it much easier to migrate an existing application to Azure.  However, the VMs underlying the application still have to be managed by the client.  This fails to take advantage of the management of resources in Azure, and uses Azure as a VM host.

A third option, Azure SQL Managed Instance, was released at the beginning of October 2018.  Managed Instance combines the best of the previous options.  With Managed Instance, the infrastructure is fully managed and the majority of the SQL Server feature set is available.  The full list of differences between a traditional install of SQL Server and Managed Instance can be found here.  A number of the most dramatic differences are listed below.

  • Filestream is not supported
  • Backups must be run to Azure Blob Storage
  • No control over file placement

In the following series of blog posts, I will detail the steps required for the following tasks:

  • Provisioning the prerequisite resources for Azure SQL Managed Instance
  • Provisioning an Azure SQL Managed Instance
  • Methods for migrating existing SQL Server instances to Managed Instance
  • Maintaining databases running on an Azure SQL Managed Instance

Additionally, I will delve into the internals of an Azure SQL Managed Instance and describe where they differ from a regular SQL Server instance.

I am really excited about the options Azure SQL Managed Instance provides for migrating existing SQL applications to Azure and am looking forward to blogging about it.


SQL Server Availability Groups and Automatic Seeding

June 20, 2018 4 comments

Microsoft released Availability Groups (AG) as a feature in SQL Server 2012. Prior to SQL Server 2016, there were two methods of adding a database to a new AG replica.

  1. You could provide the Add Database to Availability Group wizard a file share accessible by the primary and secondary replicas.  SQL Server would run FULL and LOG backups of each database to the share and use them to restore the database(s) to each replica.
  2. You could manually run a FULL and LOG backup of each database, copy the backup files to each replica, and restore the databases WITH NORECOVERY.

With SQL Server 2016. Microsoft has provided a third option, Automatic Seeding.  With Automatic Seeding, you specify the databases and the replicas and SQL Server will begin transferring data to each replica.  The duration of the seeding process depends on the size of the database and the network bandwidth available between primary and secondary replica.

There are a few considerations to keep in mind when considering Automatic Seeding.  Virtual log files in the primary database cannot be freed while the automatic seeding process is running.  For a large, active database this can lead to excessive log growth.  Automatic seeding is a single-threaded process and is limited to 5 databases.  Additionally, Automatic Seeding will not work if Change Data Capture is enabled.  Finally, compression is disabled for Automatic Seeding by default, but can be enabled by turning on trace flag 9567.  This can improve performance of the seeding process but will increase CPU overhead.

The status of the Automatic Seeding process can be monitored using the following query: (download)

USE master;

SELECT local_database_name,
database_size_bytes / 1045876 AS [Database Size (mb)],
transferred_size_bytes / 1045876 AS [Transferred Size (mb)],
transfer_rate_bytes_per_second / 1045876 AS [Transfer Rate/Sec (mb)],
(database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second AS [Time Remaining (sec)],
WHEN ((database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second) < 360000
THEN '0'
+ RTRIM(((database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second) / 3600)
+ ':' + RIGHT('0' + RTRIM(((database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second) % 3600 / 60), 2)
+ ':' + RIGHT('0' + RTRIM(((database_size_bytes - transferred_size_bytes) / transfer_rate_bytes_per_second) % 60), 2) AS [Time Remaining]
FROM sys.dm_hadr_physical_seeding_stats
WHERE internal_state_desc = 'ReadingAndSendingData'
ORDER BY remote_machine_name;

The query returns the following result set:

Note that one row will be returned per replica being seeded.

Automatic Seeding makes initializing AG replicas simple. I hope you can put the status query to good use.

RIP @sqlsoldier

April 3, 2018 Leave a comment

Today I learned that Robert Davis, aka @sqlsoldier, passed away. Among other things, Robert literally wrote the book on SQL Server mirroring. I met him briefly at SQL Saturday Oregon, but interacted with him frequently via the #sqlhelp hashtag. He was often one of the first people to reply to my many questions about the transaction log. One of my proudest moments in IT came when I answered a question about reading the transaction log. Robert asked what I meant, and when I explained myself, he wrote, “In that case, I’d do it Frank’s way.” I don’t think my feet touched the ground for the rest of the day.

Robert was a prime example of the strength of the SQL Server community. That someone with his depth and breadth of knowledge would take time to help others leaves me awed and humbled. While his death leaves me shocked and saddened, I will try to keep his memory alive by learning and sharing knowledge with the community.

Categories: #sqlhelp, SQL Server Tags: ,