More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
How to change the schema of a published table?
In SQL Server 7.0, if you have to change the schema of a replicated table, first you have to unsubscribe to all the publications, which are based on this table. Then, delete all the publications. Do the schema change. Republish the table and resubscribe. You can do NOSYNC, because the data is already there at the subscriber.
You can script the replication in 7.0, so that you don’t have to recreate the publications and subscriptions manually, every time you change the schema. In Enterprise Manager, go to ‘Tools/Replication/Generate Replication Scripts…’ to generate replication scripts.
Note: Pull subscriptions cannot be scripted and can’t be traced in Profiler for security reasons.
The next release of SQL Server is going to support Schema Replication, where you don’t need to do all the above steps if you want to change the schema of a replicated table.
(Continued on next question...)
