April 8, 2012 at 4:20 am
What are some best practice ideas for keeping track of database objects in regards to who owns them and what they support? I find myself frequently moving stored procedures, user logins, SSIS packages and SSRS reports when we have new servers. I've even had to edit stored procedures when moving from SQL 2000 to SQL 2005 and having no idea if the code is even being used.
I'd like to implement a standard on all production servers that we document every object added to a production database by logging to a table, spreadsheet, common document with who wrote it and what it supports. (this is in addition to a good comment header in the code). This would help us find and eliminate orphaned code that gets left behind when a report is discontinued or a web form gets dropped.
Clearly my predecessors did not think this was important and I inherited 100's of stored procedures that don't seem to be used. I'd like to keep them in Source Safe, eliminate them from the server and keep this from happening again.
Just looking for ideas from some of you who have had this challenge
Thanks
April 8, 2012 at 6:12 am
Sounds like you're on the right track, especially with adding them to source control. I'd suggest breaking up the storage in source control so that all the objects that belong to a given application are stored with that app, even if it's on a shared database. Another option is to use the extended events and add ownership information there.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 9, 2012 at 7:19 am
Since you mention you want to document SSRS, SSIS, as well as SQL Server I would recommend http://pragmaticworks.com/Products/Business-Intelligence/BIDocumenter/Overview.aspx. If you didn't need that non-engine stuff I would recommend Embarcadero's ERStudio or ApexSQL's Doc.
I will caution you to be VERY careful about removing code you don't think is executed any longer. There are SOOOO many ways code could be called that it is very likely you don't REALLY know that something out there (including a manually executed recurring process) won't call a sproc or report in the future.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 9, 2012 at 7:31 am
Well, since we're recommending tools, you could check out Red Gate SQL Doc too[/url]. And to get help hooking your database into source control, there's SQL Source Control[/url], also from Red Gate.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 9, 2012 at 2:05 pm
In addition to checking database scripts into source control, any deployment to QA or Production should be tracked in a change tracking system like TFS or Mercury Quality Center. That way you can always lookup the history on an object, things like requirements, who checked it into source control, and who submitted the change order.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 9, 2012 at 2:20 pm
You may have already been looking at something like this, but the following will query table usage statistics:
select
t.name
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
,last_user_update
from
sys.dm_db_index_usage_stats i JOIN
sys.tables t ON (t.object_id = i.object_id)
order by ( user_seeks + user_scans + user_lookups + user_updates) desc;
The following will do something similar for stored procedures:
select b.name, b.modify_date, a.execution_count, a.last_execution_time, a.total_worker_time
from sys.dm_exec_procedure_stats a
inner join sys.objects b on a.object_id = b.object_id
order by last_execution_time desc;
These usage stats accumulate and are cleared when the server is rebooted, the database taken offline, etc.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 10, 2012 at 1:52 am
Welcome in the club! Reminds me of the situation I was in when I joined my current company. I think you already got some good advice here. From experience I would look at the documentation tools mentioned here and spend a few $$. I can recommend the tools from Red Gate (no, I'm not working for them) as you get a lot of bang for the buck. Of course you can get all the important information by running some SQL scripts but you don't want to spend your precious time on something which others have done already for you, do you?!
Also source control is very much to recommend! However, depending on your work environment (large vs small team, centralized vs decentralized etc.) you need to have some governance around your server environment in place (coding standards, processes how changes have to be done, naming convention etc.).
From a priority point of view I would recommend to get the source control in place, document the whole shebang and then start working towards a managed environment by definig and implementing some meaningfull governance. Keep it simple and involve your team mates in the definition. If the governance is accepted, people will follow it. If it is not adding value or if it is not understood, people will start to work around it.
Not a receipe for sucess but I hope you get some ideas.
Good luck!
Rene 😀
April 10, 2012 at 6:46 am
Thanks for the great responses and information. I enjoy collecting queries like those as well. I am completely sold on Source Code Management and have been doing for years in client server app development and .NET projects. I've become the "accidental DBA" that many of you refer to and am trying to create a leaner and more logically managed environment. I figured many of you already had some type of "system" for keeping track of the many objects that are developed and dropped on servers. Going forward I'm in good shape - it's all of the "legacy" stuff that remains a mystery. Your tips help alot.
Thanks
April 10, 2012 at 7:05 am
You are welcome!:-)
April 10, 2012 at 7:26 am
As a DBA who has just inherited a database with a lot of objects and little production control process in place, another thing you'll want to do is confirm that only you have admin privillages on the production SQL Server instance. The last thing you need is for developers to be deploying objects or "data fix" scripts without your knowledge.
In addition to checking role membership within SQL Server, also confirm that only appropriate people (not including developers) are members of the BUILTIN\Administrators role on the Windows server.
Query accounts, domain groups, and members who have admin membership
http://qa.sqlservercentral.com/articles/Security/76919/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply