Managing SQL Server databases and applying meta data

Problem

I'm often asked how to convert a database that resides on the physical server/sql instance to their corresponding application name. This need arises when you are ready to schedule server downtime notifications, but this is also valuable when communicating with IT managers or non-technical people within my organization. If you are not a database administrator or an application analyst for a particular database, you will often disregard the naming conventions of the database, which support the applications you rely on every day. This is why it is important to provide transformations from the metabase at the appropriate location when the need arises.

Expert answers

Most database administrators have some form of database metabase that they rely on to track a wide range of Microsoft SQL Server environments. I use connected servers and distributed database access to build a metabase that has been in my environment for seven years. It's not beautiful, but it's very functional. Like many it developers and database administrators, even if it has its own shortcomings, I am still proud of my own creation. It's slow, it's not as new as it can be, and it's not as safe as it should be.

Since reading the article on SQL Server Integration Services (SSIS) and the database administrator Knowledge Base (DBA repositories) published by Rodney Landrum in SQL Server Magazine in May 2007 and June, I know it's time to take someone else's way. This is perfect for my environment, and some of the changes are easy to adopt. A follow-up article was published in SQL Server Magazine in February 2008, and in this article, Rodney updated his solution. I downloaded the code, reviewed it in my test environment, and quickly incorporated it into the product. When people are generally happy with what this solution provides, the missing aspect of its package is the ability to relate the database to the application. By adding two extra tables to his solution, I can add application meta data to my current SQL Server magazine method in my native metabase.

The application metadata added to my database includes creating two tables: dbo. Applications is designed to store the application names of all programs that depend on the SQL Server database and dbo in my environment. Database_applications, which holds the relationship between SQL instances, databases, and applications.

Applications Table

CREATE TABLE [dbo].[Applications]
(
[AppID] [int] IDENTITY(154,1) NOT NULL,
[ApplicationName] [varchar](100) NOT NULL,
)

Database_applications Table

CREATE TABLE [dbo].[Database_Applications]
(
[DB_AppID] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [varchar](50) NOT NULL,
[DatabaseName] [varchar](100) NOT NULL,
[ApplicationName] [varchar](100) NULL
)

You may notice that I did not normalize dbo. Database_applications table. If I normalize, I will store only two areas: a foreign key related to the table that stores my application metadata, and a foreign key corresponding to my metabase. I have my own reasons:

I'm not dealing with a lot of data: I have about 800 databases, and these databases are published in my environment in 80 instances. Although this is a great environment for a database administrator, it does not turn into a large number of records in my Meta data table, nor does it turn into a huge byte of the database.

Not through the dbo. Applications the primary key of the table, instead of the application name in the table, I can access the dbo only. The Database_applications table produces my main application metadata report (key application Metadata).

The SQL metabase in my environment uses the "scorched-earth policy" method of population processing, except for SQL Agent Job History and backup History, other tables are deleted and reload every day. I found in

Dbo. Saving information in the Database_applications table can make my life easy.