Bill H
asked on
SQL Database Copy
Hello,
We have a few clients who use some custom apps on SQL at their locations. Now, we want to have these DB's replicate to say a Cloud server - so we can have full live copy there for some analytics we'd like to run on them. How can i get this done?
We have a few clients who use some custom apps on SQL at their locations. Now, we want to have these DB's replicate to say a Cloud server - so we can have full live copy there for some analytics we'd like to run on them. How can i get this done?
Pavel Celba
Why do you need live data for "some analytics"? Are these analytics so fine and frequent that you cannot use off-line data copy created from the main database backup file?
ASKER
Yes they are that fine and frequent. Please provide a solution.
Which SQL Server version and Edition are you using?
If SQL Server 2012 or higher and if Enterprise Edition you can use AlwaysOn where the replica will be in the Cloud instance.
If SQL Server 2012 or higher and if Enterprise Edition you can use AlwaysOn where the replica will be in the Cloud instance.
ASKER
Let me check the version, does it have to be Enterprise?
I would say Always On is too heavy for this purpose.
You can use any mirroring (deprecated soon) or replication. Of course, your client servers must see your servers, you have to define publishers (client SQL servers) and subscribers (your servers) and setup all the paths.
More about replication: https://docs.microsoft.com/en-us/sql/relational-databases/replication/sql-server-replication
You have three options: https://docs.microsoft.com/en-us/sql/relational-databases/replication/types-of-replication
3rd party replication software is also available.
Your clients should be prepared for higher system load so they should have sufficient resources reserve for replication.
You can use any mirroring (deprecated soon) or replication. Of course, your client servers must see your servers, you have to define publishers (client SQL servers) and subscribers (your servers) and setup all the paths.
More about replication: https://docs.microsoft.com/en-us/sql/relational-databases/replication/sql-server-replication
You have three options: https://docs.microsoft.com/en-us/sql/relational-databases/replication/types-of-replication
3rd party replication software is also available.
Your clients should be prepared for higher system load so they should have sufficient resources reserve for replication.
ASKER
OK, we are using Express or Standard on 2014 for most environments currently - will that be OK?
What 3rd party software works for just SQL? Looking for the easiest route here!
What 3rd party software works for just SQL? Looking for the easiest route here!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@pcelba, AlwaysOn is too heavy and you're suggesting Mirroring (that's what AlwaysOn came to replace) and even worst Replication? I would really like to know what do you consider heavy.
@cobra25, with Express Edition you can't do almost nothing. For Standard Edition you have some limitations also but you can do something. By the way, old Mirroring solution doesn't let you connect to the Replica. That's one of the bigger benefits with AlwaysOn.
@cobra25, with Express Edition you can't do almost nothing. For Standard Edition you have some limitations also but you can do something. By the way, old Mirroring solution doesn't let you connect to the Replica. That's one of the bigger benefits with AlwaysOn.
Heavy is the price of necessary licenses. You have to setup new SQL Enterprise at both sides to have it running legally.
SQL 2016 Standard allows Basic Availability Groups with significant limitations: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups
In SQL Express you can do nothing not "almost nothing" at the High Availability field...
Mirroring was a nonsense...
So we are back in the (3rd party) replication software most probably.
Of course, to backup the client data and copy them to the main server periodically is also possible solution at no cost.
SQL 2016 Standard allows Basic Availability Groups with significant limitations: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups
In SQL Express you can do nothing not "almost nothing" at the High Availability field...
Mirroring was a nonsense...
So we are back in the (3rd party) replication software most probably.
Of course, to backup the client data and copy them to the main server periodically is also possible solution at no cost.
Ok. I'ld prefer to call it expensive instead as "heavy" can be mislead to performance issues.
ASKER
Vitor which third party solution do you recommend?
I don't work with 3rd party solutions. Usually the solutions provided by the SQL Server itself is enough for me.
ASKER
Great thanks - is there a tutorial on how to set this up?
Cobra, in my first comment I've added a link to AlwaysOn feature. You can start from there.
ASKER
Yeah always on requires clustering so I'll pass. Last question: for the Cloud "end" can it have multiple replication partners? Essentially I will need 5-6 servers from diff sites replicating to 1 server in the cloud
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
so for always on i need enterprise then? that is costly - can i get away with std on both sides?
Yes, you'll need Enterprise Edition for AlwaysOn.
SQL Server 2016 offer a Basic AlwaysOn for Standard Edition but that has many limitations as you can see it here.
SQL Server 2016 offer a Basic AlwaysOn for Standard Edition but that has many limitations as you can see it here.
ASKER
Enteprise on both sides for always on? Hmm, that might be costly on the sending end.
I'll guess I'll have to do the mirroring, it's not a DR site, so I think that should be fine.?
I'll guess I'll have to do the mirroring, it's not a DR site, so I think that should be fine.?
Well, the Basic AlwaysOn it's very similar to the Mirroring solution but mind that the Mirroring solution doesn't allow you to perform queries on the Secondary Replica.
ASKER
Oh wow, really? That's a deal breaker..so I guess I'll have to with Enterprise, now can I stick with 2014?
Yes. That's why you need to pay more for an Enterprise Edition. It offers other kind of solutions that Standard Edition doesn't.
ASKER
Based on what read, replication allows for queries..mirroring doesn't.
http://simplesql.blogspot.com/2011/01/replication-vs-mirroring-and-what-to.html?m=1
http://simplesql.blogspot.com/2011/01/replication-vs-mirroring-and-what-to.html?m=1
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok standard with replication it is then...
Hi cobra25
do you have any success or additional questions? We are interested.
Thanks
Pavel
do you have any success or additional questions? We are interested.
Thanks
Pavel
Recommendation to close this question by accepting the above comments as solution.