SQL Server DBA Tutorial 110-How to Setup Database Mirroring in SQL Server

welcome to Tech Brothers in this video we're going to learn how to set up database mirroring in SQL server and the things we will learn in this video uh how to prepare database for mirroring how to configure principal server how to configure mirror server how to configure witness server even though in this demo we will not be using witness server but I'll show you in live demo what how to configure witness server uh anyway and number five how to fail over and then we'll be talking about permission required for mayor setup so that you will avoid all the permission related errors uh during your mirroring setup so when we talk about prepared database for mirroring whatever the database that you're uh uh preparing to mirror to so you have one source uh database SQL Server instance and other is destination um mirror server so in in mirroring World the principal server is basically in the beginning when you're setting up the mirror is principal server and in the beginning the mirror server the destination server is your mirror server so that's in the beginning once we set up the mirroring then they can switch back and forth so there is a handshake between them mirror can act as a principal and principal can act as a mirror server so um when you let's talk about in the beginning that when you do that uh you need to take a full backup you need to take a transaction log backup of the database that you wanted to mirror uh on the principal database and then you go ahead and restore your full backup on the mirror server uh in the no recovery mode so that you can go ahead and restore your transactional log backup so when you restore the transaction log backup restore that transaction log backup also in no recovery mode so database has to be in restore mode in order for our mirroring to work and you will see that in live demo let's go ahead and do that all right so here is my uh SQL Server right here uh SQL prod this is going to be the Principal in the beginning during the setup and this is Tech Brothers client SQL mirror is going to be our mirror server so what we're going to do is select one database in this particular demo and mirror that to SQL mirror so as I said the first point is to prepare that database for mirroring so what we're going to do is we're going to go ahead and take the we're going to mirror TFS datawarehouse DW to our mirror server so in order to do that let's go ahead and take the full backup of this database so we're going to go ahead and take full backup of this database so I'm going to TFS DW full. Bak so that is my full backup so in the media option I will overwrite any existing backup set and that's all good so we're going to go ahead and click okay full backup is done let's go ahead and take the transaction log backup as well so we're going to go ahead and select transaction log backup and we're going to remove this ad tww uh TF TFS DW tr. TRN so this is our transactional log backup so we're going to go ahead overwrite if it ex exist already media backup options looks good so we're going to go ahead and take backup so okay now the backup database tww completed so next our next thing is that we're going to restore it on our mirror server so first thing that we need to move the files between the these two um databases so let me close this I'm going to go ahead and this is my client server so I'm going to go ahead and put it right here going to delete this so I will go ahead and get SQL the files we need to move the files so we're going to go ahead and get the files that is uh full backup and transaction log backup so I'm going to do that and these are the two files that we need on our Target server which is also mirror server at this time all right our files are moved TFS dw4 and TFS DW transactional log backup uh keep in mind that you can restore it using using UNC path as well but it takes a little bit longer so it's for me it's much easier that I will go ahead and move the files from source to destination and then do the restore so what I'm going to do right now is go ahead and restore the database full database restore a database so we're going to click a device and add where we put the files which is data right here and this is full backup keep in mind that uh we have to do two things first restore the full backup in non recovery so that we can uh restore the transaction log backup and then restore the transaction log backup in no recovery so our database remains in restore mode so we're okay with this let's go in file options file options look okay so we're going to go ahead and go in option and select this with no recovery if this database is already there you can go ahead and click override this datab database is not on my mirror server so I'm not going to click that so I'm going to go ahead and restore with no recovery so let's go ahead and do that database is restored successfully and that is full backup so now it's in restore mode so we're going to go ahead and click and restore the transaction log backup which we just took and moved over so we're going to go ahead and click on the device add this is our um right here is our transaction log backup in option keep in mind that you have to select the option second option restore if you make it big restore with no recovery so our database remains in restore mode when we set up the mirroring so that um we're going to go ahead and click okay so our database transaction is restored so next thing is this database preparation First Step that we talked about in our video preparation is completed so we're going to go ahead and select the database from our source which is also called principal at this moment so we're going to go ahead and get our TFS DW in order to set up mirroring for TFS data warehouse database we're going to go ahead and right click on the database go to task and click on mirror so first thing that you will see is configure security this security is related to your principal database your mirror database principal database in this particular demo is my SQL prod instance and um my mirror server is uh SQL mirror instance so I'm going to go ahead and click on configure security and click next now here is where I was talking about the witness server if you have a witness server keep in mind the principal uh server SQ SQL Server and uh the witness server they cannot act as uh um witness you have to have a third SQL Server instance that you can use as a witness all those three servers can be on one SQL all can be on one server you know that's that's okay but they need to be all three different SQL Server instances so we're going to I'm not going to go ahead and basically configure the witness all you need to do is if you click yes you need to uh go ahead you will be selecting witness server instance keep in mind that principal and mirror are grade out here so you can't use principal and mirror server as a witness you have to provide a new uh SQL Server instance so if you click next it's going to ask you that what is going to be your principal what is going to be your uh mirror and what is going to your going to be your um um witness server so you will be configuring basically three SQL Server instances in this particular demo but I'm going to go back I don't have the third SQL Server instance so I'm going to say no basically you don't need a witness server in order to set up your database miring but it's always a good idea to have a database mirroring but um in in organization and production you need to you you should basically uh uh set up your mirroring uh witness server in your mirroring so we're going to go ahead and click no in this particular demo so we're going to click next now this is the principal server instance this is the instance where your first instance your Source instance when you're setting it up listener Port keep in mind that this port needs to be open between uh your principal server and your mirroring mirror server your firewall need to allow this port you can change this port later on but your firewall needs to have this port open whatever the port you choose and and communication needs to happen between principal server and mirror server and a lot of time there there there are scenarios under which the these ports are not allowed via firewall so keep in mind that uh if you are setting in your production you need to provide this to your file War administrator and let them know that please allow this particular uh listener port or whatever the port you choose let's say it's not 5020 uh 2 you can choose it 1522 so that that particular uh listener Port you need to have your uh firewall guys set up to allow between these two hosts uh your uh principal server and your mirror server so in this case I'm going to keep it I'm going to keep it uh 5022 and endpoint name is mirroring you can change the endpoint name basically uh and click next and this is my mirror server which is uh Tech Brothers SQL mirror so I'm going to go ahead and click connect and connect as you can see right here listener Port is 5022 and endpoint name is this so you can keep the same port between these two and you should basically but you can change the name if you want to and I wouldn't change the endpoint names between if you're not using witness server but witness server will keep track of all this if you set up your witness server so no worries around that we're going to go ahead and click next now here is if you have a SQL Server account and you wanted to use SQL Server account that particular account needs to be and I'm talking about the last part when we're uh uh you know in the beginning of the video that uh uh mirror setup security um and permissions related keep in mind that right here this this account if you use up here this this is a SQL Server account and if you use that SQL Server account it needs to have a a permission on on your mirror server to restore and same on your you know on your principal server so that needs to have a restore permission and all other permission that needs to be if you don't know what permissions you need for database mirroring please go ahead and look at in online book or you can go on Microsoft website and they have explained pretty good what exactly this account needs to have permission but if you don't choose this account keep in mind this account your SQL Server service account in my case is uh TBS SQL SVC that's that service account will act as the account that needs to be needs to have permission on your principal server and let me go ahead this account uh up here um when uh the the service account let me show you quickly service accounts sorry this is wrong the account that I'm talking about is right here the service account right here the service account will act as you know that particular account if you have if you didn't put SQL server account SQL Server account which is not a Windows account but usually your SQL Server uh service account or uh Windows account so this account your principal in my my case is not Tech Brothers cluster admin it's Tech Brothers back/ SQL SVC so that is my uh service account basically in this uh TBS SQL so that service account needs to have permission to create or restore the database uh on this particular um uh mirror server so let's go ahead and get back here and I'm not going to put a SQL Server account so we're going to go ahead click next and click finish as you can see the endpoint configuration is finished click click close And now it's asking right here is the what I was talking about that this is the this becomes for the endpoint these are endpoint names right here this is a principal endpoint TBS SQL dotech brothers. looc which is my domain right here and this is my host and and this is right here is the port and same with mirror server these two needs to have a good handshake between um uh each other you know um handshake between um principal and mirror so if there there is a failure between handshake between these two then it's not going to work so I just thought that I should mention that a lot of folks run into an issue that their database go in recovery mode and then uh there is no other way for for them to basically even if they restart the their um end points still it does not come online so basically they end up you know um uh what I what I would recommend that go get the MDF and ldf from the uh principal to the mirror and have it come online first and become restore mode and then you can restore it later and delete it because it's a lot lot of hassle if if if this things fail so make sure that there is a really good handshake between principal and mirror so we're going to go ahead and click on start mirroring as you can see the synchronized database are fully synchronized they're successful so keep in mind that if you have done the witness right here there would be a witness uh uh address right just like these there would be a witness here so we're going to go ahead and click okay and let's go in our principal server refresh the databases and see what is doing as you can see TFS DW is in the mirror and it's principal synchronized keep in mind the principal and synchronized where where the principle databases you can connect to that uh uh particular database and you can do anything um such as you know what you do normally on your database you can connect to it and if I do new query then you can do whatever you need to do on this database if it's not principal and it's a um on the mirror then it's not going to let you select that you have to fail over to the mirror server in order to do that which we will do in in a second so let's go ahead and um close this and let's look at the status of the databases on the mirror okay let's try to connect with it and let's open it as you can see the database DW is not accessible as you can see right here so what I need to do what next thing we need to do is do failover uh but uh I just wanted to show that a mirror is a mirror right now this TBS client SQL mirror is acting as a mirror keep in mind in the beginning I told you that principal and mirror uh definition changes when you set up the basically uh the mirroring now if I go ahead and and let's go ahead and fail over fail over means that I want this database to be failed over and available on my mirror server so I'm going to go ahead and basically fail over so you click on right click go to the mirror again and right here is the option called failover so I'm going to go go ahead and fail over and it is asking me that uh your database all the uh roles of the database and principle of the database will be swapped onto your mirror server I'm okay with that because that's my intention that's why I'm clicking on failover so I'm going to go ahead and click yes and let's refresh this this should become mirror and synchronized and let's refresh this one it should become mirror uh principal right here as you can see all the data is available for us so basically this is how you um set up your mirring in SQL Server um as we saw during our video we learned how to prepare database how to configure principal server how to configure mirror server how to configure witness server and we did the failover and also we talked about uh we talked a lot about permissions required for Mirror setup and I hope this video helps

Channel: TechBrothersIT

Views: 125,817

Keywords: Database, Mirroring, Microsoft SQL Server (Software), Database Administrator (Job Title), SQL, Server, Developer, DBA, Scripts, DBA Tips, SQL Developer, SQL Server Interview Questions, SQL Server 2014, SQL SErver Adavance Tutorial, SQL Server Tutorial for beginners, SQL Server 2014 DBA, SQL Server DBA tutorial for Beginners, SQL Data Adminstoration Tutorial Step by Step, Database Mirroring in SQL Server, Step by Step Configure Database Mirroring, TechBrothersIT

Id: C_npQPAI1Yo

Length: 18min 54sec (1134 seconds)

Published: Mon Mar 16 2015