\[Editor's Note: It looks as if this month's Reader Challenge was a real stumper. We had no winners this month, but keep looking for the Reader Challenge in SQL Server Magazine UPDATE, a free, weekly SQL Server email newsletter, which you can order at http://www.sqlmag.com. The author's solution follows this month's problem.\]
Problem
Happy Numbers is a software company that develops and markets business-administration packages. The software supports several SQL Server code pages and sort orders. Unfortunately, because some developers use a SQL Server configured as case-sensitive and others don't, duplicate object names (using different cases) have turned up. The development team caught these duplications before the software launch, but the problem delayed new releases.
Marla, the IS director, decides to beef up testing. She wants to ensure that the development and support teams have access to SQL Server 6.5 and 7.0 installations, each with the various code pages and sort orders. Each developer has a Microsoft Developer Network (MSDN) subscription and can install SQL Server on local workstations for immediate functional testing. Switching between versions and sort orders needs to be as quick and easy as possible. Marla also wants to encourage testing across the network and on a server, which can hold more data than is practical on a workstation. She will install both configurations at several locations. To encourage frequent testing, Marla must let the developers easily synchronize restarts of SQL Server. Marla also wants the configuration to be Microsoft-supported.
Help Marla cut down the number of server machines to the minimum necessary for the testing. Provide step-by-step instructions for setting up the system so that the developers have access to the different SQL Server releases, character sets, and sort orders, both locally and on the server.
Author's Solution
The first step is to install SQL Server 6.5 and 7.0 on each local machine. Developers can easily switch between the two through the Switch program group (from the Windows Start menu, choose Microsoft SQL Server, Switch).
Each version of SQL Server needs to accommodate several character sets and sort orders. You could use the s switch to point out an alternative location for the master database and an alternative set of Registry keys, but I don't recommend this method. An easier setup is to establish several \data directories and swap their names when switching between database files and devices. This approach requires a restart of SQL Server and two rename operations, but this process is quick and easily automated.
Suppose you have a current SQL Server (6.5 or 7.0) with a case-insensitive sort order. You need to stop the SQL Server, rename the \data directory \INSENS, and create an empty \data directory. Then create a new set of database files, including a new master database (use SETUP.EXE for 6.5 and REBUILDM.EXE for 7.0) with the character set and sort order (case-sensitive) that you want. You're now ready to switch between the two sets of database files and devices. The following .BAT file switches from case-sensitive to case-insensitive, but you can easily reverse the order.
SwitchtoIns.bat NET STOP MSSQLServer ren Data SENS IF ERRORLEVEL 1 GOTO END ren INSENS Data :END NET START MSSQLServer PAUSE
This setup isn't complex, and Microsoft supports it. Instead of copying the database files, this approach renames directories. You can use the same method for setting up SQL Server 6.5 and 7.0, but you must have remote-control software to switch on the non-local server machine unless the developer walks to the server console to switch it. Considering the advantages this solution offers, this drawback is minor.
