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

In this video you will learn how to setup Database mirroring step by step process. Process explains how to prepare database for Mirroring, how to configure Principle server, what is principle and Mirror server in SQL Server Mirroring, how to configure Mirror server, how to configure witness server, how to failover and how to get required permission for Mirroring configuration in SQL Server.
Blog post link for the video:
sqlage.blogspot...
Visit our website to check out SQL Server DBA Tutorial Step by Step
www.techbrother...

[@charlesBleu]

This lesson saved me from a full night of mirroring frustration... Many thanks.

[@AbdirahimAli10]

i am feeling what you were feeling

[@sarif75]

This guy should be awarded!!!!!!

[@arslannkhan8129]

You deserve million likes 👍🏻

[@Venkat6728]

the awesome lesson about database mirroring.

[@TechBrothersIT]

Thank you

[@whatiknowallmix841]

clear cut explanation excellent .......... tq Tech Bro's

[@oliviersanzot]

I'd still recommend Database mirroring for pricing considerations. If I'm not wrong, you don't have to pay a licence for the mirror instance. For some organizations, this is a great argument...

[@renatocastaneda4990]

TITLE: Database Properties ------------------------------ One or more of the server network addresses lacks a fully qualified domain name (FQDN). To start mirroring without using a FQDN, click "Yes." To specify the FQDN, click "No." Then, specify every TCP address by using the syntax for a fully qualified TCP address, and click Start Mirroring again. This syntax is: TCP://.[.]: ------------------------------ BUTTONS: &Yes &No ------------------------------

[@lahorifm792]

Many thanks for such a nice detailed explanation.

[@TechBrothersIT]

You are most welcome. Thanks for watching, Glad to hear that you liked our effort.

[@amosmaxwelldeen5124]

this lesson have help me so so much thx

[@raghvendramhanta7654]

Simple and informative!

[@JamesEstes927]

Outstanding Video. Thank you for the clear and concise instruction. Worked flawlessly - even on SQL Server 2008 R2.

[@BryanVella]

Excellent explanation. Thanks man.

[@TechBrothersIT]

Thank you!

[@AminulIslam-hv2eh]

so, easy & helpful. @thanks

[@TechBrothersIT]

Glad it was helpful!

[@Ingles4allYou]

I'm facing disk space issue and because of that I need to resize the disk in my principal source server is it possible I split the target data in the target servrer in 2 different locations I mean in 2 drives units while my source server the data are concentrate in only one driver unit? Currently my Source serverr is in E drive and Target server is in E. I want to keep the source server in E and target mirror server to E and D

[@charlesderber]

Thanks man, very much helpful !!

[@MotionInMotion1975]

This is used in case a SQL goes down, the other one takes over? Thanks!

[@0220AM]

On point. Excellent explanation.

[@susantakumarrath3311]

Excellent explanation Tech Brothers. I had a confusion about the use of credentials during connecting to the Mirrored instance while configuring. Why not include the Mirror Configuration using T-SQL, like you did for Log shipping?

[@mohamedabdullahi9885]

Thank you for explanation. I tried to follow the steps in the video, and it fails when i click start mirroring and it says "The server network address "TCP://servers:5022" can not be reached or does not exist. Check the network address name and that the ports for the local remote endpoints are operational." The network of two severs are OK, when i telnet the two ports from each other they connected. Please help

[@oneabdelilah]

Thank you very much man.

[@shrenikshah7552]

What If we take full backup with compressed mode, will DB mirroring work?

[@Venkat6728]

hi sir, ms sql server standard will support mirroring. kindly help me.

[@mansichaoji9705]

nice xplaination

[@srikanthvulcan1413]

Tq

[@revianravilathala4246]

u using domain controller or certificate?

[@Celsogoncalves20]

Thanks you help me a lot....

[@JulioCruzcoach]

I just tried this and i receive an error that the database cannot ne opened because it's in the middle of a restore! :( Why were you able to?

[@wonheeryu87]

I am getting the same error message. Mirror server DB is still showing restoring...

[@MrSyrsh]

Thanks. Very Good.

[@achulu87]

Fentastic👍

[@pedroemanuel5506]

Hello, I have a lab which is 1 domain controler, shared storage and 2 node sql. Its necessary to mirror if the two node are on a failover cluster? Because they are getting the data from the same storage.. Thanks

[@kundanivlogs]

I have one question here.! If fail over happens mirror DB became principal DB right.! we may get orphan user right? because only user's will be replicated to mirror DB not logins right? please ans me.

[@RajeevRanjan-pf3jy]

I am facing the issue while configuring the Database Mirroring like "TCP://......:5023" can not be reached or does not exist. check the network address name and that the ports for the local and remote endpoints are operational.(Microsoft SQL Server , Error : 1418). please suggest me what i have to do..please its very urgent.

[@salmanm990]

What permissions did you grant to the Sql server service account?

[@nishantrana2710]

Sir, I had a doubt.Suppose i have two servers whose database is mirrored and if the primary server burns out then how to get the secondary server running without any human interference so that my IIS keeps accessing the secondary server now.

[@TechBrothersIT]

It’s not possible without using witness server as it requires automatic failover and synchronous status. You have to setup witness server as a third server. Hope it helps.

[@nishantrana2710]

@@TechBrothersIT thank u soo much sir

[@yaman8943]

