Database Mirroring in SQL Server 2008 R2 – Part 15

let’s talk about database mirroring monitor GUI let me open this Panama thing go to the database right click tasks launch database mirroring monitor this will open the monitoring GUI when you open the monitoring GUI for a database it automatically register the database here if you are already in the monitoring GUI and wanted to monitor the status of other databases other mirror databases then you need to register those databases ok for example we have we have three databases that are mirrored ok right now I have this database register HJ I can select other databases to monitor okay this is a high availability database and this is high performance database let’s talk about the GUI you can monitor the database mirroring session using GUI or using stored procedure okay using the store procedure but you have to understand the result set and you know it will be in textual format and this monitoring GUI basically it is going to execute the same third procedure and show it in a nice format for this database this is the monitoring status at the moment okay so basically on the right side it shows what is the status of the database mirroring for this database currently this is principal and this mirror both are synchronized and witness is connected okay this is high availability that’s why we have the witness server address here okay it shows what is the operating mode here okay here in this section this is the principal server log information okay what is the size of the unsend log Q okay how much log records in kilobytes we have in the answered Q okay and what is the oldest and sent transaction in the principal q that needs to be shipped over to the mirror and time to send love all the log log records in the principal server okay what is the current send rate from principal to mirror what is the transaction rate that are coming into the principal database okay so this is the mirror log information how much we have in the renew queue to be restored on the mirror database time to restore those log records current restore rate okay this is probably self Aggies external entering and this middle commit overhead basically what it tells how long the the principal server is going to wait for that nourishment from the mirror server okay so this is important in the synchronous mode operation that much important in the asynchronous because there is no two-phase commit in the asynchronous okay and unsent log this is important for the asynchronous not for the synchronous okay in the synchronous mode when a transaction comes in it’s going to update in the principal and the mirror server okay you will see the value in the synchronous mode when the mirroring session is suspended in our past okay so but in a synchronous mode typically you’ll have value here in the peak time okay time to send a rich tour all current log probably self-explanatory and that’s pretty much it this is the learning information that you can view in the GUI okay an interesting thing is you can go and set up the threshold for example if we have more than two kilobytes of log records in the unsent queue send me an alert okay and similarly if we have more than 10 kilobytes of log records in the reroute you send me an alert things like there are if if more than a threshold value then you can send an alert okay or you can set up some job to do certain things okay things like that and the thresholds generally what we have here is unsent log record exceeds a threshold

these are the four threshold are others we can set using the mirroring GUI okay this is unsent log exceed certain value unrestored log exceed certain value age of the oldest unsent transaction for example if we have a transaction in the same queue that is sitting in the send queue for more than two minutes then you know send me an alert or dos do something you know we can fire a job sequence or job produce on something okay and we will come into overhead exceeds that are shown again on this counter you can set at the shop okay if a transaction is waiting more than one minute to send the results back to the user or self transaction is waiting for more than one minute from the mirror server then send me an alert okay things like that and these are the four alerts you can set you can set the alert using set thresholds in the GUI are you can use the alert stored procedure for example if you type in the intellisense you can see here change alert drop alert Halperin okay using these stored procedures you can set dollars or click here and set that okay for example I’m going to set the alert for the DB mirror high availability sorry not for this database let us select the high performance okay for this database it is in asynchronous mode and I’m going to set the other if the log records exceeds more than two kilo byte in the send queue then send me an alert okay send me an email okay let’s set up the alert so this alert is enabled on the unsent log records exit threshold enable this on the principle enable is on a mirror okay okay I have said this a lot okay here I can change it to 10 you if Varg records in the same queue is more than 10 KB then fired a lot okay you can see that here the other tea set you can also helper you you can see that here what is the values I have set here okay and if you want to change you can change the values using the store procedures change amount you need to specify the database name and there sholde indicator and okay I think let’s look into the manual our tidy one is for the oldest a lot i2 is for the unsent lock okay I’m going to say I’m going to change our tidy to and value is 15 15 kV okay and this warning is enabled one for enabled and zero for disabled one if I execute this let’s see refresh you can see that here 15k be okay if you want to disable it I think I guess if you pass zero then it is disabled on the server where executors okay if execute this on mirror up then it will be disabled on mirror okay let’s enable it and set it to well you

