[RESOLVED] What is the Best Approach for this Request

Good Day

Application Design


My application is a web application that works on databases. Meaning that there is a login page that has list of Database. A user logs into the database and do whatever on that database. Now a user can create a new Database from the front end and login to it and do whatever the application does, The new Database is created from a Master Database, this simply means that when a user creates a new Database, i do the following.
Create a new Database--> Restore the Master Database over the newly created database. Now this is fine but i have a challenge. My Challenge is that a user can 10 Databases and i will have Different clients from Different geographic places and if i find a bug in a stored procedure or if i want to change on stored procedure , that means i have to go to each client and update every database. i use Redgate to Script the Master Database.

Challenge

I am looking for a Way to Update all the Databases for each SQl instance in a client centrally without connection to a client and do that manually. if the clients are many it takes too much time.

Thanks

the logic here sounds wrong to me... i think you can achieve the same thing using single database and using its tables.

The application is doing something complicated and its good if the databases differ. and it is carrying a lot of data , Millions of records , so combining all databases will be a nightmare, i will experience the dead end of SQL

Will running a script take that time?

It depends when i run the Script, i tried to run the script before the user logs in.

but if i can get a way to update a list of Databases from a remote location it will be cool

You can script the process at each client but you will still have to do it per client since other factors will have to be considered; per client backup and recovery, per client audit related requirements, per client regression testing, per client downtime (unless patch be applied while DB is online), etc... if clients require support during update then turn-around time and other delays (scheduling) will also have to be considered. I doubt clients would appreciate you connecting to their databases remotely and doing an update without giving them chance first to do whatever they need to do at their end.

Alternative would be similar to patching and migration... patching would involve proving clients with scripts they can run (they can do their own backups before applying your patch)... for migration clients download new app and DB, then give them option to migrate data from old database when creating new DB. That way you can sync version control of front-end modules with their back-end counterparts since a break in backward compatibility is inevitable. Comparable would be Oracle technology (9i, 10g, 11g versions for front-end tech and DB)

I'm not exactly sure what you are doing but you can hit all the databases on a server via sp_msforeachdb. It's an undocumented stored procedure but there is plenty of information on it via Google.

Thank you guys for your advice.

I have decided to write something that will Connect to an Existing VPN and accept a Script and it should run on the remote Server and Update the Databases. The reply from you guys gave me an idea thank you. i think
TysonLPrice's advice is cool, i will test it and tell you a feedback about this .


Kind regards

Vuyiswa