Installing SQL the right way

How well SQL is installed before CM can have a dramatic effect on how people perceive CM to be as a product. Common complaints heard are CM is slow, The console is slow, and It can't keep up with these many clients. A well thought out installation will go unnoticed where the reverse can cause downright agony for admins.

Getting ready

Get the latest supported version of SQL, the latest supported service pack, and the latest version of the cumulative update files. An already slipstreamed set of files from Microsoft will make things easier if available. The enterprise version has many benefits such as online re-indexing of tables, support for more than 50,000 clients and more, but the decision of which edition to use usually comes down to cost, as the enterprise edition is far more expensive than the standard version.

The more memory SQL has access to, the better it will run. The more disks and controllers it can use, the better it will run. SQL doesn't perform well in a virtual machine on virtual disks. This can be done in a lab or even on a laptop as a lab, but for production, memory and disks will define the CM experience.

How to do it...

Consider the following disk layout optimized for an enterprise-class primary site or CAS:

Disk	Controller	Number of Drives	Drive letter	Partitions
0	0	4	C	OS
1	1	4	T	TempDB
2	1	4	X	TxLogs
3	1	6	R	SQLDB1
4	2	6	S	SQLDB2
5	2	8	D	Data\Backup

External controllers 1 and 2 get as much RAM as you can afford (1 GB optimally). Each gets one hot, spare drive. All controllers are formatted with RAID 10. SQL activity is split across two controllers. RAID cache settings should be set to Write Back, no Read Ahead .

From the previous table, you can peel away the number of drives as costs constrain your budget in the following order:

1. The OS could be on a simple mirror.
2. TempDB and TxLogs could be on a single drive.
3. The SQL files could be on the same drive.
4. The SQL files could be mixed with TempDB.
5. The SQL files Data\Backup and TempDB could be on the same drive.
6. Move TxLogs to C: and all other data on the second drive.
7. Everything sits on one drive (small lab scenario).

How it works...

With the best layout of disks you can afford and the most memory you can afford, SQL will be able to stand under the stress CM puts on it. If using SAN, multiple dedicated LUNs are best, if available. Notice TxLogs were the last to be compromised as nothing can be committed to SQL until first written to TxLogs. Even with plenty of RAM, data must still be written to disk, which makes TxLogs an important point in any design.

There's more...

Drive layout is the key to smooth SQL operations. But that's just the start. A few more easy steps will keep your installation bug free and optimized for CM use.

Installing SQL with an unattended file

After the preparation of the drives, SQL can be installed using an unattended file, which has the additional benefit of being reused for a reinstall, or being used on similar primary sites. An example of an unattended file is included in this chapter. It includes two sections of note:

PCUSOURCE=\\Server\Share\SQLServicePackX 
CUSOURCE=\\Server\Share\SQLCUX 

The location of any service pack not already slipstreamed should be used for the PCUSOURCE and the location of the latest cumulative update should be used for CUSOURCE. If service packs have already been slipstreamed into the setup files, simply comment them out.

To callout the unattended file, simply use a command similar to the following:

Setup.exe /CONFIGURATIONFILE=cmsqlconfig.ini

Edit the unattended file as needed to match your drive layout. It is currently set to use R, S, T, and X drives so read carefully. The file works only for SQL 2008 R2, but SQL 2008 and SQL 2012 are similar enough that some simple editing can make them work. The key here is that you can read the file to see how to properly lay out the files and options in advance.

Setting some limits

SQL will be happy to eat all the memory on a server leaving nothing for the OS, base applications, or CM. So you need to limit it. Simply open SQL Server Management Studio (SMSS) and right-click on your server to view properties, and navigate to Memory. Because CM is all x64, leave AWE alone. But you do want to enter a maximum server memory here. Leave the OS with 2 GB, your base apps could vary, but 1-2 GB should suffice, and leave CM with 4 GB. Add all that and subtract it from the server's total memory and enter that number here. Note that a CAS requires 8 GB minimum to be dedicated to SQL (anyone choosing to use a CAS is likely to use 16 GB or more anyway).

Transaction logs have been known to grow to consume the entire drive and when that happens, everything stops as nothing can be committed to SQL until first written to the transaction log. A fair limit would be 15 percent less than the entire free space of the drive. Refer to the SQL file layout section, (step 5) for where to do this.

SQL file layout

With SQL installed, it now has to be configured to make the best use of the processors on the server. Use more than one file for the SQL database. The rule of thumb is to use as many files as there are physical processing cores.

1. From the Microsoft SQL Server Management Studio (SMSS) right-click on your CM database and choose Properties. Go to Files and then click on the Add button.
2. If you had eight cores and two drives for the SQL database (R and S), you would add four files to R and three more to S (assuming you initially installed SQL to S).
3. Set the initial size of each file to one-eighth the size of what you expect your entire database size to be.
4. Set Autogrowth to 1000 MB.
5. Set Autogrowth of the transaction log to 1000 MB. Additionally, restrict the growth of the file to a size that is smaller than the free space on the drive on which it resides.
6. Click on OK to commit the changes; no need for reboot.

Helping SQL

CM has a maintenance task to rebuild indexes, which is disabled by default. Over time, SQL will slow down as the indexes grow stale.

1. From the CM admin console, navigate to Administration | Site Configuration | Sites and click on Site Maintenance in the ribbon.
2. Change the properties of the Rebuild Indexes task to be enabled to Weekly.
3. Choose a time of day where CM isn't busy. The default of 1 a.m. on Sunday is probably a good choice.
4. Repeat for all primary sites (and the CAS if you have one).

Additionally, if you have no need to keep data around for 3 months, then help keep the database size smaller by shortening the clean-up tasks from 90 days to something you can live with (perhaps 21 days or 30 days).

Lastly, verify that the recovery model for the CM database is from Full to Simple. Because CM runs backup itself, only its point in time backup can be used to recover the database so you will never recover to some point in time with a full backup. This also keeps the transaction log from having to be backed up. This setting can be found in SMSS by right-clicking on the database, navigating to Options and selecting Simple for the Recovery model.

See also

• Install SQL Server 2012 Using a Configuration File at https://technet.microsoft.com/en-us/library/dd239405(v=sql.110).aspx
• Install SQL Server 2016 Using a Configuration File at https://msdn.microsoft.com/en-us/library/dd239405.aspx