During the normal day-to-day
work, we DBAs deal with a huge amount of information for the many purposes like
troubleshooting problems, collecting data, analyzing figures …etc.
In fact it is very useful to store
this information “or part of it by default” in a single repository, this simply
give the DBAs the opportunity to analyze the collected data and the can
generate some kind of figures, trends and also tracking.
This is the main idea behind
creating the “dbatoolsdb”, a database that holds this kind of information and
also divided into multiple schemas based on the section related to the
collected information, for example a schema with name “sec” is used to hold all
tables related to security information collected, “lck” schema can be sued then
to hold locking and blocking information …etc.
My first use for the dbatoolsdb
was a request for generating capacity reports for SQL server memory and storage
on specific group of servers for a specific period of time, simply this can be
handled by collecting this information on the target SQL servers and automating
this process by using SQL Agent jobs then storing it in database tables that
could be queried later and generating the required reports based on this stored
information.
In this post I will provide the
code for creating the dbatoolsdb database and its major used schemas, this
database will be used many times in the upcoming posts as a repository for all
information we will deal with.
SQL
Code:
Create Database
Create Database
USE [master]
GO
CREATE DATABASE dbatoolsdb ON
PRIMARY
( NAME = N'dbatoolsdb', FILENAME
= N'D:\SQL_DBs\dbatoolsdb.mdf' , SIZE = 40960KB , MAXSIZE = UNLIMITED, FILEGROWTH
= 10240KB )
LOG ON
( NAME = N'dbatoolsdb_log', FILENAME
= N'D:\SQL_DBs\dbatoolsdb_log.LDF' , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 10240KB )
GO
Create
Schemas
USE [DBAToolsDB]
GO
CREATE SCHEMA [sec]
GO
CREATE SCHEMA [trc]
GO
CREATE SCHEMA [lck]
GO
0 comments:
Post a Comment