Planning the System


Preinstallation planning will pay off with happier users and time-savings for you. Because it is so easy to pop in the CD and click through to install the product, it is equally easy to forego the planning process - but resist the temptation!

You will involve several functions in your planning, including the funding source, network group, and infrastructure (hardware/operating systems group). The funding source or business group will need to tell you something about the dollars available and the planning period that this configuration should support. Are you building something to last for four months or two years? The business people will also have some requirements around up-time and scalability. Incremental up-time and scalability is always more expensive as your tolerance for problems decreases. The infrastructure group will be able to advise you as to your company's configuration rules, hardware-vendor recommendations, and approved levels of software. Don't forget about support as well.

Let's drill into some of the details.

Hardware Choices

If you are in a growing organization, you should probably buy bigger and more than you think you need. More memory and faster or more processors are almost always good. At the very least, ensure that you have room to grow. For instance, if you have four memory slots and plan on buying 2GB of memory, do not buy four 512MB sticks of memory, because you will have used up all of the slots. Instead, consider buying two 1GB sticks, leaving two slots available for expansion.

Microsoft recommends some minimum configurations, and you'd better believe they mean minimum. Even for a small test machine, the minimum will not be enough. You should take this to heart. The minimum configuration means the product will most likely not run with fewer resources. It also means that you will need more hardware for any significant usage.

You are going to have to do some homework here. How many concurrent connections do you expect? How many of them will actually be doing work at any given point in time? How will that translate into disk I/Os? If you have an existing system, measure it. If you are using some purchased software (such as SAP, PeopleSoft, or any of a host of others), talk with the software vendor about hardware requirements. Many vendors have people who specialize in configuration of their application; use them.

Although we are primarily discussing SQL Server Engine in this section, you may be working with other parts of SQL Server.

Processors

For SQL Server installs where you expect many connections, more processors are better. This means that two 1.6Ghz processors will likely be better than a single 3Ghz processor. Although SQL multithreads, having two processors instead of one (or four instead of two), help prevent a situation where a single connection ties up the system, it's just a safer bet. The new multicore processors are great also. While dual-core processors are not quite as fast as two separate processors, they are certainly cheaper to purchase and license for software. Intel has also announced plans to develop chips with tens to hundreds of cores on a single processor chip. Most serious production systems should be 64 bit, especially Analysis Services and Reporting Services Report Manager.

Disk Configuration

When you plan your disk drives, consider availability, reliability, space requirements, and throughput. You will need to design the disk I/O subsystem to meet your needs. You will need to decide how to implement availability and reliability, which usually transfers into Redundant Array of Inexpensive Disks (RAID) and Storage Area Network (SAN) decisions.

  • How will master and model databases be protected? (Probably mirrored)

  • How will the data files for the database be configured? (Probably Raid 5 or Raid 10)

  • How will the log files be configured? (Mirrored)

  • Where will tempdb be placed and configured? (Away from other files and probably mirrored)

We suggest three rules for data-disk configuration to get maximum recoverability. Although these are used mostly when creating user databases, the system databases need to be on a different drive from the one you expect to use for your user databases. The rules are:

  • Keep the log away from data. Keep a database's log files on a different physical drive from the data.

  • Keep the data away from the master. Keep the user database files on a different physical drive from master/model/tempdb.

  • Mirror the log.

SQL Server will not know anything about your decisions regarding RAID levels and SAN or Network Attached Storage (NAS) storage. It uses only the drive letters that have been exposed to the operating system. Therefore, you need to make and implement all of these decisions before you install SQL Server.

Disk Throughput

Disk throughput is the most common mistake people make when configuring SQL Server. Suppose you've been told by an administrator, "I need to have 300GB of storage for my production database." You buy a couple of 1500GB hard drives, and off you go. If this database is expected to get 300 IOs per second of requests, though, you are going to be in trouble. Imagine an Ultra-320 Scsi with the following specifications:

  • Capacity: 147GB

  • Spindle Speed: 10K RPM

  • Average Seek: 4.5 msec

  • Average Latency: 3.0 msec

  • Track-to-Track Seek Time: 0.3 ms

  • Max External Transfer Rate: 320 Mbits/sec

