|
|
You're in the
sql server replication
group:Replication vs Log Shipping vs 3rd Party
sql server replication:
implementation. we would like to create a reporting server to off-load some of the resource intensive reports. Our original thought was Replication, but Axapta does not have primary keys on thier tables so Replication will not work. Then we thought of Log Shipping, but management wants the data to be realtime (Log Ship every minute). I don't know if log shipping is suited for this or if it is even feasible to log ship every minute for a database that is already working hard on transactional data. Does anyone know what our best option is? Is there a third party tool that would be better suited for this functionality? Can Log Shipping
Will,
as you say, log shipping works at the most frequently once per minute. The obligation to have a primary key in replication which you refer to is related to transactional replication. Snapshot and merge have no such restriction. In fact, I would suggest investigating merge for your requirements, which can have a much reduced latency. Regards, Paul Ibison
I completely agree that Log Shipping just is not practical for a real time solution. Also, I don't think that I can use transactional replication because most of the Axapta tables don't have primary keys and adding keys to that many tables is not really an option. Not to mention the possible effect it could have on the performance. So my last thought was merge replication. But in walking through an example I find out that it will automatically generate primary keys for tables that don't have them. It then displays some warning like INSERT statements that don't specify column names will fail. Not good. I think I would have a dozen or so developers hunting me down and management would be providing me with my very own cardboard box. : ) If any of my findings are wrong please let me know. I'm willing to try just about anything. Thanks. *** Sent via Developersdex http://www.developersdex.com ***
I don't advise using log shipping to provide real time replication.
Dumping your tlog every minute, although possible, is not practical as your SQL Server will be continually dumping the log causing a performance impact. You would be better off using transactional or merge replication. As you don't have any primary keys on your tables you should think about either changing your tables into views, and putting pk's on the underlying tables or using merge replication. [quoted text, click to view] "Will Wirtz" <WillCWirtz@Yahoo.com> wrote in message news:223f5e88.0405070826.77836ab9@posting.google.com... > my company is running SQL Server 2000 ENT for its Axapta > implementation. we would like to create a reporting server to > off-load some of the resource intensive reports. Our original thought > was Replication, but Axapta does not have primary keys on thier tables > so Replication will not work. Then we thought of Log Shipping, but > management wants the data to be realtime (Log Ship every minute). I > don't know if log shipping is suited for this or if it is even > feasible to log ship every minute for a database that is already > working hard on transactional data. > > Does anyone know what our best option is? Is there a third party tool > that would be better suited for this functionality? Can Log Shipping > work with the realtime requirement?
the type of insert statement that will fail with the GUID column is an
unqualified insert ie insert into table1 select * from table2 where table 1 does not have an added guid column and table2 does. You can solve this problem by adding a guid column to table1 as well, which sometimes works, or to recreate your tables as views and have the new underlying tables having PK's. Most tables in a relational database by definition should have PK's. If they don't it is highly possible that you will be running into other errors due to the overall poor database design. [quoted text, click to view] "Will Wirtz" <willcwirtz@yahoo.com> wrote in message news:eh$PZHMNEHA.2488@TK2MSFTNGP10.phx.gbl... > > > I completely agree that Log Shipping just is not practical for a real > time solution. Also, I don't think that I can use transactional > replication because most of the Axapta tables don't have primary keys > and adding keys to that many tables is not really an option. Not to > mention the possible effect it could have on the performance. So my > last thought was merge replication. But in walking through an example I > find out that it will automatically generate primary keys for tables > that don't have them. It then displays some warning like INSERT > statements that don't specify column names will fail. Not good. I > think I would have a dozen or so developers hunting me down and > management would be providing me with my very own cardboard box. : ) > > If any of my findings are wrong please let me know. I'm willing to try > just about anything. > > Thanks. > > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it!
Hilary,
The database I'm refering to is an Microsoft Axapta database. It's my companies ERP package and contains hundreds and hundreds of tables. I am told that primary keys were not used in most tables for performance reasons. And because it was a purchased package, I don't know how most of the SQL Insert statements were written and I can't really allow Merge Replication to just starting adding columns. It sounds like I'm running out of options. Do you know of any other software packages that can provide somewhat real-time database replictation to a read-only server? *** Sent via Developersdex http://www.developersdex.com ***
I've worked with a few, the only one I might recommend is Data Mirror, but
it also required PK's for replicating from SQL Server. -- Hilary Cotter Looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html [quoted text, click to view] "Will Wirtz" <willcwirtz@yahoo.com> wrote in message news:%23O8akQXNEHA.1388@TK2MSFTNGP09.phx.gbl... > Hilary, > > The database I'm refering to is an Microsoft Axapta database. It's my > companies ERP package and contains hundreds and hundreds of tables. I > am told that primary keys were not used in most tables for performance > reasons. And because it was a purchased package, I don't know how most > of the SQL Insert statements were written and I can't really allow Merge > Replication to just starting adding columns. It sounds like I'm running > out of options. > > Do you know of any other software packages that can provide somewhat > real-time database replictation to a read-only server? > > > > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it!
Sounds a little like PeopleSoft. All tables have Clustered unique indexes
but no Keys. Could you turn these indexes into Keys? [quoted text, click to view] "Hilary Cotter" <hilaryk@att.net> wrote in message news:OoVXiwgNEHA.3668@TK2MSFTNGP11.phx.gbl... > I've worked with a few, the only one I might recommend is Data Mirror, but > it also required PK's for replicating from SQL Server. > > -- > Hilary Cotter > Looking for a book on SQL Server replication? > http://www.nwsu.com/0974973602.html > > > "Will Wirtz" <willcwirtz@yahoo.com> wrote in message > news:%23O8akQXNEHA.1388@TK2MSFTNGP09.phx.gbl... > > Hilary, > > > > The database I'm refering to is an Microsoft Axapta database. It's my > > companies ERP package and contains hundreds and hundreds of tables. I > > am told that primary keys were not used in most tables for performance > > reasons. And because it was a purchased package, I don't know how most > > of the SQL Insert statements were written and I can't really allow Merge > > Replication to just starting adding columns. It sounds like I'm running > > out of options. > > > > Do you know of any other software packages that can provide somewhat > > real-time database replictation to a read-only server? > > > > > > > > *** Sent via Developersdex http://www.developersdex.com *** > > Don't just participate in USENET...get rewarded for it! > >
unique indexes are just like primary keys, although a unique index will
accept a single null where pk's don't. I have been able to convert unique indexes into pk's. -- Hilary Cotter Looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html [quoted text, click to view] "Dean" <deann@dtn.com> wrote in message news:ejHQLtrNEHA.1952@TK2MSFTNGP11.phx.gbl... > Sounds a little like PeopleSoft. All tables have Clustered unique indexes > but no Keys. Could you turn these indexes into Keys? > > > "Hilary Cotter" <hilaryk@att.net> wrote in message > news:OoVXiwgNEHA.3668@TK2MSFTNGP11.phx.gbl... > > I've worked with a few, the only one I might recommend is Data Mirror, but > > it also required PK's for replicating from SQL Server. > > > > -- > > Hilary Cotter > > Looking for a book on SQL Server replication? > > http://www.nwsu.com/0974973602.html > > > > > > "Will Wirtz" <willcwirtz@yahoo.com> wrote in message > > news:%23O8akQXNEHA.1388@TK2MSFTNGP09.phx.gbl... > > > Hilary, > > > > > > The database I'm refering to is an Microsoft Axapta database. It's my > > > companies ERP package and contains hundreds and hundreds of tables. I > > > am told that primary keys were not used in most tables for performance > > > reasons. And because it was a purchased package, I don't know how most > > > of the SQL Insert statements were written and I can't really allow Merge > > > Replication to just starting adding columns. It sounds like I'm running > > > out of options. > > > > > > Do you know of any other software packages that can provide somewhat > > > real-time database replictation to a read-only server? > > > > > > > > > > > > *** Sent via Developersdex http://www.developersdex.com *** > > > Don't just participate in USENET...get rewarded for it! > > > > > >
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |