SQL Server database relationship to applications

The following article mainly introduces the SQL server database and its actual application metadata, I saw the SQL server database on the related website two days ago Rather than actually applying the metadata data, it feels good to share it with you, hoping to bring some help in this regard.

I'm often asked how to convert a SQL Server database that resides on a 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're not a data

I'm often asked how to convert a database that resides on a 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 meta SQL Server database that they rely on to track a wide range of Microsoft SQL Server Environment. 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 about SQL server Integration Services (SSIS) and databases published by Rodney Landrum in SQL Server Magazine in May 2007 and June Admin Knowledge Base (DBA repositories) article, I know it's time to take someone else's solution. 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, which is designed to store the application names of all programs that depend on the SQL Server database in my environment, and

Dbo. Database_applications, which holds the relationships between SQL instances,SQL Server databases , and applications.

Applications table CREATE table [dbo]. [Applications] (

[AppID] [INT] IDENTITY (154,1) not NULL,

[ApplicationName] [varchar] (MB) not NULL,)

Database_applications Table

CREATE TABLE [dbo]. [Database_applications] (

[Db_appid] [INT] IDENTITY (1,1) not NULL,

[ServerName] [varchar] () not NULL,

[DatabaseName] [varchar] (MB) Not NULL,

[ApplicationName] [varchar] (MB) 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 sql Server databases Publish 80 instances in my environment. 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.

After loading the data daily from my environment, I can get a good presentation of any new database generated in my environment with the following script.

SELECT d.[Server], d.databasename from dbo. Databases D left JOIN dbo. Database_applications da on d.databasename = da. DatabaseName and d.[Server] = DA. [ServerName] WHERE DA. Db_appid is NULL order by d.[Server], d.databasename The result of this query provides a list of any databases that SQL Server the database was generated the last time I updated the application metadata and server, it was not only a notification of a Cross-domain database creation activity, but also a list of data that was dedicated to updating two of databases to conform to application information. This query is also appropriate for the SQL server Reporting Services report datasheet, and when I'm not in the office,SQL server The Reporting Services report also provides me with a new database of daily notifications to my BlackBerry (BlackBerry).

Finally, I created the following stored program, which merges the dbo with any new database information. Applications tables and Dbo.database_applications tables. It accepts three parameters: servers, databases , and applications. If the application no longer exists in dbo. Applications The table, it will be replenished. A record is then inserted into the DBO in the server/ database /application relationship. Applications table.

CREATE PROCEDURE [dbo]. [Padd_application]

@ServerName varchar (50),

@DatabaseName varchar (100),

@ApplicationName varchar (100)

As--add any new databases created,

But not recorded in the repository, to the repository

UPDATE dbo. Database_applications

SET ApplicationName = @ApplicationName

WHERE Servername = @Servername

and DatabaseName = @DatabaseName

and ApplicationName is NULL

--determine If there is already a application

For this database in the repository, if not, then add it

IF (SELECT COUNT (*) from dbo. Applications

WHERE ApplicationName = @ApplicationName) = 0

BEGIN INSERT into dbo. Applications (ApplicationName)

VALUES (@ApplicationName)

PRINT ' Added new application: '

+ @ApplicationName + ' to applications table '

SELECT * FROM dbo. Applications

WHERE ApplicationName = @ApplicationName

End--list the "new record" in the repository

SELECT ServerName, DatabaseName, ApplicationName

FROM dbo. Database_applications

WHERE Servername = @Servername

and DatabaseName = @DatabaseName

and ApplicationName = @ApplicationName

Although I can easily consolidate the execution of this stored program into the final step in the SQL Server Integration Services (SSIS) package, which can assemble my storage database , I choose not to do so, This is so that in my environment, I can pay close attention to the activities surrounding the creation of the new SQL Server database .