Installing a new backend database | Deploying Visual FoxPro Solutions

You have several options when installing a new database on your customer ‚ s database server. You can restore a backup from another server (like your development machine), run a script generated by your backend database server or hand-coded by a developer, or run automation code if your backend is SQL Server. We address applying changes to an existing backend database in the section ‚“SQL database structures and maintenance ‚½ in Chapter 9, ‚“Support and Ch-Ch-Changes. ‚½

Restoring a backup

Restoring a backup file differs depending on your backend database. The process might be different, but the concept is the same: take a backup from a different database server and restore it on the customer ‚ s server.

In SQL Server you first select the database in Enterprise manager, right-click the database, and select All Tasks Restore Database on the shortcut menu. The Restore Backup Dialog is displayed. Use the Help file to determine which of the restore options you want to set. You can restore from a different database, file or filegroups, or from a device.

The key in a new installation is to restore the entire database by selecting the ‚“database ‚ complete ‚½ option (see Figure 1 ). Restoring anything less is not complete and likely useless to your deployment.

Figure 1. The database restore process in SQL Server provides numerous options. The key to installing a new database is the restoration of the complete database.

The options page of the Restore database dialog (see Figure 2 ) has several key settings when restoring a new database. The first item is the Recover completion state. If your users will use the database with your application right away, make sure to leave the database operational. If you are restoring the fresh database ready for production over an existing beta or test database, make sure to check the ‚“Force restore over existing database ‚½ setting. The one setting confusing to developers when restoring a database is the backup retains information about the location of the original data and log files, in particular the original locations of the data and log files. In the case of restoring the database from your development machine or server, the logical file name and physical file name might not be compatible with your customer ‚ s database server. There is a very good chance you need to change the default settings to match the new database server folder structure.

Figure 2. The Options page of the Restore database dialog has the critical setting ‚“Force restore over existing database ‚½ if you need to overwrite a beta database.

This method is easy because built in tools exist to create the backup and restore it on the target machine. It is also a manual process on the restore because you typically have to make a couple of changes to the configuration.

Running a script to create the database and database objects

Visual FoxPro developers use the GenDBC program shipping with Visual FoxPro to create a program to generate a database container, tables, views indexes, relations, and add the stored procedures from an existing database container. What it does not do is populate the tables with data. This same concept exists in SQL backend databases, but instead of Visual FoxPro code, it generates script code in the native database manipulation language. This code is used to generate a new copy of the database on the customer ‚ s database server.

The generated script can be edited if necessary, deployed to the client database server, and executed. In SQL Server you can script the entire database following a few easy steps:

Start SQL Server Enterprise Manager
Select the database
Right-click for the shortcut menu
Select All Tasks Generate SQL Script ‚

The Generate SQL Scripts dialog is displayed. Depending on your requirements, the amount of detail you want scripted, and the changes needed on the customer ‚ s server, you can make a number of choices to customize the script. First click on the Show All button to make choices on the individual object types or to script all objects (see Figure 3 ). If necessary, you can add and remove individual objects with the Add and Remove buttons . There are numerous formatting options on the Formatting page, and several more customization options on the Options page. Click OK to generate the scripts (see Figure 4 ).

Figure 3. The SQL Server Generate SQL Scripts dialog allows you to generate one script with all the Transact -SQL code necessary to regenerate an empty database.

Figure 4. The SQL Server Generate SQL Scripts dialog displays a progress bar when you start the scripting process.

Once the script is deployed, it can be executed on the database server in a tool similar to the SQL Server Query Analyzer. We recommend testing the script to verify it works as you would expect before shipping it to the client ‚ s site. Once the database is created, run your application and verify how it works against an empty database.

Using SQL-DMO

SQL Server has a complete object model available through the SQL-DMO object. You can automate the creation of database, tables, views, add users, triggers, and stored procedures. All the features a person can administer within SQL Server Enterprise Manager can be programmatically controlled through the SQL-DMO object model. A Visual FoxPro program can process all of this. You create an empty database when the program is deployed and implemented by you, your users, or the database administrator.

Why would you want to programmatically create the database using SQL-DMO? It is a repeatable process and requires little to no user interaction. The process of generating the code is definitely tedious . It works on the same premise of executing scripts, but the user does not need to understand running the SQL Query Analyzer or another script executing tool. If your users can run scripts or you have an automated script runner, you will find the script generating capabilities of SQL Server to be much faster than writing code. This is also SQL Server specific because it is an object specifically for automating SQL Server.

Note ‚

We can recommend an excellent book on SQL-DMO if you are interested in this topic called ‚“Real-World SQL-DMO for SQL Server ‚½ by Mark Allison and Allan Mitchell (ISBN ‚ 1590590406).

The users need several files loaded and registered to access the SQL-DMO object. These files load on your development machine when you install SQL Server. You cannot count on your users having these files installed so you need to include them in your deployment package. See Table 1 for the complete list and their location.

Table 1: List of SQL-DMO files you need to distribute with your application.
File	Development Machine
SQLDMO.DLL	{SQL Server folder}\80\Tools\Binn
SQLDMO.RLL	{SQL Server folder}\80\Tools\Binn\Resources\1033
SQLRESLD.DLL	{SQL Server folder}\80\Tools\Binn
SQLSVC.DLL	{SQL Server folder}\80\Tools\Binn
SQLSVC.RLL	{SQL Server folder}\80\Tools\Binn\Resources\1033
SQLWID.DLL	{Windows System Folder}
SQLWOA.DLL	{Windows System Folder}
W95SCM.DLL	{SQL Server folder}\80\Tools\Binn

