7

First of all, I am sorry if this is a duplicate. I was unable to find anyone who adressed this particular question, or I might have failed to realize it as I read it. Most likely I used the wrong search-phrases, as I am not all that familiar with the terminology.

We have 3 servers:

A. SQL-server

B. Alarm system

C. Website + API + Other

Server B and C has to connect to server A all the time, and there have been several instances of downtime which has resulted in a lot of customer dissatisfaction.

We want to have the following: (different letters to make commenting easier, though it may not :p )

W. Alarm system (with SQL-server)

X. Website + API (with SQL-server)

Y. Website + API (with SQL-server)

Z. Other (with SQL-server)

EDIT: X and Y are technically identical, but X would normally host the website, and our apps and 3rd party solutions would normally connect to the API on server Y, to ensure that a hickup is only felt through one channel. If X were to crash, then Y would serve both the website and the API until X was back online and vise versa.

It is vital that the SQL-servers are synchronized. If a client makes a change in the database through the website (server X) and then activates the Alarm system W (to which the change matters) then it must have allready been synchronized.

Each system is using only some of the databases and tables, to save on resources we could sync only the vital data instantly, and the rest every few hours or so. Edit: not an option

We will be using SQL Server 2014 on all servers (unless someone convinces me otherwise)

  • Do we need any 3rd party software to accomplish this?
  • We are obviously keen on using the most cost-efficient solution, both money-wise and resource-wise, but customer dissatisfaction must end at all cost, we have never lost a client and we don't intend to lose one for having been cheap.
  • Would we have to use triggers, or should we, for the vital tables?
  • Could we use views?
    • That is to say; create the same views on all SQL-servers which loads from whatever server has the latest timestamp for that perticular table, in such-and-such a way that it will have instant sync. A real sync would still be necessary, but less frequent.
    • Not sure how this would work, but I wouldn't be surprised if someone have done this at some point, and found a solution to all the obvious and less obvious problems.
  • We have several functions, procedures, and jobs that are changed every now and then, can these be synced, or do I have to update them manually (which is not a big deal)?

UPDATE:

We've been in contact with a company that has helped us in the past with setting up servers, and getting the correct versions and licenses. Their suggestion was the following:

  • Give each SQL server a copy of the tables, to which they will read and write data.
  • Have replicated all tables from the other servers on each server; from which it can only read.

This way, each server can work independently, and have access to any changes made on the other servers.

I am not quite sure how to detect deletion of rows, other than to run through and see what's missing, unless TRIGGERS work post-synchroniously in replicated tables?

EDIT: Another concern of mine is what would happen something is inserted in the same table, on different servers, while the replication is down due to network problems or whatever. Then we'd have 2 rows with the same ID, and we'd have no reason to assume the latter is not an edit of the former (other than the fact that the ID is in only 2 of 4 severs, but it could well be in all 4 if the network is down for a long time)

These problems could all be solved ny adding more colums for creation date, last edit date, insert-id, and global-id. But then we'd have to change so much on our website and in other systems as well, that we'd prefer to avoid it.

  • Any other obstacles we might face with this solution?
  • Any better ideas?
Improve this question
11
  • Are all the 4 servers located in the same data center ? How frequent is the schema changes on the main database and how big are the databases ?
    – Kin Shah
    Commented Oct 7, 2015 at 15:04
  • Is there a reason these are not simply on the same SQL server? Seems like a lot of division within the data layer that is causing issues instead of solving them.
    – Dave
    Commented Oct 7, 2015 at 15:19
  • 1
    Why not use a single, highly-available, SQL Server database, configured with perhaps an Availability Group, or at least a mirroring partner. This will give you very low downtime (on the order of 10 to 20 seconds) in the event the SQL Server goes down for any reason, and all data will automatically and continuously "synchronized".
    – Hannah Vernon ♦
    Commented Oct 7, 2015 at 15:21
  • @kin They are not located in the same center. The databases are 500mb, 200mb, and a few more with less than 50mb. I am not sure what you mean about frequenzy of schema changes, but we add some columns and tables every know and then for further development of the services, probably something every 3-4 months.
    – Levi
    Commented Oct 8, 2015 at 11:32
  • @Dave It is very vital that these systems are never down, ever. So if the website is down, people should still be able to use it throught the API (with an app on their smartphone), and so on. The alarm server is the most important, and having a remote SQL-server has caused alot of problems. So, the alarm system needs a local SQL-server, and if that is the only SQL-server then nothing would work if it crashes.
    – Levi
    Commented Oct 8, 2015 at 11:42