This is a commonly available, high-performance drive. Now consider how this performance should play into your thinking about SQL Server install and configuration.

With no data transfer, the theoretical max random IOs/second would be 1,000 Mbits/second / (4.5 seek + 3.0 latency) = 133 IOs/second. The theoretical max Serial IOs/second would be 1,000 Mbits/second / (3.0 latency) = 333 IOs/second.

For your calculations, however, you should use a more conservative capability estimate of 100 random IOs and 200 Sequential IOs per second for this configuration. It is much more likely that you will be constrained by the IO transfers per second than the 32Mbytes/second the hard drive can yield (320 Mbits/second / 10 bits/byte including parity bits = 32 Mbytes/second). Now, you'll find that 100 random IOs/second * 8K per transfer = 0.8 Mbytes/second.

This calculation is for random reads and writes to a single drive. SQL Server's lazy writer, worker threads, and other processes do 8K random IOs. Notice that this is well under the maximum advertised throughput of 32 Mbytes per second.

Even if we are doing sequential IOs, 1.6 Mbytes/second is still way under the max throughput of 32 Mbytes/second: 100 Sequential IOs/second 8K = 1.6 Mbytes/second.

Read-ahead Manager does 64K IOs. Read-ahead manager also tries to order the IOs so that they become sequential IOs, which provide much better throughput. The results are 100 Random IOs/second 64K = 6.4 Mbytes/second and 300 Sequential IOs/second 64K = 12.8 Mbytes/second.

Even at the fastest throughput one might reasonably expect from this hard drive, the limitation will be the IO transfer rate, not the 32 Mbytes advertised maximum throughput.

If you use RAID 5, each logical write is physically two reads and two writes. This IO will be spread across two drives, so the throughput of each drive is divided in half. That would bring your IO transfer rate for write operations down from 100 to 50.

In this example, you need 300 logical writes per second against a RAID 5 array, so you must have 300/50 or six drives). Even though you need only about 300GB of disk storage, your configured RAID 5 array will contain about 600 GB of usable storage (five usable drives of the six, each 150GB/drive). The bottom line is this: Do not configure only for storage; you must think about transfer-rate needs as well.

Note that the actual time to transfer the data is a small part of the overall IO time. Transferring an 8K chunk of data would take about 1/4 of a millisecond (32,000,000 Bytes/second/8,000 bytes). Transferring 64K bytes of data would take about 2 milliseconds. This is small when compared to the average seek and average latency time of 7.5 milliseconds for this drive (4.5 + 3.0).

Note

Some disk drives are faster than the one we used as an example here, and disk capabilities change over time, so you must do your own research and come up with your own numbers.

Best Practices

In general, to get higher throughput, you should spread the IOs across multiple physical drives. Hardware-based arrays are faster (and more expensive) than software arrays. Consider the transfer rates necessary to support the concurrent users you expect on your system. Also note that even though you may have 500 users connected, most of them will likely be waiting rather than active. You will have to do some research to better understand your users to get an idea of what percentage of connected users will actually be active at any given time. If most of your users are heads-down, data-entry folks, you may have nearly 90 to 100 percent of them active most of the time. If your users are a mix of inquirers and updaters who keep the application running all day but only use it occasionally, you may have as few as 10 percent of your users active. This knowledge should be part of your research and part of your plan.

Because most large IOs in SQL Server are 64K bytes, that would be a good stripe size to choose when you are configuring your RAID arrays.

SQL does not officially support data files on NAS or network file shares but will run if you enable Trace flag 1807. Do not expect to get good performance from NAS or network file shares. Better advice would simply be to always use direct-connected hard drives or SAN for your SQL files. Finally, you should always use NTFS for your data files. NTFS provides better security and more fault tolerance via soft sector sparing.

Disk Controllers

