Automate creation of 'n' number of Data Warehouses in SQL Server

I am currently creating a generic 'data warehouse' for multiple clients. Generic in the sense that each client database uses an identical structre.

I am trying to find a way of automating this process so that if I update a view/procedure in a 'master' copy I can run a procedure to re-create all the client copys.

Each client has:
database
Linked server
views to extract data from linked server
stored procedures to feed the reports and generate tables from views

I was think along the lines of having a table in the 'master' copy with a list of clients that could be used as parameters to create databases, logins, users, linked servers ,views etc for each client.

Is this possible?
alito_procurementAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
I gotcha.  So, here is what I would do.  In your "master" db, save a list of clients.  Each night, create a full db backup of this master db.  After the backup, have a script go through each of your other databases and drop them.  Have another script go through and restore the database backup for each of your clients, giving each db the same name as before...you can use your clients table to do all of this....make sense?
0
 
momi_sabagCommented:
there is no way of doing what you want without coding
you would need to keep track of which object is relevant for which client, and when that object is changed, to go and change it in the clients

don't forget that if the clients tables already has data in them, it would be harder to implement the change if a drop / create is needed
0
 
chapmandewCommented:
Sure, it is possible.  couple of questions though...are these databases going to be on the same instance of sql server?  It might be as easy as having one backup, and restoring it a bunch of times.  Another idea would be to put everything you need in the model database and then just create databases as need.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
alito_procurementAuthor Commented:
Hi

All clients are all on one instance. Each night we take a backup of each clients Live data which is held in MySql. This is restored locally to a MySql Server.
A scheduled job is then run from sql server to execute a stored procedure (in each database) which drops all tables then recreates them based on the views which are pointing to the MySql server via the linked server.

This works like a charm. At the moment there are 9 clients and the list will grow, albeit slowly.

My problem is when I make an amendment to a view or a stored procedure, I have to apply it to all Clients independently. Apart from being time consuming there is a risk of them becoming out of sync.
Potentiall there could be frequent changes e.g when a new report is requested I have to write another stored procedure.

I want to store one master copy which i can update then execute a procedure to drop and recreate the clients with the reflected changes.

I guess thats similar to what you (chapmandew) suggested.

I hope this is clear.

Thanks for the prompt response guys
0
 
chapmandewCommented:
Are you doing this through mySQL or SQL server?

Also, there is really no getting around the fact that if you change a proc in one spot, that you'll have to apply it to all other dbs as well.  The advantage is that you can change it in model and then all NEW databases will have the change.

Of course, there are always things that can be written to do this for you.  One idea would be to get the text of the procedure you want to change, and write a routine to loop through the other databases and make the change....just one idea.
0
 
alito_procurementAuthor Commented:
To be honest the fact that the data originates in MySql is irrelevant. The views might as well point to Sql Server for the point of the question.

You say:
"if you change a proc in one spot, that you'll have to apply it to all other dbs as well. "
and then
" The advantage is that you can change it in model and then all NEW databases will have the change."

I don't follow. These statement seem to contradict one another.(don't mean to be rude!)  When you say change it in model do you mean SystemDatabases.model?

Here's an example of what I want (which may not be possible):

Databases
Client 1
Client 2
Client 3
Client 4
Master Client

I want to be able to apply a change to Master Client then run a procedure to copy the entire structure of Master Client to the other clients so they are all identical. I don't care about data as this gets refreshed every night.

Ideally I would like to drop each database every night and recreate it from the master client structure.

Thaks again







0
 
chapmandewCommented:
No problem....I'll explain a bit more.

>>"if you change a proc in one spot, that you'll have to apply it to all other dbs as well. "
Meaning if you make a change to a procedure, you'll have to go apply it to all other databases one way or another...existing databases.

>>" The advantage is that you can change it in model and then all NEW databases will have the change."
You can put the change in the model database so that any NEW client databases created (only new ones) will have the most up to date script.

If you can drop each db each night and then just recreate them from model, then you're set.  
0
 
alito_procurementAuthor Commented:
Ah I see.....

What I was looking for was some way of automating the process of creating all Client databases from scratch on a nightly basis based on the Master Client. I was looking for a pointer on how to begin to go about this.

I basically mean cloning the master for each client. I was hoping to automate this so all clients wil always be identical.

I was thinking maybe having a table of clients which could be passed as parameters in a loop until all clients are created. If there is a new client it is added to the list.

I'm not looking for a complete solution just some ideas of the best way to go about it

Thanks again


0
 
alito_procurementAuthor Commented:
Ah.....I'm with you. Didn't think of that. Doh!

Points are yours.....No doubt I'll be back
0
 
chapmandewCommented:
You're welcome...come back anytime.  :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.