October 30, 2008 at 8:03 am
Hi,
I have sql server 2005 running our production db.
Wanted to separate report users from production db on the same server.
While exploring various possible replication scenarios, decided to go for log shipping
which should satisfy our requirements.
That said, I have test run before deploying it in prod.
I have a test server.
1. Created a test db t1.
2. Created a small table mytable.
3. Did full backup and txn log backup onto a sharable network folder.
4. Restored this db on the same server with a different db with standby option
5. Confirmed that the jobs (backup, copy and restore) was running fine.
6. Initially had issues on the restore side citing not able to find txn logs.
7. Copied the txn logs to the default backup folder and things started moving.
8. Tested replication by inserting a few rows onto the table and noticing
that it reflects in the standby
Questions:
1. In the standby side, my understanding is that when the restore happens, no user
should be logged in. If so, it fails. Also, i read that it will disconnect users.
How can i mitigate this issue?
2. I added a new table onto the primary and inserted rows onto it hoping that the
txn log restore will take care of it on the standby. Did not see that happen.
Do you know why?
3. What is the standard procedure to add/modify/drop tables (meaning dml operations)
in primary in this log shipping scenario? My thoughts were to disable the jobs, do the
ddl and enable the jobs. My doubts are will the tables be created on the standby?
Pls respond asap.
Thanks
Murali
October 30, 2008 at 10:54 am
Muralidharan Venkatraman (10/30/2008)
1. In the standby side, my understanding is that when the restore happens, no usershould be logged in. If so, it fails. Also, i read that it will disconnect users.
How can i mitigate this issue?
Unfortunately you cannot mitigate this issue. Whenever a restore runs it will disconnect users, assuming you've selected the option to disconnect users.
Muralidharan Venkatraman (10/30/2008)
2. I added a new table onto the primary and inserted rows onto it hoping that thetxn log restore will take care of it on the standby. Did not see that happen.
Do you know why?
3. What is the standard procedure to add/modify/drop tables (meaning dml operations)
in primary in this log shipping scenario? My thoughts were to disable the jobs, do the
ddl and enable the jobs. My doubts are will the tables be created on the standby?
You shouldn't need to do anything in this scenario. Adding tables will be captured by log backups and corresponding log restores. Are you sure that the log backup was copied to the secondary server and successfully restored?
October 30, 2008 at 12:36 pm
yes, i did see the jobs complete successful but the table was not shipped to standby
October 30, 2008 at 1:20 pm
Muralidharan Venkatraman (10/30/2008)
yes, i did see the jobs complete successful but the table was not shipped to standby
Just because the jobs completed successfully doesn't necessarily mean that the log backups are being copied or restored to the secondary. You need to check the log ship history, or check the error log on the secondary to make sure the backups are being restored.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply