Lab Exercise 4

SQL Recovery with Auto Snapshot Manager

Purpose: Upon Completion of this lab exercise the student will be able use the Dell EqualLogic Auto-Snapshot Manager tool to create application-consistent snapshots of Microsoft SQL Server databases Goals and tasks: Setup and configure a test database on the Dell EqualLogic SAN Perform SQL Database protection and recovery with Auto-Snapshot Manager Create a Smart Copy schedule Locate the schedule on the server Configure the database volumes for replication Perform a Smart Copy replication of the database Hardware and software requirements: 1. 2. 3. 4. 5. PS Series Storage Array. Active Gigabit Switch Necessary cables to connect the PS Series Storage Array to the network. Host Integration Tools CD PC running GUI.

Terminal Console SQL Server

Lab Exercise 4 Version 3.2

SQL Recovery with Auto-Snapshot Manager

Page 4-1

I.

Setup and configure a test database on the Dell EqualLogic SAN

This section of the lab describes the steps you need to create a database that is stored on the Dell EqualLogic SAN.

NOTE: All servers used in this class have Microsoft SQL Server 2005 installed.

1. Create 2 volumes on your storage groups as follows: Name DB-DATA DB-LOG Size 50 GB 10 GB Access Control SQL Server IP Address SQL Server IP Address

2. From your server, log on to both volumes and format and initialize the volumes. 3. Create a directory called SQL-DATA on the DB-DATA volume. Create a directory called SQLLOG on the DB-LOG volume.

4. From the Start Menu on your server, launch Microsoft SQL Server 2005 -> SQL Server Management Studio, and connect to your SQL server (In lab, the SQL server name will be the same as your system name). 5. Expand the Databases object and review the list of existing databases 6. Right-click on Databases and select create new database. A New Database window will appear. a. In the Database Name field, give your database an easy to remember name like Test b. Navigate through the Database Files area of the window to the Path column, and place your files as follows: i. Test data file: volume DB-DATA, directory SQL-DATA ii. Test log file: Volume DB-LOG, directory SQL-LOG c. In the upper left area of the New Database window, highlight the Options selection. Make sure that your Recovery Model is set to Full.

d. In the lower right corner of the New Database window, select OK to finish creating your database. e. Verify that your new database is listed in the Databases list of SQL Server Management Studio. f. Optionally, create a small, simple table in your new database, and enter some data. Ask your instructor for assistance if necessary.

II.

Create a Smart Copy Set of a Database

This section of the lab describes the steps needed to create a Smart Copy of a database, and to locate the elements of that smart copy.

Lab Exercise 4

SQL Recovery with Auto-Snapshot Manager

4-2

1. In Auto-Snapshot Manager, in the left-hand column, right-click on Dell EqualLogic AutoSnapshot Manager, and choose Refresh. 2. In the left-hand column, select Applications, then SQLServerWriter, then your server, and ensure that your newly created database is visible with a blue icon. Right-click on the database, and select Create Smart Copy Set. In the Create Smart Copy window, then select Snapshot and Backup Type: Full, and press Next. Review the summary screen and press Finish. 3. Open the Group Manager for your PS Series. Were snapshots created for volumes DB-DATA and DB-LOG? _____________ 4. Return to the Dell EqualLogic Auto-Snapshot Manager window. Under Smart Copies, highlight the smart copy that you just created. In the right-hand Actions area, select View Backup Document. 5. Remember that a smart copy consists of: a. The volume snapshots on the PS Series SAN b. The backup document related to those snapshots. The backup document is critical to recovery. III. Create a Smart Copy schedule

In this section, you will create a schedule to perform smart copies of your database. 1. In Auto-Snapshot Manager, right-click your database, and select Configure New Schedule or, left-click the database, and select configure new schedule from the Actions area on the right hand side. a. When warned that e-mail notification is not configured, select no to skip e-mail configuration. b. Select Daily or more frequent as the frequency for this schedule , then press Next c. Accept the defaults for Daily Schedule Settings; press Next. d. In the Advanced Schedule Settings window, select repeat task and choose the shortest interval possible. What is the shortest repetition interval allowed by the system? _________________________________________________________________ e. On the Smart Copy Options screen, select Snapshot and full as the options. Set the Keep Count Setting to 12. f. On the following screen, enter the Administrators password (eql) when prompted.

2. Check the SAN to verify that snapshots are created per the schedule. 3. If you create a schedule using Auto-Snapshot Manager, is the schedule itself stored on the SAN? ____________________ 4. Locate the schedule, and describe its location here: ________________________________________________________________________ ________________________________________________________________________ IV. Corrupt the Database

Next we will corrupt the database and then recover it.

Lab Exercise 4

