Docstoc

DBIS-L03_SQL Server 2008 Database Mirroring in Action

Document Sample
DBIS-L03_SQL Server 2008 Database Mirroring in Action Powered By Docstoc
					SQL Server 2008 Database Mirroring Hands-on Lab

Table of Contents
Lab Introduction.............................................................................................................................. 3 Objectives ................................................................................................................................. 3 Prerequisites ............................................................................................................................. 3 Lab Scenarios ........................................................................................................................... 4 Virtual PC Configuration and Setup ............................................................................................ 5 Using Virtual Machines ........................................................................................................... 5 Tips on how to successfully complete the lab exercises .......................................................... 7 Database Mirroring in Action ......................................................................................................... 8 Exercise 1: Configuring and Implementing the High Availability Database Mirroring Configuration – using Transact-SQL through a SQLCMD master script ................................... 9 Exercise 2: Using the Dual Database Monitor and Transparent Client Redirect ...................... 11 Exercise 3: Initiating Failover ................................................................................................... 13 Failover in the High Availability Configuration .................................................................... 13 Exercise 4: Automatic Page Repair ........................................................................................... 15 Additional Database Mirroring Resources.................................................................................... 19

Content created by

SQL Server 2008: Database Mirroring Microsoft Hands-on Labs Page 3 of 19

Lab Introduction
Objectives
The goal of these hands-on lab materials is to get an understanding of when to use one of the more advanced features of SQL Server 2008: Database Mirroring. The intent of these exercises is to provide you with best practices and implementation details for SQL Server 2008. As with all software development projects, your production environment may differ from this build and this environment. Be sure to design, implement, and test your final architecture extensively to minimize downtime and data loss. For the latest details on SQL Server 2008, please visit http://www.microsoft.com/sql/2008/.

After completing these self-paced labs, you will be able to:             Understand appropriate uses for Database Mirroring Understand pre-requisites required to setup a Database Mirroring Partnership Understand the implicit and explicit configurations for the Transparent Client Redirect Create TCP Endpoints specifically configured for Database Mirroring Create a High Availability configuration with Database Mirroring Create a High Protection configuration with Database Mirroring Create a High Performance configuration with Database Mirroring Manually failover using SQL Server Management Studio (SSMS) Manually failover using Transact-SQL Use the Database Mirroring Monitor Use Transact-SQL in a parameterized SQLCMD script to automate the process of configuring Database Mirroring Use SQL Server Management Studio to modify and execute SQLCMD mode scripts Experience with High Availability Technologies of SQL Server 2000 or SQL Server 2005 Experience with SQL Server Administration Experience with the SQL Server 2005 Tools or SQL Server 2008 Tools is very helpful but not required Familiarity with the Transact-SQL language is very helpful but not required Desire to sink your teeth into SQL Server 2008!

Prerequisites

    

SQL Server 2008: Database Mirroring Microsoft Hands-on Labs Page 4 of 19

Lab Scenarios

1. Database Mirroring in Action This series of exercises is meant to show you how Database Mirroring works and how failover can be initiated both manually and automatically. This exercise also explains how the transparent client redirect is used to create seamless application connectivity – and reconnect – in the event of a failover. Furthermore, it shows one of the new features in SQL Server 2008 – automatic page repair between Database Mirroring partners. These exercises should be executed first – especially with limited time constraints. Time for Completion: 60-75 minutes 2. Understanding and Implementing Database Mirroring This series of exercises is more complete – from end to end – and focuses on setup, implementation (via SQL Server Management Studio and Transact-SQL), monitoring and failover – incorporating many best practices and recommendations along the way. While there is some overlap between the two series (in terms of monitoring and failover), the Database Mirroring in Action series should be performed before continuing to these lab exercises. Time for Completion: 3 Hours

SQL Server 2008: Database Mirroring Microsoft Hands-on Labs Page 5 of 19

