Moving SQL Servers
Moving SQL Servers
Copyright 2008 Sophos Group. All rights
reserved. No part of this publication
may be reproduced, stored in retrieval
system, or transmitted, in any form or
by any means electronic, mechanical,
photocopying, recording or otherwise
unless you are either a valid licensee
where the documentation can be
reproduced in accordance with the lic e ns e
terms or you otherwise have the prior
permission in writing of the copyright
owner.
All other product and company names
are trademarks or registered trademarks
of their respective owners.
Document version 3.0
Published January 2008
2
Moving SQL Servers
Table of Contents
Moving SQL Servers with Sophos NAC Advanced....................................................................... 4
Moving Only the SQL Server..................................................................................................................................4
Moving Both the Applicatio n Server and SQL Server.............................................................................. ...............5
SQL Server Detach/Attach Proc edur e....................................................................................................................6
3
Moving SQL Servers
Moving SQL Servers with Sophos NAC Advanced
At some point, it may become necessary to move the production Sophos NAC SQL server to another SQL server
because of hardware problems or upgrades. Additionally, you may have detailed policy data set up in a test
environment that is necessary to keep when moving the Sophos NAC application server and SQL server into a
production environment. This document addresses the steps that are necessary to complete these tasks.
Moving Only the SQL Server
The following steps outline the process necessary to move the Sophos NAC SQL server databases from a test
environment to a production environment while keeping the same Sophos NAC application server.
Important: Sophos recommends that you back up your SQL server prior to performing these steps to ensure that
no data is lost.
1. From the Sophos NAC application server, log on to the Sophos NAC Web interface, go to the Configure
System > Server Key area, export the public/private key pair, and save the key pair to an external location.
2. Install the new SQL databases on the new server using the Sophos NAC SQLServerInstall.msi file on the
Sophos NAC installation CD. After the installation is complete, open SQL Enterprise Manager on the new SQL
server, and detach and delete all NAC databases and LDF files, which include: AlertStore, AuditStore,
GeneralStore, PolicyStore, ReportStore, ReportStoreCache, ReportStoreWH, SecurityStore. For more
information, see SQL Server Detach/Attach Procedure on page 6.
3. Put the application server into Maintenance Mode so that the SQL connections are suspended and Sophos
NAC Agents will not receive errors when trying to register, retrieve the policy, or report. To do this, run a
command prompt, go to the following directory “C:\Program Files\ENDFORCE\Support Tools\”, and then run
the following command: maintmode.exe /start
4. From the old SQL server, open SQL Enterprise Manager, and detach all NAC databases, which include:
AlertStore, AuditStore, GeneralStore, PolicyStore, ReportStore, ReportStoreCache, ReportStoreWH,
SecurityStore. Then, copy the MDF and LDF files to the new SQL server in the following location “C:\Program
Files\Microsoft SQL Server\MSSQL\Data”, or wherever the default SQL database location is set on this server.
5. From the new SQL server, open SQL Enterprise Manager, and attach all NAC databases that were detached
from the old SQL server in step 4. For more information, see SQL Server Detach/Attach Procedure on page 6.
6. Uninstall the application server using Control Panel > Add/Remove Programs.
7. Re-install the application server using the Sophos NAC Application Server.msi file on the Sophos NAC
installation CD, and point it to the new SQL server.
8. After re-installing the application server, log on to the Sophos NAC Web interface, go to the Configure System
> Server Key area, and import the public/private key pair that was saved in step 1.
9. After confirming that the new installation was a success, uninstall the old SQL server using Control Panel >
Add/Remove Programs, and delete the SQL server databases.
4
Moving SQL Servers
Moving Both the Application Server and SQL Server
The following steps outline the process necessary to move both the Sophos NAC application server and the
Sophos NAC SQL server databases from a test environment to a production environment.
Important: Sophos recommends that you back up your SQL server prior to performing these steps to ensure that
no data is lost.
1. From the Sophos NAC application server, log on to the Sophos NAC Web interface, go to the Configure
System > Server Key area, export the public/private key pair, and save the key pair to an external location.
2. Install the new SQL databases on the new server using the Sophos NAC SQLServerInstall.msi file on the
Sophos NAC installation CD. After the installation is complete, open SQL Enterprise Manager on the new SQL
server, and detach and delete all NAC databases and LDF files, which include: AlertStore, AuditStore,
GeneralStore, PolicyStore, ReportStore, ReportStoreCache, ReportStoreWH, SecurityStore. For more
information, see SQL Server Detach/Attach Procedure on page 6.
3. Put the application server into Maintenance Mode so that the SQL connections are suspended and Sophos
NAC Agents will not receive errors when trying to register, retrieve the policy, or report. To do this, run a
command prompt, go to the following directory “C:\Program Files\ENDFORCE\Support Tools\”, and then run
the following command: maintmode.exe /start
4. From the old SQL server, open SQL Enterprise Manager, and detach all NAC databases, which include:
AlertStore, AuditStore, GeneralStore, PolicyStore, ReportStore, ReportStoreCache, ReportStoreWH,
SecurityStore. Then, copy the MDF and LDF files to the new SQL server in the following location “C:\Program
Files\Microsoft SQL Server\MSSQL\Data”, or wherever the default SQL database location is set on this server.
5. From the new SQL server, open SQL Enterprise Manager, and attach all NAC databases that were detached
from the old SQL server in step 4. For more information, see SQL Server Detach/Attach Procedure on page 6.
6. Install the new application server using the Sophos NAC Application Server.msi file on the Sophos NAC
installation CD, and point it to the new SQL server.
7. After installing the application server, log on to the Sophos NAC Web interface, go to the Configure System >
Server Key area, and import the public/private key pair that was saved in step 1.
8. After confirming that the new installation was a success, uninstall the old application server and SQL server
using Control Panel > Add/Remove Programs, and delete the SQL server databases.
5
Moving SQL Servers
SQL Server Detach/Attach Procedure
The detach/attach procedure defined in the tasks above can be accomplished through the SQL Enterprise
Manager.
1. To delete the databases and their corresponding LDF files, find the physical location of the files on the hard
drive of the new SQL server (default location is C:\Program Files\Microsoft SQL Server\MSSQL\Data), rightclick each file as shown in the following screen shot, and click Delete. Likewise, you will need to copy these
same files from the physical location you used on the old SQL server to the same physical location on the new
SQL server.
6
Moving SQL Servers
2. To detach a database from the old SQL server, open SQL Enterprise Manager, right-click the database you
want to detach, and select All Tasks > Detach Database.
3. Repeat step 2 as necessary to detach all SQL server databases.
4. To attach a database on the new SQL server, open SQL Enterprise Manager, right-click the Databases folder,
and select All Tasks > Attach Database.
7
Moving SQL Servers
5. Click the ellipsis button to browse for the database, scroll down to C:\Program Files\Microsoft SQL
Server\MSSQL\Data, select the newly copied MDF files, and click OK.
6. The log file is automatically located. You must confirm or specify the new database owner. For each of the SQL
databases, specify the NAC account that you chose when you installed the application server and SQL server
as the database owner, and click OK.
8
Moving SQL Servers
7. A dialog box displays confirming that the database was successfully attached. Click OK.
8. Repeat steps 4-7 as necessary to successfully attach all SQL server databases.
9
Loading...