SQL Server database and its actual application metadata introduction

The following article is mainly about the SQL Server database and its actual application of metadata, I saw the previous two days in the relevant Web site SQL Server database and the actual application of metadata data, feel very good, take out for everyone to share, hope will bring some help in this respect.

The following article is mainly about the SQL Server database and its actual application of metadata, I saw the previous two days in the relevant Web site SQL Server database and the actual application of metadata data, feel very good, take out for everyone to share, hope will bring some help in this respect.

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

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 meta SQL Server database 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, 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 results of this query provide a list of any databases that arose from the last time I updated the application metadata and server, not only as a notification of the creation of activities across domain databases, but also as a list of data that is dedicated to updating two of databases to conform to application information. This query is also suitable for the SQL Server Reporting Services report datasheet, and when I am not in the office, the SQL Server Reporting Services report also provides me with a new database to my BlackBerry (BlackBerry) 's Daily notice.

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,
@DatabaseName varchar,
@ApplicationName varchar
as--add any new da Tabases created,
but not recorded in the repository, to the repository
UPDATE dbo. Database_applications
SET ApplicationName = @ApplicationName
WHERE ServerName = @ServerName
and Databasenam E = @DatabaseName
and ApplicationName is NULL
--determine if there is already a application
for this Datab ASE 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 Se Rvername, DatabaseName, ApplicatioNname the
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 last step in the SQL Server Integration Services (SSIS) package, and this package was able to assemble my storage database, but I chose not to do it, and it was in my environment that I was able to keep a close eye around the new SQL Activities that are expanded by server database creation