Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MWI
Starting Member
10 Posts |
 Posted - 2010-12-24 : 00:40:02
|
| Okay I am rather new to this MS SQL Administration stuff...Ive worked with databases but never in depth.So basically here is the scenarioWe have DataBase 1 on a LAN (LAN1) which is serving data to all the computers on that LANWe have DataBase 2 on a LAN (LAN2) which can also serve data to all the computer on LAN1These two LANs are connected via a VPN software (Hamachi), so you basically have two LANs combined to create one WAN.The idea is to detect if DataBase 1 goes down, and if it does, to immediately re-direct to DataBase 2. This is very important to provide uninterrupted service to a medical facility.My Question is how do I get a to the second exact copy of DataBase 1 to DataBase 2?In other words how to I copy EVERY transaction to DataBase 2, so that if DataBase 1 goes down DataBase 2 will provide seemless service?Then How do I get DataBase 2 to BackUp all the info to a connected BackUp drive every night?Thanks much! |
|
|
MWI
Starting Member
10 Posts |
Posted - 2010-12-24 : 00:42:40
|
| Also to note the version of both servers is SQL 2005 Express |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-12-24 : 10:56:18
|
| With SQL Express, you're going to have to write your own custom export/import routines.Express doesn't support Mirroring, Log Shipping or Replication (as publisher).Another question is the speed of your connection over the WAN. If you have a 100Mb dedicated tunnel, then you have lots of options. Of course since you're using Express, it is clear that the data footprint is small, so moving it over the wire shouldn't be a big issue.If high availability is an important issue, you probably need to look at upgrading at least to Standard Edition.One option you do have is to implement your own log shipping -- take frequent log backups, ship 'em over the wire and restore them at the remote location. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-24 : 11:27:02
|
| O/P says This is very important to provide uninterrupted servicesecond exact copy of DataBase 1 to DataBase 2copy EVERY transaction to DataBase 2which sounds a lot like "5 x 9's" to me ... which isn't going to happen with freebies like SQL Express ... we are taking lots and LOTS of dollars ...although this bit:DataBase 2 to BackUp all the info to a connected BackUp drive every nightsounds like a once-a-day replication might do and, as you ahve said Rusell, given that its SQL Express it won't be bigger than 4GB so the whole lot could be copied relatively easily.1)( Fail-over server with some delay (minutes to tens-of minutes) and data loss (up to the last 5 - 15 minutes of transactions, say) can be cheaply provided2) Zero data loss, fail-over in seconds, fail-over machines geographically separated [rather than, say, two machines side-by-side connected to the same SAN disks] comes at significant cost. |
|
|
|
MWI
Starting Member
10 Posts |
Posted - 2010-12-24 : 13:01:10
|
| I have all the software and hardware in place, my question was simply how to mirror over the VPN in express.It has however been stated that express doesnt support mirroring, so then where might I look on info to write a custom script to mirror the database? |
|
|
|
MWI
Starting Member
10 Posts |
Posted - 2010-12-24 : 13:08:33
|
| from what I've read the standard version of 2005 is quite expensive, and from what I've skimmed over it doesn't seem to be able to give me a definite price or place to purchase.I am not really sure where to go from here.That is other than try my hand at writing my own code to preform the tasks I wish to preform.This task which at first seemed simple is turning into a grueling task...And as my deadline grows closer my optimism grows less.I dont even understand how sql scripts and triggers work, how to add them, how to make them execute, or anything of the sort.... |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-12-24 : 13:25:52
|
| Then you might need to hire a consultant at this point.Writing backups scripts, copying the file, restoring it is not too difficult. But if you can't even write SQL scripts, then you probably need help from someone with more experience. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-12-24 : 13:32:05
|
| By the way, if the local network hosting your SQL Server becomes unavailable, how will your clients connect to the remote one?You have this part handled already? |
|
|
|
MWI
Starting Member
10 Posts |
Posted - 2010-12-24 : 13:52:55
|
| I've already written a script to handle this occurrence using UDP to detect if the server goes offline and re-direct the application.I can code, I simply have no idea how SQL scripting works, if I had a good tutorial Im sure I could learn easily. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-12-24 : 14:10:18
|
| If you're only trying to protect from the server going offline, then there is no reason to have them geographically dispersed. Clustering should have been your choice here.Anyway, take a look at backup and restore in BOL.You can take log backups every few minutes, copy the backup file over, then restore it with NORECOVERY.When you fail over, recover the database at the remote location.This will give you a very limited outage window should the server crash.http://msdn.microsoft.com/en-us/library/ms186865.aspxhttp://msdn.microsoft.com/en-us/library/ms186858.aspxRead these, try your hand, let us know where you get stuck. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-24 : 14:14:38
|
| "I have all the software and hardware in place"With all due respect, from the nature of your posts I am sceptical on that point. Folk here will be happy to help ... but it does seem implausible that you would be using SQL Express to do this, or that you would be mentioning ANY concern about cost i.e. "from what I've skimmed over it doesn't seem to be able to give me a definite price or place to purchase" - I'm not meaning that your budget should be infinite, or that you should be profligate with the money, and I am sure that YOUR boss would be concerned about cost, but a database that is mirrored over remote locations to be immune to any sort of geographic interference, and will fail with 100% transactional integrity WILL cost a lot of money and won't be built with tools such as SQL Express.For example, we provide our clients with duplicate machines which are side-by-side sharing a single SAN for shared fail-over storage. The two machines normally run the web server on one machine and the database on the other, but are capable - if one machine fails - of running both tasks on a single machine; in order to achieve this the two machines are virtualised. This is FAR from ideal as the single-machine solution would be LIMPING along if failure happened at a time of heavy load, and it provides NO geographic Disaster Recovery security. So this is about the cheapest you can get, plus we are only using SQL Server Standard licenses as there is none of the complexity of log shipping to a remote location, nor clustering, nor witness servers etc. which would most probably require the significantly more expensive Enterprise licenses; even so we are into $100,000 to $200,000 for the hardware and SQL licenses and even then it is only for a scheduled failover (e.g. hardware upgrade) that we have instant switchover, whereas for an unscheduled failover we may have 30-60 seconds downtime before the machines detect the failure and sort themselves out. We do have transactional integrity in an unscheduled failover, but we will also have some annoyed customers ... this is by design and deemed to be acceptable for our clients for the once a year, or hopefully less, that it is likely to happen - coupled with the fact that it will be really bad luck if this happens at a time of high server activity.So ... IMHO a geographically-remote instant-no-transactional-loss system is going to cost a lot - plus the software is going to have to be designed for fault-tolerant 2-phase-commit, which probably adds an order of magnitude to the software development cost.OR ... your database will be read-only and a snapshot of yesterdays-data-at-midnight is all that users need, in which case provision is much MUCH easier to provide in a redundant manner, and will be a lot cheaper as, in essence, you just need to copy the database once-a-day.Fire away with any questions you have, in particular if I have misunderstood that you are looking for an OLTP system with "instant" switchover on failure and with zero transactional loss and geographic separation of the servers [i.e. for disaster recovery security] |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-24 : 14:33:05
|
| "This will give you a very limited outage window should the server crash."Forgive me Russell, but I think you are over simplifying.Lets take an Order Processing application (as that is my familiarity)You are taking orders at a reasonable rate - lets say 1/second. You are taking Log backups every minute (which lets face it is very frequent for a Log Shipping solution). Those log backups are being "shipped" to a stand-by server.Therefore your worst case scenario is 1 minute's data loss.Lets say that you are also passing orders to some 3rd part system for fulfilment.You get a failure. The system detects that failure has happened and sets the stand-by server live. The stand-by server had all logs restored to within one minute. So there is 1 minute's worth of data "lost".Some/all/many of these orders have already been passed to the 3rd party system for fulfilment.The stand-by server takes over, but its next-order-ID is already 1 minute behind, so 60 new orders are allocated with the same number that has already been allocated.Customer have received confirmation EMails for those missing 60 orders, new (different) orders / customers are recreated for those same order numbers. Those that are sent to the 3rd party system are in duplicate of the data already received from the failed system.All this can be programmed again - on fail-over you can add 60 (or 1,000 to be on the safe side) to the order number to prevent duplicates.The call centre can deal with customers phoning up asking what happened to their ordersand so on ...but ... that all has to be built in, tested, and so on. Which costs a bit more than just building an Order Processing system.I also think detection of failure on the primary system, across a WAN, takes a while. The bane of my life is building software that will QUICKLY determine that a remote system has failed. It often takes 30 - 60 seconds to determine that a system has failed (meantime we have a HUGE queue of impatient people pressing RETRY and all sorts .... which further stresses the system ...)Even when you detect that the primary server is dead you still have a failed transaction, and reapplying the transaction to the standby server is not necessarily a question fo just running it again ... the user's Basket (or part of it) is lost - everything they added in the last 60 seconds is not on the secondary server, perhaps even their registration is missing ...and so on ...... on solution is to COMMIT each transaction to both servers. If one server fails it might take you a while to detect the fact, but you can then just carry on using the connection to the second server only. But this requires the whole application to be built using 2-phase commit and built to be fault tolerant at any point in the transactions.In summary: can be done, but is NOT easy nor Plug&Play nor cheap nor is it for the faint-hearted nor for people who don't have experience of the issues (or at least some help from someone who does have experience) |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-12-24 : 16:28:28
|
I did oversimplify it, but I did so for two reasons: One, the OPs expertise level doesn't allow for much else and two, it is a simple setup that can give him/her a potential outage of not much more than 5 or 10 minutes.I agree with all of your points Kristen, and as I stated above, the OP really needs to bring in an expert. There is little reason to suspect that someone who "dont even understand how sql scripts and triggers work, how to add them, how to make them execute, or anything of the sort..." can pull this off.Another imprtant point, I think, is that they intend to geographically disperse the database for HA/DR but doing nothing in the event the entire local network becomes unavailable. In this situation, the failover machine(s) might as well be in the rack right next to the production one(s).By the way, my experience is mostly OLTP systems too, and I did setup a DR site two thousand miles from the main data center a few years ago, and handled all the synchronization. Current project is similar, but doing SAN level replication. This is a fun one  |
|
|
|
MWI
Starting Member
10 Posts |
Posted - 2010-12-24 : 19:36:16
|
| I do not have a "boss" I am more a "Freelancer" if you would put it that way.I service several medical facilities with software and hardware solutions.To be honest there is about 4 transactions per minute at most with most of these facilities(as for now I am working with small facilities)This may better explain the geographical location, and lack of a "dynamic" budget. I have a server running here on my LAN which has an instance for each of my clients, running on a SSD.So basically this is a single failover server for multiple networks.I suppose that moving a transaction log over every 1 minute then applying that log to the database would work.I will take a look at the links shared later on, however if not already stated in the links, how do I automatically ship over the transactions every minute, then when they are shipped over automatically apply them to the proper instance on the failover server.I thank you all for all your help. |
|
|
|
|
|
|
|
|
Mirror Databases Across VPN