SQWareRepository:mssql

Presentation

It is the repository module, and stores rdbms indicators for all RDBMS.
It is a MySQL database.
Objects prefix by tsqw_% or isqw_% are generic.
Objects prefix by tsqw{{{4}}}_% or isqw{{{4}}}_% are specific for Mssql.
Installation guide for SQWareRepository for Mssql

Naming convention

All specific objects for Mssql are named with prefixes:

• tsqw{{{4}}}_% : for tables
• isqw{{{4}}}_% : for index
• isqw{{{4}}}_%_u : for uniques
• isqw{{{4}}}_%_pk : for primary key

Contenu de la database

tsqw_Repository

This table contains : Repository
Desc of tsqw_Repository :

/* Repository */
create table tsqw_Repository  (
	HostName	varchar(80)	not null,
	Alias		varchar(80)	not null,
	Instance	varchar(80)	null,
  	Port           	integer not null,
  	Comments	    varchar(255),
  	Contact         varchar(100),
  	Status          varchar(10) default 'ON',
  	Client          varchar(60) default 'N.A',
  	Upd_date        smalldatetime default getdate(),
  	Env				char(3)
)
go

create unique clustered index isqw_Repository_u on tsqw_Repository(Alias)
with ignore_dup_key
go

grant select on tsqw_Repository to public
go

tsqw_RepositoryFreeTds

This table contains : Repository pour serveur hébergeant FreeTds
Desc of tsqw_RepositoryFreeTds :

/* Repository pour serveur hébergeant FreeTds */
create table tsqw_RepositoryFreeTds  (
	HostName		varchar(255)	not null,
	UserName		varchar(255)	not null
)
go

create unique clustered index isqw_RepositoryFreeTds_u on tsqw_RepositoryFreeTds(HostName,UserName)
with ignore_dup_key
go

grant select on tsqw_RepositoryFreeTds to public
go

tsqw_JobsExec

This table contains : Jobs monitoring
Desc of tsqw_JobsExec :

/* Jobs monitoring */
create table tsqw_JobsExec (
	Alias		varchar(80)	not null,
	Beginning	datetime       	not null,
	Script		varchar(80)	not null,
	Parameters	varchar(400)	not null,
	End_trt		datetime	null,
	Duration	char(8)        	null,
	Status		smallint       	null
	)
go

create unique clustered index isqw_JobsExec_u on tsqw_JobsExec(Alias, Beginning)
go

grant select on tsqw_JobsExec to public
go

tsqw_GenInfos

This table contains : Informations générales sur l'instance
Desc of tsqw_GenInfos :

/* Informations générales sur l'instance */
create table tsqw_GenInfos (
	Gather_date		smalldatetime	not null,
	Alias		varchar(80)	not null,
	Size_ds		int,
	Version 	varchar(35),
	Patch 		varchar(80),
	NbDatabases		int
)
go

create unique clustered index isqw_GenInfos_u on tsqw_GenInfos(Alias, Gather_date)
with ignore_dup_key
go

grant select on tsqw_GenInfos to public
go

tsqw_VolDb

This table contains : Volumétrie des databases
Desc of tsqw_VolDb :

/* Volumétrie des databases */
create table tsqw_VolDb (
	Gather_date		smalldatetime	not null,
	Alias			varchar(80)	not null,
	Database_name 	varchar(80)	not null,
	Size_db			int,
	Owner 			varchar(30),
	Options 		varchar(600)
)
go

create unique clustered index isqw_VolDb_u on tsqw_VolDb(Alias, Database_name, Gather_date)
with ignore_dup_key
go

grant select on tsqw_VolDb to public
go

tsqw_Files

This table contains : Volumétrie des fichiers
Desc of tsqw_Files :

/* Volumétrie des fichiers */
create table tsqw_Files (
	Gather_date		smalldatetime	not null,
	Alias		varchar(80)	not null,
	Database_name [sysname] NOT NULL,
	group_name 		nvarchar(128) null, 
	logical_file_name 	varchar(128), 
	physical_file_name 	varchar(256), 
	[file_type] [varchar](10) NOT NULL,
	growth_property [varchar](30) NOT NULL,
	maxsize_property [varchar](30) NOT NULL,
	maxsize bigint null,
	[total] [numeric](15, 2) NOT NULL,
	[Used] [numeric](15, 2) NOT NULL,
	[free] [numeric](15, 2) NOT NULL
)
go

create unique clustered index isqw_Files_u on tsqw_Files(Alias, Database_name, Gather_date, group_name, logical_file_name)
with ignore_dup_key
go

grant select on tsqw_Files to public
go

tsqw_GenHisto

This table contains : Informations mensuelles
Desc of tsqw_GenHisto :

/* Informations mensuelles */
create table tsqw_GenHisto (
	Gather_date		smalldatetime	not null,
	Month		smalldatetime	not null,
	Alias		varchar(80)	not null,
	Size_ds		int,
	Version 	varchar(35),
  	Client 		varchar(60) default 'N.A',
  	Env			char(3)
)
go

create unique clustered index isqw_GenHisto_u on tsqw_GenHisto(Alias, Month)
with ignore_dup_key
go

grant select on tsqw_GenHisto to public
go

tsqw_DetailDb

This table contains : détail des databases
Desc of tsqw_DetailDb :

/* détail des databases */
create table tsqw_DetailDb (
	Gather_date		smalldatetime	not null,
	Alias		varchar(80)	not null,
	Database_name [sysname] NOT NULL,
	[Size] [numeric](15, 2) NOT NULL,
	[Used] [numeric](15, 2) NOT NULL,
	[Free] [numeric](15, 2) NOT NULL,
	[Log] [numeric](15, 2) NOT NULL
)
go

create unique clustered index isqw_DetailDb_u on tsqw_DetailDb(Alias, Database_name, Gather_date)
with ignore_dup_key
go

grant select on tsqw_DetailDb to public
go

tsqw_VolDbThreshold

This table contains : Définition des seuils spécifiques pour les databases
Desc of tsqw_VolDbThreshold :

/* Définition des seuils spécifiques pour les databases */
create table tsqw_VolDbThreshold (
	Alias		varchar(30)	not null,
	Database_name		varchar(255)	not null,
	Threshold_c		int,
	Threshold_w		int
	
)
go
create unique clustered index isqw_VolDbThreshold_u on tsqw_VolDbThreshold(Alias, Database_name)
with ignore_dup_key
go

grant select on tsqw_VolDbThreshold to public
go

tsqw_VolDbThresholdDef

This table contains : Définition des seuils par défaut pour les databases
Desc of tsqw_VolDbThresholdDef :

/* Définition des seuils par défaut pour les databases */
create table tsqw_VolDbThresholdDef (
	Size_db		int,
	Threshold_c		int,
	Threshold_w		int
)
go
create unique clustered index isqw_VolDbThresholdDef_u on tsqw_VolDbThresholdDef(Size_db)
with ignore_dup_key
go

grant select on tsqw_VolDbThresholdDef to public
go

insert into tsqw_VolDbThresholdDef (Size_db,Threshold_c,Threshold_w) values (2048, 90, 80)
insert into tsqw_VolDbThresholdDef (Size_db,Threshold_c,Threshold_w) values (20480, 92, 85)
insert into tsqw_VolDbThresholdDef (Size_db,Threshold_c,Threshold_w) values (51200, 95, 90)
insert into tsqw_VolDbThresholdDef (Size_db,Threshold_c,Threshold_w) values (102400, 96, 94)
insert into tsqw_VolDbThresholdDef (Size_db,Threshold_c,Threshold_w) values (102400000, 98, 96)
go

tsqw_AlertFile

This table contains : Alert log location
Desc of tsqw_AlertFile :

/* Alert log location*/
create table tsqw_AlertFile  (
 Alias			varchar(30)	not null,
 Gather_date	smalldatetime	not null,
 Alert_file		VARCHAR (800)
)
;
create unique clustered index isqw_AlertFile_u on tsqw_AlertFile(Alias)
with ignore_dup_key
go

grant select on tsqw_AlertFile to public
go