0

I have 80 different databases to maintain and all the databases has the same tables and stored procedures but the information stored is different, the way it works is that we have created a separate database for each client, and each client will at-least have some 2000 - 3000 rows of data to work with in some selected tables.

My first question is - now I have to change the stored procedure for all 80 databases. Normally I would have to go to each database and update the stored procedure which is a hideous task, but is there some kind of technique where I update a single stored procedure for one database and all the stored procedures for other 80 databases will get updated automatically.

My second question is - is it ideal to use multiple databases or create a single database and identify a client using his client id.

Any thoughts would be amazingly helpful,

Thank you, Prashanth Kumar B

Prashanth Kumar B
  • 566
  • 10
  • 25
  • Regarding the second question, whenever you compare two options, you will usually find that each has at least one advantage over the other. Then it becomes a case of making the best choice for the situation at hand. – Dan Bracuk Jul 30 '14 at 11:56

1 Answers1

1

If you have the list of servers and databases in Excel, you could:

1) Script the stored procedure to a file (say C:\myproc.sql)

2) Use Excel to create a separate osql command for each server/database combination like this:

ServerName  DatabaseName   SQLCommand
==========  ============   ==========
SERVER1     DATABASE1      ="osql -E -i ""C:\myproc.sql"" -S " & A2 & " -d " & B2
SERVER1     DATABASE2      ="osql -E -i ""C:\myproc.sql"" -S " & A3 & " -d " & B3
SERVER2     DATABASE1      ="osql -E -i ""C:\myproc.sql"" -S " & A4 & " -d " & B4
SERVER2     DATABASE2      ="osql -E -i ""C:\myproc.sql"" -S " & A5 & " -d " & B5

3) Copy the generated SQL commands into a batch file/Powershell script

4) Run the batch file/Powershell script at a Command Prompt.

As to your second question, I would say it would definitely be better to have 1 database and all the clients in that database (as long as your clients are happy with this arrangement), as your issue is just going to get worse and worse as you have more clients. Also you are going to have an overhead of backing up all the different database whereas you could just do it once if you have the one database.

Dave Evans
  • 26
  • 4
  • Nice, I am guessing this might work after all, I just have to wait till this weekend and find out myself. The second question, hmm I guess, all this running batch files and shell scripts could be avoided through that method, hey as you said my client should also be open for this kind of method. so I am going to stick a pin for 1 database to rule them all idea for now. thank you for the answer though – Prashanth Kumar B Jul 31 '14 at 10:30