Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789»»

9 Things to Do When You Inherit a Database Expand / Collapse
Author
Message
Posted Friday, June 15, 2012 9:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 12:47 PM
Points: 7, Visits: 99
Steve is right...you don't want to just unilaterally delete objects when you're not sure of their function.

We've started renaming objects to be deleted with the prefix "del_", and adding that day's date to the end. If no problems arise within a certain length of time, then the object can be deleted. But if a problem comes up, it can quickly be reverted back to its original name.
Post #1316756
Posted Friday, June 15, 2012 10:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:36 AM
Points: 31,362, Visits: 15,823
matt.penny (6/15/2012)
Can I just add something that might seem obvious?

Make sure you've got all the passwords! You *might* need passwords for the application, sa, odbc links etc.

Also, try to find out what you have to do to change passwords that the application depends on, if you need to.


Absolutely, though I'd hope these were stored securely somewhere. I've had this happen before where we didn't know passwords.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1316765
Posted Friday, June 15, 2012 11:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 1:33 PM
Points: 25, Visits: 211
Good article Sylvia! This would also be a good checklist for starting a new job. Very well thought out.
Post #1316794
Posted Friday, June 15, 2012 12:49 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 656, Visits: 3,960
Obsolete objects is such a huge issue. Is it technically feasible to track the last time any object was used or would the overhead of doing so be just too problematic?
Post #1316844
Posted Friday, June 15, 2012 1:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 8:26 AM
Points: 109, Visits: 490
Chrissy321 (6/15/2012)too problematic?


Really depends. If you can run the profiler for extended periods, you can still easily miss something which is only used once a year.

I tend to rely on inspection and logging because I like to design systems where the database exposes as much as possible in a controlled fashion (i.e. mainly stored procs). It is relatively easy to find and instrument any stored procs which refer to a table to find out when they are used, and you are assured that you catch all usages. You can simply use the SQL Server dependencies information (make sure it's refreshed) to inventory the system.

In a system with direct table access, it's not nearly as easy.
Post #1316853
Posted Friday, June 15, 2012 3:22 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:29 PM
Points: 752, Visits: 920
SuperDBA-207096 (6/22/2009)
"Drop obsolete objects"

It's sometimes not straightforward how and when objects are used. before dropping something you need to be sure it's really not used.

It's quite possible you could drop a sproc or table thats only used periodically (once a month or once a quarter). This could create major problems for you a few months out!


Agreed. This was an excellent article, but along with Mark I would urge caution on the drop obsolete objects step. It is definitely a good thing to do, just make certain that they are truly obsolete. (Having good backups and version control can provide some measure of protection here, but it is still a problem best avoided.)

I would also add "Read the documentation and comments in the code, if they exist." I know I still have a long way to go in writing documentation for my databases, but I do tend to heavily comment the code and that is the first place I would tell someone new on my team to look.


---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #1316905
Posted Saturday, June 16, 2012 2:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:13 AM
Points: 22, Visits: 96
mark hempen (6/15/2012)
Also don't forget to check out what user account owns the stored procedures and the security role attached to that login. I have had many instances in the past where dbo priveleges were necessary and didn't exist.


Don't you mean the other way around? I wouldn't be adding dbo permissions if your application is working without them - it just creates an uncessary security hole.

I have seen a number of instances where dbo permissions were added as a quick work around for a permissions issue and never removed, so I agree that it is a good thing to check for. If you can spare the time to audit what permissions the login actually needs, it is by far preferable to work out how to remove dbo. It could even be essential depending on the security profile of the system.
Post #1316963
Posted Sunday, June 17, 2012 2:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 14, 2014 8:22 AM
Points: 2, Visits: 24
Nice article. But what I want to specify is a list of some 3rd party tools usage like
SQL Doc: for documenting the database.
Sybase Powerdesigner: for managing database as well as understanding schema/usage.
SQL Source Control Tool: for controlling schema as well as data.
and don't forget to use the SQL Compare/Data Compare tools to rapidly manage changes among different servers and/or identify any issue by comparing the different version of databases.
Post #1317030
Posted Sunday, June 17, 2012 11:52 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, December 15, 2014 3:02 AM
Points: 912, Visits: 1,500
Very nice little article from some years ago! Thanks.

I agree with others that just dropping obsolete objects is a dangerous task, especially when you just got the job and don't know the schema very well.

I would also add a prefix to these obsolete objects (like another user suggested) so they will be grouped together in the Object Explorer or when querying the database. Also if a table has an "EntryDate" column it'd be a little easier to know when it was last used or not.

Now if it's absolutely necessary to drop those objects (because of disk space or something) I'd script them using the SSMS' "Generate Scripts Wizard" including structure and data to files so I could easily restore them if I delete something that shouldn't. If a database is not so big, I just save a private COPY_ONLY backup to a special place (or two) instead.

When there are humans involved there's always the risk of something going terribly wrong.


Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Post #1317067
Posted Monday, June 18, 2012 4:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 2, 2012 12:26 AM
Points: 5, Visits: 11
I agree with everything except the Source Code... I really don't bother.

What I find important to add is:
- Document => we keep a template which we can complete with the most important things that we need to know about the system (eg: backup, logins, known issues, etc)

- passwords and security in general, is it set up? Who has the passwords, what privileges and do they need it? Let's face it, if there's someone else running around with dba privs, then who is responsible for the DB?


All in all a good blog.

Cheers!
Jo
Post #1317251
« Prev Topic | Next Topic »

Add to briefcase «««56789»»

Permissions Expand / Collapse