April 29, 2018 at 11:50 am
I am new to SQL server 2016 and requires some information. I have 2 SQL server instances located Geographically apart location through WAN.
I have 1 database that needs to be mirrored from SQL Instance 1 (in location 1) to SQL Instance 2 in (Location 2). I have SQL server 2016 installed
. Can I do the Mirroring in place of Always On. After googling about "Always on", it seems that Always On requires these 2 SQL instance in SQL cluster.But as my 2 SQL instances are geographically apart, I do not want to go to SQL clustering as I need to depend on WAN network.
Hence I am thinking of Opting "Mirroring" solution OR What is the best possible option that I can opt for making these 2 database as mirror image at any point of time? I do not require the secondary database(in Location 2) to be accessed by any application when Primary Database (in Location 1) is active.Can some one suggest something?
April 29, 2018 at 4:13 pm
anjang2k - Sunday, April 29, 2018 11:50 AMCan some one suggest something?
The database mirroring should work for 2016 - https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/setting-up-database-mirroring-sql-server?view=sql-server-2016
For SQL Server 2017 you can use Read-scale AG - https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/read-scale-availability-groups?view=sql-server-2017
April 29, 2018 at 4:47 pm
Mirroring is supported in SQL 2016.
The question I have is about location 2. Is this a DR only site? Will there be a need for the databases to automatically fail over? If not, you can set up a mirror and a replica. If you need automatic fail over, then you will also need to configure a witness server.
There are a few good articles in the Stairways that you should read.
http://www.sqlservercentral.com/stairway/
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 29, 2018 at 8:02 pm
Michael L John - Sunday, April 29, 2018 4:47 PMMirroring is supported in SQL 2016.The question I have is about location 2. Is this a DR only site? Will there be a need for the databases to automatically fail over? If not, you can set up a mirror and a replica. If you need automatic fail over, then you will also need to configure a witness server.
There are a few good articles in the Stairways that you should read.
http://www.sqlservercentral.com/stairway/
Doe the mirroring in SQL server 2016 is similar to that we have in SQL server 2008 R2. I have done mirroring with SQL server 2008 R2 system with witness server. I know that. But I need to configure the same in SQL server 2016. That is why seeking information whether the same is possible in 2016 or not. I do not require automatic fail over. What I require to install to configure Witness Server?Is it SQL express edition for witness server like in SQL server 2008 R2?
April 30, 2018 at 2:49 am
The best thing to do is just try it in a test environment. Developer Edition is free.
Alternatively you could use Basic Availability Groups, which are essentially mirroring+, and are available in Standard Edition.
April 30, 2018 at 7:42 am
anjang2k - Sunday, April 29, 2018 8:02 PMMichael L John - Sunday, April 29, 2018 4:47 PMMirroring is supported in SQL 2016.The question I have is about location 2. Is this a DR only site? Will there be a need for the databases to automatically fail over? If not, you can set up a mirror and a replica. If you need automatic fail over, then you will also need to configure a witness server.
There are a few good articles in the Stairways that you should read.
http://www.sqlservercentral.com/stairway/Doe the mirroring in SQL server 2016 is similar to that we have in SQL server 2008 R2. I have done mirroring with SQL server 2008 R2 system with witness server. I know that. But I need to configure the same in SQL server 2016. That is why seeking information whether the same is possible in 2016 or not. I do not require automatic fail over. What I require to install to configure Witness Server?Is it SQL express edition for witness server like in SQL server 2008 R2?
Nothing has changed. The screen's and the T-SQL to set up mirroring is exactly the same. Have you installed this and actually looked at it?
As far as a witness, SQL Express will work. But if you do not need automatic failover, what does that matter?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 30, 2018 at 10:44 am
Michael L John - Monday, April 30, 2018 7:42 AManjang2k - Sunday, April 29, 2018 8:02 PMMichael L John - Sunday, April 29, 2018 4:47 PMMirroring is supported in SQL 2016.The question I have is about location 2. Is this a DR only site? Will there be a need for the databases to automatically fail over? If not, you can set up a mirror and a replica. If you need automatic fail over, then you will also need to configure a witness server.
There are a few good articles in the Stairways that you should read.
http://www.sqlservercentral.com/stairway/Doe the mirroring in SQL server 2016 is similar to that we have in SQL server 2008 R2. I have done mirroring with SQL server 2008 R2 system with witness server. I know that. But I need to configure the same in SQL server 2016. That is why seeking information whether the same is possible in 2016 or not. I do not require automatic fail over. What I require to install to configure Witness Server?Is it SQL express edition for witness server like in SQL server 2008 R2?
Nothing has changed. The screen's and the T-SQL to set up mirroring is exactly the same. Have you installed this and actually looked at it?
As far as a witness, SQL Express will work. But if you do not need automatic failover, what does that matter?
I do not require any automatic fail over. Thanks for all your support. Go the answer.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy