Sql-server – SQL Server data files in Windows Azure: filegroups and partitioning still applicable

azure-sql-databasefilegroupspartitioningsql server

I just created a database using the new functionality "SQL Server 2014 data files on Windows Azure" and this works quite well.

However, I have a table with a billion records and in our local environment we have partitioned this table into 33 filegroups, my question is whether to store as Azure blob and also is it worth partitioning it into filegroups for higher performance?

My understanding is that Azure's storage is different from that in our environment, so, can we apply the same techniques to improve performance?

Best Answer

If you anticipate that your database file(s) will exceed more than 1TB in size you should configure the server to put the data files across multiple VHDs. How can spread my databases across multiple VHDs?

Create one or more dynamic volumes consisting of multiple VHDs at the operating system level and put your database files on these volumes. Split your tables and indexes into separate database filegroups and then place those filegroups in different files (MDF + NDF) which are spread across separately attached VHDs. Note that this will not offer any benefit for transaction logs because SQL Server does not stripe across transaction log files but uses them sequentially.

If you are migrated to Azure and want to compare the performance of your databases before and after the migration then take a SQL Server baseline(enter link description here)