The Availability Group Wizard and the HADR Seeding Test Database

If you are creating an Availability Group in SQL Server 2012 using the New Availability Group Wizard in SQL Server Management Studio, you might not realize some of the background tasks that the wizard performs when it performs its validation steps.  One of those steps is to create a temporary database on the current server that has a name in the format of [HADR Seeding Test <UniqueIdentifier>], which is then backed up to the network share location specified, and then each of the secondary replica’s attempts a restore of the backup from the network share to validate that the appropriate permissions exist for the service accounts to the share.  This can all be seen in the Default Trace information on each of the servers.

The Default Trace from the server that the wizard is being run against as the Primary will show:

image

The secondary replicas being configured will show:

image

This is strictly an artifact of how the wizard validates the configuration being provided to ensure that it will be able to successfully create the Availability Group.  If you create the Availability Group from scripts or through PowerShell and manually perform the backup/restore operations this database will not be created.

Enlarging the AdventureWorks Sample Databases

The AdventureWorks set of sample databases are commonly used for presentation demos and showing how to use SQL Server, but one of the biggest challenges in using these databases is their small size which is geared towards ease of distribution, more than demonstrating all of the functionality of SQL Server.  The concept of creating a larger version of AdventureWorks is not new, Adam Machanic (Blog|Twitter) blogged last year about enlarging specific tables for demonstrations, and at the same time I found that I also needed to create tables that were larger datasets for my own purposes.  The tables that I chose to enlarge for demonstration purposes were the Sales.SalesOrderHeader and Sales.SalesOrderDetail tables which can be used to demonstrate a number of different query plan and performance issues in SQL Server.  Below is a script that can enlarge these tables into tables named Sales.SalesOrderHeaderEnlarged and Sales.SalesOrderDetailEnlarged in the specific AdventureWorks database being targeted.  The attached script has been tested against the AdventureWorks2008R2 and AdventureWorks2012 databases specifically, but as long as the table schema is the same it may be applied to any version of AdventureWorks.

These larger tables can be used to produce parallel execution plans, plans that have large execution memory grant requirements, plans that perform sort and hash spill operations through tempdb, and many other uses in SQL Server.  The size of the tables can be scaled by running the INSERT code block multiple times to increase the size of the Sales.SalesOrderHeader table by roughly 225MB, and to increase the size of the Sales.SalesOrderDetailEnlarged table by roughly 400MB per execution after the index rebuilds are run.

Create Enlarged AdventureWorks Tables.sql (8.43 kb)