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 |
|
lucifersmiles
Starting Member
2 Posts |
 Posted - 2008-05-29 : 10:26:06
|
| hi all, For data ware house project, the reporting team needs to know the delta changes to the master database.one way we were thinking was to use log shipping and run reports / ETL off the secondary server. But the team needs to know which records got changed and i was thinking of adding timestamp columns to the necessary tables (only on the secondary database schema) and that way we can track the changes.But from my research, it seems like secondary database needs to have similar schema as promary database.Is log shipping, can my secondary db have a bit different schema? if so how to do it?If not, how to accomplish the above secanrio, with out adding new columns (if possible) in the master database and with low over head.thankslucy |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-29 : 13:14:52
|
| Log shipping uses transaction log backups to sync the secondary database. You can not make any changes to the secondary database. Use triggers to accomplish your auditing.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
 |
|
|
lucifersmiles
Starting Member
2 Posts |
Posted - 2008-05-29 : 16:43:12
|
| thanks tara. just so that i am understanding correctly, the following scenario should work.1. master database which is set up with log shipping with a secondary secondary server (no change in schema).2. on secondary server,set up triggers that will monitor the changes to specific tables and spool off the delta changes either to another table or database.thankslucy |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-29 : 16:46:09
|
| No. You can not modify the secondary database. With log shipping, the secondary database can be in only two modes: unavailable (norecovery) or read-only (standby). The secondary database can not be modified in any way. Log shipping uses transaction log backups.You will need to add the triggers to the primary database.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
|
|
|
Log Shipping and secondary server