Solved

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

Posted on 2008-10-15
10
168 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now