The process of registering servers and transferring objects may be new to many users of Microsoft SQL Server. In this section, I will walk you through the steps of this process and cover the warning messages and pitfalls you should watch out for.
Registering Servers
In order to transfer objects between two SQL server databases or even two different servers, you must register both servers in the Enterprise Manager. See Figure 3.7 for the Register Server dialog box. Type the name, login ID, and password you use to connect to each server, then select the Register button when finished. Close the dialog box and make sure both servers are displayed in the Server Manager Window as shown in Figure 3.8.
Figure 3.7 The Register Server dialog box.
Figure 3.8 The Server Manager window.
Note: If you fail to properly register both servers with a user ID that has permission on both servers to create objects, the transfer will not work properly. If you are unable to register a server with the Register Server dialog box, check your DB-Library setting to ensure your client connect is using the correct protocol.
Now you should make sure that the source server and database are correct, and that the destination server and database are correct. You can transfer objects from one location—for instance, a development database on a server—to another database on the same server. See Figure 3.5 for the Database/Object Transfer dialog box.
The checkboxes for configuring the transfer process are the next thing to consider. You have the ability to replace any existing data in the transfer by leaving the Replace Existing Data checkbox selected. If you uncheck the checkbox for transferring all objects, you can select the Choose Objects button
http://www.itknowledge.com/reference/standard/1576101495/ch03/089-096.html (3 of 4) [1/27/2000 6:15:44 PM]
to select only the specific objects you wish to transfer (see Figure 3.9). Beware of selecting the checkboxes that grab all the objects of a specific type; for instance, you might not want to move all tables in some situations. For an item-by-item transfer, use the bottom of the dialog box to select each object and place it in the right-hand list box.
Figure 3.9 The Choose Objects To Be Transferred dialog box.
If you should decide not to use the default scripting options, you can uncheck the Use Default Scripting Options box (see Figure 3.5) to enable the Scripting Options button. See Figure 3.10 for the resulting dialog box. This dialog box allows you to tune the transfer to meet your specific needs. Here you can specify the level of object scripting that will be performed as well as the security and permissions you wish to transfer to the new container. Passwords will be scripted as NULL, or empty, due to the encryption of the password.
You should make a note of this and have a script prepared for assigning passwords to the transferred accounts.
Figure 3.10 The Transfer Scripting Options dialog box.
Previous Table of Contents Next
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.
http://www.itknowledge.com/reference/standard/1576101495/ch03/089-096.html (4 of 4) [1/27/2000 6:15:44 PM]
Brief Full Advanced Search
Search Tips
To access the contents, click the chapter and section titles.
Microsoft SQL Server Black Book
(Publisher: The Coriolis Group) Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97
Search this book:
Previous Table of Contents Next
Moving Objects From Server To Server
Once you have configured the transfer options presented in Figure 3.5, you simply click the Start Transfer button to begin the transfer. You have the option of scheduling the transfer to happen at any time you wish, including on a recurring basis. The scheduled transfer is basically a task that is created and run by the SQL Executive service, like any other scheduled task on the server.
If you opt to transfer the objects now, you are presented with a set of progress bars showing the status of the transfer. The files created to perform this
transfer are stored in the MSSQL\LOG directory by default.
Upon completion of the transfer, you are prompted with the results. If the transfer completed with no errors, you can simply select OK and move on.
Warning Messages
Most transfers that involve user manipulation will transfer with warning messages. If this occurs, select the View Logs button. Most of the entries should be warnings that the passwords were scripted with no password assigned. These warnings should not preclude the use of the transferred objects.
Do not assume that this type of message is the only message in the log files!
Check each resulting log file completely before moving on.
You can open the resulting files in a text editor like Notepad or Microsoft Word by going to the \LOG directory on the server and opening each file independently. Always inspect these files after a transfer.
http://www.itknowledge.com/reference/standard/1576101495/ch03/097-099.html (1 of 2) [1/27/2000 6:15:45 PM]
Go!
Keyword
---Go!
Pitfalls
I have found that over time I spend less time with the Transfer Manager and more time creating scripts to manipulate the server objects. Scripts allow much more control than the Transfer Manager. Providing you feel comfortable with SQL statements and can use a query builder tool like Rapid SQL or a set of generated scripts, you should be able to get a much more detailed and controlled transfer between your databases and servers.
Permissions are the one pitfall to any scenario. You should take great pains to ensure that user permissions are maintained on your servers. If users attempt to access a new or modified object without the proper permissions, they will get an error, leading them to believe that your software is faulty.
You can avoid this scenario by paying attention to detail when transferring objects, regardless of the method you choose. If you choose to schedule the transfer of objects or data on your system at off-peak hours, as many
companies do, test the transfer as defined before assuming that it will work.
Keep your development and production servers configured as identically as possible at all times. I cannot count the number of times that a small difference between server configurations turned into a long night of troubleshooting.
And, as always, be sure to back up the master database.
Previous Table of Contents Next
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.
http://www.itknowledge.com/reference/standard/1576101495/ch03/097-099.html (2 of 2) [1/27/2000 6:15:45 PM]
Brief Full Advanced Search
Search Tips
To access the contents, click the chapter and section titles.
Microsoft SQL Server Black Book
(Publisher: The Coriolis Group) Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97
Search this book:
Previous Table of Contents Next
Chapter 4 Replication
• Installing The Distribution Database
• Setting Publication Options
• Creating Publications And Articles
• Setting Subscription Options
• Subscribing To A Publication Administrator’s Notes...
Replication has become a popular topic of discussion within medium-to-large corporate MIS departments. Many systems have been developed in the past few years that focus specifically on replication of data between two or more sites. Not surprisingly, I am often asked how replication might work in practical applications. This chapter focuses on that issue as well as others dealing with distributed data with Microsoft SQL Server. I will cover the basics of what replication is, how it performs the tasks needed to move data, and how the scalability of Microsoft SQL Server replication can be used to fit many company requirements.