October 15, 2003 at 9:52 am
Just curious if anyone has any ideas how to update the same stored procedure across multiple databases.
Our databases are set up by month and year and they all have the same stored procedures. However, if we update one stored procedure on one of the databases, right now, I have to run a script to drop and then add the updated stored procedure on ALL the other databases as well. Does anyone have a better way to go to resolve this? This method seems to leave the door open for obvious mistakes and problems.
Thanks!
Jay
October 15, 2003 at 10:14 am
That's the method I'd use, but I have a batch file that connects to the appropriate dbs and I pass in the name of the scipt.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 15, 2003 at 11:26 am
You could also set up one database as the "Master" and then use Snapshot replication to replicate the procedures and functions. Another way is to create a DTS Package that will copy the objects. Personally, since I'm going through this right now. I'm going to use the replication route. Although the package could be easier once you get it set up, I just happen to already have Merge Replication set up for all these machines anyway so it's a simple matter of running a new snapshot and replicating the changes out to the subscribers.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
October 16, 2003 at 12:45 am
What is the purpose of segregating data by month/year into separate databases? I can't think of a business situation for which that's a good solution. Years ago, I worked with data that was one record per month per individual, all credit card holders in the US. It did not occur to anyone to hold last month's credit report and this month's credit report in separate databases.
_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."
October 16, 2003 at 5:21 am
I also distribute the procs to each db. Once you build a tool of some sort (as mentioned above), the process is painless. The nice part is that if you have to, you can customize the behavior of a proc in a single db. Of course, then you have to make sure you don't overwrite it with your next batch update of the proc!
Andy
October 16, 2003 at 6:47 am
quote:
What is the purpose of segregating data by month/year into separate databases? I can't think of a business situation for which that's a good solution. Years ago, I worked with data that was one record per month per individual, all credit card holders in the US. It did not occur to anyone to hold last month's credit report and this month's credit report in separate databases.
The reason we set it up that way is because we are dealing with huge amounts of data. Breaking it up into month-year has improved performace considerably. Setting up the databases this way has reduced one process from 2 days to 30 minutes.
And to probably answer your next question, yeah, we did everything we could to optimize the queries and performace. Breaking up the dbs this way has worked great.
October 16, 2003 at 8:04 am
One of our databases is used to control access and resides on many of our SQL Servers. From time to time, a request comes through to refresh a set of procs on all of these. I created a DTS package with several copy SQL server object tasks. Each of these has the same source server and the same set of procedures selected to copy. I also move object permissions - access is only role based. The task can be cut and pasted to make it easier - changing the destination. I set up workflow to execute one task at a time - I've seen it fail if more than one copy proceeds at the same time.
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
October 16, 2003 at 8:38 am
Some of our developers decided to break up dbs into dependent pairs. They append the word "_stage" to one of them. The pair exists in the dev, staging, and production environments. Is that "staging" in "staging"? Who is on first? I would have preferred renaming the “stage” tables and placing them in the same database.
Our ledger record table is very large. We use a "rollup" table to avoid slow queries. A nightly process loads a year of data with only the columns needed into a temp table. The temp table is used in the remainder of the process of updating the rollups. The total time for the process dropped from 60 minutes to less than 2. Perhaps....
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
October 17, 2003 at 5:57 am
Are all of you DB's on the same Server?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply