Your Production Server has crashed and everyone is pointing fingers as to who is to blame. Or maybe you just want a new development SQL Server for your lab. How do you get one up and running quickly? Here’s how!
This article will walk through the basic steps required to set up a new SQL Server 2014 instance, complete with a sample database. This article assumes that you have Windows 2012 R2 or Windows 8 installed on your machine, along with .NET 4.5 or above, SQL Server Express 2014, Microsoft Visual Studio 2013 (optional), and downloads from microsoft.com. It also requires an understanding of PowerShell, either by using it directly or having a good reference close at hand – I would recommend the “Windows PowerShell Step By Step” eBook by Ed Wilson, aka “The Scripting Guy”.
A Word of Warning
Before I start this walk-through I feel that you should be aware that this process will create a SQL Server that is not connected to your active directory domain. This means that if you want to login with a Windows user account then it needs to have local administrator permissions on the machine on which it is used. Consider creating separate service accounts for each of your users or use built-in SQL server accounts instead of using Windows logins. Also, any pre-existing databases/tables on the SQL Server will also need their own service accounts since they are not part of an active directory (I’ll show you how to do this in the walk-through).
Although it’s not necessary, I like to make use of an SSD drive for the SQL Server installation process because it will greatly increase performance in most cases. It can be used in dev/test scenarios when you don’t need to worry about production data since everything is thrown away anyway (this doesn’t mean that you shouldn’t protect your production environment, just that you can afford to create a throwaway dev/test environment without impacting your business).
Let’s Get Started!
- First things first; let’s download all of the components we’re going to need. You can skip downloading them if they are already on your machine. Open PowerShell and connect to the Internet: $Conn = New-Object System.Data.SqlClient.SQLConnection $Conn = New-Object System.Data.SqlClient.SQLConnection $Conn .ConnectionString = “Server=server;Initial Catalog=master;Integrated Security=true;” $Conn .Open ( ) Next, we need to download the SQL Server installation files, which are located on Microsoft’s website here: http://www.microsoft.com/sqlserver/en/us/get-sql-server/. At the time of this writing I downloaded the following 14 items (yes it is a lot of files):
For reference, you can also get them all in one zip file by clicking here (this should be much faster than using the individual links above). Place them in any directory and we’ll use them in the next step:
- Next, we need to download and unzip the “Sample Databases” zip file from Microsoft’s website here: http://msftdbprodsamples.codeplex.com/. Again, you can choose to place this zip file wherever is convenient as long as all of its contents are placed into a single directory (don’t just unzip it directly under C:\ drive). .NET Framework 4.5 – this will be used by SQL Server Express 2014; make sure you don’t install any version higher than 4.5 (the higher versions won’t work with our lab) Management Studio Community – formerly known as SQL Server Management Studio Express (we’ll use this to connect to our SQL Server instance) Sample Databases for SQL Server Express – contains several sample databases and other scripts we’ll use later on Batch File to Set Environment Variables – this will give us a shell script that sets our machine’s registry keys so we can access the files above under C:\ drive without typing each full directory path PowerBoots v3.6 by Andy Prissen – this is an add-on that significantly increases your PowerShell session performance Batch File to Stop SQL Server (and a link to a page with more information about it)
The “Sample Databases” zip file should look like this after you’ve unzipped it:
- Next, let’s create a directory called “%USERPROFILE%\SQL2014” where “%USERPROFILE%” will refer to your Windows account’s home directory (e.g., C:\Users\Gunther for my user). We’ll use this as the root of our SQL Server installation: mkdir “%USERPROFILE%\SQL2014” If you don’t already have a PowerShell profile script then create one now by following these steps: Open PowerShell as administrator In the command window, type notepad $profile and press Enter On the first line, which should be blank, type “. $profile”* Now press Ctrl+S to save your changes and close Notepad. The * at the front ensures that any changes we make to our profile script in the future are automatically saved so we don’t have to manually save it anymore or restart PowerShell. Your profile script should look like this:
Conclusion:
We’re done with PowerShell for now and we’ll use it again, later on, to start our SQL Server install and configure our lab.
