Microsoft SQL 2012 Unattended Installation

A few years ago I wrote a blog post detailing an unattended SQL 2012 installation process. That article needs a bit of updating, so I’m creating a fresh post. I will also follow up on this post with SQL 2014 and SQL 2016. This time around I’m using Windows Server 2012 R2, and giving you direct download links to my example files. Customers were telling me that cut/paste from the blog resulted in strange quotes in some languages, which had to be replaced with regular quotes. This unattended script will install a basic SQL 2012 server. If you wish to install additional components, then you can modify the .ini file. Before you rush through the installation, you will need to modify the .ini file which you download. At the minimum, change the paths to your desired locations. In my script I’ve used multiple drives:

C: OS
D: SQL Binaries
K: SQL Databases
L: SQL Logs
T: TempDB
Z: CD-ROM

So let’s get started on getting SQL 2012 installed via an unattended script. First, SQL 2012 requires .NetFramework 3.5. Unfortunately, in the basic Windows Server 2012 R2 install, this is an optional feature that is not installed. Frankly I think the easiest way is via the GUI, although you can use PowerShell as well.

  1. Open the Add Roles and Features wizard.
  2. Click through the wizard until you get to the Features section.
  3. Expand .Net Framework 3.5 Features and tick the box next to .NET Framework 3.5. 2017-02-28_14-52-04
  4. Click Next, then enter the path to the .Net Framework 3.5 binaries. At this point, mount your Windows Server 2012 R2 ISO to the VM, then use the path CD-Drive:\sources\sxs2017-02-28_14-53-34
  5. Wait for the installation to complete, and just to be safe, reboot the VM.

At this point .Net Framework 3.5 is installed, and I’m assuming you have your various drives mounted, formatted, and ready to go. Next, download the two files below and save them to the root of your D drive.

SQL-2012-base.ini
SQL-2012-base.cmd

Open the SQL-2012-base.ini file with your favorite editor and modify all of the paths. They are sprinkled throughout the file, so be sure to check every line. Save the ini. If your CD-ROM is not using the Z: drive, open the SQL-2012-base.cmd file and change the path as needed. If you aren’t using a D drive and have these scripts elsewhere, change the path to the ini file.

6. Open a command prompt and run the SQL-2012-base.cmd file with an argument of the group which you want to be added to the SQL studio to administer the instance. Use the format domain\group. If you just want a local group, you can use MachineName\Group. 2017-02-28_15-26-36   7. Sit back and relax, and give it 10 minutes or more. You will see the SQL installer GUI flash through various screens in an automated fashion. If the install was successful, at the end you should see:

2017-02-28_16-02-58

And there you go! If you are installing multiple SQL servers using the same configuration, I highly recommend the unattended method.

Ignite 2015: Benchmarking SQL AlwaysOn

Session: BRK3557: Baselining and Benchmarking AlwaysOn Availability Groups

In this session the speaker went through what SQL AlwaysOn availability groups is, and why the customer wanted to use it. Then he went through how he setup his testing, RAID levels, and listed the SQL perform stats that he monitored during the benchmarking. The speaker used a scripted run of SQLIO to perform his benchmark tests. He covered SQL IO sizes, number of threads, and how to scale up to simulate the customer’s environment.

He went into a long discussion about max threads, and how the type of query affects how many threads are spawned. SQL has a max number of worker threads, so understanding how many threads you are spawning when doing at-scale testing is important. He also tuned the cost threshold for parallelism to control the number of spawned threads.

In  the end, he was successful in performing at-scale benchmarks and the customer’s system was implemented successfully. Be sure to check out the session recording for all of the gory details.

 

 

vSphere 6.0 Install Pt. 7: Config SQL DBs

Now that we have the Windows PSC installed, it is time to prepare for installing vCenter. vCenter can support three database types: embedded vPostGres (supports up to 20 hosts and 2000 VMs), Microsoft SQL, and Oracle. SQL seems to be the most popular choice, so that’s what I’ll help you configure here. Now to be frank, nothing has really changed here in vSphere 6.0 for the SQL setup. But it does fully support SQL 2014, which is great. Note: VUM 6.0 does not seem to support SQL 2014. So check VMware docs to verify compatibility when you go to install. To find out if your particular SQL version is supported, you can check out the VMware Product Interoperability Matrixes. Be sure to select “Solution/Database interoperability” so you can view the supported Oracle and SQL databases. Double check VUM!

Do take note that VMware fully supports “legacy” SQL failover clusters for the vCenter database. This is distinctly different from AlwayOn Availability Groups, which are currently NOT supported. Nag your VMware TAM about AlwaysOn Availability Group support. I wrote an entire blog series about setting up a SQL 2012 failover cluster, which you can check out here. It’s nearly the same steps for SQL 2014.

Blog Series

vSphere 6.0 Install Pt. 1: Introduction
vSphere 6.0 Install Pt. 2: Platform Services Controller
vSphere 6.0 Install Pt. 3: Certificate Management
vSphere 6.0 Install Pt. 4: vCenter Upgrade Best Practices
vSphere 6.0 Install Pt. 5: ESXi Upgrade Best Practices
vSphere 6.0 Install Pt. 6: Install Windows PSC
vSphere 6.0 Install Pt. 7: Config SQL DBs
vSphere 6.0 Install Pt. 8: Toolkit Configuration
vSphere 6.0 Install Pt. 9: SSL Templates
vSphere 6.0 Install Pt. 10: Install VCSA PSC
vSphere 6.0 Install Pt. 11: VMCA as Subordinate
vSphere 6.0 Install Pt. 12: PSC Machine Certificate
vSphere 6.0 Install Pt. 13: Directory Services Certificate
vSphere 6.0 Install Pt. 14: Windows vCenter Install

Permalink to this series: vexpert.me/Derek60
Permalink to my Toolkit script: vexpert.me/toolkit60

Create DB Files

VMware unfortunately does not provide a tool to automatically create your SQL database for you. So it’s up to you to size and configure the SQL databases prior to installing vCenter. You must also configure the proper DSN, and install the appropriate SQL client. Since VMware left these tasks up to the customer to do, I’ve included them in my vCenter toolkit to help expedite your installation process.

My vCenter toolkit script was very popular for 5.5 users, so I’ve updated the script for 6.0. Some of the SSL work isn’t quite done, so I’ll be releasing future updates to complete the SSL setup. But the current version does support the SQL DB creation, so let’s get to work.

1. Go to this permalink (here) and download my PowerShell script. To create the SQL databases you can run the script from anywhere. But for simplicity I’d suggest running it on what will be your vCenter server. Run the script, and you should see a menu similar to the screenshot below. Menus may change a little between releases.2015-04-05_8-27-11

2. On the main menu select Option 1 to open the SQL Database menu. Select the option to create the vCenter and VUM SQL database file (Option 1). You will then be prompted for a series of responses, to properly size the database and log files for both vCenter and VUM. The screenshot below shows all of the prompts, and example configuration.

2015-03-24_13-06-52

3. After the configuration file is written, copy it over to your SQL server and open it in SQL Studio. Modify the paths to the files as needed, then run the script. You should not have an errors, and two databases should now appear on your SQL server.

2015-03-24_13-31-30

4. During my vCenter testing I found that even though the service account was DBO on the two databases, the vCenter installer complained. So for installation purposes, I gave the service account temp ‘sysadmin’ permissions at the SQL level, as shown below.

2015-03-24_13-47-16a

4. Back on the vCenter server run the Toolkit script again but this time we need to create the vCenter DSN. Select that option from the menu, option 2 in the version shown. Enter the required information, then download and install the SQL client as indicated.

2015-03-24_13-40-28

5.  Just to make sure the DSN will work, launch “odbcad32.exe”, click on System DSN, then find your vCenter DSN. Click on Configure, click Next through the whole wizard, then click on  Test Data Source. Verify success.

2015-03-24_13-51-45

6. If you are going to use VUM, then we need to repeat a similar process to create the DSN and test the connector. Using my Toolkit script, select option 3. Follow the prompts to create the DSN, then from the Windows start screen search for “data” and select the ODBC Data Sources (32-bit) option. Perform a DSN test and verify success. Again, verify with VMware which SQL version VUM 6.0 supports. VUM has not been updated in ages, and may NOT support SQL 2014.

Summary

We’ve now created both the vCenter and VUM databases in SQL, configured the ODBC connectors, and verified they work. The final step in getting vCenter up and running is actually installing vCenter using the databases we just created. But before we install vCenter, let’s configure my vCenter toolkit script and download our root CA public certificates, here in Part 8.

vSphere 5.5 Install Pt. 17: Install VUM

11-10-2013 1-43-42 PMSo yes, after a couple of weeks of pausing on the vSphere 5.5 series I’m back with installing VUM. The VUM install pretty much follows the process we had for vCenter 5.1. In case you haven’t heard, VUM is also growing feathers like the Windows VI client and will soon turn into a dodo bird. VMware hasn’t announced what is replacing it, but I suspect in vSphere 6.0 the new product will make a debut.

Blog Series

SQL 2012 AlwaysOn Failover Cluster for vCenter
vSphere 5.5 Install Pt. 1: Introduction 
vSphere 5.5 Install Pt. 2: SSO 5.5 Reborn 

vSphere 5.5 Install Pt. 3: vCenter Upgrade Best Practices and Tips
vSphere 5.5 Install Pt. 4: ESXi 5.5 Upgrade Best Practices and Tips 
vSphere 5.5 Install Pt. 5: SSL Deep Dive
vSphere 5.5 Install Pt. 6: SSL Certificate Template
vSphere 5.5 Install Pt. 7: Install SSO
vSphere 5.5 Install Pt. 8: Online SSL Minting
vSphere 5.5 Install Pt. 9: Offline SSL Minting 
vSphere 5.5 Install Pt. 10: Update SSO Certificate
vSphere 5.5 Install Pt. 11: Install Web Client
vSphere 5.5 Install Pt. 12: Configure SSO
vSphere 5.5 Install Pt. 13: Install Inventory Service
vSphere 5.5 Install Pt. 14: Create Databases
vSphere 5.5 Install Pt. 15: Install vCenter
vSphere 5.5 Install Pt. 16: vCenter SSL
vSphere 5.5 Install Pt. 17: Install VUM
vSphere 5.5 Install Pt. 18: VUM SSL
vSphere 5.5 Install Pt. 19: ESXi SSL Certificate

