Best way to mirror SQL database of customers to our SQL database
-
We have installed a SQL 2017 database on a Windows 2016 server at our customers (about 20) worldwide.
We Installed a SQL 2017 server on a Windows 2016 server in our company.
Now we want to mirror/synchronize the customer SQL database to the SQL database in our company. The SQL data is only from customer to our company.
At our company we want to use for every customer an instance, because the names of the databases are the same between customers.
We try to use mirroring, but without success in SQL 2017. Does somebody knows an other solution we can use? Thanks in advanced. -
What was wrong with mirroring that you chose not to use it?
The big question is how you define "synchronize" - how much of the data do you need, how often do you need it, and what's acceptable in terms of delay?
If you're going to discount mirroring entirely then I'll skip AlwaysOn and throw these standard options your way:- Log Shipping
- Replication
- Backup -> FTP -> Restore as needed
-
Below the steps I took to setup mirroring. The Principal is the SQL at a customer and the Mirror is the SQL at out company. Via VPN they communicate together.
I create a full and transaction log backup of the principal database. I restored the full and transaction log backup on mirror with norecovery.
After the successful 'Configure Security' I selected 'Start Mirroring' and this is the result (see image below)
Any idea?
-
So mirroring won't work from 20 different databases into 1 database. If you want to do something like that you're looking at either building your own tool, or using Merge Replication. However, using Merge Replication will require a redesign of your database (presumably) since you need to have GUID columns and unique constraints on them in all the tables. Also worth noting, merge replication hasn't received much love from Microsoft for many years and is probably unlikely to. They're more focused on cloud solutions to deal with problems like this.
Can you have latency on your data or is the hope to have it near real time? If you can have latency, you can pull back over the twenty databases once a day and run a restore. If you need near real time, you have a crazy amount of work cut out for you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Thanks for your reply.
We want to use 1 instance per customer, so for 20 databases of 20 different customers we want to use 20 instances. Is that possible with mirroring?
We can have latency, but not more than about 15-30 minutes. - r.ginus - Wednesday, September 12, 2018 5:50 AM
yeah, you should be able to set that up with mirroring. Although, if you can have separate databases, you might want to also look at Availability Groups as a technology. It might be easier to set up and maintain than mirroring.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - r.ginus - Wednesday, September 12, 2018 2:31 AMBelow the steps I took to setup mirroring. The Principal is the SQL at a customer and the Mirror is the SQL at out company. Via VPN they communicate together.
I create a full and transaction log backup of the principal database. I restored the full and transaction log backup on mirror with norecovery.
After the successful 'Configure Security' I selected 'Start Mirroring' and this is the result (see image below)Any idea?
This could be a bug issue with SSMS GUI
Try this...
On the mirror server
USE MASTER;
GO
ALTER DATABASE QI_IQM_Press1 SET PARTNER = 'TCP://P01IQM001:5022'
GOOn the principal
USE MASTER;
GO
ALTER DATABASE QI_IQM_Press1 SET PARTNER = 'TCP://QINLSVR-IQMSQL:5022'
GO -
Yep, that's an issue with SSMS 20167 I've just tried it. If you use SSMS 2016 it should work.
-
Thanks all.
The query works fine on the mirror, but not on the principal. The following error is displayed:
Msg 1418, Level 16, State 1, Line 3
The server network address "TCP://QINLSVR-IQMSQL:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.
I added the hostname with his IP to the host table. Telnet to QINLSVR-IQMSQL 5022 response.
Are there some other checks I can use? - r.ginus - Wednesday, September 12, 2018 9:44 AM
Check the state of the endpoints
SELECT * FROM sys.database_mirroring_endpointsEnsure that the proper accounts have been granted connect on the endpoints and make sure you've restored the latest log backup to the mirror.
-
Below the result of the SELECT * FROM sys.database_mirroring_endpoints
Principal
Mirror
How can I check if the accounts have the proper rights?
Both local accounts have the same name. -
I should've asked earlier, but did you set up the mirroring with certificates?
-
I did not setup the mirroring with certificates. The connection from the principal to the mirror is via a IPSec VPN tunnel.
Are certificates required to setup mirroring? -
Yes, using certificates did the trick. The Mirror and Principal are synchronizing now. Thanks for supporting me.
This is the first database. I will soon add the second one. Can I use the same certificates or do I have to create a certificate per customer? - r.ginus - Thursday, September 13, 2018 1:55 AM
You can backup the certificates with the private key and use that to CREATE CERTIFICATE <certName> FROM FILE on the new hosts.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply