October 12, 2004 at 11:08 am
Hi ,
Please go through below message and do suggest me the best solution.
Here it goes .......
'**********************************************************
1. We developed an application for a Finance Company which has over 11 branches in various parts of same City.
2. we deployed Vb application in all the branches with SQL server as database.
Application is running fine and Customers are comfortable with the product.
3. Now ...The management wants data from all the branches Updated in Central Office (i.e in Centralised Server).
So that ..They can View the data of each branch sitting in central office.
4. We proposed a solution that ..... Each branch will Send the database file to Central office through internet
and central office will have a Sql server with Each Branch as Individual database and the dataabse files from each branch are Updated in their respective branch databases at central office.
Central office will have leased line Internet connection with Static IP.
Branch will connect to Cental office through Modem or cable line
5. Our Strategy was .........
a. Create each Branch database at central office
b. restore FULL database backup in CO for each branch.
c. take Differential backup at each branch and Upload to central office
4. restore differential back at central office in respective dataabses.
Problem : With Differential database backup ........The size of file is growing and couldn't upload the big size file to central office from branch with Modem connection ( Speed is very less )
So ...what we thought is to Upload only current days Transactions ...
for this we thought of going with Transaction Log backup strategy. .
'***********************************************************************************************************************************************
can u please help me in suggesting best solution and more info on how to handle above issue.
I heard about Log shipping feature in sql server. will that help me
regards
VENU
October 13, 2004 at 12:36 am
If the aim is for the central office to be able to view / report on the branch data, you might want to have a look at SQL Server's Transactional Replication.
There are a number of queueing options with it, that would allow the the branches to update the central server when they can get a connection (modem/adsl). If they are able to be continuously connected, your central server could be "up-to-the minute".
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ra_6u05.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replover_694n.asp
Julian Kuiters
juliankuiters.id.au
October 13, 2004 at 6:31 pm
Sounds like transactional replication would be a good way to go. It's fairly easy to configure, and there are lots of options for scheduling the timing of updates.
Some questions I would look at first would be:
1) Is the database design fairly static? There are limits on schema changes to replicated objects, and restrictions on how you apply changes. Sometimes it's easier to kill the replication, apply schema changes, and recreate replication with a new snapshot. This doesn't sound like the kind of application that allows frequent ad-hoc schema changes, so this may not be an issue.
2) Does the central office server maintain the same uptime as the branch offices? If the replication fails, the transaction logs on the publisher database will continue to grow in spite of log backups. This is not a problem if the system is attended over weekends/holidays, or if you have notifications set up to email or page you. But if there is a problem on a Friday night of a long holiday weekend when you're out of town with no pager/email, and you're short on disk space because the web-hosting outfit charges a fortune, the ever-growing log files (plus the ever-growing log backups on the same volume) eat up the free space pretty quickly. Don't ask me how I know, that's a Tuesday I'd like to forget.
October 15, 2004 at 6:23 am
Hi Julian Kuiters and Scott coleman,
Thank you for the reply. I am going through Books online for transactional replication . This is new subject for me.
My applications database design is static .
and we are planning to use Modem in Branches to connect to internet. ( Internet Speed will be very less i.e 2Kbps)
central office will have a dedicated Internet connectivity with Static IP (Speed is not a problem).
So , with the speed we get using Modem at branches ..can we implement replication strategy for Updating data in central office.
It would be great help if you send me Steps in setting up Transactional replication .(I am using SQL server Enterprise manager 2000)
regards
VENU
October 15, 2004 at 8:45 am
You can configure everything from Enterprise Manager. It's pretty easy once you have some idea what you're doing. Reading more documentation before you begin will help.
Each branch office server will be a publisher. Replication can be managed by another server (the distributor), but in your case I assume each publisher will be its own distributor. If the branches call into the central office to transmit data, you want to create push subscriptions. If the central office calls each branch in turn to get new transactions you need pull subscriptions. I have no idea how you coordinate the distribution with connecting the modem, but I know it can be done. Maybe someone else can chime in here? You do have options to schedule how often updates are sent, I've only done this on networks where the servers are connected constantly so I can use immediate updating.
The data is transmitted by a distribution agent, so the branch office SQL Server Agent service must be running under a Windows login that has rights to login to the central office server and to write to the distribution folders. This is easy if all the servers are in the same domain, more complicated but not impossible otherwise.
Pick a branch office to start with and configure the server as Publisher and Distributor. Add the central office server as a subscriber. It may help if the servers are connected during this process, as I said that's the only way I've done it.
Create your first publication using the database to be replicated. Don't blindly replicate everything. Tables lacking primary keys cannot be replicated. You can limit which rows & columns are replicated, if you want to get fancy. It is possible to have two-way replication, where data changes on the subscriber are copied back to the publisher, but if the subscriber is just used for reporting you don't want to allow this. Think about which views and stored procedures (if any) you need to replicate. Obsolete views & procs (I've got some lazy developers) that reference nonexistent tables or columns will cause the snapshot to fail when applied.
Next you must create the subscription. I'm assuming you're creating push subscriptions. Create an empty database on the subscriber first, then create a subscription on the publisher. To get started, it must create a snapshot (a backup of all replication articles) and apply it to the subscriber. I believe the default behavior is to lock the tables during snapshot creation, which will severely annoy anyone trying to use the system at the time. There is an option somewhere to not lock tables during snapshot creation, it takes a little more time but is better if the database is in use. Setting replication up outside of normal business hours will avoid some of these problems.
Now you have to decide whether you're going to let the distribution agent apply the snapshot over the modem, or if you want to apply it manually. I assume you can write it to CD or tape and carry it back to the main office, but I'm not sure how you manually apply it. (Again, anyone with this experience feel free to jump in here). Once you create a subscription transactions will start to pile up in the log until their replication is confirmed, so you must figure out how to get the snapshot applied in a timely fashion.
Once everything is working, someone needs to keep an eye on all the replication agent processes on the branch servers. Configure notifications to alert someone if anything fails.
October 21, 2004 at 2:47 am
Hi,
I have a query on bakcup and Restore with Log files. (SQL server 2000)
I have created a Full Backup at 5PM.
and Two Transactional backups at Interval one hour
i.e at 6PM and 7PM
I Tried to restore above Files ....
a. Full Backup restore is Done Successfully
( I have Selected Leave database non operational and able to restore tranactional logs using Options TAB)
b. Next ..I have restored Log file of 6PM
( I have Selected Leave database non operational and able to restore tranactional logs using Options TAB)
C. Next ... I have restored Log File of 7 PM .
As this is my last Log File ....I went with default Options .
...After this I made some changes in the Source database data and log file at 8 PM is generated.
d. Now ....when i am trying restore a Log backup of 8 PM ...
Its giving me error saying that Last backup Should be done with No recovery or Stand by option.
..So what i Did is ... I started restoring from Full backup ..then Logs of 6PM , 7 Pm and then 8 PM .... It worked ..
...My question is ......... Can't we restore a Log File to previous restored database.
I wantt to update daily transactions from a Branch database to central Office database daily ...
So ...what i have planned is ..... To take a Full backup and have a Transaction log Daily ... and Upload the Log to central Office
Suggest me a Solution
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply