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
IEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes. IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble.Final Year Projects for CSE
DeleteSpring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining .
Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai
The Angular Training covers a wide range of topics including Angular Directives, Angular Services, and Angular programmability.Angular Training
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.
I have to agree with everything in this post. Thanks for useful sharing information.
ReplyDeleteHadoop Training in Chennai
Hadoop Training in Bangalore
Big Data Course in Coimbatore
Big data training in coimbatore
Big Data Hadoop Training in Chennai
Big Data Courses in Bangalore
python training in bangalore
salesforce training in bangalore
Right here is the right site for everyone who really wants to understand this topic. You know a whole lot its almost tough to argue with you (not that I really will need to…HaHa). You certainly put a brand new spin on a subject which has tech been written about for a long time. Wonderful stuff, just excellent!
ReplyDeleteGood blog, its really very informative, do more blog under good concepts.
ReplyDeletePHP Training in Bangalore
PHP Course in Bangalore
PHP Training Institute in Bangalore
PHP Classes in Bangalore
Best PHP Training Institute in Bangalore
PHP Course in Chennai
php training in coimbatore
php Course in madurai
DevOps Training in Bangalore
DOT NET Training in Bangalore
ReplyDeleteThanks for sharing useful information. I learned something new from your bog. Its very interesting and informative. keep updating.
Selenium Training in chennai | Selenium Training in anna nagar | Selenium Training in omr | Selenium Training in porur | Selenium Training in tambaram | Selenium Training in velachery
I admire this article for the well-researched content and excellent wording. I got so involved in this material that I couldn’t stop reading. I am impressed with your work and skill. Thank you so much. good luck
ReplyDeleteAi & Artificial Intelligence Course in Chennai
PHP Training in Chennai
Ethical Hacking Course in Chennai Blue Prism Training in Chennai
UiPath Training in Chennai
It has been simply incredibly generous with you to provide openly what exactly many individuals wouldíve marketed for an eBook to end up making some cash for their end, primarily given that you could have tried it in the event you wanted good
ReplyDeleteAi & Artificial Intelligence Course in Chennai
PHP Training in Chennai
Ethical Hacking Course in Chennai Blue Prism Training in Chennai
UiPath Training in Chennai
Transformation of marketing strategy
ReplyDeletewww.bluebase.in
https://www.facebook.com/bluebasesoftware/
https://www.linkedin.com/…/bluebase-software-services-pvt-…/
https://twitter.com/BluebaseL/
#applications #EnterpriseSolutions #CloudApplication #HostingServices #MobileAppDevelopment #Testing #QA #UIdesign #DigitalMarketing #SocialMediaOptimisation #SMO #SocialMediaMarketing #SMM #SearchEngineOptimisation #SEO #SearchEngineMarketing #SEM #WebsiteDevelopment #WebsiteDesigning #WebsiteRevamping
Bring your Organisation Brand into the Digital World, to know more contact us
ReplyDeletewww.bluebase.in
https://www.facebook.com/bluebasesoftware/
https://www.linkedin.com/…/bluebase-software-services-pvt-…/"
https://twitter.com/BluebaseL/
#applications #EnterpriseSolutions #CloudApplication #HostingServices #MobileAppDevelopment #Testing #QA #UIdesign #DigitalMarketing #SocialMediaOptimisation #SMO #SocialMediaMarketing #SMM #SearchEngineOptimisation #SEO #SearchEngineMarketing #SEM #WebsiteDevelopment #WebsiteDesigning #WebsiteRevamping #crm #erp #custombuildapplication #android #ios
For Worldwide Remote IT Support
ReplyDeleteContact Quadsel Systems Pvt Ltd
+91 9841016631 / +919841283352
girish@quadsel.in / kalyan@quadsel.in
www.quadsel.in/networking/
https://twitter.com/quadsel/
https://www.linkedin.com/company/quadsel-systems-private-limited/
https://www.facebook.com/quadselsystems/
#quadsel #network #security #technologies #managedservices #Infrastructure #Networking #OnsiteResources #ServiceDeskSupport #StorageServices #WarrantyAMCServices #datacentersolutions #DataCenterBuild #EWaste #InfraConsolidation #DisasterRecovery #NetworkingServices #ImagingServices #MPS #Consulting #WANOptimisation #enduserservices
We provide you with flexible services and complete hybrid network solutions. It can provide your organisation with exceptional data speeds, advanced external security protection, and high-resilience by leveraging the latest SD-WAN and networking technologies to monitor, manage and strengthening your organisation’s existing network devices.
ReplyDeletewww.quadsel.in/networking/
https://twitter.com/quadsel/
https://www.linkedin.com/company/quadsel-systems-private-limited/
https://www.facebook.com/quadselsystems/
#quadsel #network #security #technologies #managedservices #Infrastructure #Networking #OnsiteResources #ServiceDeskSupport #StorageServices #WarrantyAMCServices #datacentersolutions #DataCenterBuild #EWaste #InfraConsolidation #DisasterRecovery #NetworkingServices #ImagingServices #MPS #Consulting #WANOptimisation #enduserservices
Hey, would you mind if I share your blog with my twitter group? There’s a lot of folks that I think would enjoy your content. Please let me know. Thank you.
ReplyDeleteOracle Training | Online Course | Certification in chennai | Oracle Training | Online Course | Certification in bangalore | Oracle Training | Online Course | Certification in hyderabad | Oracle Training | Online Course | Certification in pune | Oracle Training | Online Course | Certification in coimbatore
Thanks Admin For sharing this massive info with us. it seems you have put more effort to write this blog , I gained more knowledge from your blog. Keep Doing..
ReplyDeleteRegards,thanks
Oracle Training | Online Course | Certification in chennai | Oracle Training | Online Course | Certification in bangalore | Oracle Training | Online Course | Certification in hyderabad | Oracle Training | Online Course | Certification in pune | Oracle Training | Online Course | Certification in coimbatore
It is a useful sharing...Thank you very much for sharing this awesome post. oracle training in chennai
ReplyDeleteI liked this blog.. I got some clear information from this blog.. Thanks for taking a time to share this blog...
ReplyDeleteEthical Hacking Course in Porur
Ethical Hacking Course in OMR
Ethical Hacking Course in Tambaram
Ethical Hacking Course in Anna Nagar
Ethical Hacking Course in T Nagar
Really, it’s a useful blog. Thanks for sharing this information.
ReplyDeleteIonic Online Course
Kotlin Online Course
social media marketing Online Training
React Native Online Training
R programming Training in Chennai
Xamarin Course in Chennai
Ionic Course in Chennai
Nice blog, Thanks for sharing this useful information.
ReplyDeleteMobile Application Testing Online Training
Mobile App Development Online Course
Mobile App Development Courses in Chennai
Google Analytics Online Course
Google Analytics Training in Chennai
Content Writing Course in Chennai
Online Content Writing Course
Really, it’s a useful blog. Thanks for sharing this information.
ReplyDeleteIonic Online Course
Kotlin Online Course
social media marketing Online Training
React Native Online Training
Xamarin Online Course
React native Online Course
I was really impressed to see this blog, it was very interesting and it is very useful for all.
ReplyDeletelist to string python
data structures in python
polymorphism in python
python numpy tutorial
python interview questions and answers
convert list to string python
Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site
ReplyDeleteAB INITIO training in bangalore
best AB INITIO training institutes in bangalore