Permalink to this series: vexpert.me/Derek55
Permalink to the Toolkit script: vexpert.me/toolkit55

Installing VUM

1. Login to your vCenter server with your vCenter Windows service account and launch the VMware vSphere 5.5a (or later) installer. Select vSphere Update Manager in the left pane.

2. Select your language and click OK. Click through the wizard until you get to the Support Information screen. If your vCenter server has internet access, then you can leave the box checked to download updates. If it does not have access, then uncheck the box.

11-10-2013 11-26-34 AM

3. At this point you should use the built-in vSphere administrator account (administrator@vsphere.local). I had no luck in trying to get my domain vCenter service account to work (although comments from a prior post indicated it worked for them). If the installer hangs, then kill the vciInstallutils.exe process, completely exit the installer, and re-run the installer. It seems to cache failed responses, so even if you enter the right password the second time it may still fail.

11-10-2013 12-19-13 PM

4. The DSN should already be present from when we ran my Toolkit script, so nothing to do here but select it. Note: There is a KB article here that describes a problem with the VUM service starting if you use the SQL 2012 client. Personally I haven’t run into the problem. The solution is to use the SQL 2008 R2 client (paired with a SQL 2012 back-end if you have one) to allow the service to start.

11-10-2013 12-24-39 PM

5. Click through the wizard (you may get a SQL recovery mode warning) and on the Port Settings page I would recommend selecting the vCenter FQDN verses the IP address.

11-10-2013 12-27-05 PM

6. I strongly recommend you change the download path for patches. You don’t want to fill up your C drive.

11-10-2013 12-28-36 PM

7. After VUM has finished installing, we need to change the account under which the VUM service runs. Open the Service Manager and locate the VMware vSphere Update Manager service. Change the Log On account to that of your vCenter service account. Restart the service and verify it starts successfully.

11-10-2013 1-22-59 PM

Yup, it’s pretty easy. My biggest headache was finding an account that worked in Step 3. Thankfully the built-in SSO administrator account did the trick. Next up will be replacing the VUM SSL certificate. You can check that out in Part 18.

vSphere 5.5 Install Pt. 14: Create Databases

10-12-2013 6-35-21 PMWe are just one post away from installing the actual vCenter service! Now that the rest of the infrastructure is ready, we need to create a service account, databases and DSNs. After all of these steps are completed we can rejoice and very shortly have a working vCenter server.

Remember that database sizing is highly dependent on your environment, and DBA preferences. So be sure to use a sizing tool (such as the one included in vCenter), and the VMware VUM sizing estimator tool. You neither want to way oversize or undersize your databases. I’m also opting to use a Windows service account for the ODBC authentication mechanism. While this is not required, I’ve done this for years and think it’s a best practice.

I’ve updated my Toolkit script to v1.2, which includes the SQL and DSN creation options. Please download the latest version from the link below.

Blog Series

SQL 2012 AlwaysOn Failover Cluster for vCenter
vSphere 5.5 Install Pt. 1: Introduction 
vSphere 5.5 Install Pt. 2: SSO 5.5 Reborn 

vSphere 5.5 Install Pt. 3: vCenter Upgrade Best Practices and Tips
vSphere 5.5 Install Pt. 4: ESXi 5.5 Upgrade Best Practices and Tips 
vSphere 5.5 Install Pt. 5: SSL Deep Dive
vSphere 5.5 Install Pt. 6: SSL Certificate Template
vSphere 5.5 Install Pt. 7: Install SSO
vSphere 5.5 Install Pt. 8: Online SSL Minting
vSphere 5.5 Install Pt. 9: Offline SSL Minting 
vSphere 5.5 Install Pt. 10: Update SSO Certificate
vSphere 5.5 Install Pt. 11: Install Web Client
vSphere 5.5 Install Pt. 12: Configure SSO
vSphere 5.5 Install Pt. 13: Install Inventory Service
vSphere 5.5 Install Pt. 14: Create Databases
vSphere 5.5 Install Pt. 15: Install vCenter
vSphere 5.5 Install Pt. 16: vCenter SSL
vSphere 5.5 Install Pt. 17: Install VUM
vSphere 5.5 Install Pt. 18: VUM SSL
vSphere 5.5 Install Pt. 19: ESXi SSL Certificate

Permalink to this series: vexpert.me/Derek55
Permalink to the Toolkit script: vexpert.me/toolkit55

Create vCenter Service Account

1. In active Directory create a vCenter service account. Make sure the password is set to not expire and use a complex password.

2. Add the service account to the local administrator’s group on the vCenter server. You need to directly add the service account into the Administrator’s group. Nested group membership seems to cause the installer problems.

10-12-2013 10-02-30 PM

3. Make sure the service account has Act as part of the operating system user right.

10-12-2013 8-01-52 PM

4. For the user right to take effect you must reboot your vCenter server. Please reboot now, then login as the service account before proceeding.

Create vCenter and VUM Databases

1. Make sure you are logged in as your vCenter service account before proceeding. Run v1.2 or later of my Toolkit script (See Part 8 for more details) and on the main menu select the Create vCenter and VUM SQL database file option. You can download the Toolkit script from the link in the top of this post.

10-12-2013 7-42-51 PM

2. After you select that option you will be prompted for a few database details. Enter the vCenter and VUM database names along with your vCenter service account name. Copy the file to your SQL server and open it in SQL Server Management Studio.

10-20-2013 6-59-54 PM

3. Once the script is open, change any additional parameters such as database sizes and paths. If you followed my SQL 2012 Failover Cluster series, the paths in the sample file should match your installation. Both the vCenter and VUM databases are configured in this script. Execute the script.

10-16-2013 8-51-15 PM

Create DSNs

Note: There is a bug in vSphere 5.5 which causes the VUM service to fail if the SQL 2012 ODBC connector is used. You must use the SQL 2008 R2 SP2 native client, even if the SQL server is 2012. I haven’t updated my Toolkit script to address this issue, so please select SQL 2008 during the DSN creation.

1. vCenter and VUM use an ODBC connector to communicate to the SQL server. The ODBC connector needs the native SQL client to communicate to the SQL server. My Toolkit script (see link above) will download and install the right native SQL client, if your vCenter server has internet connectivity. If it does not, just download the right client below and install it. The Toolkit will detect it’s installed and won’t nag you to install it.

64-bit Microsoft SQL Server 2008 R2 SP2 native client
64-bit Microsoft SQL Server 2012 SP1 native client

2. Launch my Toolkit script and select the Create vCenter DSN option.

10-12-2013 7-52-33 PM

3. The script will prompt you with a series of questions so that it can create the 64-bit system DSN. Answer according to your environment. Only select the SSL option if you’ve configured your SQL server for SSL encryption. It must be enabled on the SQL side or the connector will fail.

10-12-2013 7-54-40 PM

4. Repeat the process for the VUM DSN, but select option 7 instead.

5. Open the Windows Server Manager and from the Tools menu select ODBC Data Sources (64-bit). You should see two System DSNs listed, one 64-bit and one 32-bit.

10-12-2013 8-13-34 PM

6. Click on the vCenter Server entry and then click Configure. Run through the wizard until you get to the final page. Validate the settings all look correct.

10-12-2013 8-17-15 PM

7. Click on Test Data source and verify the test is successful. If it is not, then you probably goofed up the server name, database name, permissions, or the SQL firewall is not allowing the connection. Remember if you are clustering the SQL database to configure firewall rules on BOTH nodes.

10-12-2013 8-18-45 PM

8. Close the 64-bit ODBC tool and Open the 32-bit ODBC tool from the Server Manager Tools menu. Repeat the verification process on the VUM database.

Summary

Now that we have a working service account, created our databases, and configured the ODBC connector we are ready to install vCenter. So yes, that’s coming up in Part 15.

SQL 2012 Failover Cluster Pt. 11: Jobs n More

9-26-2013 8-57-52 PMThe SQL 2012 failover cluster series is finally starting to wind down. While this is not the last installment, it’s pretty darn close. In this session we configure some critical SQL maintenance checks and run a configuration check script to see how badly our SQL server is configured. A big shout out to Brent Ozar and Ola Hallengren for their outstanding SQL Server scripts and blog resources. These scripts are must-haves for all SQL installations.

Blog Series

SQL 2012 Failover Cluster Pt. 1: Introduction
SQL 2012 Failover Cluster Pt. 2: VM Deployment
SQL 2012 Failover Cluster Pt. 3: iSCSI Configuration
SQL 2012 Failover Cluster Pt. 4: Cluster Creation
SQL 2012 Failover Cluster Pt. 5: Service Accounts
SQL 2012 Failover Cluster Pt. 6: Node A SQL Install
SQL 2012 Failover Cluster Pt. 7: Node B SQL Install
SQL 2012 Failover Cluster Pt. 8: Windows Firewall
SQL 2012 Failover Cluster Pt. 9: TempDB
SQL 2012 Failover Cluster Pt. 10: Email & RAM
SQL 2012 Failover Cluster Pt. 11: Jobs n More
SQL 2012 Failover Cluster Pt. 12: Kerberos n SSL

SQL Maintenance Script

Many people, it seems, just install SQL server and forget it about. They don’t realize that SQL server is just like a race car. If you leave it alone and don’t do routine maintenance it will fall apart. To that end, Ola Hallengren has the de-facto database maintenance scripts, that I’ve been told Microsoft uses extensively internally.

Download the MaintenanceSolutions.SQL script from here. Just run the script in SQL studio and it will create a number of maintenance jobs for you. It gets regular updates, so check back every few months for improvements. After you run the script you should see the following list of jobs under the SQL Agent.

9-26-2013 7-53-54 PM

SQL Blitz Script

