TOP (10) Bad Things to Do In a Database – Voting Begins
The TOP (10) list is coming along quite nicely. I’ve been very busy trying to read through all of the submissions for Bad Database Practices and consolidate them into items that are concise, and meaningful. Wow! Open something like that up to 800,000+ readers and you’ve got a lot of experience contributing to the list. Thank you everyone for each submission.
One thing that will really help make this list more meaningful, and help drive future SSWUG content, is for you to vote. I have already received a number of votes through my Email, btaylor@sswug.org.
*UPDATE* Sam Brace, our SSWUG Community and Site Manager, has automated this survey and posted it here. Please go and fill out this survey to log your vote.
I’ll be reviewing the votes next week and publishing the results. I will extend the close of the pole a day or two to make sure you have an opportunity to vote.
Don’t Write Stored Procedures Only Accessing Data in a Remote Database
One of the items in the list of bad things was to have a stored procedure in a database that accessed only data in another database. Sowbhari sent an Email asking for clarification.
Sometimes a database may be separated into two or more databases. This technique is sometimes used to do things like separate confidential data or optimize tasks such as re-indexing or backups. The ability to use partitioned tables and rebuild indexes in the background has reduced this practice for optimization. However, separating confidential data is a growing concern.
Having many databases makes the database access layer a little more complicated. You need to connect to the database where data is stored. Often a query is required that joins data from two or more databases (heterogeneous join query). Historically this kind of query did not perform as well as having the tables in a single SQL Server database. I have no statistics on current database engines.
The primary reason this is a bad technique is that when you access the remote database tables, somewhere on the database where the query is written, there is a hard coded link to where the remote server/database/schema/table resides. Even if you use synonyms, a better option than embedding the table location in a stored procedure, there is a hard coded link in the synonym, it is just in a single location.
Why is this bad? What happens when you move a database from one server or instance to another? All links break, and therefore, any query using those links no longer functions. If you are using separate databases for development, QA and Production, this technique makes deployment of changes more complicated.
Sometimes, this is still the best architecture. With the need to get faster processing and larger databases, this is going to become an even more popular technique of storing data. However, the merging of data will most likely move out of stored procedures or packages and into a data access tier.
Back to the question…why is it bad to have a stored procedure that only queries data in another database? Because, remote database access is a technique that is best avoided in the first place. When you have a stored procedure that only accesses remote data from a single remote data store, then simply put the stored procedure on the remote data store and access it directly.
Would you like to share your perspective on remote database access? Have you done some performance testing that would be of interest? Drop me a note through facebook, twitter or btaylor@sswug.org. We’ll include your comments in a future newsletter.
Triva Question
How many databases will a single instance of SQL Server support? Think you know? You can add your answer to the question on my facebook page. I’ll have the answer on Monday.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Game-changing Features in the Silverlight 5 Beta (Part 1)
Today, I am going to talk about two game-changing features in the Silverlight 5 Beta. First, we are going to explore XAML Debugging. In my opinion, this is one of the most important features in the Silverlight 5 beta. Then, we will investigate “Multiple Windows Support” in a trusted Silverlight 5 application. The full source code for both of the projects will be available as a separate download with this article.
Featured White Paper(s)
Upgrading to Microsoft SQL Server 2008 R2 from Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000
More than ever, organizations rely on data storage and analysis for business operations. Companies need the ability to deploy… (read more)