improve.dk - http://improve.dk/sql-server-mirroring-a-practical-approach/

SQL Server Mirroring - A Practical Approach | Mark S. Rasmussen

Improve.dk Spined HTML

SQL Server Mirroring - A PracticalTideway| Mark S. Rasmussen Mark S. Rasmussen improve.dkWell-nighMe Pages Categories Archive Mar 23 2008 SQL Server Mirroring - A PracticalTidewaySQL Server Comments In this post I’ll take a practical tideway at talking well-nigh what SQL Server Mirroring is, the advantages and considerations that follows. Availability, not scalability SQL Server Mirroring aims to increase database availability, not scalability. Boiled down, a mirrored database consists of a principal database on SQL Server instance X and an word-for-word mirror of that database on instance Y. Everytime a transaction has occured on X, it is executed on Y as well. While this is happening, the Y instance database is in recovery mode, meaning you cannot query it directly, and thus you cannot use this as a secondary readonly database to unzip scalability. While you can run mirroring on variegated SQL Server instances on the same machine, this defeats the purpose as most errors will be hardware/system based, and usually these will stupefy all instances on the same phyiscal server. Trying out mirroring wideness instances is a good way to test it out however. In my demos I will be using virtual PCs, each loaded with SQL Server 2005 Enterprise sp1. Operating modes SQL Server supports three variegated operating modes.Upperperformance (asynchronous) As the name implies, maintaining upper performance is the key issue in this mode. Whenever a transaction is completed on the principal, the log is sent to the mirror, but the principal does not wait for this to complete. Thus if the mirror were to die out, throw an error during execution, the databases would wilt out of synch. The mirror could moreover trail overdue due to a difference in computing power or other external factors. If the principal fails, you will have to do a transmission failover to the mirror.Uppersafety (synchronous) - moreover known as “high protection” As with the upper performance mode, each time a transaction occurs on the principal, it is sent to the mirror. The principal will not commit the transaction until the mirror has single-minded the transaction also. Thus you will never risk your databases stuff out of synch. The downside is that your mirrored setup will be no faster than the slowest machine that is part of the mirror, plus the implicit overhead in server chatting. As with the upper performance mode, you will have to make a transmission failover in specimen of principal failure.Uppersafety with will-less failover (synchronous) This mode involves a third instance besides the principal and mirror, known as the witness. The witness instance constantly monitors the principal for availability, and if a problem is detected, it will automatically perform a failover. At this point, the database as a whole will still be available, but you should manually get the failed mirror up and running then and reinitiate mirroring. Prerequisites There are a couple of things that should be in place surpassing attempting to setup database mirroring. Service pack 1 Make sure all instances have been upgraded to service pack 1. If you do not have service pack 1, you will receive the pursuit warning when trying to start the mirror: Unified SQL Server service worth If you’re usingZippyDirectory, make sure the SQL Server service finance are running as the same user. Local System will not work as it does not have any network credentials. If you’re not usingZippyDirectory, just make sure the services are running on an worth with the same name & password on each machine. Make sure you transpiration the service worth through the SQL Server Configuration using and not the services console. Alternatively you can specify user finance that should be used for mirror replication, but having the servers run on the same domain worth is the easiest way. Full recovery model Make sure the database you want to mirror is setup to use the full recovery replacement model, otherwise there’ll be no log to ship to the mirror instance and mirroring will not be possible. Licensing Mirroring is supported in the SQL Server Standard and Enterprise editions. Neither Express nor Workgroup edition will work. Enterprise supports the upper performance operating mode, Standard only supports the two upper safety modes. You can use the self-ruling Express version for the mirror server. Note that you do NOT need an uneaten SQL Server license for the mirroring server, provided that it does nothing else but maintain the mirrored database - take note of the 30 days clause. Test setup My demos will be using three servers, all running Windows Server 2003 Standard (fully updated) through Virtual PC. All three have SQL Server 2005 Enterprise installed. I will be using the Microsoft sample database Adventureworks. You can download the AdventureWorks database at CodePlex. The three servers are RIO, MANDALAY and MGM (yes, I like Vegas). MGM will only be used for setting up a witness, RIO and MANDALAY will both host the very databases, MANDALAY stuff the initial principal and RIO stuff the initial mirror. All servers are completely fresh installations using SQL Server authentication. I will be connecting to the instances from SQL Server Management Studio running on my desktop computer. Initial setup of the databases The first step is to restore the AdventureWorks .bak replacement file to both servers. On the principal (MANDALAY) we should make a normal restore (RESTORE WITH RECOVERY) so the database is online. On the mirror (RIO), we should restore into the recovering state so no changes can be made (RESTORE WITH NORECOVERY). You can watch how it’s washed-up here, or skip on to the next section. Mirroring configuration Now that we’ve got both databases setup, we’re ready to setup the very mirror. A couple of notes on what I’m doing during the setup. In the first demo, I’ll setup a synchronous upper safety mirror with a witness. As all the virtual PCs are running on the same machine, I’ll have to use variegated ports for the endpoints. Whether you want to use encryption for the endpoint liaison is scenario specific. Encryption will have an overhead - albeit a minor one - so it’s up to you to determine if it’s neccessary. As our SQL Services are running on the same worth wideness the machines, we do not have to specify any custom service worth names during the setup. For some reason, SQL Server needs a fully qualified domain name for the instance addresses. If you’re setting this up on a computer that is part of the domain, you should simply use the domain name, [Computer].[domain]:port. In this example my desktop is not part of theZippyDirectory domain and thus it’ll use addresses like TCP://COMPUTER:PORT which is not accepted. I’ll fix it by simply writing the machine IP addresses manually instead. The IP for MANDALAY is 192.168.0.31 and for RIO it’s 192.168.0.33. Note that you should ALWAYS use FQDNs, using IPs are not recommended as it may result in configuration as well as runtime issues. See Adam Machanics blogpost on the same issue as I ran into a couple of times. Testing the mirror Besides my DBA excesses, I’m a developer. And what’s increasingly natural than to whip together a small using that tests the mirrors availability? It continuously attempts to connect to the databases using three variegated connection strings: string principalConnection = "Data Source=Mandalay;Connect Timeout=1;Initial Catalog=AdventureWorks;User Id=sa;Password=sadpassword;Pooling=false"; string mirrorConnection = "Data Source=Rio;Connect Timeout=1;Initial Catalog=AdventureWorks;User Id=sa;Password=sadpassword;Pooling=false"; string totalConnection = "Data Source=Mandalay;Failover Partner=Rio;Connect Timeout=1;Initial Catalog=AdventureWorks;User Id=sa;Password=sadpassword;Pooling=false"; The first connects directly to MANDALAY, the principal database. The second one goes to RIO, the mirror. And the last one is the mirror enabled connection string that combines the two. The principal should respond and act like any other normal database. The mirror will throw an exception as we cannot interact with a datbase in recovery mode. The combined connection will automatically connect to the current principal database, whether it be MANDALAY or RIO. To snift a wrenched connection quickly, I connect to the databases every 250ms and exhibit a untried bar if the connection succeeded (and an UPDATE & SELECT went well), and red if any kind of exception arose. To snift a connection timeout in a timely fashion, I’m using my QuickOpen functionality. The SUM(SafetyStockLevel) is the result of a simple SELECT query stuff washed-up on the database (the UPDATE modifies the same table, hence the waffly values), just to make sure we’re unquestionably talking to a live database. In the pursuit test, it gets a bit increasingly complicated to follow. I’ve got two SQL Server Profiler windows open, the left one is monitoring the MANDALAY server, the right one is monitoring the RIO server. The windows are so small you cannot see what unquestionably gets logged, but that is the point. The only relevant data in this specimen is the marrow rightmost value, Rows:X that displays an increasing rowcount when the server is active. I will start out by starting up the mirror testing application. We should see a red bar for the mirror database (RIO) as we cannot connect to it directly, while the principal (MANDALAY) and the mirrored connection should both show untried bars. The MANDALAY profiler should moreover show activity, whilst the RIO profiler should not show any activity. When it’s running, I’ll initiate a transmission mirror failover. A failover ways we’re switching roles, thus RIO will wilt the new principal and MANDALAY will wilt the mirror.Powerfullythis should midpoint the combined connection still shows a untried bar, MANDALAY shows red and RIO switches to green. The TCP/IP connection retry algorithm The failover went perfect. There’s a short value of reviviscence as the very failover takes place, but shortly thereafter, we get untried bars then - but one too many. When we started out, we got a red bar when trying to connect to the mirror, RIO. Shouldn’t we be getting a red bar when trying to connect to MANDALAY without we’ve switched the roles so MANDALAY has now wilt the new mirror? As you can see in the profilers, only RIO is stuff queried, so although MANDALAY is not responding, the connection string pointing to MANDALAY is succeeding. And what’s increasingly troublemaking is that the new instance of the testing using showed the expected result, a untried bar for RIO and red for MANDALAY - at the same time as the existing one showed all greens. The subtitle is due to the connection retry algorithm for TCP/IP connections. When we have a mirrored connection, the partner names are cached when used. Although we couldn’t query RIO before, the partner name was cached. Thus when we make the failover and MANDALAY looses connection, it’ll automatically make a retry struggle by connecting to the mirror partner, RIO. When the database comes up again, RIO is responding to both connections successfully. So although the connection string specifies MANDALAY as the endpoint, we’re unquestionably talking to RIO directly. Now, when the enshroud times out, or if we start a new using (the enshroud is tied to the SqlClient within a specific AppDomain), the partner name has not been cached and a retry will not be attempted, and that’s why the new instance shows the expected result slantingly the old instance. When a database dies This is the scenario we’ve been preparing for. But what happens when one of the databases die? In upper safety mode, a transaction has to be single-minded on both the principal and on the mirror surpassing it’s supposed successful, but in specimen the mirror dies (whether due to the service stopping, the physical hardware crashing or something else) the principal will enter a shredded state, still offering full availability. When you get the mirror database up and running again, it will automatically synchronize with the principal and the mirror will protract as if nothing had happened.Upperperformance mode will moreover protract unaffected with a sufferer mirror, and it will moreover automatically resynch when the mirror comes when online. Here’s a quick video demonstrating the availability of the principal when the mirror goes lanugo (the short red bars are caused by Virtual PC pausing all VPCs when the menu bar is open). If the principal dies, we need to promote the mirror to the principal role. As soon as the mirror has undertaken the principal role, we have wangle to our database again. This can be washed-up safely in the synchronous upper safety operating mode as we do not risk any dataloss due to all transactions stuff made simultaneously in both databases. In the upper performance mode thugh, we cannot do this as there could potentially be transactions that has not yet been transferred to the mirror, which would result in data loss. In this specimen we have to get the principal when online - or winnow possible data loss, depending on what’s acceptable. In the pursuit video I’ll simulate a sufferer principal. I’ll show the availability tester using running, using MANDALAY as the principal, RIO stuff the mirror. I’ll the pause the MANDALAY server, powerfully simulating it dropping completely off the network. You’ll then notice all red bars in the tester application, as expected. To get the database up again, we have to do a transmission failover to the mirror server, making it the new principal. We do that by executing the query: ALTER DATABASE AdventureWorks SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS There is no GUI that’ll execute this query. Soon without I’ve executed the query, we get untried bars again. Notice that all bars are green, this is due to the connection retry algorithm as explained older - the MANDALAY server is still offline. As I refresh the database list on RIO, you’ll notice that the database is now listed as “Principal, Disconnected”, confirming that the RIO database has undertaken the principal role, while shredded from the mirror. I’ll then resume MANDALAY, and as I refresh the MANDALAY database list, you’ll notice that the database automatically reverted state into “Mirror, Suspended / Restoring” - it picked up on the role transpiration and is now pensile remoter commands. It will not automatically resynch as mirroring is suspended when we gravity the principiality transpiration through the ALLOW_SERVICE_DATA_LOSS parameter. We first have to resume the mirroring functionality.Withouthaving resumed mirroring, I’ll wait for the databases to synch up completely, without which I’ll do a transmission failover so MANDALAY becomes the principal again. There’s a short reviviscence as the failover takes place, but without that, we’ve got untried bars and MANDALAY returns as the principal, RIO waffly to mirror. And there we go, we just experienced a principal going lanugo with minimal service unavailability.Uppersecurity with a witness The restoration process we just experienced can be streamlined if we segregate to utilize a third server known as the witness. The witness server continually monitors the principal and will initiate a failover in specimen the principal dies, as well as restoring the mirrors functionality when it returns (that is, converting the previous principal to the new mirror). It requires a third server, MGM (IP 192.168.0.35) for the witness part. Setup is increasingly or less as usual, we just need to include the FQDN for the witness server. In the last video I’ll show how to setup the mirroring including a witness server. I will then start the availability testing using and pause the principal server afterwards. This will immediatly result in red boxes all over, but without a short while, the RIO server (the new principal) becomes green, and a moment after, the mirrored connection itself moreover becomes green. The MANDALAY box is moreover green, but again, this is due to the retry mechanism as explained earlier. You’ll then see that the previous mirror database has now reverted status to “Principal, Disconnected”, proving that it has overtaken the principal responsibility and that it has lost connection to the mirror. I’ll then show how the MANDALAY database has reverted status to mirror, and how we can do a transmission failover so everything goes when to normal. This is the point when FQDNs became neccessary. Using IPs resulted in the mirror not stuff worldly-wise to make the failover. As stated earlier, using IPs is a bad practice, you should unchangingly use FQDNs. I’ve widow the AD hostnames to my hosts file so I can enter them from my non-AD member desktop machine. Conclusion SQL Mirroring is a unconfined way to increase your availability rate in specimen of database failures. You need to understand precisely where mirroring will help and where it won’t make a difference. It won’t help when you fire out a TRUNCATE [WRONG_TABLE] statement since it’ll just be replicated on the mirror, for that you’ll still have to make a rollback via the logs. It’ll help you in the specimen of a server crashing due to either hardware, software or network failures (depending on your network setup) and so forth. It’ll moreover enable you to do rolling upgrades. While configuration is rather straight forward, mirroring will add complexity to your setup and errors may be harder to track down. You moreover have to consider the licensing requirements depending on the level of mirroring you’re planning to use. Downloads SQL_Mirroring_Tester.zip - Test solution Mark S. Rasmussen I'm the CTO at iPaper where I petting with databases, mold lawmaking and maintain the overall technical & team responsibility. I'm an voracious speaker at user groups & conferences. I love life, motorcycles, photography and all things technical. Say hi on Twitter, write me an email or squint me up on LinkedIn. CATEGORIES .NETAS/Flex/FlashAmazon Web ServicesComputer ScienceConferences and PresentingIISLifeMiscMiscellaneousPerformancePokerSQL ServerSQL Server - CommunitySQL Server - Data TypesSQL Server - InternalsSQL Server - OptimizationSQL Server - OrcaMDFSQL Server - TricksTestingTools of the TradeUmbracoVisual StudioWebWindbgWindows ARCHIVE 201420132012201120102009200820072006 Copyright © 2014 Mark S. Rasmussen