I first heard Brent Ozar at TechEd a couple of years ago in a SQL performance session. Besides being able to double as a stand up comedian, he’s one of the world’s few Microsoft Certified SQL Masters and an outstanding speaker. Much of the material for this series came from his site. He has some really funny tweets.

Besides having a blog full of great material, his team has developed the sp_BLITZ script. It performs dozens of SQL configuration checks and warns you about potential issues. This script is also regularly updated to add new checks and support new SQL versions. Plus his site has links to many common issues that the script highlights and provide remediation guidance. You can download the script here.

After you download the script, execute it in SQL studio. You won’t see much happen, since this execution just installs the stored procedure it doesn’t actually run it. We will execute the checks a bit later.

Database Integrity Checks

1. In SQL Server Management Studio locate the SQL Server Agent node. Right click on DatabaseIntegrityCheck – SYSTEM_DATABASES and select Properties.

2. On the Schedules page configure the daily job as shown below, and then click OK.

9-26-2013 8-13-26 PM

And the end result is….

9-26-2013 8-14-22 PM

Click on the notifications page and enable email and application log events. I suggest ‘when the job completes’ email alerting.

9-26-2013 8-16-19 PM

3. Repeat the same configuration process for DatabaseIntegrityCheck – USER_DATABASES but stagger the job run, say an hour later.

4. Run both jobs.

Index Optimizations

1. In SQL Server Management Studio locate the SQL Server Agent node. Right click on IndexOptimize – USER_DATABASES and select Properties.

2. On the Schedules page configure the weekly job and then click OK.

9-26-2013 8-21-42 PM

Configure the same notification settings…

9-26-2013 8-22-54 PM

Database Backups

1. Turning on backup compression is recommended. Do this by running the following SQL query.

USE master;
GO
EXEC sp_configure "backup compression default", "1";
RECONFIGURE WITH OVERRIDE;

2. In SQL Server Management Studio locate the SQL Server Agent node. Right click on DatabaseBackup – SYSTEM_DATABASES – Full and edit the job steps. Open step 1 and modify the backup path as needed. In my case I changed it to S:\Backup. This should be on the S drive, so that backups are available to each node.

9-26-2013 8-34-10 PM3. Modify the other backup scripts to change the path as well.

4. Execute the DatabaseBackup – SYSTEM_DATABASES – Full job and validate that it runs successfully.

SP_Blitz

Now that we’ve performed backups and integrity checks, its time to run Brent’s store procedure. Type exec sp_blitz and review the output at the bottom of the screen. Not all issues need to be corrected. Some are informational. Brent has a lot of detail for some issues the script finds here. I would run this on a periodic basis, and make sure you check his site for the latest version.

9-26-2013 8-49-26 PM

Summary

In this section we have installed scripts from two of the foremost SQL authorities in the world. We also configured scheduled maintenance jobs to kick off to preserve DB performance and validate their integrity. In the last installment of this series we will configure Kerberos and SSL before calling it a day. Check out Part 12 here.

SQL 2012 Failover Cluster Pt. 9: TempDB

9-24-2013 7-40-46 PMOne of the most important aspects of SQL performance is TempDB. Applications can use TempDB as a scratch space, and in most cases you should not rely on just a single TempDB file. Current best practices is to create 1/4-1/2 the number of TempDB files that you have processor cores. You don’t really want to go above 8 files, in most cases.

Another important aspect of TempDB is their size and file growth. SQL will proportionally use the TempDB databases based on their size. So it’s important to create all TempDBs with the same size and not allow for autogrow. Size them properly up front to accommodate your application requirements. If you do need to grow them in the future, grow all of them equally. All TempDBs will use a single “centralized” log file, so you won’t see multiple log files.

Just to conserve disk space the script below creates four TempDB files at 5MB each. Increase all TempDB sizes equally for your environment. I’d probably go with 1-5GB per file, if the SQL server will be under light usage and only supporting an application like vCenter.

The script renames the SA account, as a security protection measure. This is optional, but recommended. It also enables DAC, which can be used for remote administration during dire SQL server problems to possibly repair the situation and get SQL server back into production.

In addition to TempDB, the script makes other minor changes to system database sizes and auto-growth. I won’t bore you with all the details, but please consult your local DBA for system DB settings specific to your environment. In the absence of a DBA the settings below are a reasonable start for a small environment.

Blog Series

SQL 2012 Failover Cluster Pt. 1: Introduction
SQL 2012 Failover Cluster Pt. 2: VM Deployment
SQL 2012 Failover Cluster Pt. 3: iSCSI Configuration
SQL 2012 Failover Cluster Pt. 4: Cluster Creation
SQL 2012 Failover Cluster Pt. 5: Service Accounts
SQL 2012 Failover Cluster Pt. 6: Node A SQL Install
SQL 2012 Failover Cluster Pt. 7: Node B SQL Install
SQL 2012 Failover Cluster Pt. 8: Windows Firewall
SQL 2012 Failover Cluster Pt. 9: TempDB
SQL 2012 Failover Cluster Pt. 10: Email & RAM
SQL 2012 Failover Cluster Pt. 11: Jobs n More
SQL 2012 Failover Cluster Pt. 12: Kerberos n SSL