2 Answers 2

Reset to default
3
+25

I may be understanding this slightly wrong so I apologise if I am.

If you have two databases that you need to be identical and are on 2014 then use the AlwaysOn High Availability Group.

Since you're data centres are at separate locations use the Async mode

This will mean the database is kept completely up to date (all be it possibly with a few second delay) and you can have the secondary node as a read-only replica, this means that your alarm system can read into that database run all the checks etc you would normally.

the Always on system keeps everything up to date, so if the connection drops, when it comes back online it will merge over all changes

It also means that if your main centre goes down you can set it to automatically failover to the secondary, when the main datacentre comes back online it will re-sync with the (now) primary node, at which point you can fail it back over to your main centre.

You can run this on multiple databases, so we have our main DB and our Admin DB synced across our nodes, however what runs all the jobs and direct actions on each side is not replicated so can stay independent of each other

Improve this answer
9
  • It is very important to us, that the sync-prosess is not relying upon a single server, so that if the main server goes down, then the other will work independently, but will not sync with eachother until the main is back online. Does either of your suggestions allow the servers to sync nomatter if the main is down?
    – Levi
    Commented Oct 20, 2015 at 11:26
  • If you have multiple secondary nodes, whichever takes the role as the primary while the main node is down will replicate everything it does to all other secondary nodes, then when your main node returns that will be a secondary node and sync back up to the most recent state, at which point you can manually fail back or set up a script to automatically fail back when the main node is detected as on-line but not the primary
    – Ste Bov
    Commented Oct 20, 2015 at 12:03
  • Sounds great! Would the role as primary be passed on to a third node if the secondary were to fail? Systems does not have to use the primary, right - we could build some less critical systems to use the local SQL Server?
    – Levi
    Commented Oct 20, 2015 at 12:25
  • This is what you're talking about: https://msdn.microsoft.com/en-us/library/ff877931.aspx right?
    – Levi
    Commented Oct 20, 2015 at 12:37
  • 1
    When the primary node goes down one of the secondary nodes becomes the primary, so you can then write to that, (see msdn.microsoft.com/en-us/library/hh270280.aspx Quorum for what goes on here) you're generally never without a primary database unless one has just gone down and you're waiting for another to take over, generally not a long process once the drop-off has been detected)
    – Ste Bov
    Commented Oct 20, 2015 at 14:32
2

It appears you can use Merge Replication between servers and on objects you need, you can make the replication synchronize every 1 minute, so every change will be delivered in less than a minute. (not real-time, but it's very efficient) it can sync objects other than tables, if you don't have a very complicated database design, it doesn't need much maintenance. you can have updates even on the same record simultaneously in different databases, Merge Replication it has a conflict resolution mechanism, that will resolve any conflicts, if possible in your business based on the priority you define. since changes are gathered and then synced , even if a server goes down, other servers will work without a problem and capture changes, and when the server is back, everything will be synced.

Improve this answer
4
  • Could it sync more often than every minute? Does this method require alot of prosessing power?
    – Levi
    Commented Oct 20, 2015 at 10:22
  • if you use it's continuous schedule it syncs every minute, but you can use "Scheduled" method, so technically you can set scheduling in seconds, but the effective time distance possible is based on the amount of changes you have in databases and the connection bandwidth you have.I don't think processing overhead is big deal in this solution, I used this for databases in more than 500 GB and about 1 GB change, and the processing power wasn't a problem. but you can use another server as distributor to move the overhead, if you like.
    – Sina Hassanpour
    Commented Oct 20, 2015 at 13:17
  • But if the main server goes down, then the others will not sync with eachother until the main server is back online?
    – Levi
    Commented Oct 20, 2015 at 13:44
  • It's true. Publisher is the heart of this infrastructure and should taken care of more carefully.
    – Sina Hassanpour
    Commented Oct 20, 2015 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.