Virtual PC Configuration and Setup
This Virtual PC environment contains five already installed instances of SQL Server running on Windows 2003 Server SP2. The VPC machine name is S Q L H A V P C . The instances, versions and environments are described in the following table: SQL Server Service Name MSSQL$SQLDEV01 MSSQL$SQLDEV02 MSSQL$SQLEXPRESS MSSQL$SQLDEV03 SQL Server Version Edition SQL Server 2008 build 10.00.1075.23 (November 2007 CTP) Developer Edition SQL Server 2008 build 10.00.1075.23 (November 2007 CTP) Developer Edition SQL Server 2005 build 9.00.3054.00 (SP2) Express Edition with Advanced Services SQL Server 2008 build 10.00.1075.23 (November 2007 CTP) Developer Edition

The SQL Express Edition is not the SQL Server 2008 November 2007 CTP version as the upgrade tool in that CTP did not fully support SQL Server Express instances. The accounts and passwords used in the following exercises are shown in the following table: Account Name Administrator SQLService sa Account Password Pass@word1 SQL!Service@05 SQL!Service@05 Account Usage Login account for VPC Startup account for all SQL Server services SQL Server System Administrator

Using Virtual Machines
Using Virtual Machines allows you to isolate and control an environment independently from your host operating system. Your host operating system can run any operating system that supports Virtual PC or Virtual Server. Then, within your Virtual Machine, you can run any “base” operating system desired. In the case of this self-paced lab series, the base operating system is Windows Server 2003 Enterprise Edition with Service Pack 2. A virtual machine has multiple components: a virtual hard drive (*.vhd) and a virtual machine configuration file (*.vmc). With Virtual PC or Virtual Server, you can open the vmc file in order to configure your virtual machine. The initial configuration of this vmc is that it expects 1GB of memory allocated to the virtual machine; this is memory in addition to what your host operating system needs. If you do not have more then 1.5GB of physical memory, reconfigure the virtual machine to use 512MB of memory instead. Once setup for your environment, start (or turn on) your virtual machine. Once started, you will see a login dialog. In order to access the login dialog within your virtual machine, there are special keystrokes required. The details for the login are provided in the following table:

SQL Server 2008: Database Mirroring Microsoft Hands-on Labs Page 6 of 19 Tasks Login to your VPC image. Detailed Steps To access the login dialog on the VPC, use Right-Alt + Del and then login in as Administrator with a password of Pass@word1

Within Virtual PC or Virtual Server, most commands focus on using the Right-alt key. The Right-alt is the alt key immediately to the right of the spacebar. Command Description Login Full Screen/Return to Host (in VPC) Keystroke Combination Right-alt + delete Right-alt + enter

Customize the page file
The page file size has been reduced to 0 bytes in order to minimize the cost of expanding the .vhd (when you ran setup to install this VPC image). If you do not have 1.5-2GB of physical memory on your machine, you will need a page file. You have two options:   Set the Page File to System Managed Size (recommended) Increase the size of the “custom” configuration

If desired, use the steps below to configure your Page File to a System Managed Size. Tasks Customize your Page File settings. Detailed Steps 1. 2. 3. 4. 5. 6. Start the Control Panel, System applet. From the Windows task bar, select the Start | Control Panel | System menu item. In the System Properties dialog, select the Advanced Tab. In the Performance section, click the Settings button. In the Performance Options dialog, select the Advanced Tab. In the Virtual Memory section, click the Change button. Select the System managed size radio button in the Virtual Memory dialog. This radio button is in the Paging file size for select drive section. NOTE: Drive C: will be selected as it is the only drive available to our VPC. Select Set. Click OK and continue to click OK as you exit the Control Panel, System dialogs.

7. 8.

SQL Server 2008: Database Mirroring Microsoft Hands-on Labs Page 7 of 19

Tips on how to successfully complete the lab exercises

