Replication and DR Site

[niall5098]

HI All,

I have a large database (130GB) that had logshipping enabled to a dr site.

There is due to be a release which will add new tables, add columns to existing tables and the sam,e for stored procedures etc.

My question is, will the changes need to applied separately on the dr site? as in bring the dr online, apply release then re-enable log shipping?

Thanks in adavance.

 

[mrdenny]

The columns will be added automatically. The new tables will need to be added to the replication which will cause a new snapshot to be taken and sent to the DR site.

This is one of the reasons that Replication isn't recommended for DR.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[niall5098]

whats the best way of creating the tables on the dr site? dr is currently in read only/standby mode. my plan was to

* disable log shipping
* backup both live and dr (i will bring dr db online)
* have another job creating tlog backups on live
* have changes applied on live
* verify changes on live
* if successful apply changes to dr db
* backup dr db again
* restore that backup on the dr site
* apply logs from live
* re-enable log shipping

something along them lines anyway

 

[mrdenny]

Sorry, for some reason I red your posts as replication, not log shipping.

If you bring the DR database into read/write mode you won't be able to apply any more log files to it.

Stop the log shipping apply your changes. Once you have verified that everything is working correctly restart the log shipping. All the schema changes will be rolled to he DR site automatically.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[niall5098]

I have set up a test database with 1 table, i set up log shipping then added a column on the live side, waited for a while but still no sign of the column on the dr side. Am i missing a step?

 

[mrdenny]

Are the logs being backed up and applied to the remote site?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[niall5098]

all logs are being shipped and applied, all jobs are succeeding and i have queried for last applied logs and there are up to date

 

[mrdenny]

Try adding a row to the table, and see if it makes it way across.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog