SQL Server 2012 Test Setup

I’m often asked what I would recommend for a development or test environment on a local machine for working with SQL Server 2012.  Often, requirements increase or change with the next, greatest version of SQL Server but when it comes down to it, for testing, this setup is the same for SQL Server 2008.  Keep that in mind if you are in need of a 2008 solution and not ready for 2012.  This article will describe my setup.  There will be some follow-up articles that will go through the steps that are needed to mimic the setup.

So, what is the setup?

I first have to consider what I’m in need of testing; configuring and scripting those configuration changes out, developing for, collecting performance or, simply, looking into the internal mechanisms of SQL Server and features.  This leads to having not one but several virtualized configurations available.

I place these into two categories.

  1. SQL Server Setup/Configuration/Operations
  2. SQL Server Integration/System utilizations
  3. I said 2 but 3 retains both 1 and 2 for articles, presentations and blogs so it deserves a 3.

This covers many situations where SQL Server would be needed.  Especially as a consultant, preconfiguring a setup is a must.  Recording that setup is even more important.  This setup is close to a “real-life” configuration (with the exception of more nodes added).  So this could be utilized to run through an Availability Group configuration, Cluster configuration, Cluster failover testing, Availability Group testing, or more.  It also provides me with space for testing SSAS, SSIS and SSRS with SharePoint from configuration to deployment and development.  Truly, there isn’t much we couldn’t do with this other than older version support.

One important note: make clones of all your stripped setups.  This means, get Windows Server 2012 setup – clone it, get Server Core setup – clone it, get SQL Server 2012 SP1 setup – clone it, get a Domain setup – clone it, get Windows Admin machine setup – clone it.  Save those clones off on an external drive. When the testing causes issues in recovering the initial setup, simply put it back to a snapshot or put the full clone over the current virtual machine.  Just remember, configure the network settings, DNS and so on before cloning and making snapshots.  This will make replacing machines easier and quicker.  They will simply drop in and start working together again.

SQL Server/Configuration/Operations

This encompasses the need for one or more SQL Server instances at my disposal for determining configurations that I would need such as Availability Groups or digging into a performance problem and stressing a system while tuning problems.

This setup is as follows

Or, as it appears in VMWare

Of course, not all these virtual machines are running nonstop.  Allocating enough resources to them on a laptop would be next to impossible, if they were.

The setup contains a 3-Node cluster running Windows Server 2012 Server Core, SQL Server 2012 SP1 and availability groups set up for synchronous commit to Node-2 and asynchronous commit to Node-3.  Another machine is set up and acting as a domain controller and file services.  All the database files are placed on the server set up for file services.  This way, the Server Core machines are constrained to resources they will only need to ensure the task being performed, will be successful.

As a second stage or area of this setup, 3 other nodes are configured and running Windows Server 2012 and SQL Server 2012 SP1.  This is due to Server Core simply not being able to perform some tasks that are needed.

The last part of this setup is a virtual admin control machine running Windows 8 Enterprise.  This machine is a key piece to the entire setup.  The admin control machine runs all server tools for configurations and monitoring needs while running any test or processing.  The control machine also acts as a deployment machine to the cluster or SQL instance.  The control machine is also needed more as the entire network this setup is done on is completely set on an island from the host network.  With that, internet connectivity is more difficult to access or setup.  While it can be done, I chose not to so the interference of that variable is not part of the work I am performing.

SQL Server Integration/System utilizations

This setup is almost identical to the first setup but it has a completely different configuration need and feature services installation performed.  The cluster is set up the same: 3-Node, Windows 2012 Server Core and Full GUI, SQL Server 2012 Instance SP1.  However, this installation is configured with services like SSAS, SSRS and SharePoint over the SQL Engine.  Each SQL Server instance takes on the configuration needs that those services require while utilizing SQL Server.  The OS is configured slightly differently as well as the VM client and resources allocated.  In all, the setup is a clone but requires a different level of configuration.

How the ** do you run all that?**

Of course, all this running on a regular laptop is rough to chew on.  Luckily, Solid State Disk (SSD) has come down a great deal in cost in the last year.  In order to truly get this setup to function well, you’ll need a laptop maxed out on RAM (16 to 32GB) and optimized with one or more SSD drives.  For my setup, I run an SSD in a drive caddy instead of having a DVD ROM.  Not much comes on DVDs these days, and over the last 4 months, I have not had to plug the DVD ROM into a USB port once.  In addition, I enlist one external SSD drive and another external HDD.  The SSD holds as many virtual machines as I can manage on it. The slower HDD drives are used for file storage, database file storage and anything that I can manage to use on slower disk.  Much of the integration/system utilizations (a fancy way of saying, applications) run on the HDD drive.

All of this is separated from the local HDD that is running the actual laptop.  It’s important to manage the resources much like managing a virtual server – host to client.  My host, the laptop, is managed as much as possible so it will not consume more than 6-8GB of RAM and everything is set to the C Drive, or local HDD that it came with.  At some point, the primary will be converted to SSD, but for now, that isn’t in the budget.  As much as the host is monitored for resource usage, the clients are as well.  They are started and stopped only during the times I need them.  For example, the domain controller could be set to run in the background but that would consume 1.5GB of RAM and make the IO on the caddy SSD work a little harder.  There is a local instance on the primary of SQL Server that still utilizes the caddy SSD for anything being done like working with execution plans, designing databases and so on.  So there is a line that is crossed.  That is the game of using all the resources as efficiently as possible.  Since I have the ability to turn things off and on, it makes it easier not to choke the laptop altogether.  That being said, all local services are always set to manual startup.  There will be no rogue instance on this machine.  It simply cannot handle that occurrence or it may be starved to death (or, blue screen).

Windows 8 Pro

With everything I’ve mentioned here on how I set my laptop up for what I need, there is one important part to all of this: Windows 8. Windows 8 has provided me with a much easier to manage experience with all of this.  There is a nice separation as I move between the virtual experience and back to the laptop itself.  I would have to say, Windows 8 has provided a minimum of a 20% increase in my overall productivity working with a laptop 24⁄7.

What’s up next?

As a follow up to this, I’ll run through setting up one part of a 2-Node Server Core 2012 cluster, SQL Server 2012 HA, domain controller, and Windows 8 admin control machine.  If you are limited to a less beefy laptop, you can get away with the 2-Node setup for doing most of what you need.  With resources, if they get to a certain level, we need to move to either a standalone machine or a server chassis.  Remember, these are just laptops.  This will not be restricted to just these setups either.  Take advantage of virtualization and make a virtual machine of as many servers and machines you may run into.  One 500GB external HDD can contain Windows 2003, 2008, 2012 Servers, SQL Server 2005, 2008, 2012 instances, Windows 7, 8 machines, Domain Controllers, File Servers…you get the point.

If you can’t wait for the articles showing the stages of setting this up, the below links will get you through the complete setup.   These links were collected during the process of setting things up and proved valuable to completing it.