IMPORTANT
For best results, read the Tasks column and its associated notes first. Then proceed to follow the detailed steps for step by step instructions on what to verify, review, execute, etc. Please check off steps as you complete them and execute steps in the order in which they are listed. As a best practice, read the entire step, think about what it is meant to accomplish and then execute it. There are numerous steps that you do NOT immediately execute and instead may have you review and/or alter what you might expect your normal behavior to be in order to show you a new feature or two. There are lots of notes along the way, all of which give you insight into special features, cool tips and often best practices. It‟s not a speed contest to finish first. Take it slow, review the steps and you‟ll get a lot more out of your workshop experience. Finally, as the complexity of the exercise increases, the need to follow steps in a certain order also increases. Some exercises may fail if steps are missed and worse yet, other exercises might require you to start over if/when steps are missed. Consider using a pen/pencil to mark off steps as you complete them. Additionally, consider taking a step back from each step to think about what you’re trying to accomplish. This will help to minimize errors and increase learning as you may be able to predict certain steps and/or think of additional things to test and learn!

SQL Server 2008: Database Mirroring Microsoft Hands-on Labs Page 8 of 19

Database Mirroring in Action
Scenario
In this scenario, you will setup Database Mirroring for a TicketSales database; this database is critical to sales and operations for your rock concert ticket sales company. As with all critical databases, minimizing data loss and downtime are of the highest importance. You have learned that SQL Server 2008 supports an AlwaysOn configuration that allows a database to be highly available by automatically failing over to a “mirror” image of the database in the event of a disaster to the “principal” server. This new feature is called Database Mirroring. Database Mirroring allows a critical database (the Principal Database) to send changes – via synchronous or asynchronous transaction log writes – to a secondary database copy (the Mirror Database). If a third server is available (the Witness) to form a consensus (quorum) in the event of a disaster, SQL Server can detect and automatically failover to the Mirror copy of the database. To help the client‟s connect to the appropriate server, Database Mirroring also supports client connectivity options that allow your client applications to be redirected to the currently processing database in the event of a failover. For all exercises, the instances used are listed here:    Partner instance (Principal or Mirror) = (local)\SQLDEV01 Partner instance (Principal or Mirror) = (local)\SQLDEV02 Witness instance = (local)\SQLDEV03

To start the required resources needed for this lab and ensure that your VPC is not running unnecessary instances (and therefore wasting valuable resources), execute the following batch file: Tasks Start only the required instances for this exercise. Detailed Steps 1. Start My Computer From the Windows task bar, select Start | My Computer OR Press WindowsKey+E The WindowsKey does not exist on every keyboard. Often, when it does, it resides between the Ctrl and the Alt keys or sometimes next to a Fn key. The icon on the key is usually the Windows logo .

2. Navigate to the following directory: C:\AlwaysOn Labs\ 3. 4. Double-click StartSQL4Mirroring.cmd. After the command file has run you are ready to proceed to Exercise 1.

SQL Server 2008: Database Mirroring Microsoft Hands-on Labs Page 9 of 19

Exercise 1: Configuring and Implementing the High Availability Database Mirroring Configuration – using Transact-SQL through a SQLCMD master script
In this set of lab exercises (Database Mirroring in Action), the intent is not to learn every line of code and/or every feature, but instead, learn the basics of how Database Mirroring works. In this case, we will setup the High Availability configuration for Database Mirroring using Transact-SQL through a SQLCMD master script. If you would like to learn more about the requirements of this script, please continue the lab exercises to the “Understanding and Implementing Database Mirroring” series. In exercise 4 of that series, the details of a very similar script are fully exposed and explained. Tasks Setup Database Mirroring using Transact-SQL through a SQLCMD master script Detailed Steps 1. 2. If open, close SQL Server Management Studio. Start My Computer From the Windows task bar, select Start | My Computer OR Press WindowsKey+E The WindowsKey does not exist on every keyboard but when it does, it resides between the Ctrl and the Alt keys, sometimes next to a Fn key. The icon on the key is usually the Windows logo .

3. Navigate to the following directory: C:\AlwaysOn Labs\Database Mirroring Lab 4. 5. Double-click Database Mirroring Lab.ssmssln. By opening a solution file (SQL Server Management Studio Solution), you can have a group of scripts all centralized for ease of use and management. Once within SQL Server Management Studio, navigate to the Solution Explorer window. If this window does not appear, you can add this window back into SQL Server Management Studio by using the View, Solution Explorer menu item. From the list of projects within the Solution Database Mirroring Lab, find the DB Mirroring in Action project. Double-click TicketSalesDBMSetup.sql. Set the query window to execute in SQLCMD mode by using the Query drop-down menu and then selecting SQLCMD Mode. IMPORTANT NOTE: This script will NOT execute unless the SQLCMD Mode is used. 8. Review, and then Execute this ENTIRE script. Do NOT execute it partially as the variables and parameters defined by this script must be defined only once at the beginning of the script – in order for the script to succeed. If you accidentally run the script before entering SQLCMD mode, the easiest way to reset state is to restart the VPC.

6. 7.

9.

10. Execute the script by pressing F5 or clicking Execute button on the toolbar. Executing the script will cause a new pane to be opened in the query window. In this new pane click Messages tab to review the output from the script. For each connection you will see: Connecting to (local)\SQLDEV01…

SQL Server 2008: Database Mirroring Microsoft Hands-on Labs Page 10 of 19 Tasks Detailed Steps Disconnecting connection from (local)\SQLDEV01… 11. Once completed, click Results tab to view the output, verify that you see something like the following (the server name may be different):

12. Open VerifyDatabaseMirroringState.sql script from the Additional Scripts Project. Set the query window to execute in SQLCMD mode by using the Query drop-down menu and then selecting SQLCMD Mode. NOTE: You can make SQLCMD the default mode when opening new queries by changing a SQL Server Management Studio option. Use the Tools drop-down menu and then select Options… to launch the Options dialog. Single-click on Query Execution and then set the checkbox “By default, open new queries in SQLCMD mode”. 13. Modify the VerifyDatabaseMirroringState.sql script so that the Verify database name is set to TicketSalesDB. This is done by changing line 27 to the following: :SETVAR VerifyDatabase TicketSalesDB To display line numbers, on the menu bar click Tools then select Options. In the Options dialog in the leftmost pane expand Text Editor and select All Languages, to the right click Line numbers checkbox and then click OK. 14. Execute the entire script. Make note of the mirroring_state_desc – which should now be synchronized rather than synchronizing.

Your TicketSalesDB is now setup and in the High Availability configuration of Database Mirroring. However, it is not in the High Availability configuration until the databases are fully synchronized.

SQL Server 2008: Database Mirroring Microsoft Hands-on Labs Page 11 of 19

Exercise 2: Using the Dual Database Monitor and Transparent Client Redirect
In this exercise, you will see the impact of the implicit transparent client redirect. Tasks Use the Dual Database Monitor to load new sales into the TicketSalesDB database. Detailed Steps In this exercise, you will load data into the TicketSalesDB database using a precreated/contrived application called the Dual Database Monitor (DDM). This application was created solely for these labs and is not meant to be used for anything other than this particular set of exercises. This application has two components – one on the left side of the DDM and one on the right side of the DDM. Each side can connect to (or attempt to connect to) a different server and each side will attempt to enter sales information. Each sale placed is handled by a new connection – effectively simulating many users connecting from many different clients, each with short/simple transactions. 1. To Launch the Dual Database Monitor, use My Computer. From the Windows task bar, select Start | My Computer OR Press WindowsKey+E The WindowsKey does not exist on every keyboard but when it does, it resides between the Ctrl and the Alt keys, sometimes next to a Fn key. The icon on the key is usually a Windows logo. Navigate to the following directory: C:\AlwaysOn Labs\Database Mirroring Lab 3. 4. Double-click Mirroring DDM. Once started the Dual Database Monitor should show and look like the following:

2.

Notice that the left and the right side show two different servers. The initial server to which each side is going to connect is shown at the top of each section. The left side will initially try to connect to (local)\SQLDEV01 and the right side will initially try to connect to (local)\SQLDEV02.