We found several copies of the DMO files listed in Table 1 because we have several third- party tools loaded to interact with SQL Server. It is our understanding SQLWID.DLL and SQLWOA.DLL need to be installed in the Windows System folder, the rest can reside in the application folder or in a folder on the Windows path .

On The Web ‚

We included a sample program call SqlDmo_CreateDb.prg included in the chapter downloads available from www.hentzenwerke.com to show you how to create a database using SQL-DMO. You need to change the lcSQLServerName and lcSAPassword variables to match your SQL Server installation to make this program work.

Listing 1. The following Visual FoxPro code creates a database called DeployFoxCS in your instance of SQL Server.

 LOCAL loSQLServer AS "SQLDMO.SQLServer", ;  loDatabase AS "SQLDMO.Database", ;        loDatabaseFile AS "SQLDMO.DBFile", ;        loLogFile AS "SQLDMO.LogFile", ;        lcDirectory AS Character, ;        lcSQLServerName AS Character, ;        lcSAPassword AS Character   *? Change these to match your SQL Server  lcSQLServerName = "SubstituteYourServerNameHere"  lcSAPassword = "ChangeToYourSAPassword"   loSQLServer = CREATEOBJECT("SQLDMO.SQLServer")  loSQLServer.Connect(lcSQLServerName, "sa", lcSAPassword)   loDatabase = CREATEOBJECT("SQLDMO.Database")  loDatabaseFile = CREATEOBJECT("SQLDMO.DBFile")  loLogFile = CREATEOBJECT("SQLDMO.LogFile")   * Ensure folder exists before creating database   * Otherwise it fails with automation error  lcDirectory = FULLPATH(SYS(2023)) + "\Data\"   IF DIRECTORY(lcDirectory)     * Nothing to do  ELSE     MD (lcDirectory)  ENDIF   * Define the database name  loDatabase.Name = "DeployFoxCS"   * Define the primary data file  loDatabaseFile.Name = "DeployFoxCSData"  loDatabaseFile.PhysicalName = lcDirectory + "DeployFoxCSData.MDF"  loDatabaseFile.PrimaryFile = .T.  loDatabaseFile.FileGrowthType = 0 && Growth in MB  loDatabaseFile.FileGrowth = 1 && 1 MB  loDatabase.FileGroups("PRIMARY").DBFiles.Add(loDatabaseFile)   * Define the transaction log file  loLogFile.Name = "DeployFoxCSLog"  loLogFile.PhysicalName = lcDirectory + "DeployFoxCSLog.LDF"  loDatabase.TransactionLog.LogFiles.Add(loLogFile)   * Add the database to SQL Server  loSQLServer.Databases.Add(loDatabase)   * Release object reference variables  loLogFile = .NULL.  loDatabaseFile = .NULL.  loDatabase = .NULL.  loSQLServer = .NULL.   RETURN  *: EOF :*

The code listing does not demonstrate how to create tables, add users, populate stored procedures, and various other database objects. The SQL-DMO model is well documented and we decided it is an exercise best left to the readers. Be cautious, this method of deploying a database requires careful management of changes, manually programming all objects, and tighter project management in teams .

Data Transformation Services (DTS)

Developers with deployments on the same network might select SQL Server ‚ s Data Transformation Services (DTS) to move the tables, views, stored procedure, users, and other SQL Server objects directly from one SQL Server to another. DTS has an Import/Export Wizard process to transfer objects from one SQL Server installation to another.

The wizard part of the SQL Server Enterprise Manager is started by selecting the Data Transformation Service node and selecting All Tasks Import Data ‚ option on the shortcut menu. In the first step of the Wizard (see Figure 5 ) pick the server and database containing the database you want to copy. You select the security login based on the server. If the server supports Windows Authentication, your current user ID is authenticated as a user on the database server machine. You can select SQL Server Authentication if the server supports this security scheme, but you need to provide the user ID and login password. The selection here would be your development database.

Figure 5. Step 1 ‚ Choose a Data Source.

Step two is the selection of the server and database where you want to copy the database objects (see Figure 6 ). In the case of a deployment, this would be the customer ‚ s database server and the database. Again, select the appropriate security model to login on the server.

Figure 6. Step 2 ‚ Choose a destination.

Step three of the Import/Export Wizard (see Figure 7 ) is where you determine exactly what is copied . The first option is tables and views only. This option skips many of the objects necessary for a complete deployment, like stored procedures and users. The second option,

Figure 7. Step 3 ‚ Specify Table Copy or Query.

‚“Use a query to specify the data to transfer, ‚½ allows you to write queries that produce result sets for moving information. Again, this is not the easiest approach to initialize an entire database. The third option, ‚“Copy objects and data between SQL Server databases, ‚½ provides the flexibility and simplicity to move the database objects into the production environment.

If you select the ‚“Copy objects and data between SQL Server databases ‚½ option you are presented with the fourth step of the Import/Export Wizard (see Figure 8 ). This is the step where you can determine how you want the objects created, if you want the data copied, which objects you want to copy (tables, views, stored procedures, user-defined functions, defaults, rules, user-defined data types), and advanced options (users and roles, logins, permissions, indexes, triggers, and keys). You specify the script file directory so the scripts that run the entire process are saved in a folder. You can review these scripts once they are created.

Figure 8. Step 4 ‚ Select Objects to Copy.

Lastly, you determine if you want to run the process immediately or schedule it for later execution ( Figure 9 ). This allows you to schedule a deployment off-hours if necessary.

Figure 9. Step 5 ‚ Save, schedule, and replicate package.

The wizard is easy to use for moving a development database into production, but it can also be used to convert the data from one database format to another. You can save the package to run over and over. This is especially handy when you deploy a test version for user acceptance testing and want to override the old test database with a current one for the new round of testing.