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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply