• Article

SQL Questions & AnswersClusters, Isolation, and More

Edited By Nancy Michell

Warm Standby Options

Q Is it possible with aliases and scripted DNS manipulation to have client applications start using a warm standby SQL Server™ (kept up-to-date via Log Shipping) in the event the primary SQL Server has an outage?

A Yes, this is possible. It’s described in a few places, including the book Microsoft SQL Server 2000 High Availability by Allan Hirt, Cathan Cook, Kimberley Tripp, and Frank McBath (Microsoft Press®, 2003). In the book, the authors describe how to accomplish this using Windows NT® Load Balancing Service (WLBS), but using a DNS switch would effectively be the same. You should note that this only works for log shipping (a failover solution provided by SQL Server 2000).

If you use WLBS, you would share an IP address between the active server and the standby server. Configure the WLBS cluster to put 100 percent of the load to the Active node, and then in the event of a failure, you could just switch this load value the other way. This move would be instantaneous, and you wouldn’t need to change anything in DNS for the clients to find the server.

With DNS, assuming it is integrated with Active Directory®, you may see some time lag in that record actually being updated on all DNS servers (obviously this depends on how many of your domain controllers are running DNS, and so on), resulting in the end user experiencing a delay of 15 minutes or more between when the server went online and when it came back online. And that is assuming that you made the DNS record change immediately following the failure.

Also, you have the local machine DNS Resolver Cache which, if the machine has resolved the name to the SQL Server within its time to live (TTL), will still be in the local cache and the DNS will not be queried again for this record until the TTL expires in the local cache.

Bad Coding Detector

Q I need a parser to detect bad SQL coding practices (like not using a fully qualified object name, old ANSI syntax, implicit conversions, and so on). Does Microsoft have one?

A Yes, absolutely. Check out the SQL Server Best Practices Analyzer at: Best Practices Analyzer Tool for Microsoft SQL Server 2000 1.0. This database management tool allows you to check that common best practices for the maintenance of SQL Server databases have been followed.

Clustering SQL Server 2000

Q I want to replace my standalone SQL Server 2000 with a cluster with the same netBIOS name, IP address (for the virtual server), and so on, so the end users don’t have to change any settings. I’ll be using new hardware for the cluster, so I thought I should build the new cluster, move the database to the cluster, then decommission the old server. Will this work?

A Unfortunately, that approach will not work because you cannot rename a SQL Server 2000 clustered instance after it is installed. The only way to rename a SQL Server 2000 failover cluster is to uninstall it and reinstall it with a new name. See the Knowledge Base article "How to Change a Clustered SQL Server Network Name".

The recommended way to do what you want is to build the Windows® portion including the server cluster. Then, if you really need to keep the same name and IP address, back up the databases (or detach them), shut down the old server completely, and remove it from the network. Then install the clustered SQL Server instance with the old name, and finally attach or restore the databases. This is the only way that you can keep the old SQL Server instance name since the old server is no longer on the network and there will be no IP address or name conflicts. To add the old server back to the network, it must be renamed and have a new IP address assigned.

If you did not need to keep the same name for the instance, things would be much easier. For example, if your application only used an ODBC DSN to connect to SQL Server, you could keep the same DSN, but just change the SQL Server that it is connecting to. That way you could configure a completely new SQL Server failover cluster without worrying about its name.

If you do not keep the same name, you can use log shipping to do your migration. That way you could do all of the installation and testing of the cluster, and start log shipping. Then, at a prearranged time, you can stop all application traffic that would access SQL Server, make sure the last transaction log is applied to the new clustered instance, and then do the cutover. You would encounter only minutes of downtime rather than have to face the possibility of a much longer downtime when doing a full database restore or a copy and attach.

Another potential option (assuming the current hardware is part of a Windows Catalog certified cluster solution which matches the other) is to add the current machine to the Windows server cluster and the Storage Area Network (SAN), move the databases to the SAN, and then use the standalone-to-cluster upgrade option in the SQL Server 2000 GUI. This would not be an option, however, in SQL Server 2005. This approach is not recommended for SQL Server 2005 because while it will work, there may potentially be more risk involved, and you will still have to have a new name for your SQL Server instance because the node name is already on the network (in other words, the old standalone database server which you just attached to the cluster and SAN).

Rebooting Advice

Q I have a dedicated SQL Server 2000 Enterprise (SP3+) Windows Server 2003 (no SP1) 2-node EMC SAN-attached cluster. Can you tell me how to properly reboot this box to avoid any potential disk corruption? I know this sounds so basic, but I’m stumped.

A It depends if you also want to reboot, restart, or do maintenance on the SAN. You may have to power down the cluster nodes. If you’re not performing online maintenance, then the order of the nodes really doesn’t necessarily matter as long as they are shut down properly. In general, shut down the node (or nodes) that doesn’t own any resources first, then the one owning the resources. Then you can cycle the SAN.

If you don’t have to do any maintenance to the SAN, you can cycle the node that doesn’t own resources, then cycle the one that does. That increases your availability since the one node will be recycled and the node that owned the resources can now switch ownership.

No scenario should corrupt data at the disk level. Corruption for the most part happens from poor management at the OS level, where you may have the wrong drivers which may cause hiccups when dealing with the SAN.

Importing XML Files

Q I have a 3.5GB XML file that I need to import into SQL Server. What’s the best way to do it?

