More Q & A
Today we follow up with more Question and Answer interaction with our readers. Some are providing knowledge for ongoing questions. Others ask questions themselves. So, here we go…
Richard:
I have been getting your newsletter for quite a while now, and I find it often contains something of interest.
For a long time I have wished for a simple way to determine if any of our stored procedures are broken. Do you know of any tools or scripts that will go through each of the stored procedures of a database and identify any that will no longer execute?
Editor:
I am not aware of any tool that would simply be able to execute all stored procedures in your database. The primary difficulty you will have is providing appropriate values as parameters for the stored procedures, as well as knowing what the expected result would be.
Few database systems are written using automated testing for stored procedures. Automated testing of stored procedures using NUnit has been my practice for the last 7 years. By creating and maintaining unit tests I would simply execute all my tests to determine if the stored procedures continue to work. Of course, these tests are created and maintained manually. This would be incredibly daunting task for an existing mature database.
It may be possible to use data from the INFORMATION_SCHEMA views to build templates for executing all your procedures. You could also reverse engineer the resulting dataset(s) as they would be returned from the stored procedure. But I don’t think you would be able to assume what the expected result would be.
There is another technique used by NetTiers against SQL Server (a Code Smith template driven ORM engine) where they execute a stored procedure only to the point of exposing input parameters so that they can reverse engineer an interface for calling each stored procedure. Again, it cannot know what the expected results should be.
Worst case, you could always create a stub providing bogus data for all parameters of a stored procedure and run it. If the stored procedure does not throw an error, you may be able to assume the procedure at least compiles and executes some query plan.
In my college computer internship I did something similar for a system looking for old deprecated programs. Perhaps that may be an option you could consider; getting an intern to execute each of the stored procedures and document the results.
Sarita provides some additional insight for using SnapShots in a log shipping situation. She is responding to the question posted yesterday by our reader interested in allowing the database receiving log shipping to be used as a data warehouse in a read only mode.
Thank you for great discussions every day which is very informative.
About Log Shipping solution for reporting it’s important that transaction log backups are stable. Because every transaction log failure causes to set log shipping again which can be painful and interruption to service.
Database snapshot is a good solution for reporting. This solution has some requirements. For example this feature is available only in Enterprise edition of SQL Server, database should be on same server like source database, database snapshot itself cannot be backed up and restored, if source database becomes unavailable for some reason then database snapshot also becomes unavailable,
Source database cannot be dropped or detached/attached, snapshot database inherits security constraints of its source database at the time of creation which cannot be changed and security changes made in source database will not be reflected in snapshot database.
Two most important considerations for snapshot database are capacity and performance. Snapshot database server must have capacity available the size of source database otherwise if database grows to the size of source database and server runs out of capacity, database snapshot will become unavailable and will need to be created again. If source database has lot of changes updates, inserts then it increases I/O to snapshot database and impacts performance. Therefore server resource consumption needs to be researched before creating snapshot database.
Hope this helps.
Editor:
Thanks Sarita for your input. I am still working through a couple tests for other possible solutions, but probably won’t finish working on that until next week.
If you wish to get into the conversation please send your thoughts to btaylor@sswug.org.
Cheers,
Ben
SSWUGtv – Security is More than…
With Stephen Wynkoop
In the modern computer environment we ask the question, "What is Security?" Is it simply passwords and Access Controls? Does it include encrypted data? Check out this edition of SSWUGtv as Steven considers what modern security encompasses.
Watch the Show
$$SWYNK$$
Featured Article(s)
Addressing Security Issues In Windows Server 2008 R2
Security Issues In Windows Server 2008 Release 2
Featured White Paper(s)
Go Beyond SCOM Monitoring with Foglight for SQL Server
read more)
Featured Script
dba3_Beginners_InsertFromAnotherTable_Demo
http://bitonthewire.wpengine.com/forum/ShowPost.aspx?PostID=2432 get all the data from cases and insert into case_history + date and by w… (read more)