Rebuilding SQL Database Indexes

A few months ago I wrote a blog post titled “Don’t Forget About Index Maintenance on Azure SQL Database“. Since then, Microsoft Azure SQL Database has changed a lot. We aren’t as concerned about the size of the database anymore, because databases can reach 500 GB in size. Take a moment to think about that number. 500GB is a lot of data! Before you get excited and move on to more important things, ask yourself this question, does all that data really belong in my SQL Database? Put some thought into it, you may be surprised by the answers you come up with.

My Recommendations For Windows Azure Literature

In 2010 I bought a Kindle and it changed my life! Since then I’ve been catching up on books I should have read years ago. Back then, reading technical books meant carrying bulky/ heavy printed books in my bag. The day I got my first kindle, is the day I started reading again!

Since then, I read quite a few books! Below are some of the books that got me hooked on Windows Azure!

Windows Azure SQL Data Sync – Is This Service Right For Your Database?

I recently tried to use Windows Azure SQL Data Sync to replicate the data from my production database to a staging SQL Database (located in the same datacenter as the production SQL Database) instance and also to an
on-premise SQL Server Database instance.

Every 24 hours, the application inserts 10 to 40 million rows and removes all stale data from the tables. As you can imagine, this generates quite a large change-set for Windows Azure SQL Data Sync. In summary, the volume of data is quite important and it is short-lived.

Windows Azure SQL Data Sync – Optimizing The Initial Sync Process For Large Data Sets

Today I setup SQL Data Sync to synchronize
30 million records between 3 database instances.
 
A master database (HUB) needed to be replicated to a staging database hosted in Windows Azure SQL Database and to a second instance hosted on-premises in SQL Server 2008 R2.

Needless to say that I rapidly hit a bottleneck… The initial replication took forever, so I started looking for ways to get it to complete in a timely manner.

The first alternative I came up with, only works when you’re replicating databases within the Windows Azure SQL Database Service. Using the Create Database as Copy of statement as described in "Creating a Staging Database Instance From a Production Instance on Windows Azure SQL Database". I created a copy of my Hub database and proceeded to creating a sync relationship between the Hub database and the newly created copy.

The second alternative also relies on creating a copy of the Hub database. The main difference is that the copy can be created on-premises or in a other datacenters. Using the export functionality, create a backpac of your Hub database and restore (import) it in the environment of your choice. Then create a sync relationship between both database instances.

The prescribed best practice is to start with data in only one database. If you have data in multiple databases,
SQL Data Sync treats each row as a conflict that needs resolution. This causes the initial synchronization to go very slow – taking several days to several months, depending on the database size.

Following this advice, it took close to 5 hours to complete the first synchronization and it took close to 20 hours to complete the synchronization with the on-premise databases. Originally, this is why I looked into making a copy of my database before creating the sync group. 

Creating a copy of my database took close to 4 hours and the initial synchronization took close to 10 hours. The process of synchronizing two exact copies of my database took me about 14 hours instead of the 5 hours it took me when I started off with an empty database. 

References

• Avoid a slow and costly initial synchronization
• SQL Data Sync (Preview) Best Practices

Windows Azure SQL Data Sync – The Maximum Limit For Index or Statistics Key Column List is 16

I recently used SQL Data Sync to sync a Windows Azure SQL Database with an on-premises database instance hosted in SQL Server 2008 R2. When SQL Data Sync tried to create the schema it threw the following exception.

Database provisioning failed with the exception "SqlException Error Code: -2146232060 – SqlError Number:1904, Message: The index ‘XI_IndexName’ on table ‘dbo.TableName’ has 21 column names in index key list. The maximum limit for index or statistics key column list is 16. " For more information, provide tracing ID ‘802aeb36-fa15-****-****-b8acc99125d3’ to customer support.

Confused as to where this exception came from, looked around and found a few posts (listed below) explaining restrictions for creating Indexes. 

Then I realized that my schema has already been created on Windows Azure SQL Database, so I tried publishing the  schema from my Database Project to my on-premises database instance and for some reason, the error was not raised.

Then I reinitiated the data sync process and was able to complete the initial synchronization.

My guess is that by publishing the schema myself, I was able to relieve SQL Data Sync from trying to create the schema and allowed it to directly start synchronizing the data.

 

More Posts About This Limitation

• Creating Indexes (Database Engine)
• SQL Azure Data Sync error – maximum limit for index key column list is 16?
• Any way around unique index 16 column max
• What are the maximum number of Columns per index key,foreign key,primary key in SQL Server
• SQL SERVER – Maximum Columns per Primary Key – Fix : Error : Msg 1904, Level 16, The index on table has column names in index key list. The maximum limit for index or statistics key column list is 16

What Does Your Cloud Disaster Recovery Plan Look Like?

I previously wrote about how important lessons usually don’t come easy… and that replication is important. A few weeks ago the Windows Azure platform was going through a rough patch and all SQL Database instances went offline for a few hours.

At that moment, I recommended the use of SQL Data Sync to keep an on-premise backup of your data.

Since then I took a few steps back and read more on the subject. I also setup my first cloud to on-premise SQL Data Sync group and I must say that I’m impressed!

What’s interesting about SQL Data Sync, is that a backup isn’t limited to the cloud. It provides bidirectional synchronization between two or more databases, which can be located on-premise or in datacenters.

Configuring SQL Data Sync doesn’t require any code! Everything is configured through the Windows Azure Management Portal and on your on-premise servers.

There are many different ways to backup your SQL Database instances and they all come at a price. The available options do not cover all the possible disasters on their own so a mix and match may be the solution you are looking for.

The scope of this post will be limited to SQL Data Sync. Future posts will explore other ways to backup your databases. If you are curious about creating an instant copy of your production database within the same SQL Database Server see my posts about creating a staging database instance from a production instance on Windows Azure SQL Database.

Important Lessons Usually Don’t Come Easy… Replication is Important!