A This depends on whether you want to keep it as a single document or whether it can be shredded into relational form or into multiple XML documents. In SQL Server 2000 and SQL Server 2005, the largest data cell can only contain up to 2GB of data. So even with the new XML datatype in SQL Server 2005, you cannot store your file in a single XML datatype; you’ll have to split it.

The best place to split it is on the middle tier. If you need to shred it into relational form, a good option is the SQLXML XML Bulkload object. If you just need to shred it into two or more XML documents, the approach depends on how you split it.

For a more complete look at the issue, read up on the use of bulk copy (bcp.exe) in SQL Server 2005 for importing XML data. This approach might provide better performance on the data import. (The Bulk Importing and Exporting XML Document from SQL Server Books Online is available at Bulk Importing and Exporting XML Documents. Just note that bulk copy does not perform the shredding. Also note that the SQLXML bulkload is also based on the bulk copy infrastructure.

Proper Isolation

Q If I start a transaction with snapshot isolation level on a database that was set to ALLOW_SNAPSHOT_ISOLATION, will this transaction be isolated from changes that happened on a second database that also happens to be set with ALLOW_SNAPSHOT_ISOLATION?

A The answer is yes. Here the isolation is implemented by having a global snapshot across all the databases in one instance. Row versions from all databases are stored together in tempdb—they could even be on the same tempdb page.

Q Where can I find some information about available isolation levels and which I should use for SELECT, UPDATE, DELETE, and INSERT operations within a particular transaction?

A The SQL Server Books Online and msdn.microsoft.com contain overviews as well as technical details on how to determine the correct isolation level. For example, the following isolation levels are explained at "Accessing and Changing Relational Data: Isolation Levels":

  • Read uncommitted (the lowest level, where transactions are isolated only enough to ensure that physically corrupt data is not read)
  • Read committed (the SQL Server default level)
  • Repeatable read
  • Serializable (the highest level, where transactions are completely isolated from one another)

As the document states, "Transactions must be run at an isolation level of repeatable read or higher to prevent lost updates that can occur when two transactions each retrieve the same row, and then later update the row based on the originally retrieved values. If the two transactions update rows using a single UPDATE statement and do not base the update on the previously retrieved values, lost updates cannot occur at the default isolation level of read committed."

Rebuilding an msdb Database

Q I have a corrupted msdb, I don’t have any backups, and the database consistency checking (DBCC) Repair was unable to fix all the errors. I was going to create a new msdb; however, I have set up replication with Microsoft CRM. So after creating a new database, will I need to reinstall SQL Replication from scratch or is there an easier method? Also, I’m not sure if there is any specific Microsoft CRM information kept in msdb.

A If you rebuild the msdb database, you’ll be required to reconfigure SQL Replication. SQL Replication (merge replication in your case since this is CRM-configured replication) creates many jobs (corresponding to Replication Agents).

The msdb database keeps information about all the jobs and Data Transformation Services (DTS) packages on SQL Server. If msdb is rebuilt, these jobs will be lost and a reconfiguration of SQL Replication will be required.

You will also have to recreate any DTS packages that are stored in SQL Server (as opposed to stored on disk), any maintenance or DTS jobs, and any custom created jobs. If you customized permissions in msdb, they will need to reapply the permissions changes.

Before you reinstall msdb you should try to remove replication cleanly. Depending on the type of corruption, this may not work, in which case you’ll have to clean up replication manually using the instructions in the Knowledge Base article 324401 "How to manually remove a replication in SQL Server 2000".

Monitoring Database Growth

Q I need to monitor database growth month over month, database log percentage growth per month, transaction volume per second, queries per second, and transaction disk recording per second. What tools can I use?

A You could use PerfMon. You can start with the following, but may have to add simple scripts to query system tables:

SQLServer:Databases\Log File(s) Size (KB)
SQLServer:Database\Log File(s) Used Size (KB)
SQLServer:Database\Percent Log Used
SQLServer:Database\Transactions/Sec

See the document "SQL Server 2000 Operations Guide: Monitoring and Control". You could also write some simple scripts or use Microsoft Operations Manager (MOM). Some of the Dynamic Management Views (DMVs) that are relevant for SQL Server 2005 might be useful:

  • Sys.dm_tran_active_transactions
  • Sys.dm_exec_connections
  • Sys.dm_exec_sessions
  • Sys.dm_io_virtual_file_stats
  • Sys.dm_io_pending_io_requests

SQL Server 2000 Backup Solutions

Q Can Microsoft System Center Data Protection Manager (DPM) be used as a backup solution for SQL Server 2000, or is the Volume Shadow Copy Service available for this purpose only in SQL Server 2005? Can I only use DPM to store away the backup files after they have been created?

A DPM currently only protects file systems, not SQL Server databases. It can protect applications that can be dumped to file. So you can use DPM to back up a file which contains a SQL backup. Just do a SQL backup to disk and protect that backup file with DPM. You can read more about Data Protection Manager on the DPM Web site.

Thanks to the following developers at Microsoft for their technical expertise: Alazel Acheson, Kevin Farlee, Cindy Gross, Allan Hirt (Avenade), Umachandar Jayachandran (UC), Dylan Kruger (Getronics), Michael Lisin, Marco Dorantes Martinez, Brent McCarthy, Akshay Mittal, Gabriela Nemeth, Al Noel, Allan Da Costa Pinto, Pragya Rastogi, Steve Schmidt, Gil Lapid Shafriri, Nirman Shetty, Charlie Shi, Chris Skorlinski, Barry Tousley, Aaron Vincent, Wei Xiao, and Greg Yvkoff.

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.