when I try to restore the database my backup file goes invisible at the folder I kept it .. what do u think the problem was

[@TechBrothersIT]

Type of backup or you didn’t have right extension.bak you can rename the file. Also if you did t provide the extension, it doesn’t show until you select all files from the drop down.

[@lekkalabalakrishna186]

I did same as you said and every thing fine. but files not moving

[@bextla3737]

in sql you connect same server or different? one is SQLMirroe

[@salehuddinisa6602]

Would you please show how to create other 2 instances, the mirror server and witness server. And also would you please go a little bit slow, 'cause for you it's just abc but for a new learner it's tough to follow when move fast. When after back up you go to the folder delete them and do other stuff, we didn't understand, so would you please go a little bit slower and explain what are you doing. thanks

[@1487atul]

do we really need to have the shared drive between principal and mirror server for backup and restore database. or can we configure mirroring without having shared drive like just by transferring db backups from principal to mirror server and restore it on mirror. Is shared drive mandatory between these 2 servers for db backup and restore ?

[@TechBrothersIT]

As long as Principal and mirror has permissions on their respective Paths, you will be fine without share, just be sure sql server accounts or setup accounts have proper permissions to write and read from folders that need to have that permissions.

[@TechBrothersIT]

I noticed, you don’t really respond whether your issue is resolved or question is answered. It helps others who seek for an answer in comments.

[@navinly1030]

I tried to do Mirror Database on SQL Server 2017 with SSMS 17.3. but, I have problem where it said: Database can not open, It is in the middle of restore. Can you help me to solve this problem? Regard, NavinLY

[@TechBrothersIT]

This is SSMS issue, please use SSMS 2016 if you have it to set mirroring up, even 2014 SSMS would work.

[@badrali2539]

So in case of my principal server get down how i can use the mirror data if no failover option Thanks

[@TechBrothersIT]

You can bring database on mirror server online using TSQL

[@TechBrothersIT]

restore database “dbname”with recovery

[@syedrafiq3732]

sir can you please make a video on log shipping failover ,because since i have done log shipping my secondary database in instance is still in restoring state only. can you please help me with this..

[@TechBrothersIT]

There is already a video on log shipping - it remains in restoring mode until you break log shipping and bring database online.

[@noorhirjee2274]

ARE YOU DOING THIS DEMO IN LOCAL COMPUTER OR IN WINDOWS SERVER 2012 ?PLEASE HELP !

[@TechBrothersIT]

+Noor Hirjee We are using Windows 2102 Server in most of the demos. you should be able to do in home editions as well.

[@lakshayarora3935]

Thanks for the awesome video. However, please solve my one issue. I face this issue frequently. When I restore db to other instance, it always fails. Error: access denied. I have also checked the permissions and have also given EVERYONE permissions to the backup folder. Still the db restore fails with 'access denied' error. Also, I am using developer edition on both instances. Please solve this problem. I takes a lot of time and ultimately I am not able to get hands on.

[@TechBrothersIT]

Ok, please follow these steps to resolve your issue - Try from Top to perform/double check everything thing 1. Since you are mentioning that you are having issue restoring db on to another instance, when you restore, does it read your backup? If so then backup folder permissions are all set since you already given everyone Full permissions right? If it can't read the file, then go from top directory level and start giving permission to Engine and Agent accounts of backup folder i.e. D:\Backup ... Check D permissions first then backup coz sometimes inheritance is not ON so giving Target folder only might not work for you. 2. Now restore is happening with SQL Server Engine Server (MSSQL, or MSSQL_INstance) Service account, and during the restore Engine has to write your MDF and LDF on Target File system, find out where mdf and ldf are being written, if you are not providing manually the folder, it would be default folder of the Target instance (Right click on the instance, properties and database setting) That's where you need to be sure permissions are all set, try everyone Full permissions on both folder (data and log files) 3. I assume you are SysAdmin on target server? Make sure Target Agent and Engine accounts are sysadmin 4. Lastly if everything fails, check SQL Server (Target) error log, EventViewer of Target Server to see what exactly (which file system) it is complaining about. 5. If nothing works, send me an email with screenshot of all settings that I have mentioned, including permissions to [e-mail] I hope it helps :)

[@lakshayarora3935]

@@TechBrothersIT thanks will follow your steps and update.

[@lakshayarora3935]

@@TechBrothersIT Hi , I have sent you mail with subject "Issue while DB restore".

[@MrBrijendrakumar]

Dear Sir, when we configure SQL server 2016 for mirroring we find the error: configure without domain environment database cannot be opened. it is in the middle of a restore error no 927 sir please help me step by step thanks

[@TechBrothersIT]

Please do not use SSMS 2017 - that’s the issue with SSMS - try using 2016 or 2014 SSMS.

[@MrBrijendrakumar]

@@TechBrothersIT Sir we installing SSMS 2016 after uninstall SSMS 17.3 but we find same error. one more thing we can not using domain environment.

[@worldcupbrazilfullhd]

Howhow to config "logon as" service sql, sql server agent, sql server browser,...??

[@TechBrothersIT]

+Long Nguyễn Hoàng you can use SQL server configuration manager (recommended) or can setup from the services.msc (not recommended).

[@TechBrothersIT]

+Long Nguyễn Hoàng during mirroring, if you use source sql service accounts, make sure those accounts have permission on target sql server.