live databases (MSSQL)

Posted on 2014-07-15
Last Modified: 2014-07-20
We are doing some risk assessment work on some older SQL Servers. There are a few instances that seem to be hosting databases that drive a number of differernt applications.

1) What potential risks are there here is storing databases from different applications on the same instance?
2) And can you think of any logic why an admin would set things up this way (typically cost cutting in this company!)
3) Is there any easy way to determine if these databases are live/being used? Where could you look for clues as to whether these databases are actively in use/being updated etc? Any queries which could help? Some may be orphaned and just never been removed.
4) Is there any risk in leaving old orphaned databases on a live database server?
Question by:pma111
    1 Comment
    LVL 18

    Accepted Solution

    As long as you have separate log in credentials for each application, there should be minimal security risk associated with having multiple databases on the same instance. That being said, your programmers  should also be following good practices like having an application ID that can only access a schema of stored procedures. This schema is the only area of the database where the application can interact with the database and all of the code uses stored procedures. Applications should never have full admin access to the database. If they need full access to function, they should probably be re-written.

    Cost cutting would most likely be the reason to set up this way. I believe that SQL licensing can be done on a per server basis, but you would have to check how your pricing is set up.

    Do you have application contacts for the databases? Reach out to them and see which of these are still necessary. With no contact information, you can look at any log tables in each database and see the most recent entries. There should be some sort of table at least capturing log in data for each application. If you know it isn't a mission critical application, you could disable log ins for the database and see if anyone screams. But I would be real sure that you aren't disrupting the business and that you have exhausted other avenues before disabling log ins. That could be a very bad resume generating event if you stop the business from running just 'to see' if the application was active. You should be pretty sure that it is a dead application before disabling log ins.

    Orphaned databases wouldn't cause any issues unless you are running out of disk space.

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now