January 31, 2011 at 3:03 am
In our server instance, I am planning to have common database which has all the default tables, common utility stored procedures and UDF
and it can be accessed from all other databases. Is this a better design?
January 31, 2011 at 4:09 am
One problem is security. Your user will need permission on both databases to use theUDF's.
Another is portability - you will need both databases to operate, not just one. That may make backups etc more difficult.
The upside is that you only have to maintain one set of UDF's, functions etc. But then all databases will need to be on the same version and share the same data structure.
Unless I am in total control of the environment (not being installed on lots of customer sites) I would not want to do it.
January 31, 2011 at 6:13 am
Another problem is cross-database dependencies. If you have lookup tables in different databases, you can't use declarative referential integrity, foreign keys, to ensure the consistency of the data. You'd have to write cross-database triggers, which can be a very problematic design.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 1, 2011 at 7:20 am
This will be a totally AWFUL design for reasons already covered. Throw in almost certainty of linked server usage and cross-server data pulls/joins. Disasterously bad performance will result.
Speaking of performance, UDFs will very likely be a killer too.
Seems like you are starting out with about 7 strikes against you here. And the fact that you even considered such a design makes me need to STRONGLY recommend that you get a professional on board for some design review and mentoring before you get too far along in your work.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 1, 2011 at 8:23 am
I didn't even notice that you were talking about UDFs. Assuming you're talking about multi-statement table valued UDFs, my best advice, avoid them like they have AIDS, because, effectively, they do. Also, if you're using a lot of UDFs, even of the non-multi-statement variety, what ever you do, don't nest them (UDF A calls UDF B which JOINS UDF C & D which JOINS UDF E & ZZ...). That will also destroy system performance.
Excellent catch Kevin.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 1, 2011 at 1:45 pm
>>Excellent catch Kevin
I get lucky sometimes - especially when my favorite three-letter word (UDF) is dropped!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 17, 2011 at 4:01 am
Sorry. I need to give more info on the environment. The common database and all databases ( 4 or 5 ) using the common database are in the same server instance.
This is our database structure and we don't need to install in customer sites etc.
I agree, we can't use declarative referential integrity and performance impact.
But, there are around 20 tables and 20 UDFs/SPs common? Syncing these has been painful. Any other options?
Thanks for your time.
KRS
March 17, 2011 at 5:43 am
So you're just trying to sync some common code sets? Oh, that's a different problem. First, I'd get your database under source code control. Second, I'd design a deployment process that deploys the database from source control and only from source control. That's the absolute best way to get your database code under control. I wrote a couple of chapters in the SQL Server Team-based Development[/url] book on that exact topic (free download by the way).
To get your databases into source control, I recommend either Visual Studio Team System (the expensive method) or Red Gate SQL Source Control. If you just want to syncronize your code between databases, take a look at Red Gate SQL Compare.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 17, 2011 at 2:39 pm
If you are just syncing stuff and not actually using this common database it CAN be a good thing.
You probably want to set up replication on those objects so when they get changed on the common table they get replicated to the other DBs.
Also, get a source control. It's help beyond imagine, as mentioned, RedGate product is good and another, simpler alternative would be Visual Source Safe
--
Thiago Dantas
@DantHimself
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply