Quick Fan-Out Queries to SQL Database

SQL Azure, sorry I mean Windows Azure … sorry, Microsoft Azure SQL Database is an interesting database platform.  Connections to it have to be made per database meaning the more traditional connections made to a SQL Instance don’t work quite the same.  Add to that a sharded dataset spanning multiple SQL Databases and you’re into the realm of “tricky queries”.  Even a simple query can be quite challenging if when you don’t have the right tools available.  Take for example a request for the number of users by country – it may look something like this:

select countrycode, count(*)
from dbo.myusers
group by countrycode;

Oooooh, but hang on.  You have your users sharded across 100 databases, each one requiring a discrete connection.  We have to run a fan-out query hitting all 100 databases, gather the results, then apply a second aggregation on the result set to get the answer we want.

Some time ago in my early days of SQL Database, it became clear very quickly that a tool was needed to run fan-out queries and return a single merged result set.  I had started learning Powershell so played around with it for a while, but in truth an application was needed with a nice(ish) interface to use.  A developer colleague of mine knocked together such a tool allowing me to import a configuration file, i.e. a list of database connections, and run TSQL against those I selected from a treeview, using the SqlCommand C# library.  It was great!  I could specify the number of threads to use to maximise performance, and get a consolidated set of data returned.  Even better, I could copy and paste the data into Excel to perform further analysis or aggregation when required.  It was something I used for a very long time until just recently…

Another colleague of mine, Antonio Vlachopoulos (Linkedin), mentioned he was using Registered Servers and it was much quicker.  It intrigued me.  I’ve been aware of the feature for years but never tried it for SQL Database.  It stemmed back to when I first needed to run those fan-out queries, and the thought of setting up each individual database connection put me off as we had hundreds.

I’ve happily used my fan-out query app for years, without ever having the need to change to something else.  But in the interest of finding “the best way” I thought I’d try it out.  And today things are different.  Today I know Powershell.  With Powershell I don’t have to manually create every Registered Server connection to my databases.  Instead I can use my configuration database to generate the connections and create them automatically.

Below is a portion of the Powershell I used.  You need to write a process to import your database connections somehow, whether it’s from a config file, or a database etc.  After that the code will go off and create your Registered Server entries.  It does assume it’s running on the local instance and that you have the appropriate permissions, so you may need to do a little experimentation for your environment.

cls
$ParentGroupName = "MyAzureDatabases"

# import the sqlps module to enable us to use the relevant features
import-module sqlps

# navigate to the Server Group folder where the Registered Servers are stored
CD 'SQLSERVER:sqlregistration\Database Engine Server Group'

# add the parent group
new-item $ParentGroupName

# navigate to the new group
CD $ParentGroupName

################################################# 
#    Get your connection details in this section.
#     This script expects an array of nested hash
#     tables. 
#
#     Server
#      `-Database
#         `-Username (secure)
#         `-Password (secure)
#
#     It is easily adapted to add more nesting 
#     for greater flexibility with groupings.
#################################################

# NB - this is not how I specified my connections and credentials, I keep them all in a configuration database
#      but it is useful as a demonstration.
$username = (ConvertTo-SecureString "myUsername" -AsPlainText -Force)
$password = (ConvertTo-SecureString "myPassword" -AsPlainText -Force)
$connections = @{ "MASD_server1" = @(@{"ShardedDb1" = @($username,$password)},@{"ShardedDb2" = @($username,$password)}) }
$connections += @{ "MASD_server2" = @(@{"ShardedDb3" = @($username,$password)},@{"ShardedDb4" = @($username,$password)}) }


#################################################
#    - END
#################################################

# specify the standard SQL Database server name extension for connections
$ext = ".database.windows.net"

# loop through each key and pull out values to generate Registered Server entry
foreach ($server in $connections.Keys) {
    # Add each server as a group
    New-Item $server
    # Navigate to server path
    CD $server
    foreach ($databaseList in $connections.get_Item($server)) {
        foreach ($databaseHash in $databaseList) {
            foreach ($database in $databaseHash.Keys) {
                $bstr = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR((($databaseHash.get_Item($database))[0]))
                $uid = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($bstr)
                $bstr = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR((($databaseHash.get_Item($database))[1]))
                $pswd = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($bstr)
                New-Item "${server}_${database}" -itemtype registration -Value "server=tcp:${server}${ext};integrated security=false;uid=$uid;password=$pswd;database=$database;Encrypt=True"
            }
        }
    }
    # go back up a level to parent group
    CD ".."
}

So, onto the testing.  I created my Registered Servers and decided to run something simple, but with enough records returned to make it a good test.  In steps my original query at the start of this post:

select countrycode, count(*)
from dbo.myusers
group by countrycode;

For me, I was pulling back 100+ records each from about 400 databases.  My fan-out query app went off and pulled the data back in around 17 seconds.  Not bad for hitting that many databases in the cloud and getting the results collated, I thought.  I ran it again, to allow for cached results.  The time was about the same.  Now for the turn of my Registered Servers – 4 seconds!  If it was going to be quicker, I expected it to be marginal - but this was 4 times quicker.  Remarkable.  It was very important for me at that point to test out the speeds over both large and small datasets.

Large(r) Dataset

I started by taking 800kb of data from each of my 400 databases.  This was done by generating 8000 bytes of nonsense data over 100 rows:

select top 100
    replicate(cast(0x0 as varbinary(max)),8000) as mydata
   ,cast(sysutcdatetime() as varchar) as runtime
from dbo.MyTable;

Surprisingly, the Registered Servers query came in slower.  29s compared to 13s for the fan-out query app.  Tweaking the size of the binary data, I plotted out a number of timings for both the Registered Servers (RS) queries and fan-out query app (FQA).

You can quickly see that around 50MB the Registered Servers query hits a saturation point and slows badly.  The fan-out query app stays fairly steady regardless of the size of the dataset being returned.

Explanation

The reason my test query contained a ‘runtime’ field was to allow me to see when the data was returned by each database.  By comparing the earliest time against the latest across the complete set, we can get a feel for how long it took each method to execute the queries across all of the databases.  The chart below shows the comparison.

It seems that with Registered Servers the queries are all executed as quickly as possible, which suggests a high number of threads in use (perhaps even one thread per target).  With the fan-out query app, I control the number of threads in the config – for these tests I was using 32 threads.  Having a staggered approach to returning the data, rather than everything coming back at once, can be beneficial for larger datasets.  Any SSIS developers out there know all too well that trying to do too much at once ends up with much slower results.  That certainly seems to be the case here – Registered Servers try to do too much.  As soon as the result set grows too large for the local instance to handle efficiently, the process slows down considerably.

Conclusion

Registered Servers provide a brilliant mechanism for running lightweight queries against SQL Databases (and on-premises ones too for that matter).  It is super fast as long as the result sets aren’t too large, but you’ll have to test out the thresholds for yourself as they’ll differ for every environment.  The best part about it?  It’s provided out-of-the-box with SQL Server.

The downside is the lack of granular control, in terms of threads etc.  Also, when it comes to SQL Database my fan-out query app has a distinct advantage that it includes retry logic, a beneficial aspect when running queries in the cloud.

But for general ad hoc querying, Registered Servers will be my first choice from now on.  Only when I need larger datasets will I switch back to the fan-out query app, but I suspect that will be a rarity.

Slide Deck for SQL NE Talk

The slide deck for my talk "Introduction to WASD for DBAs & Developers" is now available for download at the link below.  I have added some notes to the slides, so hopefully you'll find them useful.

Introduction to WASD for DBAs and Developers

Can I COPY a database? Can I?

I decided to write a post about a pitfall of the WASD COPY command.  A pitfall I experienced during what should have been a pretty routine DBA task, albeit in the cloud.  Retracing the steps however, led me to a surprising result.

For those of you unfamiliar with WASD or the COPY command, it is a TSQL command used to copy a database.  It’s straightforward to use, is transactionally consistent, and makes the process of creating a database copy very easy indeed.  You can even copy the database to a different database server, providing both servers are hosted in the same datacentre.  The syntax is also easy to use (courtesy of BOL):

CREATE DATABASE destination_database_name
     AS COPY OF [source_server_name.] source_database_name
[;]

Aside from the variable performance (due to it creating 3 replicas in total as per WASD architecture, with multi-tenant nodes meaning unpredictable network and server speeds), it is a useful tool for the DBA, and the developer for that matter.

You’ll notice from the syntax that copying a database across servers requires you to run the command on the destination server, referencing the source server.  This page, again in BOL, provides some information about permissions when copying across servers.  I’ll not regurgitate the article, but I do want to draw focus to key point - the login you use becomes the new database owner, and the SID of that login is assigned.

So let me get back to where I started, the “pretty routine” DBA task.  I was asked to copy an application database from the development server up to the test server.  Not uncommon.  There are a few ways of doing this, but both database servers were in the same datacentre, so I opted to use the COPY command.  Also not uncommon is to have different credentials across environments, and this was no exception.  As I needed at least dbmanager permissions on both servers, the next logical step was to create a login on the destination server that matched the admin account on the source.  So that’s what I did:

create login DevAdmin with password = 'MyDevPa55w0rd!';
go
create user DevAdmin from login DevAdmin;
go
exec sp_addrolemember 'dbmanager', 'DevAdmin';
go

 

Note, this account is the admin account on the source server, but needs to be added as a user in the master database on the destination server, and added to the dbmanager role.

 

Next, I log in with the new DevAdmin user (on the target server), and run the COPY command:

 

create database myAppDb
as copy of <dev server>.myAppDb;
go 

The command completes successfully, and the database is now being copied asynchronously.  We can track its progress using the following query:

select state_desc
from sys.databases
where name = 'myAppDb';

When in progress you will see a value of ‘COPYING’, and when successfully completed the state will show as ‘ONLINE’.

Being a good DBA, after the copy was complete and I had applied the test credentials, I tidied up after myself.  I dropped the user in the master database, then dropped the login.

drop user DevAdmin;
go
drop login DevAdmin;
go

Done.  Testing was underway, and I was pleased with how efficient I had been.

It was now time to copy the database into Pre-Production.  Well, it was so easy last time, why use a different method?  I created the test admin credentials on the Pre-Production server in the same way as above and started the copy:

create database PreProdAppDb
as copy of <test server>.myAppDb;
go 

But the result was NOT what I expected:

I intentionally changed the name of the target database here, to highlight that this error message relates to permissions on the source database.  Yup, even though we’re using the administrator credentials, we can’t copy it.  Turns out you can’t COPY a database unless you are the database owner – admin or not.  What about the same server?  Nope, that doesn’t work either, same error message.

WOW.  I mean, WOW.

<several minutes of stunned silence>

After recovering from this bombshell and regaining my composure, I headed down the thought process of the on-premises world.  I’ll change the database owner.  Yes, let’s do that:

alter authorization on database::myAppDb to TestAdmin;

But:

Sure enough, this is confirmed in BOL:

I’m going to have to recreate the user that is the database owner.  So I re-run the SQL from earlier to create the DevAdmin account.  Can I copy the database now, even on the same server?  Well, no I can’t.  Remember the point I drew attention to at the beginning of the post, about the database copy obtaining a different SID on creation?  Well recreating the login assigns a different SID, which is different to the owner SID of the database.  The following queries confirm this:

select name, sid
from sys.sql_logins
where name = 'DevAdmin';

select name, owner_sid
from sys.databases
where name = 'myAppDb';

Results:

Off to BOL again to see if we can recreate the login with the correct SID.  Awwww, no luck then:

And this is the point where it got interesting.  I hit this error around a year ago, and have only recently decided to blog about it.  I wanted to show the error message, and was expecting something like this:

But instead it completed successfully!

WOW again.  Seems the Azure team have added the ability to set the SID on creating a login, but not updated the documentation yet.  It ruined my attempt at publicising this pitfall, but on the other hand it gave us a solution.  Those original credentials, in my case the DevAdmin login, must be carried through to each server you wish to copy the database to, as only the owner of a database can copy it.  Other options exist as a workaround, the easiest alternative being to export/import the database to change the owner.  However this is not transactionally consistent (ironically without a database copy) and requires a little more effort as well as BLOB (or local) storage.

The COPY command is a great feature of WASD, but suffers from some difficulties around the permissions required to perform the operation.  In an attempt to improve the user experience, I have raised 3 connect items, please up-vote them!

Allow the server admins and users in the dbmanager role to COPY a database, regardless of owner:

https://connect.microsoft.com/SQLServer/feedback/details/808957/wasd-allow-admin-or-users-in-dbmanager-role-to-copy-a-database-regardless-of-owner

Allow the server admin to change the owner of a database:

https://connect.microsoft.com/SQLServer/feedback/details/808958/wasd-allow-the-database-owner-to-be-changed

Update BOL to show that CREATE LOGIN..WITH SID is now available:

https://connect.microsoft.com/SQLServer/feedback/details/808955/wasd-bol-incorrect-create-login-with-sid-now-works

Thanks for reading.

Partitioned Views in WASD – Easy Peasy?

In my last post (http://sqltuna.blogspot.co.uk/2013/10/index-fragmentation-in-wasd.html) I ended with a reference for using Partitioned Views to reduce index sizes and allow defragmentation processes to run successfully.  This is because they allow each member table to have their indexes rebuilt individually meaning smaller datasets and smaller transactions.  My claim of it being “easy peasy” is possibly far fetched, so I felt a follow-up post was needed.  Although Partitioned Views is not a new concept in SQL Server, WASD is a different platform, and I thought it may prove useful to focus on how to partition your data in this environment.

First of all, as the observant reader, you have no doubt guessed that Partitioned Views are indeed supported on WASD.  You may not know however that Partitioned Tables & Indexes are not supported.  In the context of rebuilding indexes that’s not an issue, as partitioned tables or indexes cannot have individual partitions rebuilt online, unless you’re a crazy fool running SQL Server 2014 CTP1+ in production.  Therefore Partitioned Views do us just fine.

So, without getting into a full explanation of a Partitioned View, we essentially need to find or create one or more columns to constrain using CHECKs, keeping the datasets in each member table mutually exclusive.

What do I partition on?

The link above gives a nice, neat example using years and months for sales data.  In practice, your data does not always have an obvious partition.  In such cases, you need to create one.  In WASD you have 2 approaches – calculate in the database, or calculate in the application tier.  We don’t have the luxury of CLRs, so any code-related hashing must be moved into the application tier.  This is not a bad option, as it moves the processing where there are commonly more CPUs available, and in the case of Azure, more control over the resources available.  For more simplistic hashing in the database, this can be done in the form of a computed column or scalar UDF.

TIP: Whichever approach you take, ensure the source columns contain static data – you do not want to be coding data movements between your member tables due to a data update!

Let’s assume you don’t have a natural key for partitioning your data.  We need to hash a source column to produce a “partitionable” column.  If we consider a member table to be a “bucket” of data, the hashing algorithm must distribute the data as evenly as possible across the buckets.  A very simple example would be where an incremental integer PK exists – your partition column could take the modulus of the PK against the number of buckets, e.g. for 10 buckets, use PK % 10.  This will give you an even distribution for the values 0 to 9.  BEWARE – if your table contains such a key, which is an IDENTITY field, this is not the solution for you.  As soon as you split the data into member tables, you lose the ability to generate an incremental ID across the entire set, as each IDENTITY value will relate to the individual member table.  If it’s not an IDENTITY field, test your distribution across the number of desired partitions to see if this approach is appropriate.

Another point is we’re working in WASD here.  And this solution is being considered due to large data volumes and index sizes.  The likelihood is your data is sharded across multiple databases to increase throughput, and to allow for scalability you have designed your tables without an incremental key to remove PK clashes when merging datasets.  You may already have a hashed ID that you are using for sharding, or perhaps you’re using GUIDs or COMBs.  To explore the distribution of these various options I ran a comparison based on the following hashing options, where n is the number of partitions (or member tables):

1. The rightmost 4 bytes of a GUID, converted to an integer, split into n equal ranges
2. The rightmost 4 bytes of a GUID, converted to an integer (absolute), mod n
3. The leftmost 4 bytes of a GUID or COMB, converted to an integer, split into n equal ranges
4. The leftmost 4 bytes of a GUID or COMB, converted to an integer (absolute), mod n
5. The hash of a GUID using an implementation of the Jenkins hash, split into n equal ranges
6. The hash of a GUID using an implementation of the Jenkins hash, mod n

This list is clearly not exhaustive, but is a very good start to a simple hashing mechanism for creating a Partitioned View.  I excluded using the rightmost part of a COMB due to the way it is constructed, i.e. using the system date & time.  It is clear even without testing that the results would be heavily dependent on the time records were inserted, rather than based on a consistent algorithm – I wanted to avoid this approach.  The Jenkins hash is a simple, yet effective, hashing algorithm that I have seen used in a large-scale sharding architecture.  The implementation I am using for the tests is in C#, and produces a number in the positive BIGINT range from a String input.  It is highly effective at producing an even distribution across the positive BIGINT range, even from the smallest change to the input value, and with very little chance of duplicates.  I have personally tested this algorithm using 2.5 billion usernames without any collisions (!).  Yes, it needs to be run in the application tier, but is a worthwhile consideration for many architectural partitioning concerns.  Here is the code (courtesy of a developer colleague of mine):

    public static long LongHash(String input)
    {
        ulong hash = 0;

        foreach (byte b in System.Text.Encoding.Unicode.GetBytes(input.ToLower()))
        {
            hash += b;
            hash += (hash << 10);
            hash ^= (hash >> 6);
        }

        hash += (hash << 3);
        hash ^= (hash >> 11);
        hash += (hash << 15);

        return (long)(hash % long.MaxValue);
    }

The comparison was performed in a single table, with results shown across 1k, 100k and 1m rows for 2, 10 and 20 partitions.  I am cheating a little for these tests and running it all locally so I can make use of a CLR function for the Jenkins hash.  It was that or process each row one at a time in WASD… (from an application perspective this is not an issue, as rows tend to be dealt with individually).  Here is the script I used for creating and populating all the hash values:

IF OBJECT_ID('dbo.DistributionTest') IS NOT NULL
    DROP TABLE dbo.DistributionTest;
GO

-- I am using computed columns to do the work here and populate partition numbers based on my criteria
CREATE TABLE dbo.DistributionTest
(
     ID int IDENTITY(1,1) NOT NULL PRIMARY KEY
    ,baseGUID uniqueidentifier NOT NULL DEFAULT NEWID()
    ,right4range2 AS (CASE WHEN CONVERT(int,CONVERT(varbinary(4),RIGHT(CONVERT(char(36),baseGUID),8),2)) < 0 THEN 0 ELSE 1 END) PERSISTED
    ,right4range10 AS (FLOOR((CONVERT(bigint,CONVERT(int,CONVERT(varbinary(4),RIGHT(CONVERT(char(36),baseGUID),8),2)))+2147483648)/429496730)) PERSISTED
    ,right4range20 AS (FLOOR((CONVERT(bigint,CONVERT(int,CONVERT(varbinary(4),RIGHT(CONVERT(char(36),baseGUID),8),2)))+2147483648)/214748365)) PERSISTED
    ,right4mod2 AS (ABS((CONVERT(int,CONVERT(varbinary(4),RIGHT(CONVERT(char(36),baseGUID),8),2)))%2)) PERSISTED
    ,right4mod10 AS (ABS((CONVERT(int,CONVERT(varbinary(4),RIGHT(CONVERT(char(36),baseGUID),8),2)))%10)) PERSISTED
    ,right4mod20 AS (ABS((CONVERT(int,CONVERT(varbinary(4),RIGHT(CONVERT(char(36),baseGUID),8),2)))%20)) PERSISTED
    ,left4range2 AS (CASE WHEN CONVERT(int,CONVERT(varbinary(4),LEFT(CONVERT(char(36),baseGUID),8),2)) < 0 THEN 0 ELSE 1 END) PERSISTED
    ,left4range10 AS (FLOOR((CONVERT(bigint,CONVERT(int,CONVERT(varbinary(4),LEFT(CONVERT(char(36),baseGUID),8),2)))+2147483648)/429496730)) PERSISTED
    ,left4range20 AS (FLOOR((CONVERT(bigint,CONVERT(int,CONVERT(varbinary(4),LEFT(CONVERT(char(36),baseGUID),8),2)))+2147483648)/214748365)) PERSISTED
    ,left4mod2 AS (ABS((CONVERT(int,CONVERT(varbinary(4),LEFT(CONVERT(char(36),baseGUID),8),2)))%2)) PERSISTED
    ,left4mod10 AS (ABS((CONVERT(int,CONVERT(varbinary(4),LEFT(CONVERT(char(36),baseGUID),8),2)))%10)) PERSISTED
    ,left4mod20 AS (ABS((CONVERT(int,CONVERT(varbinary(4),LEFT(CONVERT(char(36),baseGUID),8),2)))%20)) PERSISTED
    ,jenkinsrange bigint NULL
    ,jenkinsmod2 tinyint NULL
    ,jenkinsmod10 tinyint NULL
    ,jenkinsmod20 tinyint NULL
);
GO

INSERT INTO dbo.DistributionTest DEFAULT VALUES;

-- insert 1m rows - ID can be used to find distributions up to 1k and 100k
GO 1000000

-- use CLR to apply Jenkins hash
UPDATE dbo.DistributionTest
SET jenkinsrange = dbo.LongHash(CONVERT(char(36),baseGUID));

GO

UPDATE dbo.DistributionTest
SET     jenkinsmod2 = jenkinsrange%2
    ,jenkinsmod10 = jenkinsrange%10
    ,jenkinsmod20 = jenkinsrange%20;

GO

SELECT TOP 10 *
FROM dbo.DistributionTest;

GO

RESULTS:

 

Conclusion

That’s a lot of data to take in!  Essentially though, all approaches produce an acceptable distribution across the partitions, even for low data volumes.  If you have a unique GUID in your dataset, then any of these approaches will work for you.  Consider putting the partitioning logic into the application tier too, as there is often more processing power available than in WASD.  No GUID?  No worries!  Any unique column should work with the Jenkins hash – simply convert to a String first and feed it in.  Adding a unique GUID is not exactly difficult either…  In fact my WASD preference (in most cases) is to use a GUID as the Primary Key, allowing for scalability through sharding, as well as the basis for a well distributed partitioning key.  A quick statement for the anti-GUID clan out there - your Primary Key does not have to be your clustering key.  Cluster on an incremental field if fragmentation on insert is a concern.  Having your Primary Key as the basis of your partitioning allows the application to generate the partition key and utilise the Partitioned View without (much) additional work.  Bear this in mind when designing your solution – if the Primary Key is not your app’s usual entry point then use a column that is.

Finally, if you want your Partitioned View to be updateable then pay attention to the requirements for this (a useful link here: http://technet.microsoft.com/en-us/library/ms187067%28v=sql.105%29.aspx).  Avoid computed columns, timestamps and IDENTITY columns etc, and remember to add your partitioning column(s) to your member tables’ Primary Keys.

Partitioned Views in WASD – easy peasy?  Armed with a few techniques, not far off I think

Index Fragmentation in WASD

I first wrote about this subject back in June 2012 (see: http://beyondrelational.com/modules/2/blogs/76/posts/15290/index-fragmentation-in-sql-azure.aspx) after several conversations with Microsoft about it, and particular concerns over fragmentation in the primary database compared to the secondaries (that’s right, the replicated secondary databases can have different fragmentation levels to the primary).  I was convinced that defragging the indexes would help with this scenario, but bowed down to Microsoft’s advice and wrote the post accordingly.

As with anything though, advice and thoughts can change over time.  Whilst the advice given in the linked post is still mostly correct – there are still no guarantees on enhancing performance through defragging indexes - there are occasions where an index defrag is beneficial.  Take the scenario mentioned above for example.  Say you have a lot of data, multiple GBs in fact, with a decent number of indexes present.  Your data is sharded, for performance and scalability reasons, meaning you are regularly using a non-sequential ID as the Primary Key, such as a GUID.

Interlude: The reason behind using a GUID?  Well, scaling out using shards should also allow scaling in, i.e. merging databases.  If your Primary Key for a table is not unique across all shards you’ll get Primary Key violations on the merge (cue heated discussion about sequential IDs, secondary lookups, COMBs, blah, blah).

FILLFACTOR is fixed at 0 (100%) in WASD, meaning as you insert records you naturally incur page splits and fragmentation in your indexes.  This means under-filled pages and wasted space, bloating your overall index sizes.  Not only does this cost you more money, but it also has another peculiar side effect – that of mismatches in fragmentation levels between replicas.  In a multi-tenant architecture, you have no control over the physical nodes on which your databases reside, nor their secondaries, and the Azure fabric controller performs lots of black box operations unbeknownst to us such as moving databases between physical nodes when necessary.  Continuing our example, imagine you have the following physical setup:

Now suppose Node B becomes extremely busy due to other tenants residing there.  Your replication is struggling to complete slowing down the transactions in the primary, and the fabric controller ultimately decides the database needs to be moved to a different node:

The process of moving and rebuilding the database on a different node can trigger a rebuild of the indexes.  The end result?  You have replicas with different fragmentation levels.  At this point, a failover to the relocated secondary will cause a jump in space used.  The more indexes and fragmentation you have, the larger the jump.  Initially, the jump will be downwards – that’s cool right?  Works out cheaper, and fragmentation is fixed.  But how about if we fail back?  Yup, our space used in the database suddenly increases (assuming the failover is to the former primary database, and that nothing has triggered a rebuild of it in the meantime).

No doubt you’re keeping track of how full your databases are though, so there’s no way the jump would take you up to your maximum database size, right?  That’s ok then.

As I alluded to earlier, defragging your indexes regularly can mitigate against this scenario.  But CAUTION – the same rules and limitations apply.  There is limited transaction log usage, tempdb, and it’s a large physical operation so there is increased risk of throttling.  Rebuilding ONLINE reduces the transaction size, but with large indexes (multi-GB) you still run the risk of it failing.  My advice – consider a nice old-fashioned technique called Partitioned Views (see http://technet.microsoft.com/en-us/library/ms190019%28v=sql.105%29.aspx).  If no logical partition exists on a table, create one using a hash function.  It increases the administration overhead a little, but allows you to reduce your index sizes by splitting the data into separate partitions.

Rebuilding indexes in WASD?  Easy peasy.