Disk controllers have a throughput limit as well. Take the maximum throughput expected from your earlier drive calculations to determine the maximum number of drives you should attach to a controller.

You need to be careful about the caching on these controllers. Disk caching can bring wonderful performance improvements to your system, but you can easily get into trouble by using it. The issue is that you must be able to guarantee that transaction-log writes will be completed under all circumstances. Controller caching works like this: When SQL Server sends a write command to the controller, the controller can save the data in cache and immediately send an IO Complete message back to SQL Server. One of the things the hardware controller can do is attempt to batch up multiple smaller IOs into what might become a single larger sequential IO, instead of many smaller random IOs. SQL Server continues to work, even though the IO has not actually been committed to disk. As long as the IO eventually makes it to disk, life is good. But if the IO gets lost, for any reason, SQL recovery will not work, because part of the log is missing. Then you are in big trouble.

The way to avoid this problem is to ensure that you are using a disk controller designed for use in a data-critical transactional DBMS environment. You should be able to turn off write caching and take advantage of read-only caching. Not all controllers allow this. You should be covered by a UPS, but this is not enough to ensure that the IO will be completed. UPSs cannot cover everything, such as operating system traps, memory parity errors, or operating system errors that cause a system reset.

Another problem that can circumvent proper write caching is an operations issue. A normal server shutdown (from the operating system) will wait until all of the IOs are complete. Operators should generally wait until disk activity is complete before rebooting a system. When Ctrl+Alt+Delete is used to shut down or the system reset button is pressed, cached writes can be discarded, corrupting the databases.

There are disk controllers that avoid all of these problems. They can intercept the RST (reset) bus signals to bypass resets that would discard cached writes. They also include ECC (error checking and correcting) memory, as well as onboard battery backup.

Although many controllers have a cache, most of the cache is protected by capacitors, not onboard battery backup. Capacitor-based caching cannot protect data from power failures and only ensures that the current sector is written. Onboard battery backup can protect a cached write for several days. As long as power is restored within these time limits, the controller will prevent new access, complete the pending IOs, and then allow new access.

Some controllers also allow selective write-through, allowing transaction-log IOs to bypass caching, while other writes are cached to improve performance.

Software and Install Choices

The next step in the plan is making choices about the software and installation options, such as choosing where the files will be located, setting security options, and configuring your disks.

Collation

When you choose a collation, you choose a character set, called a code page. The code page contains the mapping between the hex digits stored in the database and the characters they represent. For instance, x20 (hex 20) is a space character. The thing that makes it a space character is the code page you are using. You look up the hex 20 in the character-set map (code page), and you can see that it represents a space in the language (or character set) that is part of your collation.

Collation affects two other things: case sensitivity and sort order. You will see more details about these two items in a moment.

In versions of SQL Server before SQL Server 2000, choosing the correct collation was a big deal. The collation you chose during the install was the collation used for everything on the entire server. If you needed a different collation, you had to install another server. SQL Server 2005 allows you to make collation choices at the server, database, column, and expression levels.

The collation you choose during install is the collation for the system databases, including tempdb, and the default collation for everything else. Those who add new objects to this instance of SQL Server may choose other collations for their objects.

There are two general types of collations: SQL Server collations and Windows collations.

SQL Server Collations

SQL Server collations affect the code page used to store data in char, varchar, and text columns. They also affect how comparisons and sorting is done on these data types. They do not, however, affect Unicode data types. Unicode data types may compare and sort differently. This is the primary shortcoming of using SQL collations. You can set up an environment where single-byte and double-byte character sets use different collations. You should use SQL collations for backward compatibility. All of the SQL collation names begin with SQL_. Any other collation name is a Windows collation.

Windows Collations

Windows collations use rules based on the chosen windows locale for the operating system. The default behavior is that comparisons and sorting follow the rules used for a dictionary in the associated language. You may specify binary, case, accent, Kana, and width sensitivity. The key point is that Windows collations ensure that single-byte and double-byte characters sets behave the same way in sorts and comparisons.

Case Sensitivity

Your collation is either case sensitive or not. Case sensitive means that U is different from u. This will be true for everything in the region to which the collation applies (in this case, master, model, resource, tempdb, and msdb). This is true for all of the data in those databases. Here is the gotcha: Think about what the data in those databases actually is. The data includes the data in all of the system tables, which means object names are also case sensitive. The function to list the collations supported by SQL 2005 is fn_helpcollations(). On a case-sensitive server, this command would give you an "invalid object name" error:

 Select * from FN_HELPCOLLATIONS() 

This command, however, would work correctly:

 Select * from fn_helpcollations() 

If you have a case-sensitive database where all of the objects are also case sensitive, you have a table name Emp, with a single column named Firstname, with four rows with values Tom, Mary, tom, and mary.

The following command finds no rows, because the objects are case sensitive:

 SELECT * FROM Emp WHERE Firstname = 'TOM' 

The following command files only one row, Tom:

 SELECT * FROM Emp WHERE Firstname = 'Tom' 

To find all the rows regardless of case, you would have to write something like:

 SELECT * FROM Emp WHERE Upper(Firstname) LIKE 'TOM' 

Binary collations are case sensitive. If you choose a dictionary collation, you may choose the case sensitivity you need. However, as you can see, collation affects comparisons.

Sort Order

The collation you choose also affects sorting. Binary orders (Latin1_General_BIN, for instance) sort based on the bit value of character; they are case sensitive. Using the example table from earlier and a binary order, consider the following statement:

 SELECT * FROM Emp ORDER BY Firstname 

The result set would be:

 Mary Tom mary tom 

If you choose a dictionary sort order (Latin1_General_CS_AI, for instance), the previous statement would yield the following result set:

 mary Mary tom Tom 

Most business applications that contain character data types generally need a dictionary sort. Microsoft Books Online for SQL Server will provide you with a list of the extensions (_CI, _CS, and so on) and their meanings. Here you need only to understand the conceptual differences.

Collation Best Practices and Warnings

The big deal here is compatibility. If you try to compare two text fields that have different collations, which collation should be used for the comparison? You must provide the collation name in your code. This is a pain to do. Some collations are not comparable, which is even worse. Think about your use of linked servers in an environment of mixed collations. If you are using SQL replication between this instance and some other server, particularly SQL Server 2000 and SQL Server 7, you'd better make sure the data being shared also shares the same collation.

Try to standardize a collation across the enterprise.

Ensure compatibility with other servers where necessary. Change the default only if you need to do it for compatibility with other servers. The caveat here is that if you have an earlier version of SQL Server installed on this server, it will automatically choose a SQL collation. In this case, you must decide whether you need compatibility with that earlier version. If you do not need compatibility, choose a Windows collation.

SQL collations cannot be used with SQL 2005 Analysis Services. If you choose a SQL collation and are also installing Analysis Services, the install will choose a Windows collation that most closely matches your SQL collation choice. Your results, however, may be inconsistent. To guarantee consistent results between your database engine and Analysis Services, use a Windows collation.

If you have servers that have regional settings for both English (UK) and English (United States), you might be in for a surprise. The default collation for English (UK) is Latin1_General_CI_AS, whereas the default collation for English (United States) is SQL_Latin1_General_CP1_CI_AS. You will have to choose the proper collation for one of these to ensure consistency. They both use code page 1252, however.

In general, when you do not have to ensure compatibility with multiple servers, use the default Windows collation, and choose the case-sensitivity options to suit your needs.

If you are upgrading an earlier version in place, use the default SQL collation chosen by install.

If you are trying to replicate with another SQL Server 2005 instance, you can discover its collation with the following statement on the existing system:

 SELECT SERVERPROPERTY(N'Collation') 

If you are trying to replicate with SQL Server 7.0 or SQL Server 2000, for instance, use the following command on the existing instance to get the collation information:

 EXEC sp_helpsort 

