Instructions for moving the MessageStats Database from SQL 2000 to a SQL 2005 or SQL 2008 server. (4333978)

Instructions for moving the MessageStats Database from SQL 2000 to a SQL 2005 or SQL 2008 server.

Please note for Messagestats 6.7 or higher only SQL 2005 SP3 or SQL 2008 RTM or SQL 2008 SP1 is supported

Backup the MessageStats database on SQL 2000:

1. Start SQL Enterprise Manager on the source machine.
2. Expand the databases node.
3. Right-click on the MessageStats database and select All Tasks | Backup Database.
4. Under the Destination section of the backup database window, click the Add button.
5. Choose a name for the MessageStats database backup (i.e. MessageStatsDb.bak) in the File name field.
6. Ensure the Overwrite Media option is set (This is in the Overwrite section of the backup window).
7. Click OK from the backup database window.
8. Copy over the .bak file to the new SQL 2005\2008 Server

Restore the MessageStats database from a backup to SQL 2005\2008:

1. Start SQL Server Management Studio on the destination machine and connect to SQL Server.
2. Right-click on the Database node and select New Database, calling it MessageStats and click Ok to create
3. Right-click on the Databases node and select Restore Database...
4. Select MessageStats in the To database: field under Destination for restore.
5. Copy over the .bak file from the SQL 2000 Server to the SQL 2005\2008 under \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup
6. Under the Source for restore, select From device, click the ellipsis button (...), click the Add button, locate the .bak file which was copied over from the SQL 2000 Server, select the .bak file and click Ok twice.
7. Under Select the backup sets to restore: put a check mark in the Restore Box beside the restore.
8. Select the Options on the left of the window under Select a page and put a check mark in the box for Overwrite the existing database.
9. Click OK from the Restore database window to restore the database.

After the restore completes please do the following:

1. In SQL Management Studio - Right click on the Messagestats database and choose properties.
2. Click on Options.
3. Change the Compatability Level to match the version of SQL the Messagestats database resides on.

The following section will have to be completed on the new SQL server.

Security Groups are needed to assign rights to the database. Either Local or Global Groups can be used called MessageStats Admin and MessageStats Web.

***NOTE*** If the MessageStats database resides on a cluster the Groups will have to be created in Active Directory.

1. Launch Computer Management and expand System Tools | Local Users and Groups| Groups to create Local groups or launch Active Directory Users and Computers to create Global groups.
2. Create two groups one called MessageStats Admin (for accounts performing a gathering and administering the MessageStats console) and another called MessageStats Web (for accounts running reports).

***NOTE*** Make sure the MessageStats service account is added to the MessageStats Admin group.
***NOTE*** The Everyone group is typically added to the MessageStats Web group.

To assign the MessageStats Admin group to the MessageStats database â€
1. Launch SQL Management Studio and expand Security | Logins
2. Right click on Logins and choose New Login.
3. Click on search and find the MessageStats Admin group. Click OK.
4. In the left hand column click on User Mapping.
5. For the Users Mapped to this Login section put a check mark in the box beside the MessageStats database.
6. Under the Database role Memberships for section select Public, MessageStats_Admin and db_ddladmin

To assign the MessageStats Web group to the MessageStats database â€
1. Launch SQL Management Studio and expand Security | Logins
2. Right click on Logins and choose New Login.
3. Click on search and find the MessageStats Admin group. Click OK.
4. In the left hand column click on User Mapping.
5. For the Users Mapped to this Login section put a check mark in the box beside the MessageStats database.
6. Under the Database role Memberships for section select Public, and MessageStats_web.
Â

IIS Security:

To ensure that security is properly setup on the IIS Server, open Regedit and edit the HKLM\Software\Quest Software\MessageStats\Client DB and change the SQLConnect value to reflect the new SQL Server Computer Name. If you are using the .UDL file, please open it on the IIS Server where the SQLConnect value indicates and change the SQL Server Computer Name to the new SQL 2005\2008 Server.

Switching the Console to use new SQL 2005\2008 Database:

When launching the Console, you will have to go into the Properties of the Scheduler Server which is located under the MessageStats node by right clicking. Once in the Properties on the Database Tab under the Locate the Database on the following SQL Server and click the ellipsis button (...) and enter in the name of the SQL 2005\2008 Server. Test the connection and click Ok.