SQL Server Mirroring 1 - Using Full Backup

hello also in last video we had seen how can we configure sql server 2019 active active cluster and this was the architecture diagram of that entire network so in our network we had three different nodes these two nodes node one as well as node two we had installed sql server 2019 instance on each of the node third node we had configured just for domain controller and as well as dns server also we had configured shared drives or san on this particular node now these shared drives were were accessed by node one as well as node two so how that clustering works is in case of in case node one goes down then all instances sql instances and other sql services uh as well as shared drives we are moved over to node 2 and same for if node 2 goes down shared drive as well as sql instance belongs to this node 2 will move over to node 1. now this uh clustering uh logic works fine but what if if what if our san or storage device itself goes down so this is single point of failure if this goes down you cannot access the database basically and there is no recovery unless and until you uh from from backup you restore those database again but till the time this uh sand goes down uh you cannot access the databases or till the time you recover that sand or shared devices you cannot access the databases now how you can overcome this problem so there is something called database mirroring so what database mirroring is so as soon as you do some transaction that same transaction will get mirrored to a different sql instance will come come to that uh so this is the diagram of our new network now in over here we had active active instance that means we had sql instance installed on both of these nodes but in this case we have used active passive uh architecture so that means we again had three different nodes in our network node one had sql server 2019 instance install whereas node 2 doesn't have we just added this node 2 in this node 1 cluster all right so that's why i'm calling that as active passive third this particular node is again acting as domain controller dns server and also we have configured shared drives or san on this particular node so basically those shared drives were accessed by node one as well as node two now all right now there is another extra instance we created in the same network so that is called mirror node all right so basically we are configuring this node uh mirroring against this particular mirror node what that means is whatever transaction we are going to perform on this node 1 will get available immediately instance will see that immediately on mirror mirror node as well okay so in case this sand goes down we can so if sand goes down basically you cannot access node 1 as well as node 2 because this node 1 cannot fail over to node node 2 because sand the shared drives itself are not available but still since we are committing those transaction on mirror node we can still access our database so just one thing you have to keep in mind is in case of mirroring mirroring node you cannot have your data and log drives on same storage okay now node 1 and node 2 are accessing this same storage so that's why if this storage goes down you cannot have node 1 as well as node 2 accessible but that's the same reason to avoid that single point of failure we are mirroring those transaction of node one in mirror one so this will have its own storage it will not use the san or that same shared storage we'll see that now let's go what i will do is so same architecture i have done on my local storage or my virtual machines so if you can see here i have domain controller this one i have three different uh three different virtual machines first is the domain controller we don't need domain controller right now uh we just need we also don't need node 2 because we are not going to do failure activity what we are interested in we are new we need node 1 first and after that mirror node so let's first of first of all let's validate the connectivity between node 1 and mirror this mirror node so let me go back i will open so that's my node one let me login okay so this is my node one instance okay let me open that from sql server configuration manager okay so this is my sql server instance sql 2019 all right and i can show you that from failover cluster manager as well so that is my sql server instance okay so d is my node name okay there are two different nodes one is down that's fine and second first node is up okay now let me close this guy okay so basically let me go back to my documentation so my node one is up node two is down which is fine we don't have to worry about node 2 now i am going to mirror this particular instance against this mirror database so let's check that as well so let me minimize this and i will open node 2 so this is a mirroring node so as you can see gupta f1 mirror iphone one okay so as you can see sql mirror instance okay now it doesn't have any database and let's see first of all if i can connect this instance so what i will do let me grab that connection okay if i should be able to establish a connection from node 1 to this mirror nodes so let's first identify that connection is happening or not okay so that is happening these are the this is the main instance and this is a mirror instance okay and let's check the user which is there for services so as you can see sql prd server is the user running sql server instance okay let's see sql server sql prd server right so this is the user and that is admin that's fine so next is let's let's understand the user for the mirror instance as well so i will go to mirror instance and even for mirror instance the sql server is running with sql prd server so let's check how what access that sql prd server user has on mirror instance as well so that is also sysadmin perfect so we are good to go but before going forward let's understand let's understand uh reading the documentation itself okay so let me scroll down need so let's understand the need first actually i had gone through that need itself already but let's still let's go benefits of database mirroring so this is a microsoft documentation increase the availability of database so i already told in case of disaster let's say failure cluster will work but uh still the uh your san or storage is still a single point of failure so if sand goes down you cannot access your failure cluster uh will be helpless you cannot use your database so in case of uh for failover for disaster recovery basically if this or your sand goes down uh then you can use your mirroring instance okay so that basically still you you can uh connect to this instance and go ahead with your business okay increase the data protection improves the availability of the production data during the upgrades all right database mirroring terms failure these automatic these things will look into it one by one in next subsequent videos so overview of database mirroring operating mode okay so let's check this one so there is so there are two ways you can configure the database mirroring will see that so as you can see one one of them is witness server and another is direct without the witness server so for witness will not see that witness server option in this video we'll just see the direct option so roll switching this is fine let's check the second one so that's the need basically then pre-requisite so what are things we need to look into it or consider before we start actually start the uh database mirroring all right so the very first is it needs windows authentication and as you can see we whatever database services running right now those are with uh windows authentication only so this is the windows user all right so it's not sql user so that one thing we have taken care of if using certificates we are not going to use certificates establishing a mirroring session okay so what it says is so these are the basic steps to create a mirroring create a mirror database by restoring the following backups using restore with no recovery so that's the main point restore the recent full database backup of the principal database after making sure that the principal database was already running in full recovery model so that is the second thing so first thing is restore with no recovery let me know down the very first thing is restore with no recovery second thing is our database which needs to be mirrored that has to be in full recovery model will validate that this is a second thing mirror mirror database must have the same name as the principal database if you have taken any differential backups we'll see that maybe not this in this video but in next video if you have taken any differential backups of the database since the restored full backups restore you your most recent differential backups okay but that is only if you have taken the uh log backup differential backups restore all lock backups done since the full or differential backups restore all the log backups done since the full load differential backup database backup you can set up mirroring by using either transaction sql or database mirroring wizard we are not going to use transaction sql we are going to use database mirroring wizard because that is the easiest way to do okay by default uh folder so that is fine these things the we'll see these options as we proceed in this video so as you can see let's first identify if our database first of all we have to create database database we don't have the database right now so what i'm going to do is i'm going to create a dummy database or hr database on node one okay which we are going to mirror on this second instance so let's go ahead uh i will go to node one okay and i will disconnect this instance database create new database hr okay and in options you can specify what type of recovery model you need so as as you saw in the documentation we need to have recovery model as full then only the mirroring will work so i will consider so that is our default option i will click ok perfect now what i will do uh let's create one more table here create table employees id [Music] integer name where care 30 let's say or 40 then create it on get date default oh i'm sorry default is created and this has to be date time to three perfect so we created the table what i will do now i will take the backup so what they are saying is there it is restore audit recent full backup okay so in order to restore the full backup we have to take the full backup first so let me go back to node one i will take the full backup so task and uh backup okay and yeah so my backup will happen on z m sql backup hr click ok so that's my full backup now i will restore this backup so let me go back to node three so that's my node three now i need to copy that backup which i have taken on first node so what i will do i will go to run so that's my node one and in c drive slash z dollar ms sql backup so that's my full backup i will copy this guy and remember this z drive is a shared drives configured on san but for our mirroring database or mirroring instance we are not going to use the shared drives we are going to use the default local drives okay so that's why i am copying that backup on this c drive okay now i will restore this so it's on cms sql backup i will restore this database restore database device add so it's on cms sql so that's a local drive now let me go back here what they were saying is while restoring you have to select restore with no recovery now what if we don't select that so by default so if i go to options it says restore with recovery i had to select restore with no recovery but let's assume let's go ahead with restore with recovery i will click ok ok so now it's in active stage i can access this database now what does what is the difference between no recovery and recovery is if you select no recovery that means it understands there are still more backups or still more data to be restored on this particular database if it is no recovery that means it's it says basically there is nothing to restore on this particular database so how that mirroring works is so whatever transaction we are doing on main node that will get restored those transactions will get simultaneous in i mean immediately restored on this particular database but now since this is not in restoring mode or no recovery mode uh uh i mean it cannot accept more transactions we'll see that it should fail so let's try let me go back to node one now and let's see what error we are getting so i will right click so in order to start the mirroring basically using ssms right click on that click on task and there is option called mirror okay now click on this configure security next and as i said there are two different ways you can configure with witness server and without witness in with witness server instance and without ins without witness server instance i will go ahead with no i mean i will go ahead with without uh witness server instance so i'll click on no next so this is my principal node okay and my mirrored node would be this one okay i will connect so i can connect perfectly fine i will click next now this option will see later so basically if you do not want to use the current user so since i have logged in into this database with my id amigo it will take for this basically mirroring it will take my credentials if you want if if a user doesn't have enough credentials if since i am a sysadmin it will work but if you want to use some other user you can still specify that as a user for this measuring purpose but we'll see that later so i will click next i will click finish perfect and i will click start mirroring let's say now it failed now what error it gave is an exception occur while executing a trans sql statement or batch uh database hr is not configured for database mirroring okay error1416 so if i go back i should have mentioned that error one four one six yeah see so basically what it says is it's it's not x yeah c because we chose that restore with no recovery because it's not able to access more transactions to restore okay so basically what i will do i will go back to my let me cancel this guy i will go back to node one sorry in mirror node i will delete this database perfect and i will restore the database but now okay i will go to options and i will select no recovery so it will accept more transactions for restoration so it still says restoring okay now let me go back to main node all right and i will go to task ah let's see mirror configure security next as i said we'll this option will check later as of now we'll go ahead with no next so that's my principal instance this is my mirror instance let's establish the connection perfect next we'll see this option in next video next finish perfect and let's start the mirroring perfect now as you can see here refresh so it's my principal database and it's in synchronized state if i go back to mirror database or mirror mirrored instance over here it is still showing as a restoring okay mirror synchronized restoring so basically that mirroring happened it got successful the connection got successful and this database is currently in restoring uh mode so whatever transaction i will perform on my main node though should get reflected on this uh mirrored node so we'll see that i will minimize this and let me go back to made node what i will do whatever table i have created i will add records there insert into employees and id name created on or let's leave it and values so one mandar and i don't need that i will add three or fourth one okay perfect and let me insert okay now i haven't touched the mirror database so as per documentation uh mirroring should reflect or commit all these transactions to the mirrored node so we'll see that what i will do for that now is i will stop the mirroring task mirror remove mirroring okay so if i refresh now it's it's not in synchronized state i will go go back to my mirrored instance over here it says mirrored synchronized and restoring phase so if i refresh it should show only synchro restoring mode now how can we now it's not in mirroring mode so in order to access this we cannot simply accept because if i try to query this i cannot access that because it's still in restoring phase so in order to recover that database or remove from restoring state we have to run some command so if you go to this documentation or if you simply google how can we uh remove the database from restoring state you will come across these first links and if you scroll down it's a simple command restore database with recovery all right so i will connect to master database because anyway you cannot access the hr database and you cannot alter the database from same instance so i will change to hr perfect now if i refresh it's no more in recovery pending state and i should be able to see the table and if i select top 100 thousand rows yeah exact four records okay so basically what we saw in this video is we establish a mirroring between these two nodes and entire purpose of that is even if our sand goes down or all the three nodes goes down you can still have your data available on this mirrored instance all right so thank you for watching this video and see you in the next video

Channel: Mandar Gogate

Views: 5,114

Keywords: SQL Server 2019, Database, Mirroring, Full Backup, Log Backup, Restoring, Recovery, No Recovery, Windows Server 2019, SQL Server 2019 Cluster, database cluster

Id: Oi46-HwO0_4

Length: 24min 18sec (1458 seconds)

Published: Sun Apr 24 2022