can 10 KB of thank you so what I have done I have created the alarm to alert to send alert if the send Q is more than 10 KB okay log records are piled up in the same queue more than 10 KB then fire dollar okay what the alert is going to do it’s going to write the event in the Union to be work okay let us go to the Event Viewer and I just cleared the event we were to make sure okay so we have so many things let’s clear the log again so I’m clearing the Event Viewer so we have the clear even – you are now that I’m there’s nothing in the interior right now okay so let us write some transaction and insert a bunch of records into the table in this database okay in this database right now I have two tables t1 and t2 okay and both of them are identical it has three columns integer character and text okay I’m going to dump a bunch of rows during that time it is going to build the send queue okay if the send queue size the queue size is more than 10 KB then they even will be entered in the even fewer okay and you can write a job or you can write a W in my script or you can do anything you want once you have the even even bigger okay even if you have Enterprise read mom Microsoft operations manager monitoring the even viewer then you can set up mom alerts to do to alert devious okay once you have the even registered in the Event Viewer you can play with it basically so this is the database mirroring monitor alert that will enter the event in the same appear okay now all I need to do is just to create the scenario to find the even okay so for that I am going to write a script to create some 10,000 rows into the t1 table and the principal server okay you okay so what I am doing is I’m going to insert some 10,000 rows let us say hundredth of the nose into the table t1 so that I can create the scenario okay so let’s sexy the status is nothing nothing is going on okay everything’s zero blank okay so when I start executing this script you okay the script is executing let’s refresh now you can see the current send rate is increasing but still it is in synchronous mode okay there’s not piled up yet let’s refresh refresh refresh at one point of time you will see the the send queue will grow okay instead of refreshing here the script is still executing we can just monitor here okay let’s also do this and copying the script and I’m going to execute the script against another table that we have again this is also the same layout I’m going to execute against t2 table okay so now we have two scripts that are running and dumping lot of data in t1 t2

tables okay so definitely we should have the the unsent log event here it looks like my computer is a bit faster and quickly catching up with the transactions that’s why we couldn’t builder send queue more than 10 KB these two scripts are executed so what I am going to do I am going to change this threshold to one Kb 10 KB at least we can at least I can show you the alert okay so I know I’m changing the let us make it a zero okay so no one KB is the minimum it looks like okay there’s good so let us change it to 1 KB it means if if the same queue contains a log record same queue size is more than 1 KB then $5 ok that’s what I have set here now I’m going to execute the same script with you okay so now I’m going to execute the script which is going to dump a bunch of rows into the T 1 and T 2 tables I have the other set here for one Kb let us see if we can succeed this time HP and HP okay performance fine so now we have the two scripts that are running let’s take a look at the mirroring session it looks like both are synchronized let me see if we have any others here okay good we made it this time we guard our okay so this is what the alert is a lot of the unsent log has been raised current value of four surpasses the – hold on okay it looks like okay if you refresh it again okay good now the alert is firing and it is writing the events in the Event Viewer okay now what we’ll do we’ll create a sequel server alert okay and we will send email to DBA okay to send email to DBA we need to have the database mail set up okay first I am going to create the database mail account and database profile and you know create the operator and after that I’m going to create the sequel alert okay first I need to create the database mail okay okay if this is the first time you are sitting up the database male you will get this popup so say okay to continue now my profile I’m going to say DB profile and to set up the account I’m going to use my pop3 account my meta manage account you I need to select the profile as public

okay now I have set up the database male okay let’s send their Testament to make sure we have setup correctly you okay we got the Dutchman that means we have set up correctly our database male future is working okay now let us create the sequence our alert now what I’m going to do whenever three to zero for two when this unsent alert has been raised unsaid alert has been entered in the human tree / then I want to alert I sent an email to dB I’m going to set up a sequel server to do that okay you okay and now I have to see all databases the reason is I if I want to specifically say HP for this high-performance database I can say that but there is no database keyword here so it cannot really detect that I have no other option other than sitting into all databases typically you might want to send alert for specific database okay so in this case I’m going to say all databases okay the error number I wanted to look for in the email fever three two zero four two this is the even I have to look for this specific even and if that event occurs in the even tree or then send me an email so response for this alert is I can say execute a job you know create some job do something okay in this case I’m going to keep it simple I’m going to notify the operator okay since I don’t have any operator let me create on okay so for this operator send me the email okay in the email I can say please look into the issue okay now I am creating a lot like this you okay it looks like the script is still executing if we get any new alert okay then it’s going to fire it’s going to enter into the human River and from there the sequence of alert is going to catch up and send an email to dB okay so now we have the alert which is constantly looking at the inventory oh okay so let us see if we have any new alert in the Event Viewer okay we got by Jeff alerts so at this time we have the hour and we need to have three two zero four two alert after this time then only we can will get the email okay let us keep refreshing okay it looks like both the scripts are only you let’s stop the script and execute the script again I think that may work out what I’m going to do I’m going to stop the script both the scripts you

okay I again started executing the script dumping more rows into t1 and t2 tables and this time hopefully we can build the same queue more than one KB and $5 let’s take a look at the event we were okay so we got the alert good at least now we have the event raised in the Event Viewer and sequencer our alert should have been fired and sent an email to us let’s take a look at our email okay still we didn’t get the email so probably the sequel our you okay it looks like we finally got the alert email this is the description that we entered in the sequel server alert okay so this is how you fire an alert and send an email to DBA or you know support people okay