Source Code Management and Database Deployment - Database Deployment
(Page 6 of 9 )
Traditionally, RDBMS installation is perceived as complicated, and your customer will understand that you need to send a database administrator (or a couple of them) to set up the environment. When you work with a file-based database system such as Access, database deployment is not a big issue. You usually create a setup program for your client application, and your database (mdb) file is just one more file that must be installed on the computer. When you are working in a client/server environment with SQL Server, you first have to install and configure the database server and then install databases.
Fortunately, installation of SQL Server has been simplified significantly. Almost anyone can perform it and there are usually no problems. In fact, SQL Server can be configured to be installed unattended. Microsoft SQL Server Desktop Engine (MSDE) is designed to be deployed on client computers using a special set of setup files that can be included in your setup program.
Some early versions of SQL Server required that all dependent objects be present on the server before a new object could be created. Administrators had to use many tricks to transfer a database from one server to another. The introduction of Deferred Name Resolution has reduced the complexity of database deployment in the SQL Server environment. For example, a stored procedure can be created even if it references a stored procedure that is not yet on the server. Unfortunately, it is not perfect yet. For example, it is not possible to create a foreign key that references a table that is not yet in the database.
The methods for database deployment can be divided into two groups:
- Deployment of a complete database
- Deployment of individual objects
Deployment of a Complete Database: Traditional Approach The idea behind this method is to use some means of moving the complete database so that relationships between individual database objects do not have to be managed once they are established. There are several options:
- Detach and reattach the database
- Use the Copy Database Wizard
- Back up and restore
The idea behind the first two options is to detach the database from the server, copy the database files to the production server, and then attach the database files to the new server (and reattach the database files to the original server, if applicable). To detach the Asset database manually, you can use the following script:
EXEC sp_detach_db 'Asset'
SQL Server checks the integrity of the database, flushes everything that is in memory to disk, stops further changes to the database, and releases database files.
NOTE
You must have exclusive use of the database to perform this function.
You can then copy the files (in this case, Asset.mdf and Asset_log.ldf) from the \mssql\data folder to a data folder on the target server. To attach the Asset database, you can use
EXEC sp_attach_db @dbname = 'Asset',
@filename1 = 'c:\Program Files\Microsoft SQL ',
+ 'Server\mssql\data\Asset.mdf'
@filename2 = 'c:\Program Files\Microsoft SQL '
+ 'Server\mssql\data\Asset_log.ldf'
If your database consists of more files, simply add them to the list of parameters. But if your database contains just one data file, you can use an alternative command:
EXEC sp_attach_single_file_db
@dbname = 'Asset',
@physname = 'c:\Program Files\Microsoft SQL ',
+ 'Server\mssql\data\Asset.mdf'
TIP
There is no harm in dropping the transaction log file and attaching just the data file (as long as you do not have some special reason, such as replication, to preserve the log).
You can execute these Transact-SQL statements manually in Query Analyzer or from the setup program. The setup program can use the command-prompt utility osql.exe to run a script file or use ADO to execute the script.
NOTE
I have chosen this method for deployment of the sample database to your computer.
A new feature found in SQL Server 2000 is the Copy Database Wizard. You can use it to copy (or move) a database on a known (production, testing, or some other) server. Behind the scenes, the wizard uses stored procedures for detaching and attaching the database. It also contains features for copying logins, error messages, jobs, and system stored procedures, which may be useful for completing server configuration. The disadvantage to this wizard is that it can be used only between servers on the same network. It is useful for deployment of databases used internally in a local department or organization, but not for deployment of databases that are required for shrink-wrapped software.
Another solution is based on creating a backup of the database on a development server and then restoring the database on a production server. Again, this can be performed manually or it can be scripted and included in the setup program.
Unfortunately, these techniques will not restore the links between server logins and database users. Server logins are stored in the master database; on different servers, different logins will have different IDs. Database users are stored in each user database. One of the parameters for a database user is the ID of the login to which it is attached. However, that ID is likely to refer to a different login on the production server. The simplest way to handle this problem is either to create all users again using Enterprise Manager or a script that you have prepared in advance, or to use roles instead of users as the foundation of your security solution. See the “Security” section in Chapter 10 for more information. SQL Server offers another solution to this problem—see “Synchronization of Login and Usernames” in Chapter 10.
Another disadvantage to these methods is that you have to maintain a “clean” database—a database that contains just database objects and seed data. Such a database can be delivered to a customer, but it cannot be used for development and testing. In both development and test environments, you need to add test data in order to test all features. You need to develop either scripts for adding test data or, alternatively, scripts for removing test data from a development database.
Next: Deployment of Individual Objects >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from SQL Server 2000 Stored Procedure & XML Programming, second edition, written by Dejan Sunderic (McGraw-Hill/Osborne, 2004; ISBN: 0072228962). Check it out at your favorite bookstore today. Buy this book now.
|
|
/ 15 
