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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-11-29 : 09:41:46
|
| Manoj writes "hi thereI m working in a company who has two offices. The live data changes occur in only one branch and in the other branch we need to have the exact replica of this database including structure and we need to have only read-only copy of this database so I tried replication. In replication i have some doubts & problems. these are:1. Because this project is still under development so the basic data structure changes so frequently and same is the case with some Dump tables(those tables which we used to generate the reports from the actual table) also. Now the data of the dump tables need not to be replicated(if i do so then i will be getting nothing but just wasting time, effort and performance because the data in these dump tables are very massive) in the second branch but these tables's structural changes only need to be sent there. how can i achieve this?2. How can i replicate the structure and structural changes(schema changes) of the Stored Procedures, Views & User-Defined Functions?(Remember: only structure and not their execution)3. If i add a column in Publisher in a replicated table with some default value then how can i drop that field?4. How can i increase the size of the column of the replicated field?" |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-11-29 : 16:37:59
|
| Replication as far as publisher/subscriber is data only. If your making schema changes still (which I would wonder why your making changes to schema with something in production... but different point) you are better off to use back-up and restores to move the schema. Back-up boh the Master and the database your using (depending on what your doing you may need to move the MSDB as well) and restore on the other server.MS assumes with replication that you have stable table schemas by then. If it's only a read only copy of the database in this second office... Have you considered using views to view data on the main server from the second one? Then you would just alter the views if the tables schemas alter? (might not be relevent in this situation though....)-----------------------SQL isn't just a hobby, It's an addictionEdited by - M.E. on 11/29/2002 16:38:55 |
 |
|
|
Tim
Starting Member
392 Posts |
Posted - 2002-12-02 : 17:57:21
|
It sounds like you are using SQL7 which doesn't allow major changes to published tables (like dropping/adding columns), and also does a crappy job of replicating objects like triggers, sp's and especially RI contraints.If you are on SQL7 you will have to drop the publication, make your changes and then reinitialise all the subscribers (ewww). I scripted all of the non-table objects and run the script at each subscriber after every sync, just to be sure the objects are created properly and in the right order.However, SQL2000 is much better. It let's you replicate changes to tables. Info is in BOL but there is one main trick: quote:
Important Schema changes to a published table must be made only through the replication publication properties dialog box in SQL Server Enterprise Manager or through replication stored procedures. Do not make schema changes to published tables using the SQL ALTER TABLE statements in a tool such as SQL Query Analyzer or by using SQL Server Enterprise Manager visual database tools. Changes made to the schema of a published table using these tools will not be propagated to Subscribers.
You may consider back/up restore or DTS alternatives if you have good bandwidth, small databases and can live with some latency. Otherwise you should stick with replicating data changes (which in your case would only mean transactional replication)M.E just because you have a database in production doesn't mean you'll never change it. Enhancements always come along. The issue is that SQL7 makes you reinitialise all subscribers which is a logistical nightmare and a drain on resources, just for a simple db change. One technique is to create filler fields in your tables and use them as needed, just to save all that work that the crappy SQL7 replication implementation forces on you for a db change. Either way it is horrible. The move to SQL2000 is really worth it for anyone involved in replication.----Nancy Davolio: Best looking chick at Northwind 1992-2000 |
|
|
|
|
|
|
|
|
Regarding Schema Changes in Replication 