A/D Test Lab, Introduction

The challenge:
I need to create a test lab using Active Directory, so I can run SQL Server in various testing configurations. Also, having my own domain under total control makes me giggle with uncontrollable personal glee, so there's that.

Anyway, back to the point.

I need to create a test lab, which has the following requirements:

1. Fully self-contained
All domain traffic should stay within the domain. This is important, as quite a bit of the work I want to do on this is going to involve being offline or on slow connections, and if my servers are hanging and timing out waiting for the slow internet connection I'm on at the time, I'm never going to get anything done.

2. Fully configured
This can't be a single-box setup, or I won't be able to configure things like SQL clustering. This has to be an actual domain, with a primary domain controller, member servers, etc.

3. Easily destroyed
One of the requirements for doing this is that I have to be able to blow away and bring back the entire thing within minutes. I don't have the patience to wait for hours for machines to build - ultimately, I want the entire thing, from pushing the "File >> New VM" button to starting the install of SQL to be as close as possible to 5 minutes as I can get.

4. Free
This entire setup HAS to use evaluation copies or release candidates. It has to work with what I can get for free, so I can be sure that not only can I do it for free, but anyone who follows along at home can do so, too.

I'm presenting below a series of articles about how I did exactly that. I'll cover these four steps

• Building the machines
• Stand up the domain
• Add a domain user
• Add the member server

SQL Server Error 0x8004100e

You might be forgiven for thinking that SQL Server 2014 components, every single one of them, should be backwards compatible with 2012.

For example, let's say I have a test Active Directory (AD) lab made up of two servers.

The first is the domain controller and has the SQL Server 2014 Management Tools - Complete installed on it.

The second server is the SQL 2012 machine, and it has only the database engine, although I've seen the same issue with the machines where more than just the engine is installed.

The good part of all this is that, using SQL Server Management Studio (SSMS), I'm able to use my test domain credentials and connect to the SQL Server 2012 instance, so I know SQL's working, the firewall is either proper or turned off (This IS a lab setup), and run queries, which return the expected results. That's the good news - to everyone else (users, clients, etc.) this machine looks and acts online.

The downside is that I need to administer this machine remotely, using Computer Management via Server Manager's "All Servers" tab.

And I can't, because I get the following error in Computer Management when I navigate to Server Manager >> All Servers >> *ServerName* >> Computer Management >> Services And Applications >> SQL Server Configuration Manager.

error 0x8004100e

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Noe that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid Namespace (0x8004100e)

If you right click the message, and click "Help", what you get isn't really all that helpful:

Microsoft has moved the documentation for this product online in order to provide the most current information to our customers. See the Microsoft Developer Network (MSDN) Resources page (http://go.microsoft.com/fwlink/?LinkId=299888) for more information.

Which, if you click the link, takes you to the page "Install SQL Server 2014"

Remember, now, that I'm dealing with a target server that has SQL Server 2012 on it, and I have 2014 tools on the machine I'm using to access it. Installing 2014 is a red herring - it's not the real issue!

The fix, for the test lab, was to install Client Tools Connectivity on the Domain Controller from the SQL Server 2012 media, and then, not surprisingly, to reboot.

Had this been a production domain, and not just a test lab, I think I would differently about the need to reboot, but there you have it!

Atlanta Bound!

I'm going to be presenting in Atlanta this weekend at SQL Saturday 521 on 21 May. I hope to see you there!

2016 RC3 Samples

SQL Server 2016 RC3 has it's own set of samples ! 

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/10/29/samples-for-sql-server-2016-ctp3/

Baltimore Presentation

Thanks to everyone that came out to my Session on Auditing in SQL Server!

Credit and a "Thanks!" to Slava for the picture