August 27, 2013 at 2:30 pm
Hi,
My tables do not have primary keys and as I understand it, transactional replication requires primary keys so my question is: Has anybody found a 3rd party solution to implement Replication when your tables do not have primary keys? We have over 3,000 tables and it would not be possible to alter them to add primary keys so I was just wondering is there another way to implement this.
Thanks in advance, 😀
Isabelle
Thanks!
Bea Isabelle
August 27, 2013 at 2:48 pm
Isabelle2378 (8/27/2013)
Hi,My tables do not have primary keys and as I understand it, transactional replication requires primary keys so my question is: Has anybody found a 3rd party solution to implement Replication when your tables do not have primary keys? We have over 3,000 tables and it would not be possible to alter them to add primary keys so I was just wondering is there another way to implement this.
Thanks in advance, 😀
Isabelle
You are saying you have over 3,000 tables in your system and none of them have primary keys??? Why can't you add them? Your performance would get a boost system wide. Without a primary key there is little chance that replication can work. This is how you define how to isolate any given row. Without a primary key you have no way to know which row you have. You should probably do some serious reading on Referential Integrity.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 27, 2013 at 2:55 pm
Hi,
This database was created automatically when they installed a new ERP system before I got here. I agree and was shocked that there are no primary keys in any of the tables but it was developed from an IBM mainframe system and that's the way they did it. My boss does not want me to alter all of the tables to add primary keys becuase the ERP system has alot of the referential integrity built in. So I cannot change this. I currently am using log shipping to create a READ-ONLY standby database but it takes an hour or so every night to update the logs and this happens at 1:30am when no users are on the system. But we now have international users in Germany and Thailand and are having problems with the time the standby db needs to be down during log updates.
I am looking into mirroring using snapshots for a READ-ONLY solution but was just wondering if there was any way to implement replication maybe using a 3rd party solution when no primary keys exist.
Thanks,
Isabelle
Thanks!
Bea Isabelle
August 27, 2013 at 3:26 pm
Snapshot replication is what you're stuck with...I've not heard of 3rd software that would handle it (then again I probably wouldn't have heard about it anyway, they don't let me out much) :w00t:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 27, 2013 at 3:39 pm
Hi,
Well, from what I have read about snapshot replication, it may not work for us. We have an OLTP system that does a lot of frequent updates to a set of tables:
Using snapshot replication by itself is most appropriate when one or more of the following is true:
•Data changes infrequently.
•It is acceptable to have copies of data that are out of date with respect to the Publisher for a period of time.
•Replicating small volumes of data.
•A large volume of changes occurs over a short period of time
So I'm thinking if mirroring and using snapshots for a READ-ONLY database would work but one thing I can't seem to find answers on is if we are using SharePoint integrated Reporting Services for reports, how do we maintain all the different snapshots and redirect the reports each time a new snapshot is taken? SharePoint has the server and database in it's configuration setting but if we are using the snapshots, the name changes each time so does this have to be changed everytime a new snapshot is generated? Or is there a way to resolve the database name regardless of what snapshot it's using?
Thanks!
Bea Isabelle
August 27, 2013 at 3:45 pm
Sean Lange (8/27/2013)
Isabelle2378 (8/27/2013)
Hi,My tables do not have primary keys and as I understand it, transactional replication requires primary keys so my question is: Has anybody found a 3rd party solution to implement Replication when your tables do not have primary keys? We have over 3,000 tables and it would not be possible to alter them to add primary keys so I was just wondering is there another way to implement this.
Thanks in advance, 😀
Isabelle
You are saying you have over 3,000 tables in your system and none of them have primary keys??? Why can't you add them? Your performance would get a boost system wide. Without a primary key there is little chance that replication can work. This is how you define how to isolate any given row. Without a primary key you have no way to know which row you have. You should probably do some serious reading on Referential Integrity.
There wouldn't necessarily be any boost in performance. In theory, they could already have unique indexes and referential integrity fully defined in all existing tables, even with no formal PK declaration.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 27, 2013 at 3:50 pm
Yes, that is correct Scott. The tables have unique clustered indexes that was all added when the ERP system was installed to maintain its referential integrity. I don't know if this is as efficient as primary keys, but this is how it was delivered out of the box.
Thanks!
Bea Isabelle
August 27, 2013 at 3:53 pm
Wait a minute....if you have unique clustered indexes what's the issue with creating the primary keys? It would make your entire issue with replication much easier! Is there no way you could talk the boss into considering this path?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 27, 2013 at 4:13 pm
Isabelle2378 (8/27/2013)
Yes, that is correct Scott. The tables have unique clustered indexes that was all added when the ERP system was installed to maintain its referential integrity. I don't know if this is as efficient as primary keys, but this is how it was delivered out of the box.
Indeed, they are just as efficient, which was my point.
I often don't designate an official "PK", esp. while still testing. PKs can be a pain, since you can't have NULLs. And, at one time PK values were never allowed to be UPDATEd (at least in some RDBMS's). Just a hassle while still testing.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 27, 2013 at 4:40 pm
Hi,
We have a whole set of issues of trying to modify the table structure for this ERP system by adding primary keys so I don't believe that is the way to go. I've spoken to the developers here and that designed the software and it would be a huge undertaking to try and do that. They have all kinds of things built into their software based on the design of these tables and with fixes and upgrades down the road, my boss probably would not be comfortable with making those changes.
Is anyone familiar with database mirroring and using snapshots for READ-ONLY reporting? How do you point the users to each new snapshot, say every hour, if using Reporting Services with SharePoint integration?
Thanks!
Bea Isabelle
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply