I'm working on a system that will create a new DB that copies the SPs from
an older DB.
The purpose of this project is to create smaller, yearly DBs that will
require fewer table scans (since most searches are done on only the current
year). I would like to mimic the old DB year by year, copying all table
structure, a few tables worth of data (so far so good on those queries), but
I'm unsure on how to copy SPs from one DB to another.
Any thoughts?
Chad Edge
Seattle, WAYou can create the procedures in model, so when you create the new DB, they
get created automatically.
You can also try SQL Compare from www.red-gate.com, which will help you
generate scripts to synchronize two databases.
"Chad Edge" <xcasex@.hotmail.com> wrote in message
news:D2746C57-08D3-4A58-B0C0-6D7725D27C73@.microsoft.com...
> I'm working on a system that will create a new DB that copies the SPs from
> an older DB.
> The purpose of this project is to create smaller, yearly DBs that will
> require fewer table scans (since most searches are done on only the
> current year). I would like to mimic the old DB year by year, copying all
> table structure, a few tables worth of data (so far so good on those
> queries), but I'm unsure on how to copy SPs from one DB to another.
> Any thoughts?
> Chad Edge
> Seattle, WA
>|||Aaron,
Thanks for your help on that. I'll look up the link and work on the scripts.
I'm actually wondering now if we even need to copy the SP's. I guess it will
depend on what amount of weight we want to put on the interface-level.
What we're trying to do is make smaller seasonal db's without changing much
of the interfaces access (there's an internal edit tool, and an external
read-only view tool). If we keep our current, 'master' database in-place w/
the SP's, then we could adjust those SP's to choose which smaller, seasonal
db to get data from. If we do things that way, then we won't need to manage
all the SP's on multiple db's.
Chad
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eHxOBWAlGHA.1936@.TK2MSFTNGP04.phx.gbl...
> You can create the procedures in model, so when you create the new DB,
> they get created automatically.
> You can also try SQL Compare from www.red-gate.com, which will help you
> generate scripts to synchronize two databases.
>
>
> "Chad Edge" <xcasex@.hotmail.com> wrote in message
> news:D2746C57-08D3-4A58-B0C0-6D7725D27C73@.microsoft.com...
>|||> What we're trying to do is make smaller seasonal db's without changing
> much of the interfaces access (there's an internal edit tool, and an
> external read-only view tool). If we keep our current, 'master' database
> in-place w/ the SP's, then we could adjust those SP's to choose which
> smaller, seasonal db to get data from. If we do things that way, then we
> won't need to manage all the SP's on multiple db's.
I recommend having the schema spread across the dbs, which will allow for
the app to simply change its connection string, instead of your main control
database needing to generate a dynamic SQL string EXEC('EXEC ' + @.dbname + '
.dbo.ProcedureName');|||Aaron,
My worry about doing that would be having to manage the SP's from season to
season.
So just to clarify, you're saying this is not recommended:
1. Master db, currently the only db, is called MAINEVENTS1. This db has all
SP's, and stores about 10 years worth of information (events)
2. We'd like to create new, seasonal db's called 'EVENTS_YYxYY' (eg:
EVENTS_06x07)
I'm considering this approach since it would shrink the amount of table
data that would need to be scanned, since most event results are polled for
the current 'season'
3. When an administrator logs in using (currently CFM) our admin interface
(on our intranet), they would have a new dropdown "Select Season:" which
would set their session for a specific season
4. When visitors to our Web site (again, using CFM), they would by default
get the current season set as their session, but would have text-links to
previous seasons.
5. Passing the session variable of which season is selected to the query
string would fire the correct SP from MAINEVENTS1, which would then retrieve
the database information from the correct DB.
In our first year, that would mean that there'd be two DB's :
MAINEVENTS1 with the last 10 years worth of data, and EVENTS_06x07.
6. This fall, we'd be adding new event types (concerts, speaking
engagements, etc) that would only be added to the newer DB's in the seasons
that they happened (no need to add Public Speaking to the MAINEVENTS1 since
it never happened).
7. The next round of updates would allow us to split MAINEVENTS1 into
EVENTS_01x02, 03_04, etc. further shrinking our query source
8. The MAINEVENTS1 would then become a very small DB, just a gateway of SPs
that query other dbs and format the data.
The reason I've been approaching the project this way is so the Online
interface only needs minor changes, the SP's only need minor changes, and we
don't have to manage SP's from season to season (say we change the way we'd
like data formatted; we'd have to track the changes down and verify control
over each database - bad form in my opinion).
The main reasons for approaching the project this way:
1. Shrink the databases since there's thousands of rows in our ten-year db.
2. Lock past events so they don't get overwritten (we've had issues where a
venue will change it's name; if we update our database the way it's
organized now, all events in the past are then related to the new venue
name - this makes it a nightmare to try and track in the past where an event
occurred because the venue in the past will no longer exist in our db).
I know #2 should be addressed, but we're not able to make such drastic
changes to the data at this time. Perhaps a round 3 we can restructure. For
now, we're getting about 2 million hits a month for our events and I'd like
to keep the impact small.
Hopefully that's not boring you to tears, I just needed to dump my brain
onto the screen to make sure we're speaking the same language.
Thanks again for your suggestions. I'll look at the structure and the SP's a
little more and see what work is ahead. I've got to make a decision by the
end of this w
so I can roll out the replication by EOM June.
Chad
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u3DsmsIlGHA.2304@.TK2MSFTNGP02.phx.gbl...
> I recommend having the schema spread across the dbs, which will allow for
> the app to simply change its connection string, instead of your main
> control database needing to generate a dynamic SQL string EXEC('EXEC ' +
> @.dbname + ' .dbo.ProcedureName');
>|||> My worry about doing that would be having to manage the SP's from season
> to season.
What is there to manage? If you store the SPs in model, then every new
database you create will have the SPs automatically.
When you have a changed SP to deploy, you run a loop to create a series of
CREATE or ALTER procedure statements, to generate a script you can run,
deploying to all databases at once.
We have two production systems that use this model, one has over 3000
identical databases spread across three machines, and the other has over 750
identical databases across three different environments (dev, qa,
production).
I use red-gate's SQL compare to synchronize model between environments.
And by naming the databases with a common naming scheme, I was able to write
a stored procedure in master that gets all the databases from sys.databases
matching that naming scheme.
I store the create and/or alter scripts in a file called change.sql, then
run a C# command line program that calls the stored procedure, and creates
an output file for each database.
Deploying changes really couldn't be simpler (except when there are
exceptions between environments). Everything configurable (such as network
share paths, SMTP servers, ftp passwords, etc.) are stored in properties
tables, nothing is hard-coded, and it makes it really easy to take the one
customer who explodes in activity from one server to another. Just backup
the database, restore on another server, and update a flag in the control
database that tells you what server the app can find that database.
A|||> I store the create and/or alter scripts in a file called change.sql, then
> run a C# command line program that calls the stored procedure, and creates
> an output file for each database.
Sorry, let me explain that sentence a little better.
Let's say I have a fix to a single stored procedure that needs to be
broadcast to all databases in an environment.
I store a single ALTER PROCEDURE script in change.sql.
My C# app grabs the stored procedure from master, steps through the 750+
databases, and for each one, reads change.sql, adds a USE [dbname] in front
of it, and appends it to a file called deploy.sql. Now I can run a single
.sql script to deploy my changes to every single database.
Sure, there are pros and cons, and yes that many databases sounds like a lot
to manage, and it is. But "a lot" does not necessarily equate to "hard"...
there are many ways to make the multi-db scenario much easier to manage.
One of these days I'm going to write an article on it...
A|||Aaron,
I'm liking where you're going (excluding the fact that it's all new ground
to me; I've stayed as far from MS coding as I have been able in my career).
One thing I might not have mentioned: We're going to get into situations
where we'll need a *controller* sp that takes variables and chooses which
database to retrieve the data from. For example:
Right now (with MAINEVENTS1) we have a table of Artists (musicians, let's
say). If you want to retrieve information about a specific Artist (say,
their instrument, the places they've played, tickets sold, etc) you run a
query using their ArtistID (just an example). That works in the MAINEVENTS1
db.
Now lets say we create EVENTS_06x07 that adds additional columns of data
that weren't available in the past (such as, height and weight, which
changes as the year goes on - just to make it dynamic). We won't have that
information for anything in the past, so we'd like to not make any changes
to MAINEVENTS1. However, we would now need to make the query (SP) understand
where to get it's data, and how much detail (columns) to return, based on
the year passed (pre-06 or post-06).
I picture (in my ideal candy-land imagination) a controller SP that would
take two variables (the ArtistID, and the Year), determine which database to
run the SP (or better yet, add the correct formatting to the SP itself), run
the SP and return the proper column data.
This is where replicating SPs gets tricky: one DB may have changes that a
previous years DB won't.
Also, leaving SP's in the Model would not automatically roll-out the changes
until a new DB was created, or when we create an entirely separate
application (such as something in C#).
My example is very elementary; there's trickier situations (statistics about
artists, about venues, about groups, etc) that will change from year to
year. That's where centrally managing SP's and having the MAINEVENTS1 manage
the connections and returns makes so much sense to me.
Again, thanks for your insight. You're making a lot of sense and I've
already started working with the Red-Gate software today (the dependency
tracker is flipping me out).
Chad
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23ESReGJlGHA.1320@.TK2MSFTNGP04.phx.gbl...
> Sorry, let me explain that sentence a little better.
> Let's say I have a fix to a single stored procedure that needs to be
> broadcast to all databases in an environment.
> I store a single ALTER PROCEDURE script in change.sql.
> My C# app grabs the stored procedure from master, steps through the 750+
> databases, and for each one, reads change.sql, adds a USE [dbname] in
> front of it, and appends it to a file called deploy.sql. Now I can run a
> single .sql script to deploy my changes to every single database.
> Sure, there are pros and cons, and yes that many databases sounds like a
> lot to manage, and it is. But "a lot" does not necessarily equate to
> "hard"... there are many ways to make the multi-db scenario much easier to
> manage. One of these days I'm going to write an article on it...
> A
>|||> Now lets say we create EVENTS_06x07 that adds additional columns of data
> that weren't available in the past (such as, height and weight, which
> changes as the year goes on - just to make it dynamic).
So, in every db, add weight and height to the table(s) or view(s), adjust
procedure(s) that exist there, and only update those customers that have
that data (otherwise it is NULL).
Displaying these columns is something the app can decide based on the
settings in the control db, not based on the data itself.
Adding columns to a stored procedure should not change the behavior of the
app... but the app *can* use those new columns if it deems it should.
A
Motley wrote:
![Smile [:)]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_uOJPaW41zdYucBFFJkI8R3Wij7o4l6OL-HcDYzYy1ufQWAfXcgy8devh18t85NP_orTqM2UgYH2y_nf0QtaT8PsU-CxytZRpckt2zKFg7HtS40vfLYUA=s0-d)
![Embarrassed [:$]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_vR558cqLj8E702TyjEerQthGjJxsXURFMYc4RErdJSD-bLslbgGoNDE4svhvr7uKY6nclANt_GSxHFVtenMva2JKSUn4XM7ptYqEX8UbBs1td5s4TTZg=s0-d)
SBowe wrote:
Kolf wrote: