Re: SQL Replication Models
On Mon, Feb 16, 2009 at 1:16 AM, C. Hatton Humphrey wrote: The main thing I'm worried about is any performance hit on the publishing server. The target server can lag behind data-wise by a bit (30 minutes to an hour is an acceptable delay). Those do not appear to be very challenging requirements. You could probably even do this without any of the built-in replication at all but with just incremental backups. How have you currently configured your backups? Do you notice a performance impact when you run a full backup? Do you notice a performance impact when you run a transaction log backup? The problem is the size. For performance size doesn't matter all that much. What matters is update volume. What is the transaction log volume per hour? How long does it take to apply (replay) that transaction log volume currently? Johem -- Jochem van Dieten http://jochem.vandieten.net/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319367 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Replication Models
Don wrote: With regard to 1,498 tables, do you mean, User Tables? just for clarity. Yes, 1498 user tables. The application was originally built on a flat-file database system like DB3/4 or Foxpro and was ported to SQL Server without re-engineering the database. All of the tables have 8 character names and the column names are a max of 8 characters as well. The tables are not named intuitively, such as UPR00100 = Employee Main table, UPR00101 = Employee addresses... and so on. Trouble is that I don't know how often these tables are touched. I do know that the process of printing a check involves at least 20 tables. The application is a complete accounting package that has been modified to include benefit, deduction and tax compliance functionality. According to the Sysobjects view there are also 20,644 stored procedures and 166 views. Hatton ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319369 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Replication Models
Those do not appear to be very challenging requirements. You could probably even do this without any of the built-in replication at all but with just incremental backups. How have you currently configured your backups? Do you notice a performance impact when you run a full backup? Do you notice a performance impact when you run a transaction log backup? The current schedule for backups is a full backup at 3am for the group of databases and then a transactional backup every hour from 7am to 10pm during the week. As far as the performance of these, I'll have to figure that out. For performance size doesn't matter all that much. What matters is update volume. What is the transaction log volume per hour? How long does it take to apply (replay) that transaction log volume currently? Hmm... Those are questions I don't know how to answer. However, if I'm understanding where your question process is going, you think having a process that would copy and apply the transaction logs on the SQL 2005 server might be a safer idea? That's an interesting idea! How would I accurately monitor those metrics? As I said before, there are a maximum of 18 simultaneous users based on licenses, then there are reports that can run on the data at any time. The office is generally open for a 9-10 hour period each day, not including weekends. I *could* see if it would be possible to have as much as a 12 hour lag between the systems but I doubt that would fly. Thanks for the line of thought! Hatton ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319370 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Replication Models
On Mon, Feb 16, 2009 at 1:10 PM, C. Hatton Humphrey wrote: Jochem van Dieten wrote: The current schedule for backups is a full backup at 3am for the group of databases and then a transactional backup every hour from 7am to 10pm during the week. For performance size doesn't matter all that much. What matters is update volume. What is the transaction log volume per hour? How long does it take to apply (replay) that transaction log volume currently? Hmm... Those are questions I don't know how to answer. They are easy to test :) Just do a restore of the full backup somewhere and run some transaction logs afterwards. However, if I'm understanding where your question process is going, you think having a process that would copy and apply the transaction logs on the SQL 2005 server might be a safer idea? I would take it as the baseline option. You already have the individual steps running on the master, so apparently the performance impact of those steps is acceptable. Everything else happens somewhere else and has no performance impact on the master. Other options might be better in terms of lag or management, but may have progressively more impact on the performance of the master. With the backup schema you have, you can have a transaction log shipping proof of concept running in an hour or so. And if management wants more you might want to look into upgrading the master to MS SQL 2005 first ... Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319371 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Replication Models
Those do not appear to be very challenging requirements. You could probably even do this without any of the built-in replication at all but with just incremental backups. How have you currently configured your backups? Do you notice a performance impact when you run a full backup? Do you notice a performance impact when you run a transaction log backup? The current schedule for backups is a full backup at 3am for the group of databases and then a transactional backup every hour from 7am to 10pm during the week. As far as the performance of these, I'll have to figure that out. For performance size doesn't matter all that much. What matters is update volume. What is the transaction log volume per hour? How long does it take to apply (replay) that transaction log volume currently? Hmm... Those are questions I don't know how to answer. However, if I'm understanding where your question process is going, you think having a process that would copy and apply the transaction logs on the SQL 2005 server might be a safer idea? That's an interesting idea! How would I accurately monitor those metrics? As I said before, there are a maximum of 18 simultaneous users based on licenses, then there are reports that can run on the data at any time. The office is generally open for a 9-10 hour period each day, not including weekends. I *could* see if it would be possible to have as much as a 12 hour lag between the systems but I doubt that would fly. Thanks for the line of thought! Hatton The other option suggested seems to be log shipping, I'm not sure performance-wise, it's meaningfully superior to replication. A couple of things to consider, implementation-wise, which one might be more doable; future repeatable solution both for the client and developer/consultant... and cost? Also, let's remember it's always eaier to throw out theory than real-world experience. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319387 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Replication Models
Question for anyone that handles SQL Replication: What effect would setting up transactional replication from a large Windows 2000 database (17 Gb) with moderate usage (18-20 max simultaneous connections) to a Windows 2005 server have on the performance of the Windows 2000 machine? The reason behind the question - We use Microsoft Dynamics GP with 4 different SQL databases housed on a SQL 2000 server. We are building a web application to query those databases (read only) as well as store some data that they are not set up to manage. It uses a SQL 2005 server. Right now the app is running slow because the queries are running through the SQL 2005 as a linked server. My thought in speeding things up is to set up replicated copies of the 4 databases on the SQL 2005 server. This would be a transactional replication so we have instant access to any changes made in the main system. Additionally, the remote possibility of an injection attack running back to the live Dynamics databases would be removed. Our worry is that setting up the replication would place a noticeable strain on the main server, something we cannot afford to have. Any thoughts, ideas or suggestions? Thanks! Hatton I have had an opportunity to work on sql replication technology with sql server 2005 standard most recently. It is not easy but doable. Your second paragraph on the NEED for it was also echoed by some replication guru. Your third paragraph touches on the requirements a bit more, which sounds like it's a unilateral replication (one way). Your fourth paragraph about concern of its performance, or probably latency, in my experience of merge replication (bidirectional) with a small network, it could be up to 1 minute or even longer for server to servers (one to many) full sync, but your case could be server to client (one to one) I think so it would be faster. Approach-wise, I took/used the GUI/Wizard tool first to get everything working and then created and organized scripting to automate the process, which seems to work fine. I've also played with trasactional replication a bit and got it working as well but too bad I didn't have time to document it, it's supposed to be easier than merge. hih, Don Chunshen Li ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319354 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Replication Models
Thanks for the comments, Don! The main thing I'm worried about is any performance hit on the publishing server. The target server can lag behind data-wise by a bit (30 minutes to an hour is an acceptable delay). The problem is the size. A MS Dynamics GP database contains 1,498 tables (not including temp tables and local tables created for check printing and reports), none of them truly normalized. I need to make sure that the transactional replication does not create any additional delay in the primary tasks being fulfilled. I guess the only way to tell is to do it and see what happens. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319360 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Replication Models
Thanks for the comments, Don! The main thing I'm worried about is any performance hit on the publishing server. The target server can lag behind data-wise by a bit (30 minutes to an hour is an acceptable delay). The problem is the size. A MS Dynamics GP database contains 1,498 tables (not including temp tables and local tables created for check printing and reports), none of them truly normalized. I need to make sure that the transactional replication does not create any additional delay in the primary tasks being fulfilled. I guess the only way to tell is to do it and see what happens. Hatton, With regard to 1,498 tables, do you mean, User Tables? just for clarity. One strategy on least impact of replication is to put a majority of these tables, supporting functionalities like Stored Procedures, Views etc. in the Snapshot publication process vs. Transaction or Merge publication. And if these tables essentially contains static data then the snapshot would be one time only, hence, one time impact and can be done off peak hours etc. In my case, it was less than 200 user tables but with thousands of procedures etc. Don ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319362 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
SQL Replication Models
Question for anyone that handles SQL Replication: What effect would setting up transactional replication from a large Windows 2000 database (17 Gb) with moderate usage (18-20 max simultaneous connections) to a Windows 2005 server have on the performance of the Windows 2000 machine? The reason behind the question - We use Microsoft Dynamics GP with 4 different SQL databases housed on a SQL 2000 server. We are building a web application to query those databases (read only) as well as store some data that they are not set up to manage. It uses a SQL 2005 server. Right now the app is running slow because the queries are running through the SQL 2005 as a linked server. My thought in speeding things up is to set up replicated copies of the 4 databases on the SQL 2005 server. This would be a transactional replication so we have instant access to any changes made in the main system. Additionally, the remote possibility of an injection attack running back to the live Dynamics databases would be removed. Our worry is that setting up the replication would place a noticeable strain on the main server, something we cannot afford to have. Any thoughts, ideas or suggestions? Thanks! Hatton ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319335 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