Microsoft recommends that if you are trying to match the Windows locale of another computer (without changing your server's Windows locale), you must obtain the locale name from the other server. For Windows 2000 or Windows 2003, this can be found in Control Panel Regional Options. On Windows XP, this is under Control Panel Regional and Language Options. Once you have the region name, search MSDN for the topic "Collation Settings in Setup." This topic provides a mapping between locale name and default collation.

Microsoft's recommendation here does not take into account the possibility that the other server might not use the default collation. I would prefer to use serverproperty and sp_helpsort to get the collation directly. I provide Microsoft's recommendation only for completeness.

System Files Location

For most servers where higher availability is a requirement, we recommend that you mirror the C: drive where the operating system lives. You should place the SQL Server install in the default Program Files folder on the C: drive as well. This means that the master, model, resource, msdb, and tempdb will also be on the C: drive by default.

Tempdb might need some extra consideration, because it performs more duties in SQL Server 2005 than it did in SQL Server 2000. It will contain more information related to certain locking schemes, and it supports several other new features. As a post-install item, you may wish to either move tempdb using the Alter database command or have it grow to another disk drive. To do this, turn off autogrow for the existing file, and add new files in some new location. The preferred method is simply to move it to an otherwise not busy disk drive, since tempdb could see lots of IO.

Disk Setup

Without getting into the doldrums of discussing each of the RAID types, we'll offer some simple advice: Mirror the OS, SQL exe files, the system databases, and all transaction logs. Data files for other databases where high availability is necessary should be protected via RAID, with duplicate disk controllers. The more disk spindles you use, the better your IO throughput will be. If you have the money, RAID 10 or SAN will give the best performance. A less expensive option that does not provide as good performance is Raid 5. Whatever you do, use hardware RAID. For SQL Server, 64K is a good RAID stripe size.

Security Considerations

Do not install SQL Server Engine on a domain controller or on the same box as IIS. Reporting Services, of course, must be installed on an IIS box, since it depends on IIS. Use the concept of least security, only allowing the minimum security to get the job done.

While you can get the details about service accounts on MSDN (look for Setting Up Windows Service Accounts), we will give you only the reminders that you need to know. Up to 10 services are part of SQL Server 2005, depending on what you install. If you install more than one instance, that is up to 10 per instance. For maximum security, use a different local Windows login for each account, instead of sharing a single account locally. Local Windows logins are safer than domain logins, but they are more trouble because you have to keep up with more logins.

Although Microsoft's suggestion is to use separate local accounts for each service, most companies use a single domain login account for all services, using the minimum security settings. Some things can only be done when the service is running under a domain account, for example:

  • Remote procedure calls

  • Replication

  • Backing up to network drives

  • Heterogeneous joins that involve remote data sources

  • SQL Server Agent mail features and SQL Mail

As we said, most companies use a single domain account for all services on all production servers. This makes maintenance easier; for instance, you have to set up only a single file share with permissions for backing up to a network drive. There are several kinds of service accounts you can choose from:

  • Domain Account: This is an active directory domain account that you create and is the preferred account type for SQL Server services needing network access.

  • Local System Account: This is a highly privileged account you should not use for services.

  • Local Service Account: This is a special, preconfigured account that has the same permissions as members of the Users Group. Network access is done as a null session with no credentials.

  • Network Service Account: This account is the same as the Local Service Account, except that network access is allowed, credentialed as the computer account. Do not use this account for SQL Server or SQL Agent Service accounts.

  • Local Server Account: This is a local windows account that you create. This is the most secure method you can use for services that do not need network access.

If you are making any changes to properties related to a SQL Service, do not use Windows Services dialog boxes. As an example, suppose the password for a domain windows account for your SQL Service has been changed. You need to change the stored password for the SQL Service. Although this can be done via Windows Administrative Tools, you should always use SQL Server Configuration Manager to manage the services associated with SQL Server.



Professional SQL Server 2005 Administration
Professional SQL Server 2005 Administration (Wrox Professional Guides)
ISBN: 0470055200
EAN: 2147483647
Year: 2004
Pages: 193

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net