Many dependencies can make a user database a poor candidate for consolidation. It is critical that these dependencies are spotted in advance so you can plan for or work around them.
There are several classes of dependencies:
- Version
- Software
- External/hardware
- Internal
Version dependencies
SQL Server 2005 is the ideal platform for SQL Server consolidation because it offers improved functionality, performance and memory accessibility, and the ability to cluster with SQL Server 2005 Standard Edition. Run SQL Server 2005 Upgrade Advisor to identify problems you may have upgrading your user database from previous versions. Or, consult its help file under the section marked Resolving Upgrade Issues. User databases that can't be upgraded to SQL Server 2005 or must run on previous versions of SQL Server should be consolidated on their own stacks: SQL Server 7 databases should be consolidated on SQL Server 7 instances, SQL Server 2000 on SQL Server 2000 instances, etc.
You may be forced to consolidate user databases on previous versions of SQL Server because the vendor may not have certified its product to run on SQL Server 2005.
It is possible to set database compatibility mode to a previous version of SQL Server to solve differences in certain database behaviors among versions. This will not solve any problems mentioned in the Upgrade Advisor, but it may solve problems with user applications that perform version checks.
 |
|
|
|
|
If you consolidate in different geographical regions you will need high-speed links between the client system and the consolidated SQL Server to minimize the impact of the network hop.
Hilary Cotter
Microsoft SQL Server MVP
|
|
|
|
|
|
|
|
|
|
Also watch for dependencies on SQL Server 2000 32-bit. For example, SQL Server 2000 64-bit does not support Data Transformation Services (DTS), Reporting Services, Notification Services, English Query, SQL Mail, some SQL Full-Text Search iFilters and Jet Support. This again will force consolidation on a SQL Server 2000 32-bit stack. There are no incompatibilities between SQL Server 2005 32 and 64-bit.
Software dependencies
Software dependencies include applications that run on SQL Server and must be installed on the consolidated SQL Server environment. Examples include File Transfer Protocol (FTP) and Internet Information Servers (IIS) (for Reporting Services, SQL Server CE and SQL XML ISAPI), SQL Server CE, SQL Full-Text search iFilters, ODBC drivers, OLE-DB providers and Microsoft Data Access Components (MDAC). While surveying user databases you must survey the Windows server as well to check for installed applications and drivers that SQL Server, linked servers, jobs, batch files or stored procedures might use.
The Add/Remove applet does provide information about installed programs and makes a good starting point for your inventory of third-party software installed on the source SQL Server environment. Also check the scheduler (Scheduled Tasks in Control Panel) and SQL Server jobs to watch for references to third-party software.
To get a list of installed OLE-DB providers issue a call to sp_enum_oledb_providers on the candidate SQL Server and compare this with the list returned on the server where you are consolidating.
Also check for merge replication custom resolvers. You can obtain a list by issuing a call to sp_enumcustomresolvers.
External and hardware dependencies
Hardware dependencies involve hardware accessed by SQL Server or user databases that is essential to their operation. This includes 32 or 64-bit hardware, memory, solid-state memory drives, SANs and even hard-drive lettering. Hardware dependencies usually won't allow you to downgrade. For example, it is unlikely that you would be able to consolidate user databases running on a 64-bit operating system to a 32-bit operating system due to performance limitations. Stored procedures, batch files and replication processes may also expect to use a specific drive letter (i.e. G drive), path or share, which may not be on your consolidated SQL Server. Also watch out for use of the Q drive, a drive letter reserved for clustered servers.
Keeping an eye out for these hardware dependencies will allow you to plan ahead, and rewrite jobs and stored procedures to reference the new drive lettering scheme. Also examine the network infrastructure. If you consolidate in different geographical regions you will need high-speed links between the client system and the consolidated SQL Server to minimize the impact of the network hop.
|
|
|
|
|
A successful consolidation project requires that you scrupulously gather a wide variety of information about the candidate SQL Servers and their databases.
Hilary Cotter
Microsoft SQL Server MVP
|
|
|
|
|
|
|
|
|
|
Other external dependencies include batch and command files, scripts (SQL, VBScript, JavaScript, and Perl), directories, Component Object Model (COM) objects and NT accounts. Again check the jobs in Job Scheduler, Task Scheduler and stored procedures that reference any external dependencies.
Internal dependencies
Your consolidation team must also pay close attention to internal dependencies of user databases on their current SQL Servers. These internal dependencies are:
- Server-wide settings
- MSSearch (SQL Full-Text Search)
- Named instances
- Server collations and database collations
- Accounts
- Name collisions
- User stored procedures in the master database
- Extended stored procedures
- CLR assemblies
- Replication
- Model db
- Jobs
- DTS packages
- Database dependencies
- Startup objects
Because internal dependencies have the lowest visibility and present the greatest stumbling blocks to consolidation, we will be looking in great depth at them in Chapter 3.
As you can tell, a successful consolidation project requires that you scrupulously gather a wide variety of information about the candidate SQL Servers and their databases. This information will help you determine how to consolidate user databases and provide before and after metrics.
The above tip is excerpted from Chapter 2, 'Planning your SQL Server consolidation,' of our original expert e-book, "Consolidate SQL Servers for availability, scalability and cost savings." This chapter explains six steps to consolidation and other key consolidation considerations.
About the author: Hilary Cotter has been involved in IT for more than 20 years a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor applied science degree in mechanical engineering from the University of Toronto and studied economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and currently working on books on merge replication and Microsoft search technologies.
