Using the New Wizards in SQL Server 2000

Posted by arlene

SQL Server 2000 introduces the Copy Database Wizard and an enhanced version of the Index Tuning Wizard. Let’s take a look at both of these wizards, starting with the Index Tuning Wizard.

Tuning Indexes

The Index Tuning Wizard will assess your workload and give you suggestions for creating optimal indexes and statistics for your databases. The wizard can be launched from within Enterprise Manager by selecting Tools, Wizards. In the Select Wizard dialog, expand the Management list and choose Index Tuning Wizard. This will launch a screen.

Why use a wizard? I thought this is what a DBA is for? You are right! But the wizard allows us to decide on performance enhancements without having an exhaustive understanding of the intricacies between the data structures. The wizard will make recommendations for placing indexes on a workload by using the Query Optimizer to analyze the queries that you supply in the workload. It also assesses the impact on the database by placing an index on the table, view, or query and analyzes the use of the index based on the workload that you supply.

It will also recommend what you can do to enhance small queries that perform badly. Like most things in SQL Server 2000, you can change the recommendations to suit your own needs with some advanced options. Nice, isn’t

Copying Databases

The next wizard we will take a look at is the Copy Database Wizard. This wizard allows you to move databases from one server to another or between instances of SQL Server 2000 on one machine, and provides you with an easy way to upgrade SQL Server 7.0 databases to SQL Server 2000. This wizard does it all! (Well not quite, but you get the picture.)

You can launch the Copy Database Wizard from Enterprise Manager by selecting Tools, Wizards, and then expanding the Management list and selecting Copy Database Wizard. This will launch a screen.

I hear you say, “I can do this through a backup and restore operation.” Well that’s true, but when you do a backup and restore, although you get the whole database, you do not get everything that goes with that database, for example, any SQL Server 2000 logins or scheduled jobs.

With the Copy Database Wizard you get the lot. It really is just a case of point and shoot! You can even schedule the time that the process is going to take place, allowing for quiet periods on the server. This is especially useful if the database is very large.

As you can imagine, this makes migrating development databases to a live server a simple process. Now there are no more complex scripts to get all of the dependent objects on the database to replicate across to the new server, just a nice, simple process.

Possibly related posts: (automatically generated)
Using the New Wizards in SQL Server 2000

• Primary, Secondary, and Caching-Only Name Servers
• What Is a Domain Tree? What Is a Forest?
• The Active Directory and Dynamic DNS continue...
• What Role Will Your Server Perform?
• What Role Will Your Server Perform?
• Keeping Multiple Database Applications in Sync with Replication
• Active Directory Service Interfaces (ADSI)
• Windows NT Domain Controllers and Member Servers
• Directory Replication
• Future Programming, the Next Development Platform part 1