The Upgrade Wizard also lets you change the ANSI Nulls and Quoted Identifiers settings. The important point to remember is that the new database's ANSI Nulls option setting doesn't change the NULL/NOT NULL setting of the columns in the upgraded database. The NULL/NOT NULL property of the upgraded columns is determined by the ANSI Nulls settings in the SQL Server 6.5 database so as not to disrupt any existing functions, procedures, or tasks.
The upgrade is a little more complex for Quoted Identifiers. If you're certain that all the objects in the SQL Server 6.5 database were created in the same way, either with Quoted Identifiers on or off, select that same setting when upgrading the database. If you're not sure of the Quoted Identifiers setting or if the SQL Server 6.5 objects were created with varying settings, select the Mixed (or don't know) option, as Figure 7 shows. If you choose this option, the Upgrade Wizard looks for objects with double quotes, then converts them with Quoted Identifiers set to ON. Then, it converts the remaining objects and sets their Quoted Identifiers to OFF.
As a final step, the Upgrade Wizard generates a summary of the choices you made, with warnings if appropriate. You can scroll through the summary as presented, or you can view it in Notepad. The Notepad option is nice because you can view the summary report, then save it to a file for future reference. This screen contains the odd instruction Click Finish to begin your upgrade. As the upgrade progresses, it adds a line for each step to a status screen (as Figure 8 shows), so that you can follow the progress of the upgrade. A pop-up window lets you know when the upgrade is finished. You can then turn your attention to making the databases fully SQL Server 2000—compliant. You can even start taking advantage of the many new features in SQL Server 2000. For more information about what to do next, see the Web-exclusive sidebar "Before and After."
Double Parking
Now, here's a suggestion for that old server if you're upgrading to a new server. First, remove all the databases from the old server after moving them to the new server. Be sure you won't need the old SQL Server 6.5 files anymore. Then, upgrade the old server to SQL Server 2000 also. Identify the databases and tables that are most heavily used for reporting and those that analysts and managers use for browsing the data in decision-support mode. Now replicate those databases, whole or in part, as appropriate, to the old server. Redirect the users who query but don't update the databases to the replicated databases on the old server. Because these users are just querying the data, their queries can use the WITH NOLOCK option, so their queries will run faster and won't be slowed down by any update operations (except for replication, which happens quickly and only at intervals). You can experiment with setting the transaction isolation level to read uncommitted for even higher levels of concurrency. And you can build different indexes on the two servers. Data entry people want few but efficient indexes to assist in update operations. Data analysts want to index everything to speed up their complex queries. You can resolve this classic conflict and look like a hero by giving each group a separate copy of the database. Keep in mind that you need the appropriate licenses for SQL Server 2000 and the client access licenses (CALs), so the cost might outweigh the benefits. If so, you can always use the old computer as an MP3 file server instead.