The actual process of transferring data can be accomplished in many ways. I find that a few preliminary steps can make the process smooth and reduce frustration:
1. Plan your migration and test it locally first.
2. Create another container that is not under the load of production and test your transfer to ensure you have not missed anything.
3. Test run the data or schema transfer to uncover any potential problems with your process.
There are three main methods that I use to allow for the transfer or migration of objects and data from one container to another:
1. BCP is a DOS-based utility that allows you to move data from one location to another in either native file format or ASCII text.
2. An INSERT/SELECT query is another method that I use very frequently. You have control over the column data types and remapping of existing data through a script that can be run whenever you need to move your data.
3. DBArtisan has a table edit utility that is easy to use. DBArtisan actually lets you modify the structure of an existing table with data and create a single script that will rename the existing table, create the new structure, map the old data to the new table, and re-create any dependent objects that rely on the target table.
Let’s look at each of these methods more closely.
BCP
The BCP (Bulk Copy Program) utility is perhaps the most under-documented utility in the Microsoft SQL Server arsenal. I have talked with many people who wish there were a graphic interface for this utility; by the time this book is printed, I am certain someone will have written
http://www.itknowledge.com/reference/standard/1576101495/ch03/086-089.html (1 of 3) [1/27/2000 6:15:41 PM]
Go!
Keyword
---Go!
one. I anticipate that Microsoft is also working on a graphic BCP utility for release in future versions of Microsoft SQL Server.
BCP must be run through the DOS prompt. I typically use a batch file that I can type into and run repeatedly for testing purposes. Some developers incorporate BCP batch files with the scheduled events in Microsoft SQL Server to allow for unattended bulk loading of data into or out of Microsoft SQL Server.
The following syntax for BCP from Microsoft SQL Server Books Online illustrates some of the many parameters used in BCP. The entire command goes on a single line, with a versatile list of options. Notice that the BCP utility can be used for moving data in and out of your SQL tables or views. See Table 3.1 for descriptions of each of the parameters.
bcp [[database_name .]owner .]table_name {in | out} datafile [/m maxerrors] [/f formatfile] [/e errfile]
[/F firstrow] [/L lastrow] [/b batchsize]
[/n] [/c] [/E]
[/t field_term] [/r row_term]
[/i inputfile] [/o outputfile]
/U login_id [/P password] [/S servername] [/v] [/a packet_size]
Table 3.1 Parameters used with BCP.
Table_name Source or destination of the data, depending on the direction of the operation.
In | out Direction of the Copy operation.
Datafile The name, including full path, of the source or target file.
Maxerrors (/m) Maximum limit of the number of errors that will be allowed until the operation is canceled.
Formatfile (/f) Name of the format file that can be used to control the mapping of data to specific columns.
Errfile (/e) Name of the file, including full path, for any rows that cannot be processed.
Firstrow (/F) First row in data file to process. Default is first row.
Lastrow (/L) Last row in data file to process. Default is last row.
Batchsize (/b) Number of rows to process per batch. Default is all rows in one batch.
/n Microsoft SQL Server native file format.
/c Character data type (ASCII text). Default column separator is a tab (\t), and the new line character is (\n).
/E Designates that there are identity columns in the file that should override the identity values currently in the table.
Field_term (/t) Field terminator.
Row_term (/r) Row terminator.
Inputfile (/i) Name of a file that redirects input to BCP.
Outputfile (/o) Name of a file that receives output redirected from the BCP utility.
Login_id (/U) Login ID for the user account with permissions to perform BCP.
Password (/P) Password for the user account provided to Microsoft SQL Server.
Servername (/S) Name of the Microsoft SQL Server you wish to connect to.
/v Reports the current DB-Library version.
http://www.itknowledge.com/reference/standard/1576101495/ch03/086-089.html (2 of 3) [1/27/2000 6:15:41 PM]
Packet_size (/a) Number of bytes per network packet sent to and from the server.
BCP is a powerful tool that can import hundreds of rows per second. This utility is commonly used for nightly transfers of data between non-SQL-based systems to Microsoft SQL Server.
For more details on the use of the BCP utility, see the Microsoft SQL Server Books Online.
INSERT/SELECT
Another method of moving data between two locations is the INSERT/SELECT statement.
With this tool, you have the ability to insert into one table the rows selected from another table.
This method is used often in scripts and stored procedures that must archive data. The
programmer must remap any conflicting data types and handle column order within their code, but the power of this type of statement is incredible. Using a SQL statement to re-order your columns or to handle data-type conversions is a capability that many third-party tools overlook.
Scripts are a way to supplement conversions and upgrades.
The INSERT/SELECT statement can also be used to recover records that have been
accidentally deleted from a system. In some situations, users delete records without realizing their importance. As a DBA, you do not want to restore an old backup and lose data that had been entered over the past few days, so you can use this alternate method of getting the records back. You would restore the backup file to a temporary device and database container. Once the restoration to the temporary database is successful, you can then select the old missing record or records from the temporary table and insert them into the production database.
With some care and planning, you can use this method to restore most records without affecting production. I explain the use and syntax of the INSERT/SELECT statement in Chapters 5 and 6.
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/086-089.html (3 of 3) [1/27/2000 6:15:41 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
DBArtisan
Figure 3.6 shows the DBArtisan Database Copy dialog box. This is another very useful tool for migrating not only structures, but also data from one location to another. DBArtisan’s graphic interface makes moving objects from one server to another very easy for the DBA. You just select the source and destination containers, then pick the items you want to copy.
Figure 3.6 The DBArtisan Database Copy utility.
Permissions
The issue of permissions is perhaps the one stumbling block many DBAs and database programmers experience on a regular basis. Most of the transfer tools described here allow you to copy an object’s permissions along with the object when you move it. In most development environments, not all users of the production database are fully mapped on the development server. Some shops require that a small percentage of actual users be mapped on the development system for test purposes. The best approach is to have a handful of “users”
mapped on the development machine for test purposes, and then any developers.
As long as you develop a good strategy for managing permissions, you should
http://www.itknowledge.com/reference/standard/1576101495/ch03/089-096.html (1 of 4) [1/27/2000 6:15:44 PM]
Go!
Keyword
---Go!
have little problem keeping unwanted users out of your server. I have found that using views and data modification through stored procedures provides a great deal of flexibility in the development environment while still keeping the production machine secure.
Note: Microsoft SQL Server deals with data access on a
database-by-database basis. Through the use of groups and group
membership, you can control access to your data. You are allowed to be a member of only one group other than Public on Microsoft SQL Server. This can cause you some problems in defining roles on your server.
Users And Groups
Users are defined as login IDs and passwords on your SQL server. Defining a user with a login ID and password does not give the user access to a
database—mapping the login ID to a database user provides access to data.
This login ID method allows many network users to connect to your server without being allowed any access to data. You can even use the Microsoft SQL Server Security Manager to automatically map Windows NT network users to SQL Server login IDs.
Assigning users to groups enables you to provide users who have similar functions the same amount of access to the database. The only real problem is that a user can be a member of no more than two groups at any given time.
This seems like a serious shortfall for Microsoft SQL Server, but you can overcome it with special code that assigns “hats” to users to allow them to perform specific tasks. In other words, a hat is a group with permission to perform certain acts in the database. Users should be given the ability to change hats to another group if their role needs to change.
Summary
• Use multiple servers in your development plans.
• Research third-party tools thoroughly before purchasing.
• Data model at the conceptual and physical layers, and keep your models up to date.
• Know your server parameters and how they impact your server.
• Develop a table structure to support server-side parameters for client applications.
• Only use DBCC PINTABLE on small lookup tables.
• Become familiar with using scripts to configure and edit objects on your servers.
• Pay attention to permissions when transferring objects between servers.
• BCP is a powerful and potentially fast DOS-based utility.
• Use INSERT/SELECT statements to perform selective recovery of records.
• Security is implemented on a database-by-database basis.
http://www.itknowledge.com/reference/standard/1576101495/ch03/089-096.html (2 of 4) [1/27/2000 6:15:44 PM]
• Users can be members of only two groups on Microsoft SQL Server (Public and one other group).