SQL Recovery with Auto-Snapshot Manager

4-3

NOTE: The process for corrupting the database will differ slightly if you are using the Express version of Microsoft SQL Server 2005. With SQL Express, you must run a query or a script in order to take the database offline and return it to the online state. 1. Method 1 Edit the table and manually damage data a. Use this method if, earlier, you created a table in your database. b. Open the table, and type over existing data with incorrect data. c. Proceed to database recovery.

2. Method 2 Delete the entire database data file a. In SQL Server Management Studio, right-click on your database, select Tasks, and select Take offline. b. Using Windows Explorer, open the volume DB-DATA and delete the .mdf file from the SQL-DATA directory. c. In SQL Server Management Studio, right-click the database, and select properties. You should receive an error message.

d. Proceed to database recovery. e. If you are using the Express version of SQL Server 2005, see the instructor for the Transact-SQL syntax required to take the database offline and return to online state

V.

Recover the test database using the Auto-Snapshot Manager

Now we will recover the damaged database.

1. In Auto-Snapshot Manager, select a smart copy of your database that was created prior to the simulated database fault. 2. In the Actions area, select Restore (database name) a. Do not select Restore All or Restore All as New 3. In the Restore database from smart copy window, select Fully Recover. 4. Press finish. 5. It will take a few minutes for the restoration process to complete. 6. When Auto-Snapshot Manager is finished recovering the database, return to SQL Server Management Studio and verify that your database is once again operational. You will need to place the database back online in order to view it. VI. Create a Duplicate of the Database Using Auto-Snapshot Manager

In this section, we will use ASM to create a new copy of the database. The copied database can be used instead of the production database for purposes such as development or data mining. 1. In Auto-Snapshot Manager, select a smart copy of your database. Record the timestamp of that smart copy in the space below: _____________________________________________________________________________ 2. Perform a Restore All as New action on the selected smart copy.

Lab Exercise 4

SQL Recovery with Auto-Snapshot Manager

4-4

a. Review the Important Information and press Next b. Make note of the new drive letters where your database files will be located after the restore as new process. c. Give the database a new name, or accept the default, and finish the process. 3. In SQL Server Management Studio, right-click on the Databases object and refresh the list of databases. Verify that the new database exists, and that it has the same data as the original. 4. Now, locate the volumes associated with the new database on your SAN. Record the SAN volume names below: ___________________________________________________________________________ ___________________________________________________________________________ VII. Smart Copy Replication

This section of the lab describes the steps you need to perform to create Smart Copies that are replicas. 1. Enable replication for your database volumes. a. In ASM, highlight the database that you wish to enable for replication in the Properties area of the screen, make note of the volumes listed as Host Volumes. b. Locate the volumes that hold your database files in Group Manager. Enable replication on those volumes as follows. i. Replicate to the same group as in previous labs. ii. In Step 1, General Settings, accept the default for all options iii. On the Step 2, Advanced Settings, leave all settings at the default. iv. Click Finish on step 3, and select NO when asked would you like to create a volume replica now? 2. Create a Smart Copy replica of your database. a. In ASM, right-click on the Applications object in the left hand column, and select refresh. This will ensure that ASM recognizes that the database now supports replication b. Right-click on your database, and select Create Smart Copy Set. c. Select Replica as the smart copy type, and full as the backup type, and select next. d. Review the summary screen, and select Finish. 3. Using Group Manager for both sites, observe the progress of your smart copy replication. 4. When replication completes, locate the smart copy replica in the Auto-Snapshot Manager. Highlight the replica and confirm that in the properties area, Smart Copy Type is Replica. 5. Close Auto-Snapshot Manager VIII. Clone and Restore as New Smart Copy Replication 1. Run the EqualLogic Remote Setup Wizard, and configure your SQL server to access the remote SAN that stores the replica set of your database. 2. Make sure that replication of the Smart Copy set has completed before continuing.

Lab Exercise 4

SQL Recovery with Auto-Snapshot Manager

4-5

3. Start Auto-Snapshot Manager. 4. In ASM, Highlight the Smart Copy that is the replica set of your database. 5. Select Clone and Restore All as New from the context menu or the Actions pane. 6. Select new drive letters for the new volumes that will be mounted. 7. Wait for the process to complete. 8. Open SQL Management Studio, highlight Database, and select Refresh from the context menu. 9. Do you see a new database?____________________________ 10. Open Group Manager for the SAN that is the replication partner for the SQL system. Do you see new volumes? 11. Use the Group Manager to view connections to the new volumes. Which server is connected to the volumes? ______________________

Lab # 4 is completed

Lab Exercise 4

SQL Recovery with Auto-Snapshot Manager

4-6