SQWareRepository:mssql

From Wiki_dbSQWare
Revision as of 09:27, 10 November 2010 by Mpayan (talk | contribs)
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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


Warning:
Do not change the standard tables of the tool. For your custom objects, use another name convention than dbSQWare.

This will facilitate version upgrades.


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


tsqw_VolBackups

This table contains : Table de suivi des backups
Desc of tsqw_VolBackups :

create table tsqw_VolBackups (
  Alias		varchar(30)	not null,
  Database_name		varchar(255)	not null,
  Beginning		smalldatetime   not null,
  Bck_type		varchar(50)     not null,
  End_trt       smalldatetime   not null,
  Duration		char(8)         not null,
  Size_bck		int default 0
)
go

create unique clustered index isqw_VolBackups_u on tsqw_VolBackups(Alias, Database_name, Beginning)
with ignore_dup_key
go

grant select on tsqw_VolBackups to public
go


tsqw_VolBackupsExclude

This table contains : Exclusion globale de l'instance pour le check backup
Desc of tsqw_VolBackupsExclude :

create table tsqw_VolBackupsExclude (
  Alias			varchar(30)	not null,
  DbaName		varchar(30)	not null,
  Comments      varchar(50)     not null,
  Upd_date      smalldatetime   default getdate()
)
go

create unique clustered index isqw_VolBackupsExclude_u on tsqw_VolBackupsExclude(Alias)
with ignore_dup_key
go

grant select on tsqw_VolBackupsExclude to public
go


tsqw_VolBackupsExcludeGlobalDb

This table contains : Exclusion globale de database pour le check backup
Desc of tsqw_VolBackupsExcludeGlobalDb :

create table tsqw_VolBackupsExcludeGlobalDb (
  Database_name	varchar(30)     not null,
  DbaName       varchar(50)     not null,
  Comments      varchar(50)     not null,
  Upd_date      smalldatetime   default getdate()
)
go

create unique clustered index isqw_VolBackupsExcludeGlobalDb_u on tsqw_VolBackupsExcludeGlobalDb(Database_name)
with ignore_dup_key
go

grant select on tsqw_VolBackupsExcludeGlobalDb to public
go

insert into tsqw_VolBackupsExcludeGlobalDb values ('tempdb%','MP','Init edt',getdate())
go


tsqw_VolBackupsExcludeDb

This table contains : Exclusion spécifique de database pour le check backup
Desc of tsqw_VolBackupsExcludeDb :

create table tsqw_VolBackupsExcludeDb (
  DataServer    varchar(30)     not null,
  Database_name	varchar(30)     not null,
  DbaName       varchar(50)     not null,
  Comments      varchar(50)     not null,
  Upd_date      smalldatetime   default getdate()
)
go

create unique clustered index isqw_VolBackupsExcludeDb_u on tsqw_VolBackupsExcludeDb(DataServer,Database_name)
with ignore_dup_key
go

grant select on tsqw_VolBackupsExcludeDb to public
go
Retrieved from "https://wiki.dbsqware.com/index.php?title=SQWareRepository:mssql&oldid=786"