Solved

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

Posted on 2008-10-15
10
194 Views
Last Modified: 2012-05-05
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?
0
Comment
Question by:alito_procurement
  • 5
  • 4
10 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22720154
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22720171
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
 

Author Comment

by:alito_procurement
ID: 22720256
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 60

Expert Comment

by:chapmandew
ID: 22720300
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
 

Author Comment

by:alito_procurement
ID: 22720431
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22720479
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
 

Author Comment

by:alito_procurement
ID: 22720625
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
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 22720693
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
 

Author Closing Comment

by:alito_procurement
ID: 31506268
Ah.....I'm with you. Didn't think of that. Doh!

Points are yours.....No doubt I'll be back
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22720818
You're welcome...come back anytime.  :)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question