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 |
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
 Posted - 2003-11-19 : 15:44:38
|
| I am about to setup Merge Replication to our ERP database. I am concerned about the restrictions when using Replication on a database, such as the inability to modify the table structures. Who knows what happens in the ERP software where it may need the ability to change certain table structures.1. Is this restriction for the whole database, or only for the tables, views, etc. that are setup in the Publication? Of course I can understand/deal with the latter.2. What other major restrictions am I going to run into after I setup replication? 3. I've attempted to setup a view on my local server to select the data from another linked server like so...SELECT TOP 100 PERCENT ABAN8, ABALPHFROM MYMAINSERVER.JDE_DEVELOPMENT.DEVDTA.F0101 F0101_1WHERE (ABAT1 = 'CS' OR ABAT1 = 'CB') AND (ABMCU <> ' 1000')ORDER BY ABALPHIt runs fine. When I attempt to save it, it gives me the following error...ODBC ERROR: MSDTC on server MYLOCALSERVER is unavailable.Why does it require this and from where would I install the ODBC driver? The regular SQL Server odbc driver is already installed.What I thought I could do is create a database of views that I want to publish for replication in another database on another server, and then setup replication on that database instead of my ERP database. These particular tables will not be updated by the subscriber. They are only used for cross-reference purposes. Am I approaching this the wrong way?Thanks in advance |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-19 : 15:48:54
|
| The restriction is for all of the objects being replicated. You can replicate stored procedures, UDFs, etc... as well as tables. BTW, you can modify tables. You just have to drop and recreate replication. This is really simple because you can script out the drops and creates and then just run the script. It's rather fast.Do you have the MSDTC (I think it's now called Distributed Transaction Coordinator in the services applet) service started?Why merge replication if the subscriber is not going to update anything?Tara |
 |
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2003-11-19 : 16:47:20
|
| 1. Good, so objects that are not being replicated would not be restricted.2. I noted that when I attempt to replicate a View it states that all tables that are referenced by a view be available at the subscriber. Does this mean that I will also need to publish the table that the view is relied on? If so, then that must mean that a View is not sending the data, but just the Select Statement itself correct? If so, then the only reason to publish a View is to update the View Select Statement, correct? I also assume this is the same for SPROC's. Replication does not return the result of sprocs/views, but does update the views themselves.3. MSDTC is a moot point now, but yes that was the problem. 4. Should I be using Snapshot Replication for tables that are not updated by the subscriber as opposed to Merge? Any disadvantage?Thank You! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-19 : 16:49:53
|
| Ummm, views do not contain data. The tables contain the data. Yes you need to publish all tables that the view is based on. Yes you would publish a view so that it receives the view updates. I don't know what you mean by replication does not return the result of sprocs/view but does update the views themselves. When you replicate views, stored procedures, and views, it replicates the object. When you replicate a table, it replicates the object and the data. We use transactional replication for table that the subscriber does not update. Snapshot replication does not provide what we need.Tara |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2003-11-20 : 09:48:15
|
| Thanks, I know that was kind of a stupid question but it's drilled in to my hard head now.Now that I read about the replication types for the umpteenth time, you may be right that I should use transactional replication. These files are basically used for cross-refrence purposes, like Customer Names/Numbers and Employee Names/Numbers, and associated views of both. While they won't change too often, they will change. When would a snapshot replication be practical? The only instance I can think of is for objects that never change, or if the subscriber controls when they want their local server updated. Am I on the right track here?Thanks for all of your advice. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-20 : 12:11:42
|
| I believe that snapshot replication is more for environments that do not need the most current transactions sent over. They can handle being a day late or so.Tara |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2003-11-20 : 13:18:13
|
| Thanks Tara, & thanks for the Bulk Insert advice also. |
|
|
|
|
|
|
|
|
Replication Issues