Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 22nd, 2009, 01: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
Reply With Quote
  #2 (permalink)  
Old November 23rd, 2009, 02: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?....
Reply With Quote
  #3 (permalink)  
Old November 23rd, 2009, 03: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.
Reply With Quote
  #4 (permalink)  
Old November 23rd, 2009, 05: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.
Reply With Quote
  #5 (permalink)  
Old December 9th, 2009, 02:17 AM
Authorized User
 
Join Date: Jan 2009
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default Transaction Log Shiping

Subportal
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.

Regrads
Shimul Mahmud

Last edited by Shimul Mahmud; December 10th, 2009 at 06:36 AM.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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



All times are GMT -4. The time now is 05:44 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.