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:
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)?
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?