SQWareRepository:mssql

From Wiki_dbSQWare
Revision as of 15:04, 8 August 2015 by Expdbtools (talk | contribs)
Jump to: navigation, search

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 tsqwmsq_% or isqwmsq_% are specific for Mssql.
Installation guide for SQWareRepository for Mssql

Naming convention

All specific objects for Mssql are named with prefixes:

  • tsqwmsq_% : for tables
  • isqwmsq_% : for index
  • isqwmsq_%_u : for uniques
  • isqwmsq_%_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.


Database content

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 for host with FreeTds
Desc of tsqw_RepositoryFreeTds :

/* Repository for host with 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 : General informations of instance
Desc of tsqw_GenInfos :

/* General informations of 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 : Volumetry of databases
Desc of tsqw_VolDb :

/* Volumetry of 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 : Volumetry of files
Desc of tsqw_Files :

/* Volumetry of files */
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 : Monthly informations
Desc of tsqw_GenHisto :

/* Monthly informations */
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 : Detail databases
Desc of tsqw_DetailDb :

/* detail 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 : Specific dynamic threshold of databases
Desc of tsqw_VolDbThreshold :

/* Specific dynamic threshold of 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 : Default dynamic threshold of databases
Desc of tsqw_VolDbThresholdDef :

/* Default dynamic threshold of 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 : Monitoring 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 : Global exclude for 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 : Global exclude of database for check backup db
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 : Specific exclude of database for check backup db
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=7241"