Technical Library | Support

Go to the table of contents Go to the previous page Go to the next page
Migration Reference > Migrating from MSDE to SQL Server 2008 R2 Express
Migrating from MSDE to SQL Server 2008 R2 Express
Deployment and Installation Center | Web Security Solutions | Version 7.5.x
 
Applies to:                                         
In this topic                                       
*
Web Filter, Web Security, Web Security Gateway, and Web Security Gateway Anywhere, v7.5.x
*
Backing up the MSDE Log Database
*
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 connect to SQL Server Express prior to upgrade to 7.7
*
Disabling MSDE services after upgrade
MSDE (Microsoft Desktop Engine) was supported by Websense Web Security products prior to version 7.6. It is not supported for version 7.7.
This article contains instructions for migrating data from an existing installation of MSDE to SQL Server 2008 R2 Express.
In general, these procedures should be performed in conjunction with upgrading Web Security to version 7.7 (see Upgrading Websense Web Security Solutions). 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 the MSDE Log Database
To continue using your existing reporting data in v7.7, back up the v7.5.x MSDE Log Database and restore it to SQL Server Express. Although this is the preferred method, you can alternatively:
*
Copy and then attach the data (see Copying Websense data from MSDE).
*
Detach and then attach the data (see Detaching Websense data from MSDE).
To back up the data:
1.
Create a directory to hold database backups.
2.
On the Log Server machine, use the Windows Services dialog box (Start > Administrative Tools > Services) to stop the Websense Log Server service.
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 Microsoft 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, then click Add.
In the Locate Backup File dialog box, navigate to and then select the wslogdb70.bak file you created in MSDE, then 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 the MSDE Log Database). However, you may copy .mdf and .ldf files to move the data instead.
1.
Create a directory to hold database backups.
2.
On the Log Server machine, use the Windows Service Control Manager to stop Websense Log Server.
3.
On the MSDE machine, use the Windows Service Control Manager to 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 the MSDE Log Database). However, you may detach and attach database files to move the data instead.
1.
Create a directory to hold database backups.
2.
On the Log Server machine, use the Windows Service Control Manager 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 connect to SQL Server Express prior to upgrade to 7.7
MSDE is no longer supported by Websense solutions. In its place, SQL Server 2008 R2 Express (SQL Server Express) is supported.
When you upgrade a version 7.5 deployment, you can choose to use SQL Server Express. In this case, if Log Server was configured to use MSDE to store Websense data, you must do the following before upgrading:
*
The data must be migrated to the new database.
*
Log Server must be configured to point to the SQL Server Express installation.
The Websense installer is unable to upgrade Log Server if it is configured to connect to an MSDE database.
Complete the following steps to configure Log Server connect to a SQL Server Express database.
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 (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 tool to save the change.
f.
Click Start on the Connection tab to restart the Log Server service.
3.
Click OK to close the Log Server Configuration tool.
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 Service Control Manager to stop the following services:
*
MSSQLSERVER
*
SQLSERVERAGENT

Go to the table of contents Go to the previous page Go to the next page
Migration Reference > Migrating from MSDE to SQL Server 2008 R2 Express
Copyright 2016 Forcepoint LLC. All rights reserved.