Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old November 22nd, 2009, 02:04 AM
Authorized User
Join Date: Jan 2009
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Server 2008 need help

Hello everyone
How are you? I have a problem. I have two database in two pc's. One is central database and another is local database. Under the local database every day some transaction have been proceed. At the end of the day i want to shift the local pc's database to central database. How can i do that? Is there any third party software? Is there any middle layer software that done my job? Can any one help me to provide expertise answer or give me some idea? Any idea will be apriciated. Thanks all again
Old November 23rd, 2009, 03:22 AM
Registered User
Join Date: Nov 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default more info

If you want to transfer the new transactions from the local db to the central one you can use replication between the two databases or you can use SSIS to perform the data transfer and get a scheduled sql server job run this operation at the end of the day.
If you want the central database clients to connect to the local database instead of the central database at the end of the day you can get the middle tier (or a web service) do that.

Could you tell me more about your case to give you more details?....
Old November 23rd, 2009, 04:50 AM
Authorized User
Join Date: Jan 2009
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default More Information

Thanks subportal. Its good idea. I have some more info for you. I think it will help you to understand my scenario. I have two pc where SQL server is running. I have also a Central database station. Suppose every day in the local site handled 100 transaction in its own mechine at the end of the day i want to transfer this 100 transaction in central database. It is mention that i dont want to transfer the previous days transaction. I only transfer the current days transaction. How can i do this? Are there any s/w, service or agent who can do it for me? Thanks again. Keep well.
Old November 23rd, 2009, 06:59 AM
Registered User
Join Date: Nov 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Solution: Transaction Log Shipping

The best solution to such a scenario is to use Transaction Log Shipping.
Log shipping works as follows:
•Suppose we have two servers (one is called PrimServer and the other is called SecServer).The SecServer has a database called CentralDB and PrimServer has a database called LocalDB.
•The PrimServer is configured this way:
-A shared folder Called Backup on the D: drive
-A SQL Server job called BackupJob. This job is scheduled to run at 11:45 PM every day to backup the transaction log file of the LocalDB to and save the backup file in the shared folder.
•The SecServer is configured this way:
-A folder called Restore on the E: drive
-A SQL Server job Called CopyJob. This job is scheduled to run every day at 11:50 PM to copy the backup file made by the BackupJob on the PrimServer to the Restore folder
-A SQL Server job called RestoreJob. This job is scheduled to run every day at 12:00 AM to restore the log file copied by the previous job to the CentralDB

That’s it. I will explain how to configure through the following few lines.
•Right click the LocalDB node in the Object Explorer and choose Tasks, and click Ship Transaction Logs…
•Click Enable this as a primary database in a log shipping configuration check box
•Click the Backup Settings… button
•In the text box Network path enter the path of the shared folder (e.g \\PrimServer\Backup)
•Under the Backup job option click the Schedule… button.
•In the up dialog and under the Daily frequency option, beside Occurs at once option specify 11:45 PM (as an example). This is the moment you want the backup job to run
•Click Ok on the opened dialogs back to the Database properties dialog
•On this dialog click Add… button under the Secondary databases option
•On the new dialog click the connect button and connect to the SecServer and then choose the database name CentralDB
•If you have made a previous Full backup to the LocalDB and the CentralDB is already created(restore that full backup to the CentralDB and leave it in the NoRecovery mode.), then choose the option ‘No, the secondary database is initialized’. Otherwise choose the option ‘Yes generate a Full backup of the primary database……’ and in the dialog that appears after clicking the Restore Settings specify the folder of the full back file and then click ok
•Click the Copy Files tab
•In the Destination folder text box enter E:\Restore (as an example for the folder on the SecServer to get the backup files copied in.)
•Under the Copy job option configure the job schedule (Occurs at once option specify 11:50 PM)
•Under the Restore Transaction Log tab and under the Restore Job option schedule the job (Occurs at once option specify 12:00 AM)
•On the Secondary Database Settings dialog click Ok.

Make sure that the SQL Server Agent Service is running on both servers.

Good Luck.
Old December 9th, 2009, 03:17 AM
Authorized User
Join Date: Jan 2009
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default Transaction Log Shiping

At first i am sorry for late response. I was in vacation. Thank you for your cordial reply. After my vacation i try to use transaction log shiping. At first i apply the mechanism for two server where i try to transfer data from one database of primserver to seconserver. It successfully back up the database but in the time of restore the database the following error occurs in the log file

Error: 14421, Severity: 16, State: 1.

The log shipping secondary database GALAXY\GALAXYSQL.DataTransferTo has restore threshold of 45 minutes and is out of sync. No restore was performed for 137 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information

Where is the problem? Can you help me to write more detail please? I just wait for your replay.

Shimul Mahmud

Last edited by Shimul Mahmud; December 10th, 2009 at 07:36 AM..

Similar Threads
Thread Thread Starter Forum Replies Last Post
Access getting data via VBA from SQL Server 2008 jpl458 BOOK: Access 2003 VBA Programmer's Reference 0 September 25th, 2009 01:06 PM
Upgrade to SQL Server 2008 from 2000 srotondo SQL Server 2000 4 September 18th, 2009 01:39 PM
SQL Server 2008 back to 2000 mat41 Classic ASP Basics 4 September 16th, 2009 06:06 PM
Upgrading to SQL Server 2008 from 2000 srotondo SQL Server 2008 0 August 17th, 2009 02:08 PM
SQL Server 2008 XML speedlearner SQL Language 1 March 22nd, 2009 12:32 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.