Difference between revisions of "SQWareRepository:mssql"

From Wiki_dbSQWare
Jump to: navigation, search
Line 268: Line 268:
 
go
 
go
  
 +
</pre>
 +
}}
 +
 +
{{SQWareRepositoryTables|tsqw_VolBackups|
 +
<nowiki>Table de suivi des backups</nowiki>|
 +
<pre>
 +
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
 +
</pre>
 +
}}
 +
 +
{{SQWareRepositoryTables|tsqw_VolBackupsExclude|
 +
<nowiki>Exclusion globale de l'instance pour le check backup</nowiki>|
 +
<pre>
 +
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
 +
</pre>
 +
}}
 +
 +
 +
{{SQWareRepositoryTables|tsqw_VolBackupsExcludeGlobalDb|
 +
<nowiki>Exclusion globale de database pour le check backup</nowiki>|
 +
<pre>
 +
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
 +
</pre>
 +
}}
 +
 +
{{SQWareRepositoryTables|tsqw_VolBackupsExcludeDb|
 +
<nowiki>Exclusion spécifique de database pour le check backup</nowiki>|
 +
<pre>
 +
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
 
</pre>
 
</pre>
 
}}
 
}}

Revision as of 08:27, 10 November 2010

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


Nuvola apps important.png
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"