Migrating from MSDE to SQL Server 2008 R2 Express

Migrating from MSDE to SQL Server 2008 R2 Express

Applies to

Web Filter 7.6

Web Security 7.6

Web Security Gateway 7.6

Web Security Gateway Anywhere 7.6

In this topic

Overview

Backing up Websense data from MSDE

Restoring Websense data to SQL Server Express

Copying Websense data from MSDE

Detaching Websense data from MSDE

Attaching Websense data in SQL Server Express

Configuring 7.5 Log Server to SQL Server Express prior to upgrade to 7.6

Disabling MSDE services after upgrade

Overview

MSDE (Microsoft Desktop Engine) was supported by Websense Web security products prior to version 7.6. It is not supported for version 7.6.

This article contains instructions for migrating data from an existing installation of MSDE to SQL Server 2008 R2 Express for use by version 7.6 Websense Web security products.

In general, these procedures should be performed in conjunction with upgrading Web Security to version 7.6 (see Upgrading Web Security or Web Filter to 7.6.0). The instructions for upgrade will refer you to these procedures at appropriate points.

 

Important  Perform these procedures only on a properly operating installation of MSDE. Websense filtering and logging should be operating as normal. Performing this procedure will not fix a corrupted or inoperative installation of MSDE.

Note  For more about osql commands mentioned in these instructions, see Microsoft knowledge base article 325003 (http://support.microsoft.com/kb/325003) How to manage the SQL Server Desktop Engine (MSDE 2000) or SQL Server 2005 Express Edition by using the osql utility.

Backing up Websense data from MSDE

It is a best practice to back up Websense data from MSDE and restore the back ups to SQL Server Express if you want to continue using the data on version 7.6 Web Security solutions. Alternatively, you can copy and then attach the data (see Copying Websense data from MSDE) or detach and then attach the data (see Detaching Websense data from MSDE)

1.	Create a directory to hold database backups.

2.	On the machine running Websense Log Server, stop Websense Log Server:

Use the Microsoft Services console to stop Websense Log Server.

3.	On the MSDE machine, perform a backup of prior-version Web security databases (wslogdb70, wslogdb70_1, and additional partitions if present) by issuing the following commands using the osql utility:

osql -U sa -P <password>

use master

go

backup database wslogdb70 to disk = '<path to backup directory>\wslogdb70.bak'

go

backup database wslogdb70_1 to disk = '<path to backup directory>\wslogdb70_1.bak'

go

Repeat the last two commands for each additional database partition (e.g. wslogdb70_2, wslogdb70_3, and so on).

Restoring Websense data to SQL Server Express

Once you have installed SQL Server 2008 R2 Express (see Obtaining SQL Server) you can restore Websense data backed up from MSDE. If you copied or detached Websense data, instead of backing up, see Attaching Websense data in SQL Server Express.

1.	Start SQL Server Management Studio (Start > All Programs > Microsoft SQL Server 2008 R2 > SQL Server Management Studio

2.	Log in to SQL Server.

Use the credentials specified during installation of SQL Server Express.

3.	In the Object Explorer, right-click Databases and select Restore Database.

4.	In the Restore Database dialog box:

a.	Under Destination for restore, for To database type wslogdb70.

b.	Under Source for restore, select From device and click the browse button ().

c.	In the Specify Backup dialog box:

For Backup media, select File.

Click Add.

In the Locate Backup File dialog box, navigate to and then select the wslogdb70.bak file you created in MSDE.

Click OK.

d.	Under Select the backup sets to restore, select the Restore check box for the database that was entered above.

e.	Under Select a page (upper left of screen) select Options and verify the Restore As directory is correct. Also, make sure the currently logged-in user has write permissions for that directory path.

If you wish to continue accessing the old Websense database files in MSDE, change the Restore As column for both the .mdf and .ldf files so they specify a different location. If you restore these files to the default location, you may not be able to do so without overwriting the current versions of those files. If you overwrite the current versions, MSDE will no longer be able to work with them. Note that this applies only to working with old data in MSDE. Version 7.6 Web Security will use SQL Server Express for its data.

f.	Select OK to restore.

If an error appears stating the file cannot be restored over an existing file, click OK. Go to the Options page. Choose to restore the files to a different location than currently existing versions. See Step e for instructions.

If an error appears stating the file is in use by another process, click OK. Go to the Options page. Choose to restore the files to a different location than currently existing versions. See Step e for instructions.

g.	Repeat this process for each database partition (e.g., wslogdb70_1.bak, wslogdb70_2.bak, and so on).

5.	Review the SQL Server Express error logs. Resolve any issues before continuing.

6.	If you are running SQL Server Express on the same machine as MSDE, it is a best practice to disable MSDE services. See Disabling MSDE services after upgrade.

If you prefer to issue T-SQL commands to restore databases, here are sample commands:

use master;

go

restore database wslogdb70

from disk ='<path>\<file_name>'

with

move 'wslogdb70' to '<target_path>\wslogdb70.mdf',

move 'wslogdb70_log.ldf' to '<target_path>\wslogdb70_log.ldf'

go

restore database wslogdb70_1

from disk ='<path>\<file_name>'

with

move 'wslogdb70_1' to '<target_path>\wslogdb70_1.mdf',

move 'wslogdb70_1_log.ldf' to '<target_path>\wslogdb70_1_log.ldf'

go

 

(repeat for any other database partitions, e.g., wslogdb70_2, wslogdb70_3, and so on)

Copying Websense data from MSDE

It is a best practice to use the a backup-restore method for moving Websense data from MSDE to SQL Server Express (see Backing up Websense data from MSDE). However, you may copy .mdf and .ldf files to move the data instead.

1.	Create a directory to hold database backups.

2.	On the machine running Websense Log Server, stop Websense Log Server:

Use the Microsoft Services console to stop Websense Log Server.

3.	On the MSDE machine, using the Microsoft Services console, stop the MSSQLSERVER service (this stops MSDE).

4.	Copy the following files to the backup directory you created in Step 1:

wslogdb70.mdf and wslogdb70.ldf

wslogdb70_1.mdf and wslogdb70_1.ldf

.mdf and .ldf files for any other partitions, e.g., wslogdb70_2.mdf and wslogdb70_2.ldf, wslogdb70_3.mdf and wslogdb70_3.ldf, and so on.

By default, these files are located in either C:\Program Files\Websense (if Log Server is installed on the same machine as MSDE) or C:\Program Files\Microsoft SQL Server (if Log Server is not on the MSDE machine).

After installing SQL Server 2008 R2 Express, attach the data you copied from MSDE. See Attaching Websense data in SQL Server Express.

Detaching Websense data from MSDE

It is a best practice to use the a backup-restore method for moving Websense data from MSDE to SQL Server Express (see Backing up Websense data from MSDE). However, you may detach and attach database files to move the data instead.

1.	Create a directory to hold database backups.

2.	On the machine running Websense Log Server, stop and disable Websense Log Server:

Use the Microsoft Services console to stop Websense Log Server.

3.	Detach Websense databases in MSDE.

For example, using osql to log in to the database, use the following commands:

use master

go

exec sp_detach_db 'wslogdb70'

go

exec sp_detach_db 'wslogdb70_1'

go

(repeat for any other database partitions, e.g., wslogdb70_2, wslogdb70_3, and so on)

4.	Move the following files to the backup directory you created in Step 1:

wslogdb70.mdf and wslogdb70.ldf

wslogdb70_1.mdf and wslogdb70_1.ldf

.mdf and .ldf files for any other partitions, e.g., wslogdb70_2.mdf and wslogdb70_2.ldf, wslogdb70_3.mdf and wslogdb70_3.ldf, and so on.

By default, these files are located in either C:\Program Files\Websense (if Log Server is installed on the same machine as MSDE) or C:\Program Files\Microsoft SQL Server (if Log Server is not on the MSDE machine).

After installing SQL Server Express, attach the data you detached from MSDE. See Attaching Websense data in SQL Server Express.

Attaching Websense data in SQL Server Express

1.	Start SQL Server Management Studio (Start > All Programs > Microsoft SQL Server 2008 R2 > SQL Server Management Studio

2.	Log in to SQL Server.

Use the credentials specified during installation of SQL Server Express.

3.	In the Object Explorer, right-click Databases and select Attach.

4.	In the Attach Databases dialog box, select database files:

a.	Click Add.

b.	In the Locate Database Files dialog box, navigate to and then select the wslogdb70.mdf file you created in MSDE.

c.

Click OK.

d.	Repeat from Step a for each database partition (e.g., wslogdb70_1.mdf, wslogdb70_2.mdf, and so on).

Under Databases to attach, click each line and view the <name> database details section to verify the information for each database file.

5.

Click OK.

6.	If you are running SQL Server Express on the same machine as MSDE, it is a best practice to disable MSDE services. See Disabling MSDE services after upgrade.

If you prefer to issue T-SQL commands to attach databases, here are sample commands (executed in Query window or sqlcmd command prompt):

use master

go

exec sp_attach_db @dbname = N'wslogdb70',

@filename1 = N'<path>\wslogdb70.mdf',

@filename2 = N'<path>\wslogdb70_log.ldf';

go

exec sp_attach_db @dbname = N'wslogdb70_1',

@filename1 = N'<path>\wslogdb70_1.mdf',

@filename2 = N'<path>\wslogdb70_1_log.ldf';

go

 

(repeat for all remaining database partitions, e.g., wslogdb70_2, wslogdb70_3, and so on)

Configuring 7.5 Log Server to SQL Server Express prior to upgrade to 7.6

MSDE is no longer supported by Websense solutions in verison 7.6. In its place, SQL Server 2008 R2 Express (SQL Server Express) is supported.

When you upgrade a version 7.1 or 7.5 deployment, you can choose to use SQL Server Express in version 7.6. In this case, if Log Server was configured to use MSDE to store Websense data, the data must be migrated and then Log Server configured to use the installation of SQL Server Express prior to upgrading. The Websense installer is unable to upgrade Log Server if it is configured to MSDE.

Complete the following steps to configure Log Server to SQL Server Express.

1.	On the Log Server machine, update the ODBC connection to the Log Database:

a.	On the Log Server machine, open the ODBC Data Source Administrator (Control Panel > Administrative Tools > Data Sources (ODBC)).

b.	On the System DSN tab, select the Websense database (by default wslogdb70), and then click Configure.

c.	On the first screen in the Microsoft SQL Server DSN Configuration wizard, for Server, select the SQL Server Express instance on the SQL Server Express machine, either default instance (host name of SQL Server Express machine) or TRITONSQL2K8R2X.

If you named the database instance something other than default or TRITONSQL2K8R2X, then select that instance instead.

d.	Follow the on-screen instructions to update and verify the connection.

2.	Refresh the Log Server connection to the database:

a.	Open the Log Server Configuration utility (Start > Programs > Websense > Utilities > Log Server Configuration) and select the Database tab.

b.	Click the Connection button.

c.	In the Machine Data Source tab, select the appropriate Data Source Name, and then click OK.

d.	Enter the user name and password for the SQL Server account, and then click OK.

e.	Click Apply in the Log Server Configuration window to save the change.

f.	Click Start on the Connection tab to restart the Log Server service.

3.	Click OK to close Log Server Configuration.

Disabling MSDE services after upgrade

If SQL Server Express is installed on the same machine as MSDE, it is a best practice to disable MSDE so it does not consume system resources which can affect the performance of SQL Server Express.

Using the Windows Services console to stop the following services:

MSSQLSERVER

SQLSERVERAGENT

Migrating from MSDE to SQL Server 2008 R2 Express