Configure TempDB and More

1. To run the script open SQL Studio, create a new query (click “New Query”) then paste in the code below. Or save the script to a .sql file and double click on it. Execute the script and it should run successfully.

9-24-2013 8-03-18 PM

9-24-2013 8-05-57 PM

/* Configures TempDB and other settings */
/* Derek Seaman, derekseaman.com */

/* Configure TempDB */

USE master;
GO
ALTER DATABASE tempdb
ADD FILE
(
 NAME = tempdev2,
 FILENAME = 'F:\TempDBData\tempdb2.mdf',
 SIZE = 5MB,
 FILEGROWTH = 0
);

ALTER DATABASE tempdb
ADD FILE
(
 NAME = tempdev3,
 FILENAME = 'F:\TempDBData\tempdb3.mdf',
 SIZE = 5MB,
 FILEGROWTH = 0
);

ALTER DATABASE tempdb
ADD FILE
(
 NAME = tempdev4,
 FILENAME = 'F:\TempDBData\tempdb4.mdf',
 SIZE = 5MB,
 FILEGROWTH = 0
);

ALTER DATABASE tempdb
MODIFY FILE
(
 NAME = tempdev,
 SIZE = 5MB,
 FILEGROWTH = 0
);

ALTER DATABASE tempdb
MODIFY FILE
(
 NAME = templog,
 SIZE = 2048MB,
 FILEGROWTH = 256MB
);

/* Configure other system databases */

GO
ALTER DATABASE model
MODIFY FILE
(
 NAME = modeldev,
 SIZE = 10MB,
 FILEGROWTH = 10MB
);

ALTER DATABASE model
MODIFY FILE
(
 NAME = modellog,
 SIZE = 10MB,
 FILEGROWTH = 10MB
);
GO

ALTER DATABASE master
MODIFY FILE
(
 NAME = master,
 SIZE = 10MB,
 FILEGROWTH = 10MB
);

ALTER DATABASE master
MODIFY FILE
(
 NAME = mastlog,
 SIZE = 10MB,
 FILEGROWTH = 10MB
);
GO

ALTER DATABASE msdb
MODIFY FILE
(
 NAME = msdbdata,
 SIZE = 25MB,
 FILEGROWTH = 10MB
);

ALTER DATABASE msdb
MODIFY FILE
(
 NAME = msdblog,
 SIZE = 25MB,
 FILEGROWTH = 10MB
);
GO
/* Allow advanced configuration options */

USE master;
GO
exec sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

/* Rename SA Account */

ALTER LOGIN sa WITH NAME = [Matrix];

/* Enable DAC */

EXEC sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO

Summary

TempDB may be often overlooked by non-DBA people installing SQL server. Even with VMs using just 1-2 cores I would still configure a few TempDB files. Why? If the SQL server grows and you add more DBs, vCPUs, etc. you probably won’t remember to add more TempDB files.

What’s next? Configuring SQL RAM and setting up email notifications in Part 10.

SQL 2012 Failover Cluster Pt. 8: Firewall

9-22-2013 9-10-20 PMI’m a big proponent of using the Windows firewall on all computers, be they clients or servers. It may not be perfect, but it’s better than nothing. Layered defenses are key in the enterprise, so protecting each host may save your bacon some day. Many programs automatically add Windows firewall rules, so it’s not a huge burden to enable.

If you have the Windows firewall enabled (which I strongly recommend), then you will need to allow both SQL servers to communicate with each other so that you can launch SQL Server Studio on either node and connect to the active node. If you don’t do this, you may get a timeout error. I like to make firewall rules as specific as possible, so I include the path to the program and require specific remote IP(s) that needs access. No Any-Any here!

For ease of installation, I’ve also added a line to the script that configures the SQL large pages trace flag. If you wish to use large pages and your VM has 8GB or more of RAM, you can leave the script as is. If you don’t want to use large pages, then just comment out the line. The SQL services will need to be restarted to take advantage of the added trace flag.

Blog Series

SQL 2012 Failover Cluster Pt. 1: Introduction
SQL 2012 Failover Cluster Pt. 2: VM Deployment
SQL 2012 Failover Cluster Pt. 3: iSCSI Configuration
SQL 2012 Failover Cluster Pt. 4: Cluster Creation
SQL 2012 Failover Cluster Pt. 5: Service Accounts
SQL 2012 Failover Cluster Pt. 6: Node A SQL Install
SQL 2012 Failover Cluster Pt. 7: Node B SQL Install
SQL 2012 Failover Cluster Pt. 8: Windows Firewall
SQL 2012 Failover Cluster Pt. 9: TempDB
SQL 2012 Failover Cluster Pt. 10: Email & RAM
SQL 2012 Failover Cluster Pt. 11: Jobs n More
SQL 2012 Failover Cluster Pt. 12: Kerberos n SSL

1. Run the script on both nodes, using the IP address of the opposite node as the argument.

# Configures Windows Server firewall for SQL 2012
# Requires a single argument, the IP address of the other cluster node
# Usage: SQL-Firewall.ps1 10.10.10.10
$RemoteIP = $args[0]

New-NetFirewallRule -DisplayName “SQL Server (TCP-in)” -Direction Inbound –Protocol TCP –LocalPort 1433 -Action allow -profile Domain -Program "D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -RemoteAddress $RemoteIP -description "Allows inbound Microsoft SQL connections."

New-NetFirewallRule -DisplayName “SQL Server Browser (UDP-in)” -Direction Inbound –Protocol UDP –LocalPort 1434 -Action allow -profile Domain -Program "D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -RemoteAddress $RemoteIP -Description "Allows inbound Microsoft SQL browser connections."

# Configures trace flag for enabling SQL large pages
set-itemproperty -path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer\Parameters" -name SQLArg3 -value "-T834"

9-22-2013 8-44-13 AM
2. If all went well you will now have two new rules in the Windows Advanced Firewall, as shown below. Since the rule only allowed the partner node access, you will need to modify the Remote IP Address on the Scope tab to allow your application servers (such as vCenter) to access the SQL service. Again, I don’t like wide open firewall rules, so try and use IPs vice whole subnets or “any”.

9-22-2013 8-29-52 AM
9-22-2013 8-30-52 AM

3. Now on both nodes fire up the SQL Server Studio and connect to the cluster name (e.g. D001SQL03-DB) and verify it can connect. If you get a timeout error then your firewall rules are botched up. They take effect immediately, so no need to reboot.

Summary

Now that the firewall is configured on both nodes, next up we need to do things like configure TempDB, max memory, email alerting, and DB maintenance plans. So check out Part 9 here.

SQL 2012 Failover Cluster Pt. 7: Node B SQL Install

9-22-2013 8-38-17 PMYes, we are finally at Part 7 of the SQL 2012 failover cluster install. Now that your first node is healthy, we can join the second node to the cluster. The installer process is fairly different, since it will detect your existing cluster and ask you far fewer questions. It also produces a .INI file of your settings, in case you need to re-install or want it for DR purposes.

Don’t stop here, and think all of your work is done. Yes the cluster will be up and running at the end of this post, but there’s still a lot of best practices left to configure such as email error alerts, Kerberos, SSL certificate, etc. Stay tuned for several more installments to make your SQL cluster more enterprise-grade.

Blog Series

SQL 2012 Failover Cluster Pt. 1: Introduction
SQL 2012 Failover Cluster Pt. 2: VM Deployment
SQL 2012 Failover Cluster Pt. 3: iSCSI Configuration
SQL 2012 Failover Cluster Pt. 4: Cluster Creation
SQL 2012 Failover Cluster Pt. 5: Service Accounts
SQL 2012 Failover Cluster Pt. 6: Node A SQL Install
SQL 2012 Failover Cluster Pt. 7: Node B SQL Install
SQL 2012 Failover Cluster Pt. 8: Windows Firewall
SQL 2012 Failover Cluster Pt. 9: TempDB
SQL 2012 Failover Cluster Pt. 11: Jobs n More
SQL 2012 Failover Cluster Pt. 12: Kerberos n SSL

Node B SQL Installation

1. Mount the SQL server ISO to your second VM. Launch the SQL installer page and select Add node to a SQL server failover cluster. After the installer bootstrap files install (download any updates from the internet if prompted), you should get to a Cluster Node Configuration screen. I actually did my first install on node B, so that’s why you see node A listed below as “Name of this node”. Your entries will likely be reversed. Do not panic.

9-22-2013 7-31-12 AM

2. It should automatically detect your network configuration and it won’t let you change it, so just click Next.

9-22-2013 7-34-10 AM

3. You will need to re-enter the service account passwords, so do that here.

9-22-2013 7-35-04 AM

4. Click through the rest of the wizard and on the Ready to Add Node it will give you the path to the .INI configuration file. I would recommend you save that file to a safe place, such as your D: drive. It’s not required, but should you have a failure you can re-run the install with those settings. It can also help for DR purposes, to rebuild your cluster with the same config.

9-22-2013 7-39-13 AM

5. After a few nail biting minutes, you should get a screen with a long list of green checks. Your second node is now up!

9-22-2013 7-48-54 AM

Summary

Congratulations! However, we are far from done. So don’t just call it a day and think that your SQL server is done. We need to configure firewall rules, email alerting, database maintenance jobs, and do other tweaking along the way. Those steps and more will be covered in Part 8 and beyond.

SQL 2012 Failover Cluster Pt. 6: Node A SQL Install

9-22-2013 7-53-10 PMThis is Part 6 of the SQL failover cluster installation, where we finally get to install the SQL Server database engine and related tools. It’s been a tedious road, and you likely ran into some cluster creation issues. This is not for the faint of heart, and should not be done casually. But yet we plow forward and get to finally laying down the SQL binaries to disk. Technically you can have a cluster with one node, so SQL Server Manager and even applications could connect once you finish this installment. Obviously you don’t want to yet start hosting app data, but testing out connectivity with some SQL tools is a very good idea.

