Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Template - Request for Proposal for Disaster Recovery Expand / Collapse
Author
Message
Posted Monday, September 6, 2010 10:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 9:34 AM
Points: 389, Visits: 959
Does anyone have a template for RFP for a DR? It may be for SQL 2000, 2005 or 2008. It may also be for clustering, replication, logshipping etc.

Post #981186
Posted Monday, September 6, 2010 11:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 12,889, Visits: 31,839
i have this saved in my snippets as a DR framework from a post that appeared on SSC fairly recently...I thought simon provided a pretty good list of things to cover for Disaster Recovery Scenarios: the only thing i would NOT do is bother to restore the master database...i would restore it as a user db to script out login information, but i not bother restoring the master as the core db.
Simon Facer (8/13/2010)
http://www.sqlservercentral.com/Forums/Topic967807-357-1.aspx

just went through an off-site DR recovery Exercise (Scottsdale AZ, in June - no matter how dry it is, 110 is still HOT; and for all you people in AZ, 6% humidity is NOT humid, contrary to what a local weather forecaster stated). This is the document I authored and used as the step-by-step guide, and it worked really well. You may not want all of this, and I removed anything specific to my company. This is for SQL 2008, and is written for a separate SSAS Instance.




1. Install .Net Framework 3.5, SP1
If needed, install .Net Framework 3.5, SP1.

2. Configure SQL Service Account with the ‘Perform Volume Maintenance’ permission (Only do this if your standards require it and / or permissions / standards allow it)
The ‘Perform Volume Maintenance Tasks’ allows SQL Server to allocate Data files (.mdf and .ndf) without zero-filling the file space, this will reduce database restore times by approximately 50%.
To grant ‘Perform Volume Maintenance Tasks’, (this requires Local Administrator rights):
i. On the Start menu, click Run. In the Open box, type secpol.msc on the server. The Security Policy editor opens.
ii. Under Security Settings on the left, go to Local Policies and under that to User Rights Assignment.
iii. Under Policy on the right side, go to "Perform volume maintenance tasks" and double click on it
iv. On the Local Security Setting tab click on the "Add User or Group" button
v. Click the ‘Object Types’ button, and check ‘Groups’ in the Object Types dialog. Click ‘OK’.
vi. In "Select Users, Computers, or Group", "Enter the object names to select" enter the SQL Server service account / service account group in Domain\Account format.
vii. Click [OK].

3. Install SQL Server
Instance Name [Your Server name here]
Components to Install
• RDBMS (SQL Engine)
• SSIS
• SSRS, in Native Mode
Service Accounts – use standard Service Accounts
• SQL Server [Service Account ???]
• SQL Agent [Service Account ???]
• SSRS [Service Account ???]
• Full Text [Service Account ???]
• SSIS [Service Account ???]
• Browser [Service Account ???]

4. Configure SQL Server

5. Install SSAS
Instance Name [Your Server name here]
Components to Install
• SSAS
Service Accounts – use standard Service Account
• SSAS [Service Account ???]

6. Configure SSAS

7. Recreate Users – OLAP
Add following Users to Server Role on SSAS:
• [Domain\User]
• [Domain\User]
(etc)

8. Patch SQL Server
Install SQL Server SP1 – apply to all installed components.

9. Restore master Database - SQL
1. Stop all SQL Server components in SQL Server Configuration Manager, and set everything to Manual Startup.
2. Start SQL Server in single-user mode:
In SQL Server Configuration Manager:
a. In the left pane, select SQL Server Services.
b. In the right pane, right-click SQL Server, and then click Properties.
c. On the Advanced tab, in the Startup Parameters box: insert -m in front of the existing startup options.
d. Click OK.
e. Start SQL Server.
3. Using SQLCmd, restore the master database:
a. Execute RESTORE DATABASE master from <device> WITH REPLACE
4. SQL server should have automatically stopped once the master database was restored, if not Stop SQL Server.

10. Modify the system database locations to match the DR Installation
1. Restart SQL Server with the –T3608 Trace Flag (NoRecovery)
In SQL Server Configuration Manager:
a. In the left pane, select SQL Server Services.
b. In the right pane, right-click SQL Server, and then click Properties.
c. On the Advanced tab, in the Startup Parameters box: insert –T3608 after the –m startup option.
d. Click OK.
e. Start SQL Server.
2. Using SQLCmd, modify the files for msdb, model and TempDB.
At a SQLCmd prompt:
a. Execute SELECT name, physical_name FROM master.sys.master_files WHERE Database_ID < 5
b. For each file listed, execute ALTER DATABASE {db} MODIFY FILE (NAME = {name}, FILENAME = '{filename}') where {db} is the database, {name} is the logical name from the list and {filename} is the path to the installed data folder with the file name from the list.

11. Restore msdb Database - SQL
1. Restore the msdb database:
a. In SQLCmd execute RESTORE DATABASE msdb from <device> WITH REPLACE
2. Restart SQL Server in No Recovery, and multi-user mode:
In SQL Server Configuration Manager:
a. Stop SQL Server.
b. Click SQL Server Services.
c. In the right pane, right-click SQL Server, and then click Properties.
d. On the Advanced tab, in the Startup Parameters box: remove -m from the startup options.
e. Click OK.
f. Start SQL Server.

12. Drop User Databases from restored master DB - SQL
1. Drop existing user databases, including ReportServer databases.
2. For each database:
a. Set the database to OFFLINE
b. DROP the database.

13. Restore User Databases from restored backups from tape - SQL
1. Restore user databases to default locations for SQL
ReportServer databases must be restored to the same name they were backed up from.

14. Restore User Databases - SSAS
1. Restore databases to default locations for all OLAP Server databases in the backups recovered from Tape

15. Recover Reporting Services
In Reporting Services Configuration Manager,
1. Point SSRS to the restored Report server Databases:
a. In the ‘Database’ tab, click the [Change Database] button,
b. Select ‘Choose and Existing Report Server Database’, click [Next],
c. Make sure the local SQL Server is selected, click [Next],
d. Select the restored Report Server database, click [Next],
e. Complete the dialog with [Next] until finished.

2. Restore the .snk encryption key file from the production SSRS Instance:
a. In the ‘Encryption Keys’ tab, select the [Restore] button,
b. Navigate to the File Location of the .snk file restored from tape,
c. Enter the Password, click [OK],




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #981206
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse