Database must be in FULL Recovery mode and can not have the file stream file group.
Database mirroring is the SQL Server Enterprise Edition feature. The Principal and Mirror database must use Enterprise Edition. The Witness server can be Express.
The default port for the SQL Server mirroring endpoint is 5022 but it can be changed to any unused port.
Below example is setting up the database mirror on the high safety with automatic failover or called Synchronous Database Mirroring (High-Safety Mode)
- SG1 is the principal SQL Server host.
- SG21 is the Mirror SQL Server host.
- SG13 is the Witness SQL Server host.
Restore Database
Restore Database from principal to Mirror database with non recovery and restore Log from Principal to Mirror database with non recovery
- RESTORE DATABASE [AdventureWorks] from DISK = '\\SG11\C$\DUMP\AdventureWorks_20111105_3.db' with replace,norecovery
- RESTORE LOG [AdventureWorks] from DISK = '\\SG11\C$\DUMP\AdventureWorks_20111105_3.log' with norecovery
Create the Endpoint on Principal, Mirror and witness server
You will need to change the port number if they are on the same server. However, it is recommend you put in the different server ( for High availability of course )
Create end point on both Principal and Mirror server
- CREATE ENDPOINT mirror
- STATE = STARTED
- AS TCP ( LISTENER_PORT = 7051 )
- FOR DATABASE_MIRRORING (ROLE=PARTNER,ENCRYPTION=REQUIRED ALGORITHM RC4);
- GO
Create the end point on the witness server
- CREATE ENDPOINT mirror
- STATE = STARTED
- AS TCP ( LISTENER_PORT = 7051 )
- FOR DATABASE_MIRRORING (ROLE=WITNESS,ENCRYPTION=REQUIRED ALGORITHM RC4);
- GO
Setup the Mirroring database
Run below on the Mirroring database server
- ALTER DATABASE AdventureWorks
- SET PARTNER = 'TCP://SG11:7051'
Setup the principal and Witness server
Run below on the principal server.
- ALTER DATABASE AdventureWorks
- SET PARTNER = 'TCP://SG21:7051'
- ALTER DATABASE AdventureWorks
- SET WITNESS = 'TCP://SG13:7051'
The SQL Agent job will be created on the mirroring database server, we can use msdb.dbo.sp_help_job to review the job detail.
Use below tables to view the Mirror database and endpoint configuration. This can be run on either Principal, mirror and witness server.
- use master;
- SELECT name, port FROM sys.tcp_endpoints;
- select * from sys.database_mirroring_endpoints;
- SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints;
- select * from sys.database_mirroring_witnesses;
- select * from sys.database_mirroring;
Access data on the mirroring database
Since the mirroring database is under the recovery and mirroring state. We can not directly access the data. As alternative, we can create the snapshot and access the snapshot data.
- CREATE DATABASE [S_AdventureWorks_20111105]
- ON (NAME='AdventureWorks_data' ,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSG21\MSSQL\DATA\S_AdventureWorks_20111105.ss')
- AS SNAPSHOT of [AdventureWorks]
Manual failover
In the Synchronous Database Mirroring (High-Safety Mode), the failover will automatically happen in the event the principal database is not available. If you set the mirror mode as high performance mode, this will require the manually failover and use FORCE_SERVICE_ALLOW_DATA_LOSS
To manually failover in the High-Safety mode, we can run below on the principal server.
- Alter database [AdventureWorks]set partner failover
Once it is done, when we select from sys.database_mirroring, we can see the original principal database will become “Mirror” and the old mirror will become ‘Principal’.
Remove the witness
Anytime during the mirroring, we can disable the witness by running below command.
- ALTER DATABASE [AdventureWorks] SET WITNESS OFF
Remove the database mirroring
- Remove the mirroring database session
Run on any of the partner server
- ALTER DATABASE [AdventureWorks] SET PARTNER OFF
- Recover the mirroring database (Optional)
If any snapshots were created, the needs to be drop before we can recover the database.
- RESTORE DATABASE [AdventureWorks] WITH RECOVERY;
- Remove the mirroring monitor agent job (Optional)
- msdb.dbo.sp_delete_job @job_name='Database Mirroring Monitor Job'
- Remove the end point (optional)
- DROP ENDPOINT mirror
Database Mirroring Monitor
We can use this to monitor the database mirroring status.
Notes
A FILESTREAM filegroup cannot be created on the principal server. Database mirroring cannot be configured for a database that contains FILESTREAM filegroups.
Reference
Database Mirroring Overview http://msdn.microsoft.com/en-us/library/ms189852.aspx
In this competitive world embedded systems are very important for our daily life, feeding from electronic devices, medical equipments, automobiles, telecom instruments, etc. Most of the people are using embedded systems and it was rising day by day. It has been changing our life.
ReplyDeleteRegards,
Embedded Training in Chennai | Embedded system Training in Chennai AWS Training in Chennai | AWS course in Chennai
Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing.
ReplyDeleteRegards,
AWS Training | AWS Training in Chennai
This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
ReplyDeleteAndroid training in chennai
Ios training in chennai
Good Post..Thanks for sharing such a wonderful article..
ReplyDeletePLC Training in Chennai | PLC Training Institute in Chennai | PLC Training Center in Chennai | PLC SCADA Training in Chennai | PLC Training in Kerala | AutoCAD Training in Chennai | Embedded Training in Chennai | VLSI Training in Chennai | Inplant Training in Chennai
Needed to compose you a very little word to thank you yet again regarding the nice suggestions you’ve contributed here.selenium training in bangalore
ReplyDeleteExcellent Work.Thanks for sharing.
ReplyDeleteAWS Training in Coimbatore
Informative post, thanks for taking time to share this page.
ReplyDeleteAWS Training in Chennai
AWS course in Chennai
Azure Training in Chennai
Azure course in Chennai
RPA Training in Chennai
UiPath Training in Chennai
I am really enjoying reading your well written articles.
ReplyDeleteIt looks like you spend a lot of effort and time on your blog.
I have bookmarked it and I am looking forward to reading new articles. Keep up the good work..
hadoop institute in bangalore
best institute for bigdata in bangalore
big data coaching in bangalore
Core Java Training in Bangalore
Best Java Training in Bangalore
Advanced Java Training in Bangalore
Thank you for sharing this post.
ReplyDeletethesimpletruth
Education
Pretty blog, so many ideas in a single site, thanks for the informative article, keep updating more article.
ReplyDeleteSalesforce Developer 501 Training in Chennai
Salesforce Developer 502 Training in Chennai
Cloud computing Training institutes in Chennai
Best Cloud computing Training in Chennai
Cloud computing institutes in Chennai
Cloud computing courses in Chennai
Thanks for the great post on your blog, it really gives me an insight on this topic.I must thank you for this informative ideas. I hope you will post again soon.
ReplyDeleteAWS Training Institutes in Vadapalani
AWS Training in Thirumangalam
AWS Course in Bangalore
Aws Certification in Bangalore
I liked your blog.Thanks for your interest in sharing your ideas.keep doing more.
ReplyDeleteSpoken English Classes in Bangalore
Spoken English Class in Bangalore
Spoken English Training in Bangalore
Best Spoken English Classes in Bangalore
Spoken English Course near me
English Speaking Course in Bangalore
Best Spoken English Classes in Bangalore
This is really too useful and have more ideas and keep sharing many techniques. Eagerly waiting for your new blog keep doing more.
ReplyDeletehadoop institute in bangalore
Aws Training Institutes in Bangalore
Aws Certification Training in Bangalore
Aws Training Center in Bangalore
Best Institute For Java Training In Bangalore
This blog is very attractive to me and also very interesting content. I got more info from your blog. Thank you for your excellent post!!!
ReplyDeleteEthical Hacking Course in Bangalore
Hacking Course in Bangalore
Certified Ethical Hacking Course in Bangalore
Hacking Training in Adyar
Ethical Hacking Training in Annanagar
Ethical Hacking Course in Vadapalani
Ethical Hacking Course in Chennai
ReplyDeleteYour post is great. It shows your deep understanding of the subject. Waiting for your future posts.
Pega training in chennai
Pega course in chennai
Pega training institutes in chennai
Pega course
Pega training
SAS Training in Chennai
SAS Course in Chennai
IELTS Coaching in Chennai
IELTS Training in Chennai
Good to read the post
ReplyDeleteAb Initio training institute in chennai
Good work done. Great work. Keep this through out and keep updating the information
ReplyDeleteabout this technology.
JAVA Training in Chennai
Selenium Training in Chennai
Best JAVA Training institute in Chennai
Java Training
Selenium training institute in Chennai
Best selenium training in chennai
java training in tambaram
selenium training in porur
Great information you have been shared and it will be useful to everyone.
ReplyDeleteSpoken English Classes in Chennai
Best Spoken English Classes in Chennai
German Language Classes in Chennai
Japanese Classes in Chennai
spanish language in chennai
IELTS Coaching centre in Chennai
Spoken English Classes in Porur
Spoken English Classes in Adyar
Good job and great informative blog.
ReplyDeleteJapanese Classes in Chennai
Japanese Course in Chennai
Best Spoken English Classes in Chennai
French Language Classes in Chennai
pearson vue exam centers in chennai
German Classes in Chennai
Japanese Classes in OMR
Japanese Classes in Porur
Amazing Post. The way of writing is very unique. Your write-up is extra-ordinary. Thanks for Sharing.
ReplyDeleteNode JS Training in Chennai
Node JS Course in Chennai
Node JS Advanced Training
Node JS Training Institute in chennai
Node JS Training in T Nagar
Node JS Training in Anna Nagar
Good explanation with appropriate solution.
ReplyDeleteGuest posting sites
Education
Thanks for this wonderful blog it is really informative to all.
ReplyDeleteTOEFL Coaching in Chennai
TOEFL Training in Chennai
Spoken English in Chennai
french courses in chennai
pearson vue test center in chennai
German Classes in Chennai
TOEFL Coaching in VelaChery
TOEFL Coaching in Tambaram
Thanks for updating this information. Good job.
ReplyDeleteonline photo mug order india
print photo mugs online india
mac rental in chennai
used laptop rentals in chennai
pvt ltd company registration in chennai
company registration services in india
Very Informative...Glad to find your blog...Keep Sharing...
ReplyDeleteTESTING & TRAINING ON SELENIUM
ORACLE TRAINING IN CHENNAI
PYTHON TRAINING IN CHENNAI
DATA SCIENCE TRAINING IN CHENNAI
ONLINE INTERNSHIPS
ReplyDeleteWINTER INTERNSHIPS FOR ENGINEERING
SUMMER INTERNSHIP
SUMMER INTERNSHIP IN CHENNAI
WINTER INTERNSHIP IN CHENNAI
INTERNSHIP IN CHENNAI
INTERNSHIP
INTERNSHIPS
IT INTERNSHIP IN CHENNAI
Really nice...keep sharing...
ReplyDeleteSAMPLE QUESTIONNAIRE FOR YARDI SOFTWARE APTITUDE TEST PDF
UNBLOCK YOURSELF WHATSAPP APK
CHILE CLOTHING
BRAC NGO EXAM QUESTION
WHATSAPP BLOCK HACK
ZENSOFT PUNE APTITUDE QUESTIONS
JS MAX INTEGER
TP LINK HACK PASSWORD
PYTHON DEVELOPER RESUME FOR 2 YEARS EXPERIENCE
UBUNTU HACK WIFI
GREAT...
ReplyDeletePython Internship
Dotnet Internship
Java Internship
Web Design Internship
Php Internship
Android Internship
Big Data Internship
Cloud Internship
Hacking Internship
Robotics Internship
nice blog...
ReplyDeleteOracle Internship
R Programming Internship
CCNA Internship
Networking Internship
Artificial Intelligence Internship
Machine Learning Internship
Blockchain Internship
Sql Server Internship
Iot Internship
Data Science Internship
GOOD POST...
ReplyDeleteSelenium Testing Internship
Linux Internship
C Internship
CPP Internship
Embedded System Internship
Matlab Internship
good
ReplyDeleteFree Internship for cse students in Chennai
R Programming Internship
Hadoop Training in Chennai
Free Internship Training in Chennai
Robotics Training chennai
Summer Internship For BSC students
Internships in Chennai for CSE
CCNA Institute in Chennai
Data Science Internship in Chennai
Aeronautical Engineering Internship
good
ReplyDeleteGeteventlisteners javascript
Karl fischer titration interview questions
How to hack tp link router
T system aptitude questions
Resume for bca final year student
Test case for railway reservation system
T systems pune placement papers
Infrrd bangalore interview questions
Max number in javascript
Paypal integration in php step by step pdf
GREAT....
ReplyDeleteFREE Internship in Nagpur For Computer Engineering Students
Internship For MCA Students
Final Year Projects For Information Technology
Web Design Class
Mechanical Engineering Internship Certificate
Inplant Training For Mechanical Engineering Students
Inplant Training Certificate
Ethical Hacking Course in Chennai
Winter Internship For ECE Students
Internships For ECE Students in Bangalore
NICE...
ReplyDeleteHow To Hack On Crosh
Request Letter For Air Ticket Booking To HR
Zeus Learning Aptitude Paper For Software Developer
Cimpress Interview Questions
VCB Rating
Appreciation Letter To Vendor
JS MAX Safe Integer
Why Do You Consider Yourself Suitable For The Position
How To Hack Android Phone From PC
About Bangalore Traffic Essay
GOOD
ReplyDeletehacking course
internship for it students
ccna course chennai
civil engineering internship report pdf india
kashi infotech
internships in hyderabad for cse students 2018
cse internships in hyderabad
inplant training for diploma students
internship in hyderabad for cse students
GOOD
ReplyDeletenodejs while loop
icici bank po interview questions and answers pdf
craterzone aptitude test
zensoft recruitment process
java developer resume 1 years experience
python developer resume pdf
infrrd private limited interview questions
js int max value
delete * from table oracle
t systems pune aptitude questions
nice
ReplyDeleteInternship For Aerospace Engineering
Mechanical Engineering Internships in Chennai
Robotics Courses
Kaashiv
Training Letter Format For Mechanical Engineer
Internship For BCA Student
Fake Internship Certificate
MBA Internship
Free Internship For CSE Students in Chennai
Oracle Internship 2020
great
ReplyDeleteJavascript Maximum Integer
INT MAX Javascript
Acceptance is to an Offer What a Lighted Match is to a Train of Gunpowder
Who Can Issue Character Certificate
Technical Support Resume DOC
PHP Developer Resume For 3 Year Experience
Wapda Interview Questions
Power BI Resume Download
a Dishonest Dealer Professes to Sell His Goods at a Profit of 20
Failed to Find 'Android_Home' Environment Variable. TRY Setting it Manually
GOOD
ReplyDeleteiot training in coimbatore
summer training for 3rd year electronics and communication engineering students
goa current affairs 2019
project for information technology students
online internship for bca students
winter training for mechanical engineering students
ccna training
industrial training report for electronics and communication pdf
matlab courses in chennai
bba internship project
GOOD
ReplyDeleteResume Format For Bca Freshers
British Airways Interview Questions And Answers Pdf
Asus Tf101 Android 8
Crome://Flags/
T Systems Aptitude Test
Python Resume Ror 2 Years Experience
Ajax Redirect To Another Page With Post Data
Paramatrix Technologies Aptitude Questions And Answers
Adder Subtractor Comparator Using Ic 741 Op-Amp Theory
How To Hack Wifi With Ubuntu
Really very happy to say, your post is very interesting to read. I never stop myself to say something about it. You’re doing a great job. Keep it up…
ReplyDeleteLearn Best Cognos Training in Bangalore from Experts. Softgen Infotech offers the Best Cognos Training in Bangalore.100% Placement Assistance, Live Classroom Sessions, Only Technical Profiles, 24x7 Lab Infrastructure Support.