Blog Series

SQL 2012 Failover Cluster Pt. 1: Introduction
SQL 2012 Failover Cluster Pt. 2: VM Deployment
SQL 2012 Failover Cluster Pt. 3: iSCSI Configuration
SQL 2012 Failover Cluster Pt. 4: Cluster Creation
SQL 2012 Failover Cluster Pt. 5: Service Accounts
SQL 2012 Failover Cluster Pt. 6: Node A SQL Install
SQL 2012 Failover Cluster Pt. 7: Node B SQL Install
SQL 2012 Failover Cluster Pt. 8: Windows Firewall
SQL 2012 Failover Cluster Pt. 9: TempDB 
SQL 2012 Failover Cluster Pt. 10: Email & RAM
SQL 2012 Failover Cluster Pt. 11: Jobs n More
SQL 2012 Failover Cluster Pt. 12: Kerberos n SSL

Node A SQL Installation

1. Mount the SQL Server SP1 ISO and start the installer process. Click on Installation in the left pane then select New SQL Server failover cluster installation.

9-21-2013 5-59-24 PM

2. The SQL setup routine will install some files. It may also prompt you to download some product updates (which I would do). Once that completes you eventually get to the Setup Support Rules screen. Review all warnings and failures. Warnings for MSDTC appear to be normal, and if you had any cluster validation warnings that will trigger another warning message.

9-21-2013 6-36-18 PM

3. Enter/Accept the product key and thoroughly read the license terms. When you get to the Setup Role select SQL Sever Feature Installation.

4. Select the SQL instance features that you want to install. Shown below I’ve selected the minimum feautures that I’ve found most applications need. YMMV, so add additional components as needed. Don’t just click everything, as that will waste disk space, increase attack surface area, and bloat your system. Remember to change the default drive path for the binaries. I always put them on the D drive, NOT your C drive. That’s for your OS.

SQL 2012 features

5. Configure the SQL server network name and again change the instance root directory to the D drive. For the SQL server network name use the name of the computer object you created earlier in this series. It must be the exact same name, or the cluster build process will fail. Verify the SQL server directory is also on the D drive, which it will be if you configured it in the last step.

6. After the disk space validation screen you will see a resource group screen. Don’t panic if there are no qualified groups. In my experience this is normal. Click Next.

9-21-2013 6-47-35 PM

7. If you followed my disk layout exactly, then you should have two cluster disks available in the upper pane for selection. These should be the F and S drives. The other drives are mount points on the F drive, and thus you can’t select them. Check the two available disks.

SQL disk selection

8. Configure the network settings for the SQL network name. Enter an IP address and ensure the proper Network is listed. Make 100% sure the IP is not already in use.

9-21-2013 7-40-30 PM

9. The service accounts as required for your environment. If you want to change the database collation (e.g. you don’t live in the US), you can do that on the second tab.

9-21-2013 7-44-09 PM

10. On the next screen you have to choose the SQL server administrators. Don’t be lazy and just select the current user option. Add your AD SQL group that we created earlier.

9-21-2013 7-50-22 PM

11. You need to configure the data directories as all of them must be located on your shared LUNs. Following my mount point design I setup the directories as shown in the screenshot below.

9-21-2013 8-02-43 PM

12. Click through the wizard until you get to the Ready to install summary screen. You will see a long path to an .INI file. This is essentially your answer file that captured all of your settings, minus passwords. Copy the .INI to a safe location in case you have an install failure, or for DR purposes. You can’t use it on the second node, since the config steps are very different. Click Install and wait a few minutes.

9-21-2013 8-04-15 PM

13. If you are following this guide exactly and using mount points, then you will likely run into an obscure permissions issue. A scary window should pop up with an error: updating permissions setting for the file resumekeyfilter.store failed. Do NOT, I repeat, do NOT click Cancel. We can fix this problem and the installer will resume.
resumekeyfilter.store

14. If the permissions problem does appear, then navigate to your mount path and change the folder options to show protected operating system files.

9-22-2013 6-23-44 AM

Not so magically you should now see the System Volume Information folder in each of your mount point folders. Open the properties of the file and seize ownership. In my lab I changed the owner to the domain administrator account. A less powerful account could possibly be used, so if you aren’t a domain admin then try a local administrator account.

9-22-2013 6-19-15 AM

After you change the ownership then System was the only entry (ACE) in the access control list. Repeat this process for each of the System Volume Information mount point folders. Click Retry in the SQL dialog box. Pay careful attention to the path in the error, as you may have correctly fixed one file but botched up another, so don’t get frustrated “fixing” the wrong file permissions. I had to fix all four mount point files.

9-22-2013 7-06-23 AM

15. If all goes well the installation should complete with no further errors or warnings. You should now be able to launch the SQL Server Management Studio and login to your instance. If you get an access denied message, make sure your account is a member of the SQL administrator group you made in AD, and that you’ve logged off and back on the SQL server since that change to refresh your group membership.

Now that Node A is up and running, it’s time to install SQL server on node B to complete the cluster. That is covered in Part 7.

© 2017 - Sitemap