July 28, 2006 at 4:00 am
Dear friends,
I'm making a database design for Fuel Stations. i need insights from the experts.
There are some scenarios to implement :
1. CENTRALISED
using One SQL Server in one head office, and client pc(s) in every fuel stations to record fuel transaction...and every fuel station is connected by leased-line(dedicated line) to the head office. i can not recommend TCP/IP internet since the connection is lousy in indonesia. so every pc will insert, update, and delete directly to SQL Server in head office. quite heavy.
backup and disaster recovery methods :
1.1. using replication to another server in a location behind head office, just in case something happens in head office or natural disaster like tsunami and earthquake strikes. since it's heavily transaction based, i think of transactional replication.
1.2. beside replication i intend to use backup and restore, i think of complete backup every 2 weeks. and differential backup everyday...actually i'm interested in transaction log backup, but i'm not clear what transaction log backup is and the difference with differential backup ...
2. DECENTRALIZED
using 1 SQL Server in head office, and client pc(s) in every fuel stations to record fuel transaction...every fuel station has its own SQL Server to speed up transactions and is connected by leased-line(dedicated line) to the SQL Server in head office. so every pc in that fuel station will insert, update, and delete to SQL Server in that fuel station also.
then every midnight we'll do replication like 1.1. and backup like 1.2.
so dear friends all over the world, pls give advices based on your experience and smart brains...to form a best design...
Thank you,
hendy
July 28, 2006 at 7:05 am
Hi Hendy,
Both models you have described are centralized models. The first one is a connected and the second one is a disconnected model.
I favour the second model, perhaps with some variation to it. This obviously depends on various factors so I'll present the pros and cons, which will hopefully help you.
The first option relies very heavily on that leased line. Because each client pc writes to the head office, if that leased line is down, the client won't be able to process their own transactions, which doesn't sound good.
The second option is not as reliant on the leased line and if it is down each client can still process their own transactions and upload the data to the head office when the leased line is available again. Of course, this does mean that you'll need to support a SQL Server at each client, and factor in the cost of SQL Server licensing.
With the second option you can control when you want the replication to take place. You can do it at night or you can do it continuously, using merge or transactional replication (or something in between). It depends on how many clients you have and how many transactions you're going to replicate. If you have hundreds of clients you might want to think about the impact it will have on the head office server. If you start replicating all clients at once, during the night you might consider doing it at various times during the day (or continuously).
Regarding backups. How big is the database? Normally you'd want to run a full database backup every night if you can. If the database is so big that you cannot fit a full backup once a day then sure, introduce differential backups. A differential backup backs up changes since the most recent full backup.
Log backups will be essential (unless you don't care about recovering lost transactions). If your last full or diff backup was at midnight and you have a failure at 10am then you can only recover data up to midnight. However, if you take log backups every 5 minutes then you can recover your data up to 9:55am or possibly 10am (assuming you got that backup before the failure).
Regarding your disaster recovery. Assuming you're using Enterprise Edition I would consider using Log shipping rather than replication. You're already backing up the log so it's easy enough to ship those logs to a standby server. It's much much easier to set up than replication and it requires less management and generally less things can go wrong with it.
Assuming that the head office server is used purely for reporting (i.e. no other transactions run on it other than the transactions being replicated to it) the frequency with which you run log backups on the head office database is dependent on the frequency with which you replicate. There's no point backing up every 5 minutes if you only replicate once a night.
Replication is a big topic and depends on a variety of different factors so I cannot be any more specific but I hope this helps you get started.
July 28, 2006 at 9:54 am
The solution is dictated by the amount of data you can lose .. what data exactly are you planning to store and how much can you lose?
There's little point covering the backend so thoroughly if the loss of client data would be a problem. So you'd probably want to get the client data to your head office as quickly as possible, but you must also cover loss of connectivity - your system must cope should the leased line from astation dies - you would probably want at minimum a dial up - possibly to a different site so your client must be capable of storing and sending - so this means some basic redundancy at your clients too.
Head office DR would be best served with 3 centre storage - you could mirror to a close location and log ship to a site further away.
HA is a very complex subject and is constrained usually by how much you are willing to spend vs how much any data loss would cost you. e.g. if you lose your leased line can your station still sell petrol, for example
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 28, 2006 at 1:06 pm
Differential backups are backups of every page in the database that has changed since the last full backup. If you do a full backup on Sunday and a nightly differential the rest of the week, the Thursday backup will include all pages that have changed since Sunday (and will include everything written on Monday, Tuesday, and Wednesday night's backups). If your database died on Friday, to restore it you would start by restoring the Sunday night full backup and then the Thursday night differential, the other differential backups would just be ignored. Your only choices (without log backups) are to restore to the specific times the backups were written.
Transaction logs record every action in the database and the time it occurred. They include every action since the last log backup, so they are smaller than differentials, but you have to keep them all and restore each one (in order). If you backup transaction logs every 5 minutes you will have 288 files per day to keep track of. They give you the luxury of restoring to any point in time (assuming all files back to the last full backup are available). In the previous example, a restore from a Friday morning disaster would need the Sunday full backup, the Thursday differential, and every log backup from Thursday night up to the disaster on Friday.
Transaction log backups are required for either replication or log shipping. You will quickly become an expert on transaction logs if you start doing replication or log shipping. Differential backups are an option, but they are a separate issue.
If you do replication over unreliable links, you need to have room for log files to get huge. Normally a transaction log backup frees up the space in the log files for reuse. If a database is replicated, and the replication transfer agent dies because a connection is broken, the transaction logs will fill and keep growing until they reach max size or the disk is full. Either way the database is dead until you fix it. While the logs are growing out of control, each log backup is bigger so you may run out of space for the backups. High-volume replication over unreliable links is not a good plan.
Log shipping lets the source database run normally, the transaction log records are released with each t-log backkup. The transaction logs must be saved at least until they have been copied to the main office. If the connection to the main office dies the remote system can keep running as long as it has room to store the log backup files. If you decide you need to plan for a worst case of several days of log backup storage, at least they are normal log backups and not the exponentially growing ones you would have with replication.
Log shipping can be done with standard SQL 2000 if you are willing to write your own agents & procedures. All it requires is learning how to use the RESTORE LOG WITH NORECOVERY or RESTORE LOG WITH STANDBY=file commands. The Enterprise edition provides all the support for log shipping, such as scheduling the transfers, re-trying failed transfers, etc. If you want robust log shipping for a critical function you should spend the money on Enterprise edition.
July 30, 2006 at 8:49 pm
Dear all,
Thank you for your kind suggestions and advices.
Nowadays, server has harddisk mirroring function but since it's SCSI so it still has lower capacity than IDE harddisk (PATA)...
in my project, there's also taxis who have debt for the gas converter ...
so these taxis (around 500.000 taxis) pay extra for the gas to cover the overall debt...
so if the server harddisk is overloaded while these taxis still have remaining debt to pay, we have to buy new Harddisk...
if we do query, we have to do it twice, first from old harddisk and second from new harddisk until the debt is paid completely...
it's quite overwork with only 1 SQL Server Enterprise and we have to query old data too. we have to detach new database (*.MDF and *.LDF) and attach old database to query data and print report...and detach old database and attach new database....any better solution....?
Thank you,
hendy
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply