VIVEKANANDHAN_PERIASAMY
asked on
doubt in log shipping and mirrioring.
If new table is created in primary server,how will affect the secondary server in case of both log shipping and database mirrioring?
in log sipping i know only log data gets shipped to secondary server.
In database mirrioring, log buffer push the logs to secondary.
but if we create a new object in the database,then how it works?
in log sipping i know only log data gets shipped to secondary server.
In database mirrioring, log buffer push the logs to secondary.
but if we create a new object in the database,then how it works?
Barry Cunney
CREATE TABLE is a logged operation and if it is completed on the primary server then the next set of log shipping process should affect the same on the secondary server during the restore
ASKER
so my question is will the new created will taken in log? or do we need to create a table entry in secondary server.
can you brief more on this process?
can you brief more on this process?
The secondary server is updated as part of the automatic handling of the RESTORE of transaction log backups from the primary server.
Because a CREATE TABLE is recorded in the transaction log on the primary server then when RESTORED on secondary server this CREATE TABLE is executed on the secondary server - so the new table gets automatically created on the secondary server
Because a CREATE TABLE is recorded in the transaction log on the primary server then when RESTORED on secondary server this CREATE TABLE is executed on the secondary server - so the new table gets automatically created on the secondary server
ASKER
okay,in case of mirrioring?
ASKER
I also taught the something, but one of colleague told me wrong. so just i raised this question for the confirmation.
Similarly in the case of database mirroring - The Principal server sends batches of log records to the Mirror server as the Principal server is writing these records from the log buffer to disk.
The Mirror server replays these log records which it received from the Principal server.
So if a CREATE TABLE exists in the log on the Principal server this CREATE TABLE will get replayed on the Mirror server
The Mirror server replays these log records which it received from the Principal server.
So if a CREATE TABLE exists in the log on the Principal server this CREATE TABLE will get replayed on the Mirror server
ASKER
okay,can you confirm in case of log shipping?
In the case of log shipping the CREATE TABLE sql is recorded in the transaction log on the primary server and then automatically shipped to the secondary server
Yes BC is correct, there might be some confusion with replication - which usually won't pick up new objects to replicate automatically.
Yes mastoo is correct about replication - in the case of Replication if you create a new table on the Publisher you need to go through the process of adding the new table as a new article and then creating a new snaphot which is then pushed to the Subscriber
ASKER
I spoke with my senior dba, he told me whenever the any new ddl statement is ran ,log shiiping will not pick up is seems. like truncate the data.
so in such case we have to re-create the log shipping.I am not sure for this .. so is there any constrainst on log shipping
so in such case we have to re-create the log shipping.I am not sure for this .. so is there any constrainst on log shipping
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.