In-Depth

Laying the Groundwork for Microsoft SQL Server 2008

Redmond's new database server is a complex product often running mission-critical apps. You can't afford to not get it right.

• By Josh Jones
• 02/01/2008

Microsoft SQL Server 2008 builds on its predecessors in terms of its administration and management capabilities. Along with Visual Studio 2008 and Windows Server 2008, it also provides the robust data tier in Microsoft's Entity Data Platform (EDP). To take advantage of SQL Server 2008's new features, you need to plan ahead and prepare to make sure your database servers are ready. Migrating to a major platform like SQL Server 2008 takes significant preparation, even if you aren't planning advanced configurations like using the EDP for application development and support.

Moving from SQL Server 2005 to SQL Server 2008 won't be quite as monumental as upgrading from SQL Server 2000 to SQL Server 2005 (which many organizations haven't even done yet). There are several key new features and functions, such as policy-based management, built-in data collectors for performance monitoring and support for several new data types, but the fundamental components and functionality remain the same.

This leaves you with some tough decisions. Do you upgrade your SQL 2000 servers to SQL 2005 or SQL 2008? Just as importantly, do you really need to upgrade your SQL 2005 servers to SQL 2008? To help you arrive at the best decisions for your situation, you need to evaluate your usage scenarios and infrastructure considerations in several key areas.

How do you use your database servers? Is your database a simple OLTP data repository or do you execute complex business logic inside the database? Are your servers required to be up 24x7? Based on your current and anticipated usage, you may choose to upgrade to SQL Server 2008 to take advantage of its complex data-manipulation features, such as hierarchical IDs, spatial-data types and advanced XML manipulation.

That's not all. Besides data storage, what other components will you need? Will you need data reporting? What about analysis? The new features within SQL's Reporting Services and Analysis Services may drive your decision to upgrade.

What about management and infrastructure? SQL Server 2008 provides new management and performance tuning capabilities, like the Declarative Management Framework (DMF). The DMF enhances how you interact with and support your SQL Servers. For midsize environments, these tools alone may be worth the upgrade. They're particularly helpful if your company can't afford the enterprise-level tools provided by various third-party vendors to manage large (more than 100 servers) environments.

In the end, there's no simple litmus test to tell whether or not it's essential for your organization to upgrade to SQL Server 2008. For each environment, there will be a different motivating factor. You'll need to fully understand your current environment, what features are missing, what features are never really used, and what new features may replace current workarounds.

The only organizations that should consider themselves under pressure to upgrade are those still using SQL Server 7.0 or earlier. Time is very limited from a support and usability standpoint with versions this old. No matter what, once you decide that you need to upgrade, the first order of business is to evaluate your hardware configurations, so you know whether or not you can upgrade without first upgrading your hardware.

Hardware Prerequisites
When it comes to deploying a database server, hardware configuration is extremely important. You'll need to make sure you have ample disk space for the amount of data you'll be storing. You'll also need to optimize that disk space for the database's anticipated workload.

There's a limitless number of disk configuration options, so just remember the basics: separate data files from log files, allocate disk space specifically to the tempdb and make sure you include redundancy in your disk arrays (failovers and spares).

The minimum and recommended requirements for SQL Server 2008 are fairly standard (see Table 1). While the recommended configuration will work for the average standalone SQL Server for a small or midsize business, most large enterprises will likely find themselves purchasing additional hardware to accommodate their performance needs. Remember to take a good look at your applications and your environment, particularly from the perspective of the SQL Server 2008 features you plan to use.

If you plan on building out your SQL Server environment to take advantage of certain High Availability (HA) features like Microsoft Clustering, you'll need to budget for identical pieces of hardware to support your chosen configuration. It's also a good idea to plan for the "scale-out" deployment of SQL Server's components, as there are a number of possible deployment scenarios.

Upgrade Considerations
Once you've decided that you're going to go ahead and implement SQL Server 2008, you need to evaluate which editions will be appropriate to suit your needs. There aren't any changes to the hierarchy of editions when compared to SQL Server 2005, so if you're already using SQL Server 2005, you can probably use the same edition of SQL Server 2008. If you're replacing SQL Server 2000 (or older), you'll need to compare your existing edition against the newly revised editions to determine which is most appropriate (see Table 2).

Be sure to fully evaluate your current and anticipated needs before deciding which edition is right for you. Be as realistic and accurate as possible, as there are significant pricing differences. Another aspect you'll need to consider is whether or not to make the move to 64-bit computing on your database servers (if you haven't already). Regardless of which processing platform you're currently using, when you upgrade to SQL Server 2008, it may be the right time to implement new 64-bit based hardware as well. SQL Server 2008 supports both x64 and IA64 (Itanium) processors, so you should be able to find suitable 64-bit hardware.

Because both Microsoft Windows and SQL Server can run in 32-bit mode on 64-bit hardware, your choice isn't as much about purchasing 64-bit hardware, but choosing whether or not to install the 64-bit versions of Windows and SQL Server. Running your database servers in 64-bit mode will give you a significant performance boost.

You'll have more computing power in terms of the number of instructions per clock cycle on each CPU. This in turn will give you better access to large amounts of physical memory (no more "virtual" memory management issues). You can literally get more work done faster with 64-bit machines. As they say, your actual mileage will vary, but database query performance will obviously be faster on a 64-bit machine as opposed to a 32-bit machine.

Besides planning for hardware considerations, you'll need to be fully aware of any functionality changes. This includes any "breaking" changes that may happen as you migrate your existing databases to SQL Server 2008. Fortunately, Microsoft's SQL Server Upgrade Advisor can ease the process. Unfortunately, the new upgrade advisor has not yet been published. There are references to it in the documentation for SQL Server 2008, so it's coming soon.

The new Upgrade Advisor will likely be similar to the SQL Server 2005 Upgrade Advisor. The 2005 version gave you warnings to help reduce installation and upgrade problems. The Upgrade Advisor won't catch all potential problems, but it's a great way to get started and it will catch the major issues.

Potential Roadblocks
There's some information available about potentially painful (or just plain annoying) changes in SQL Server 2008. Here's a list of the changes in SQL Server 2008 that are most likely to cause issues:

Compatibility Levels: These are the settings that let databases from earlier versions of SQL Server remain "compatible" for that version, in terms of features and functions, when they're hosted on a newer version of SQL Server. The levels are: SQL Server 7.0 is "70," SQL Server 2000 is "80," SQL Server 2005 is "90," and SQL Server 2008 is "100." Support for compatibility levels is limited to the two previous versions. So SQL Server 2008 only supports levels 80 and 90. The next version will only support 90 and 100. If you have databases running on an earlier version (or coming to SQL Server 2008 from an earlier version), you'll have to upgrade the database to at least level 80, if not directly to level 100.

CLR Assemblies: SQL Server 2008 supports the new Hierarchy ID, potentially adding a name conflict with user assemblies (if your assembly happens to use the name hierarchyid). The Upgrade Advisor will note any naming conflicts. If you install without using the Advisor, and there's a user assembly with a name conflict, the setup program will rename the assembly and put the database into suspect mode. If there's a user type with a conflict, both the old type and the new type will exist, however the user type will only be available via two-part naming usage.

[Click on image for larger view.]

Figure 1. The Excel Add-in enables direct integration with Microsoft Excel spreadsheets.

DBCC CHECKDB and CHECKTABLE: Besides introducing support for spatial indexes (indexes on the new spatial-data types), DBCC CHECKDB and CHECKTABLE have one minor change. On databases upgraded to the new compatibility level (100), these two DBCC commands will only do a physical consistency check (no logical check) on XML indexes unless NOINDEX is specified. If your database is still in compatibility level 90 (SQL Server 2005), it will issue both a physical and logical check against all indexes, including your XML indexes (again, unless NOINDEX is specified).

BACKUP|RESTORE { DATABASE | LOG }: The following options for BACKUP and RESTORE are deprecated in SQL Server 2008, meaning they will not be supported in the next version of SQL Server: WITH PASSWORD, WITH MEDIAPASSWORD, WITH DBO_ONLY (use WITH RESTRICTED_USER).

sp_dboption: ALTER DATABASE has replaced this. If you have any code that references sp_dboption, replace it with ALTER DATABASE as soon as possible, as the next release (after SQL Server 2008) won't support sp_dboption.

Remote Servers: Linked servers have replaced remote servers. The related system-stored procedures (such as sp_addserver) will be removed from the next version of SQL Server.

Join Syntax: Many programmers use the old join syntax *= or =* for left and right outer joins. This will no longer work with SQL Server 2008. You should begin replacing these types of joins with the ANSI standard SQL syntax for joins.

Discontinued Features: The following features were deprecated in SQL Server 2005, and are not supported in SQL Server 2008: aliases, DUMP/LOAD, BACKUP LOG WITH {NO_LOG | TRUNCATE_ONLY}, Groups (use roles).

You can learn more about any potential problems in some of the books about SQL Server that are coming out as the product is released. Be sure to review your code (or have your programmers review their code) to remove any references to deprecated or discontinued features as soon as possible. This will prevent any unpleasant surprises when you begin upgrading databases.

Choose Your Components
Besides the basic database engine, SQL Server 2008 includes an entire suite of components designed to help form the data tier for your organization. Because of the vast number of available components, you'll need to plan for each component as if it were a separate application. This will help you scale your environment as your organization grows. You can install each of the following components on a separate physical machine (if necessary) to create a more flexible, scalable environment:

SQL Server Analysis Services (SSAS): Installing SSAS on a separate machine is no different than installing it alongside the SQL Server database engine. However, installing this separately will let you customize resources, such as CPU and RAM, to fit the SSAS workload.

SQL Server Integration Services: This is simply a service that runs on a Windows machine that enables hosting and execution of SQL Server Integration Services packages. Scaling this out makes sense if your organization has a large number of packages and needs to manage them from a single server.

SQL Server Reporting Services (SSRS): SSRS is a Web application designed to host reports you can access via a Web interface, or through an exposed set of .NET objects. Installing this component on its own server will let you get away without having IIS (an SSRS prerequisite) installed on your SQL Server. It will also let you offload report-processing workloads to a separate physical machine.

Obviously, you can install any or all of these components side-by-side with the core database engine, or combined with any other component. Installing any of these will also install a core set of prerequisites on your server (like the new .NET Framework 3.0) to support the component you're installing. This will help you quickly reallocate or consolidate servers.

Install Time
Once you've made all the decisions about which edition and components you're going to install and how you're going to deploy them, you'll be ready to begin actually installing SQL Server 2008. At this point, you'll have one of two different paths from which to choose: a clean install or an upgrade. While there are wildly differing opinions on upgrading a database server versus doing a clean install, you'll need to plan and proceed carefully either way.

Considering factors like maintenance windows and cost, most organizations will migrate to new database servers by installing the new software on a new server. Then they'll move the databases over. This is usually faster than an in-place upgrade. You can use various database copying techniques, like log shipping, to create the database on the new server without any user interruption on the old server.

[Click on image for larger view.]

Figure 2. The Management Studio is where you can control, enable and disable policies to configure SQL Server 2008.

Once you have your data in sync and you're ready to go live and cut over to the new server, there will be a minimal outage. You'll have to either re-name the servers so the new one replaces the old or update applications/users to use the new server. This also provides a failback system (albeit with out-of-date data) should something go horribly wrong on the new server.

If you do choose an in-place upgrade, remember that you should always back up your databases (some even choose to detach them) before doing any work. This will protect you from any problems if the installation is interrupted by unpredictable conditions, like power loss.

You also need to choose whether or not you'll leave your databases in a lower compatibility level after installing SQL Server 2008. Even though the compatibility level allows for some backward functionality, even the act of attaching the database to a newer version of SQL Server makes some fundamental changes to the database. This will prevent it from being attached to the original version from which it was migrated.

Before you move any production databases, be sure to test their functionality to the best of your ability to help ensure a smooth transition. If you're lucky enough to have a stable development, test and production design, you can use your development and test environments to host instances of SQL Server 2008. This will let your application support teams test application functionality and performance. Then you'll be able to flush out functional incompatibilities and test new features.

Installing SQL Server 2008 is a very similar process to installing SQL Server 2005. The installer uses the same MSI format, which gives you granular control of components and their installation destinations. Much like SQL Server 2005, you can build automated, silent install packages that you can then deploy to servers as needed. This is a non-trivial task, but is well worth the effort if your organization utilizes dozens or hundreds of SQL Servers. When you combine automated installation with the new SQL Server 2008 policy-based management framework, you can ensure that your servers are built correctly every time.

Upgrading and Beyond
Deciding if, when and how to upgrade are the big decisions. You don't always have to drive the shiny new car if your old one works just fine. SQL Server 2008 does, however, provide much-needed support for many applications and offers a host of new features in each of its components. Also, DBAs will appreciate the new management features.

This is an extensive process, so be sure to take your time. Test your applications, document your usage needs, evaluate your hardware and move slowly. You know the importance of your databases. Don't rush in without a clear picture of the outcome.