What is the Best Approach for this Request

Posted by Vuyiswamb under Sql Server on 4/13/2010 | Views : 2561 | Status : [Member] [MVP] [Administrator] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Thank you for posting at Dotnetfunda
[Administrator]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Raja on: 4/13/2010 [Member] Starter

0	I assume that when you are creating the database for several client from the master database, you must have their connectionstrings. Why not you use those connection strings and update the SP as well (not sure if this is possible), if not you can use inline SQL statements for all your query to the database and store this sql server statements into some xml file or a plain text file (I mean to say write the stored procedure code into xml or textfile). Whenever you want to execute something, just get the sql statement from the xml or text file and execute it. When you need to modify, modify the xml or text file data (as all other applications will be using this text file to execute the command so you would modify once and all will get affected). In case your customers are at different geographical location, create an executable that will update your xml or text file that contains the sql statements and distribute across all your clients. Hope this will give you some clue. Thank you Vuyiswa Regards, Raja, USA Vuyiswamb, if this helps please login to Mark As Answer. \| Alert Moderator

I assume that when you are creating the database for several client from the master database, you must have their connectionstrings.

Why not you use those connection strings and update the SP as well (not sure if this is possible), if not you can use inline SQL statements for all your query to the database and store this sql server statements into some xml file or a plain text file (I mean to say write the stored procedure code into xml or textfile).

Whenever you want to execute something, just get the sql statement from the xml or text file and execute it. When you need to modify, modify the xml or text file data (as all other applications will be using this text file to execute the command so you would modify once and all will get affected). In case your customers are at different geographical location, create an executable that will update your xml or text file that contains the sql statements and distribute across all your clients.

Hope this will give you some clue.

Thank you Vuyiswa

Regards,
Raja, USA

Vuyiswamb, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 4/14/2010 [Member] [MVP] Silver

0	Hi Greetings If you using SQL Server 2008: We can use CMS (Central Management Server) We can define an instance of SQL Server as a Central Management Server(CMS). CMS stores a list of instances of SQL Server that is organized into one or more Central Management Server groups. Actions that are taken by using a Central Management Server group will act on all servers in the server group. This includes connecting to servers by using Object Explorer and executing Transact-SQL statements on multiple servers at the same time. All Central Management Servers and subordinate servers must be registered by using "Windows Authentication". Note: This feature(CMS) can be defined only on SQL Server 2008 Cheers Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Vuyiswamb, if this helps please login to Mark As Answer. \| Alert Moderator

Hi

Greetings

If you using SQL Server 2008: We can use CMS (Central Management Server)

We can define an instance of SQL Server as a Central Management Server(CMS). CMS stores a list of instances of SQL Server that is organized into one or more Central Management Server groups.

Actions that are taken by using a Central Management Server group will act on all servers in the server group.

This includes connecting to servers by using Object Explorer and executing Transact-SQL statements on multiple servers at the same time.

All Central Management Servers and subordinate servers must be registered by using "Windows Authentication".

Note: This feature(CMS) can be defined only on SQL Server 2008

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Vuyiswamb, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 4/14/2010 [Member] [MVP] [Administrator] NotApplicable

0	Thanks guys for your reply. Pandians what if am in South Africa and i have a client in Englang,Scotland,Wales,Australia, USA Japan. Will this work ? Thank you for posting at Dotnetfunda [Administrator] Vuyiswamb, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts