July 17, 2018 at 8:06 am
Hello,
Sorry in advance if this is one of those typically asked questions, but I could not find answers to my specific situation when searching for it. Also, I am a total newb to SQL Server and databases.
I have two computers that aren't always online, i.e they must have local instances of SQL server offline for my application to work. However, I periodically connect to the internet and take backup of the database file and log file to Dropbox (so i can re-attach it if the local files got fucked up). The two computers have the exact same database, I just need two access points. One of the computers are stationary and usually connected to the internet so that I can access it to generate reports from my application etc. To keep the two instances synchronized I manually update the database files with SQL instance turned OFF, but this is cumbersome and prone to trouble if I made a mistake and performed a overwrite-operation at the wrong time (losing the newest changes) or if I tried to overwrite when SQL instance is running (this can result in corrupt database? i have read somewhere). Also, my current solution is not able to "merge" changes and I have therefore declared one server computer as Read-Only. The ideal solution would be if I had some sort of script that would merge changes with the available database files in dropbox before starting my application and also merging when exiting the application. Note that, only one of the computers will write to cloud dropbox at a given time.
So, how to manage two local SQL server instances (one database per instance), with cloud backup? Just need some pointers, which SCRIPT actions i could use etc..
Best Regards
July 17, 2018 at 11:59 am
So, if one is read only and the other is not... How high volume is the database with writes? If the volume is relatively low, you could use transactional replication to move data to the read only machine. You'd see the logs build some while the secondary machine was offline, but, assume the volume is not high, it'll recover over time. Same idea with async availability groups (might even be a bit more stable). However, it all depends on low volume on the transactions so that you can easily afford the log file to grow out some.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 18, 2018 at 2:55 am
Well, it would be nice if both could write, the reason for one being read-only was because my current setup makes it hard to allow them both to write... Does SQL server have some kind of "merge" utility where it looks at database files given to it and updates the actual "live" database in the instance?
July 18, 2018 at 6:05 am
It does. It's called merge replication, but it's more than a little bit difficult to implement and maintain. The showstopper for most people is that one of the first requirements is that every table have a GUID column as a unique constraint (the value gets updated and it's used to determine when a value needs to be moved in one direction or the other). It's also going to suffer if there is both a high transaction volume and a lack of connectivity.
Normally I'd suggest going to a cloud hosted database for this situation, but the intermittent connectivity is a severely limiting factor. Availability groups are probably your best bet, but probably not perfect.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply