SQL mirroring advise
-
I am working with a third party on a project for a client. It is a SharePoint farm with SQL 2012 standard edition. I wish this were the enterprise version to use Always-On!
So the company set up two DB servers and created a PowerShell script. Asked us to use that script whenever we deploy a new SP site, the script replicates the DB to the second server. I have not much experience with SQL other than basic installation, just learning with Always-On, but is there no way we could just automate the DB mirroring in such a way that any new DB created on server1 gets replicated to db2 server? I want to avoid any manual intervention at this DB level. This is not about automatic failover but to sync DB from server 1 to server 2.
-
I guess i found the answer!
If its 2 servers and not on cluster, this is the only way, but if this was on a cluster and a shared storage, it would've been already automated with the fact that the redundancy is only available at the host level unless you add redundancy on the storage level as well. Hope this is correct!
-
I believe that that is correct, I've never heard of nor can reasonably imagine a non-clustered databases creation sync mechanism being in place.
-
In touch with them to find out the cluster option, i dont understand why the individual DB server setup was created in the first place! Anyone knows what could be the drawback of the clustered setup which is not Always-On?
-
Are the two SQL servers sitting side by side? If not, shared storage and low latency for it's use would be super expensive.
Though I do wonder how their DR plan works if there isn't a cluster for the DB, what purpose does the second server serve? Warm spare?
-
@Dashrender said in SQL mirroring advise:
Are the two SQL servers sitting side by side? If not, shared storage and low latency for it's use would be super expensive.
Though I do wonder how their DR plan works if there isn't a cluster for the DB, what purpose does the second server serve? Warm spare?
Yes the servers are side by side, basically just sits there, and when a new site is created on SP side, run the script which mirrors the new DB created on server 1 to server 2. Eventually they were planning to introduce the witness server once our testing is complete and then enable auto failover. The downside here is that someone need to manually run the script for db mirroring of new databases
-
@Ambarishrh said in SQL mirroring advise:
@Dashrender said in SQL mirroring advise:
Are the two SQL servers sitting side by side? If not, shared storage and low latency for it's use would be super expensive.
Though I do wonder how their DR plan works if there isn't a cluster for the DB, what purpose does the second server serve? Warm spare?
Yes the servers are side by side, basically just sits there, and when a new site is created on SP side, run the script which mirrors the new DB created on server 1 to server 2. Eventually they were planning to introduce the witness server once our testing is complete and then enable auto failover. The downside here is that someone need to manually run the script for db mirroring of new databases
So the big thing here is that the databases are not mirrored, just the framework (schema) is at creation time. Very different from mirroring or clustering at that aspect level.
-
@scottalanmiller said in SQL mirroring advise:
@Ambarishrh said in SQL mirroring advise:
@Dashrender said in SQL mirroring advise:
Are the two SQL servers sitting side by side? If not, shared storage and low latency for it's use would be super expensive.
Though I do wonder how their DR plan works if there isn't a cluster for the DB, what purpose does the second server serve? Warm spare?
Yes the servers are side by side, basically just sits there, and when a new site is created on SP side, run the script which mirrors the new DB created on server 1 to server 2. Eventually they were planning to introduce the witness server once our testing is complete and then enable auto failover. The downside here is that someone need to manually run the script for db mirroring of new databases
So the big thing here is that the databases are not mirrored, just the framework (schema) is at creation time. Very different from mirroring or clustering at that aspect level.
Yes, we've told them this won't work and asked them to look at a clustered setup. Since the licenses are already in place and is SQL standard no option for Always-On. I want to know what would be the drawbacks for the clustered setup, as for sure there are some more advantages on Always-ON compared to the clustered setup.
-
I'm confused - I thought you said Always-On required enterprise licenses?
Doesn't clustering also require enterprise licenses?
-
@Ambarishrh said in SQL mirroring advise:
@scottalanmiller said in SQL mirroring advise:
@Ambarishrh said in SQL mirroring advise:
@Dashrender said in SQL mirroring advise:
Are the two SQL servers sitting side by side? If not, shared storage and low latency for it's use would be super expensive.
Though I do wonder how their DR plan works if there isn't a cluster for the DB, what purpose does the second server serve? Warm spare?
Yes the servers are side by side, basically just sits there, and when a new site is created on SP side, run the script which mirrors the new DB created on server 1 to server 2. Eventually they were planning to introduce the witness server once our testing is complete and then enable auto failover. The downside here is that someone need to manually run the script for db mirroring of new databases
So the big thing here is that the databases are not mirrored, just the framework (schema) is at creation time. Very different from mirroring or clustering at that aspect level.
Yes, we've told them this won't work and asked them to look at a clustered setup. Since the licenses are already in place and is SQL standard no option for Always-On. I want to know what would be the drawbacks for the clustered setup, as for sure there are some more advantages on Always-ON compared to the clustered setup.
Won't work... for what? What's the end goal?
-
@Ambarishrh said in SQL mirroring advise:
The downside here is that someone need to manually run the script for db mirroring of new databases
Scheduled task that runs every hour or so?
-
We replicate between none clustered SQL servers here but it's one way. All Onsite Clusters replicate back to the Corp Cluster every 30min or so. Two way would require a lot of Checks.
-
@dafyre said in SQL mirroring advise:
@Ambarishrh said in SQL mirroring advise:
The downside here is that someone need to manually run the script for db mirroring of new databases
Scheduled task that runs every hour or so?
How often are new databases created? Normally that's a once every few years thing. In companies that make new ones all of the time, normally there are DBAs that would do any mirroring and this would be trivial.
-
@scottalanmiller said in SQL mirroring advise:
@Ambarishrh said in SQL mirroring advise:
@scottalanmiller said in SQL mirroring advise:
@Ambarishrh said in SQL mirroring advise:
@Dashrender said in SQL mirroring advise:
Are the two SQL servers sitting side by side? If not, shared storage and low latency for it's use would be super expensive.
Though I do wonder how their DR plan works if there isn't a cluster for the DB, what purpose does the second server serve? Warm spare?
Yes the servers are side by side, basically just sits there, and when a new site is created on SP side, run the script which mirrors the new DB created on server 1 to server 2. Eventually they were planning to introduce the witness server once our testing is complete and then enable auto failover. The downside here is that someone need to manually run the script for db mirroring of new databases
So the big thing here is that the databases are not mirrored, just the framework (schema) is at creation time. Very different from mirroring or clustering at that aspect level.
Yes, we've told them this won't work and asked them to look at a clustered setup. Since the licenses are already in place and is SQL standard no option for Always-On. I want to know what would be the drawbacks for the clustered setup, as for sure there are some more advantages on Always-ON compared to the clustered setup.
Won't work... for what? What's the end goal?
Won't work: Current stage its 2 separate DB servers and mirroring needs to be done by executing a script whenever there is a new db is created by SP.
End Goal: A fully automated failover setup giving high availability for the SharePoint solution
Just read this looks like a clean post explaining SQL Failover vs AlawaysON https://www.concurrency.com/blog/w/should-you-choose-a-sql-server-failover-cluster-in
-
@Ambarishrh said in SQL mirroring advise:
@scottalanmiller said in SQL mirroring advise:
@Ambarishrh said in SQL mirroring advise:
@scottalanmiller said in SQL mirroring advise:
@Ambarishrh said in SQL mirroring advise:
@Dashrender said in SQL mirroring advise:
Are the two SQL servers sitting side by side? If not, shared storage and low latency for it's use would be super expensive.
Though I do wonder how their DR plan works if there isn't a cluster for the DB, what purpose does the second server serve? Warm spare?
Yes the servers are side by side, basically just sits there, and when a new site is created on SP side, run the script which mirrors the new DB created on server 1 to server 2. Eventually they were planning to introduce the witness server once our testing is complete and then enable auto failover. The downside here is that someone need to manually run the script for db mirroring of new databases
So the big thing here is that the databases are not mirrored, just the framework (schema) is at creation time. Very different from mirroring or clustering at that aspect level.
Yes, we've told them this won't work and asked them to look at a clustered setup. Since the licenses are already in place and is SQL standard no option for Always-On. I want to know what would be the drawbacks for the clustered setup, as for sure there are some more advantages on Always-ON compared to the clustered setup.
Won't work... for what? What's the end goal?
Won't work: Current stage its 2 separate DB servers and mirroring needs to be done by executing a script whenever there is a new db is created by SP.
End Goal: A fully automated failover setup giving high availability for the SharePoint solution
What they are doing is unrelated to their end goal. How does mirroring database creation help with failover. There isn't even a first step in preparing for a failover here. What is going on is totally something different.
-
@scottalanmiller said in SQL mirroring advise:
@Ambarishrh said in SQL mirroring advise:
@scottalanmiller said in SQL mirroring advise:
@Ambarishrh said in SQL mirroring advise:
@scottalanmiller said in SQL mirroring advise:
@Ambarishrh said in SQL mirroring advise:
@Dashrender said in SQL mirroring advise:
Are the two SQL servers sitting side by side? If not, shared storage and low latency for it's use would be super expensive.
Though I do wonder how their DR plan works if there isn't a cluster for the DB, what purpose does the second server serve? Warm spare?
Yes the servers are side by side, basically just sits there, and when a new site is created on SP side, run the script which mirrors the new DB created on server 1 to server 2. Eventually they were planning to introduce the witness server once our testing is complete and then enable auto failover. The downside here is that someone need to manually run the script for db mirroring of new databases
So the big thing here is that the databases are not mirrored, just the framework (schema) is at creation time. Very different from mirroring or clustering at that aspect level.
Yes, we've told them this won't work and asked them to look at a clustered setup. Since the licenses are already in place and is SQL standard no option for Always-On. I want to know what would be the drawbacks for the clustered setup, as for sure there are some more advantages on Always-ON compared to the clustered setup.
Won't work... for what? What's the end goal?
Won't work: Current stage its 2 separate DB servers and mirroring needs to be done by executing a script whenever there is a new db is created by SP.
End Goal: A fully automated failover setup giving high availability for the SharePoint solution
What they are doing is unrelated to their end goal. How does mirroring database creation help with failover. There isn't even a first step in preparing for a failover here. What is going on is totally something different.
Exactly what i was asking earlier.
