live databases (MSSQL)

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?
LVL 3
pma111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jerry MillerCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.