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.

All Forums  General SQL Server Forums  Database Design and Application Architecture  Where to put logic for multiple databases

Author  Topic  Zadj Starting Member 1 Post Posted - 2010-03-16 : 13:40:18 Hi, I'm trying to work out the best practice for a complex system that I'm going to be upgrading, and I was wondering what opinions people had on the options. I hope this is the correct forum as none of the others seemed to match. The scenario: This is for a payment management system. We have a central database that contains all the payment details and account information. Each account has 1 or more databases ("client DB") for their operation. The structure of these databases are almost the same, and could be made identical if required. The payment system needs to check the data in the client DB against the data in the payment DB and according to various business rules charge accordingly. Our current method of doing this is a service that calls a stored procedure for each of the client DBs. The stored procedures are largely the same, with about 3 main patterns of payment logic. This worked while we had a small number of databases and less payment options, but is becoming less manageable. We have also added the requirement for reports of payment prediction, making the amount of work for any change unbearable. We are running SQL server 2005 Workgroup edition. The question: What is the best way to centralise the business logic when running it against multiple databases, while not damaging performance? I currently have 4 ideas that I'm considering: 1. Use dynamic sql to create the relevant logic on the correct database each call. I have my doubts about this option as the server will not be able to cache any plan, and as the tables get bigger it may grind to a halt. 2. Create a master stored procedure which will create more stored procedures with the correct business login. This is similar to the dynamic sql option but will allow plans to be created to improve performance. The down side is that it will need some method of managing changes. 3. Creating a service that will collect all the relevant data from the payment database and the client database. It can then work out what payments are required and insert or update the data back to the database. The downsides to this are that it will be a lot of data to load and the updates will probably need to be done row at a time with possibly a transaction wrapping any related updates, which will be slow and may cause table locking. 4. Using CLR assemblies to create functions or procedures that can be used by all the databases. This option feels like it could be the right one, but I don't know enough about the abilities and limitations of CLR assemblies to be able to establish its suitability. If anyone has any words of wisdom, or even grunts of generalness, then they are greatly welcome. I hope I've given enough of an outline to make my question understood. Lots of thanks, Zadj