SQL Server 2008: Database Mirroring Microsoft Hands-on Labs Page 12 of 19 Tasks Detailed Steps 5. Now, click Add Rows on the left side – so that this connects and tries to add rows to the TicketSalesDB on the (local)\SQLDEV01 instance. Once connected, the Row Count and Rows Added should increment based on the number of rows added. 6. Now, click Add Rows on the right side – this connects and tries to add rows to the TicketSalesDB on the (local)\SQLDEV02 instance. Notice that it connects – but once connected, the title has changed. The right side will also show (local)\SQLDEV01 and not (local)\SQLDEV02. Why? Because a mirror database cannot be accessed directly – except by the Principal database through Database Mirroring. So, why did the server connection change to (local)\SQLDEV01? Using transparent client redirect, SQL Server will know the name of the partner when a connection is attempted. When we attempted to connect to the mirror, SQL Server transparently redirected our connection to the principal. This redirection works by default in SQL Server 2008 when using SNAC (the SQL Native Client). There are two methods of redirection: implicit and explicit. In this particular case, the application leveraged implicit redirection. The implicit redirection method works with NO code changes. As long as the server you‟re connecting to is available (in this case, the mirror instance), then the server responds with the name of the principal and the application is redirected to the correct – and functioning server. This method of connecting, only works when the server is available and just cannot handle requests. In other words, when you connect to a mirror database you will automatically be redirected to the principal – regardless of who is the principal and who is the mirror. With a mirroring partnership, failovers can occur and the applications do not need to be made aware of where the database is currently running. However, if the server to which you‟re connecting is not available – then the server will be unable to respond and redirect the client. In this case, the explicit form of redirection should be used. In your application you would change the client connection string to include the new failover partner server‟s name. Here is what that syntax looks like:

;Failover Partner=server\instance

SQL Server 2008: Database Mirroring Microsoft Hands-on Labs Page 13 of 19

Exercise 3: Initiating Failover
Failover in the High Availability Configuration
This exercise focuses on how to initiate failover. In some cases failover can occur automatically while in others it must be forced. In the first part, you will manually initiate failover using Transact-SQL. In this same series, you will shutdown the principal server and watch how the mirror server automatically comes online to handle requests. Finally, you will bring the former principal back online and watch how it comes back online as the new mirror database. Tasks Initiate a manual Failover from the Principal to the Mirror – using SQL Server Management Studio Detailed Steps 1. In SQL Server Management Studio in the Object Explorer window click Connect and select Database Engine in the Connect to Server dialog verify the following then click Connect: Server type: Database Engine Server name: (local)\SQLDEV01 Authentication: Windows Authentication 2. 3. 4. In the Object Explorer window expand Databases then right-click on the TicketSalesDB (Principal, Synchronized) database and select Properties. In the Select a page pane of the Database Properties - TicketSalesDB dialog, click Mirroring, then click Failover to initiate the failover. On selection, a Database Properties message box will appear stating:

5. 6. 7.

Click Yes to confirm the failover. In the Object Explorer window, expand Databases if necessary (under the (local)\SQLDEV01 connection). Right-click Databases and select Refresh. The TicketSalesDB database, the former principal server should be listed as the mirror database after failover: TicketSalesDB (Mirror, Synchronized / Restoring…) In the Object Explorer window click Connect and select Database Engine in the Connect to Server dialog ensure the following settings are correct then click Connect in this case you will need to change the Server name to (local)\SQLDEV02: Server type: Database Engine Server name: (local)\SQLDEV02 Authentication: Windows Authentication

8.

9.

Expand Databases under the (local)\SQLDEV02 connection.

SQL Server 2008: Database Mirroring Microsoft Hands-on Labs Page 14 of 19 Tasks Detailed Steps 10. Right-click Databases and select Refresh. The TicketSalesDB database on the new principal server should be listed as: TicketSalesDB (Principal, Synchronized) 11. Return to the Dual Database Monitor and notice the titles now – during the failover, the client application detected the network timeout and reconnected to the NEW principal. The left side status is now REDIRECTED and the right side status is ONLINE. Both sides are listed as being connected to (local)\SQLDEV02 - the new principal. 12. Repeat steps 2 through 5 on (local)\SQLDEV02 to fail back to the initial state. This must be done before proceeding to the next exercise. 13. Ensure that both sides of the Dual Database Monitor have switched over to (local)\SQLDEV01 and then Exit the Dual Database Monitor. 14. 1. You are now ready to proceed to the next exercise.

SQL Server 2008: Database Mirroring Microsoft Hands-on Labs Page 15 of 19

Exercise 4: Automatic Page Repair
This exercise shows the new automatic page repair feature of Database Mirroring in SQL Server 2008 Enterprise Edition. This facility allows SQL Server to „repair‟ corrupt pages either on the principal or mirror by requesting the same page from the other mirroring partner. Although this is not a permanent solution to a corruption issue, it does allow SQL Server to continue running on that database until more extensive action can be taken. For more information, see the SQL Server 2008 Books Online topic:  Database Engine | Operations | High Availability  Database Mirroring  Database Mirroring Overview  Database Mirroring Sessions  Tasks Initiate an automatic page repair and analyze the relevant system tables. Detailed Steps 1. From the list of projects within the Solution Database Mirroring Lab in SQL Server Management Studio, find the DB Mirroring in Action project. Double-click AutomaticPageRepair.sql and make sure the connection is set to (local)\SQLDEV01. Notice that the script is divided into sections numbered Step 1, Step 2, etc. You will be using this script for the remainder of this Exercise. When the instructions say, for example, to execute the statements in Step 1 that means you should only execute those T-SQL statements between the “** Step 1 Begin **” and “** Step 1 End **” markers. The TicketSalesDB database on the VPC has been corrupted to simulate an IO subsystem problem. There is a table CorruptTable that was changed so that a page in it has an invalid page checksum. Automatic page repair will kick in whenever an 824 error; an 823 error caused by a disk CRC (Cyclic Redundancy Check); or an 829 error (page is marked as restore-pending) is reported by the SQL Server buffer pool. Execute the statement in Step 1 of the script to force SQL Server to attempt to read the corrupt page. SELECT COUNT(*) FROM TicketSalesDB.dbo.CorruptTable; GO You should see the following error: (local)\SQLDEV01(SQLHAVPC\Administrator): Msg 824, Level 24, State 2, Line 1 SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x7d2d39d2; actual: 0x7dadb9d0). It occurred during a read of page (1:19) in database ID 7 at offset 0x00000000026000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TicketSalesDBData.MDF'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency Automatic Page Repair During a Database Mirroring Session

2.

3.

4.

SQL Server 2008: Database Mirroring Microsoft Hands-on Labs Page 16 of 19 Tasks Detailed Steps check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. 5. Execute the same step again. This time notice that there is no error and a row count of 10 is returned. What happened? Whenever database mirroring is running in SQL Server 2008 and automatic page repair is triggered, the mirroring partner that has the corrupt page sends a message to the other partner asking whether the same page on its database is valid. If so, the page is sent across the network and the corrupt page is replaced with the good one from the mirroring partner. This mechanism works regardless of whether the principal or mirror server hits a corrupt page:  If a query on the principal hits the corrupt page, the query will fail and an attempt will be made to repair the page. The page is marked as being restore-pending until it is fixed – this means subsequent queries will also fail and it prevents an issue where a transient IO subsystem problem could allow a subsequent update to change the page after it‟s been queued for repair, and then the update is overwritten with the page copy from the mirror. If the mirror hits the corrupt page, the mirroring session is suspended. The mirror tracks all corrupt pages it knows about and only resumes the mirroring session once all the corrupt pages have been repaired with copies from the principal. If the principal is Enterprise Edition and the mirror is Standard Edition, corrupt pages can be automatically repaired on the principal but not on the mirror.





Note however that not all pages can be repaired – certain allocation bitmaps, plus the database boot page and per-file header pages cannot be repaired in this way. 6. Execute the statement in Step 2 of the script. SELECT * FROM sys.dm_db_mirroring_auto_page_repair; GO 7. You should see the following output:

The DMV sys.dm_db_mirroring_auto_page_repair is new in SQL Server 2008. It tracks the last 100 corrupt pages that were found in any mirrored database on that instance, as well as the type of corrupt that was found and the status of the page after a repair attempt. In this case the page_status of 5 indicates that the repair attempt was successful, which is why the query in Step 1 succeeded the second time you ran it. For more information this DMV, see the SQL Server 2008 Books Online topic:  Database Engine | Technical Reference | Transact-SQL Reference  System Views

SQL Server 2008: Database Mirroring Microsoft Hands-on Labs Page 17 of 19 Tasks Detailed Steps  Dynamic Management Views and Functions  Database Mirroring Related Dynamic Management Views  8. sys.dm_db_mirroring_auto_page_repair

The page should also have been logged in the suspect_pages table in msdb. Execute the statement in Step 3 to check. SELECT * FROM msdb..suspect_pages; GO The output should look like:

The suspect_pages table tracks pages from all databases in the instance, regardless of whether the database is mirrored or not. It can be used to drive single-page restores. An event_type of 5 means the page was restored. For more information, see the SQL Server 2008 Books Online topic:  Database Engine | Technical Reference | Transact-SQL Reference  System Tables  Backup and Restore Tables  9. suspect_pages (Transact-SQL)

Finally, we‟ll examine the SQL Server error log to see what‟s been added during the automatic page repair. In Object Explorer, expand Management then expand SQL Server Logs. Right-click the first log (with name starting „Current‟) and select View SQL Server Log. The Log File Viewer should appear. You should see lines similar to this below:

This is ordered with the oldest entry at the bottom. You can see that mirroring started and then the 824 error was found. Mirroring reported that it was attempting to repair the corrupt page by requesting a copy from its partner, and then it reports success. 10. Select Close to exit the Log File Viewer. 11. Exit the Dual Database Monitor. 12. In SQL Server Management Studio select File, Exit. If query windows are open, do NOT save when exiting.

SQL Server 2008: Database Mirroring Microsoft Hands-on Labs Page 18 of 19 Tasks Detailed Steps 13. Shutdown your VPC and do NOT save any changes. Restart your VPC cleanly before moving to Part 2 – Understanding and Implementing Database Mirroring.

SQL Server 2008: Database Mirroring Microsoft Hands-on Labs Page 19 of 19

Additional Database Mirroring Resources
  TechNet Whitepaper: Database Mirroring in SQL Server 2005 by Ron Talmage http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx TechNet Whitepaper: Database Mirroring Best Practices and Performance Considerations by Sanjay Mishra http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb163482c95e0b/DBM_Best_Pract.doc TechNet Webcast: How to Increase Availability Using Database Mirroring in SQL Server 2005 (Level 200) presented by Mark Wistrom, Program Manager for Database Mirroring at Microsoft Corp., November 2005 http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032282290&EventCategory =5&culture=en-US&CountryCode=US TechNet Webcast: Implementing Database Mirroring, Part 1 of 2 (Level 200) which was shown as Part 8 in an 11 part series for ITProfessionals, presented by Mark Wistrom, Program Manager for Database Mirroring at Microsoft Corp., April 2006 http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032290576&EventCategory =5&culture=en-US&CountryCode=US TechNet Webcast: Implementing Database Mirroring, Part 2 of 2 (Level 200) which was shown as Part 9 in an 11 part series for ITProfessionals, presented by Kimberly L. Tripp, SQLskills.com, May 2006 http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032290565&EventCategory =5&culture=en-US&CountryCode=US








				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:44
posted:9/21/2008
language:English
pages:19