SQL Express to SQL Server Database Migration MonitorIT v10.5

(1)

SQL Express to SQL Server

Database Migration

MonitorIT v10.5

(v10.5) March 2013

www.goliathtechnologies.com

(2)

2 Legal Notices

MonitorIT v10.5 Installation Guide

Goliath Technologies believes the information in this publication is accurate as of its publication date. The information is subject to change without notice.

THE INFORMATION IN THIS PUBLICATION IS PROVIDED “AS IS.” GOLIATH TECHNOLOGIES MAKES NO

REPRESENTATIONS OR WARRANTIES OF ANY KIND WITH RESPECT TO THE INFORMATION IN THIS PUBLICATION, AND SPECIFICALLY DISCLAIMS IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE.

Use, copying, and distribution of any Goliath Technologies software described in this publication requires an applicable software license.

Linux is a registered trademark of Linus Torvalds.

Windows is a registered trademark of Microsoft Corporation.

VMware, ESX, ESXi, vCenter, and vSphere are either trademarks or registered trademarks of VMware Corporation.

Citrix, Xen, XenServer, and XenCenter are either trademarks or registered trademarks of Citrix Systems Inc.

All other trademarks and copyrights referred to are the property of their respective owners.

(3)

3 I. Overview:

This migration document will cover the process of migrating from the embedded SQL Express 2005 Database to a new local or remote SQL Server Database.

II. Prepare the Database Files:

Prior to setting up the new server you will need to ensure that the original MonitorIT SQL Database file is ready to be moved and the new SQL Server is up and running.

1. Detach the database from its current location.

2. Make sure you know the database server name and instance name of your new SQL Server.

3. Management Studio is available to connect to the new SQL Server to facilitate setting up the database.

III. Configure the new SQL Server:

In this section we will cover the steps necessary to attach your MonitorIT SQL Express Database to the new SQL Server Database.

Attach the new database

1. Open SQL Server Management Studio 2. Right click on Databases and choose ‘Attach’

3. Choose ‘Add…’ and then find MonitorIT.mdf in the file tree.

(4)

4

a. When migrating to a remote SQL Server instance, this file would have been copied over to the local data repository after being detached

b. When migrating to a local SQL Server instances this file would be in the SQL data repository or it may still reside in MonitorIT’s default directory located at:

 On 32-bit systems go to C:\Program Files\MonitorIT\Databases

 On 64-bit systems go to C:\Program Files (x86)\MonitorIT\Databases

4. Under Database Details, just below where the MonitorIT.mdf file has just been listed, select the log file, and click the ‘Remove’ button at the bottom right.

a.

b.

c.

d.

e.

f.

g.

h.

i.

j.

k.

l.

a. You can identify which file in the table is the log file by looking at the File Type Column b. We are removing this log file because we want SQL to create a new log file

5. Select OK to complete attaching the database.

6. If MonitorIT will be located on a different server, check the security properties for this database.

There should be a Windows User with DBO rights to the database.

(5)

5 IV. Connect MonitorIT to your SQL Database

In this section we will cover the steps necessary to complete the final steps in the migration process by creating the necessary DSNs and appropriately modifying the windows service.

Create the Database DSNs

This section will cover the process for creating a connection to the database you have just attached to SQL.

1. On the MonitorIT server create a DSN to point to the database that you attached to SQL:

 On 32-bit Systems go to the ‘Start Menu > Administrative Tools > Data Sources (ODBC)’

 On 64-bit Systems go to ‘C:\Windows\SysWOW64\odbcad32.exe’

2. Once open, go to the System DSN tab:

2. If you have an existing DSN for ‘BreakoutRPM’ highlight the entry and select ‘Configure’ to rename it to be ‘OLD.BreakoutRPM’

3. Next click ‘Add’ and select the driver type:

a. For local SQL Server instances: choose ‘SQL Server Native Client’

b. For remote SQL Server instances: choose ‘SQL Server’

Note: Please ensure that you select the correct driver for the Product Version of SQL Server that you are connecting to:

 SQL Server Native Client 9.0: Local SQL Server 2005

 SQL Server 9.0: Remote SQL Server 2005

 SQL Server Native Client 10.5: Local SQL Server 2008 R2

 SQL Server 10.5: Remote SQL Server 2008 R2

(6)

6

4. Name the connection and then click ‘Next’

a. Define the Name as ‘BreakoutRPM’

b. Define Description as ‘Primary MonitorITDatabase’

c. For ‘Server’ use the dropdown menu to pick the appropriate server name

Note: Be sure to pick the choice with the name in the correct format server name/instance name of the new database from the dropdown menu.

5. Keep the default settings to use the Integrated Windows authentication to connect to the database and select ‘Next’

6. Check the box next to ‘Change the default database to’ and select ‘MonitorIT’ from the dropdown menu. Click ‘Next’

7. Keep the defaults for the additional options. Click ‘Next’

(7)

7

8. On the last screen, select the ‘Test Data Source’ button to validate the connection. Click ‘OK’ and

‘Save’

a. If your SQL Server is local, go to Windows Services and restart the MonitorIT Server Service, and you are finished!

b. If your SQL Server is remote go on to the next section for one last step

IV. Configure the MonitorIT Windows Service for Remote SQL Databases

When connecting to a remote SQL Server, it is necessary to modify the MonitorIT Server Windows Service with the appropriate logon rights to access the database.

1. Open Windows Services: ‘Start > Administrative Tools > Services’

2. Find the ‘MonitorIT Server Service’ in the list, right mouse click, and choose ‘Properties’

3. Go to the ‘Log on’ tab

4. Specify a Windows account that has DBO rights to the MonitorIT database you attached on the SQL Server (we recommend using the ‘Browse’ function to ensure the account gets validated)

5. Restart the Service. You have completed the configuration!