SQWareRepository:mssql: Difference between revisions

From Wiki_dbSQWare
Jump to navigation Jump to search
← Older editNewer edit →
No edit summary
Replaced content with '{{SQWareRepositoryPresSgbd|Mssql|database|alias FreeTds|msq}} == Specific database content for Mssql =='
Line 1: Line 1:
{{SQWareRepositoryPresSgbd|Mssql|database|alias FreeTds|msq}}
{{SQWareRepositoryPresSgbd|Mssql|database|alias FreeTds|msq}}
== Database content ==
== Specific database content for Mssql ==
{{SQWareRepositoryTables|tsqw_Repository|
<nowiki>Repository</nowiki>|
<pre>
/* 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
 
</pre>
}}
 
{{SQWareRepositoryTables|tsqw_RepositoryFreeTds|
<nowiki>Repository for host with FreeTds</nowiki>|
<pre>
/* 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
 
</pre>
}}
 
{{SQWareRepositoryTables|tsqw_JobsExec|
<nowiki>Jobs monitoring</nowiki>|
<pre>
/* 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
 
</pre>
}}
 
{{SQWareRepositoryTables|tsqw_GenInfos|
<nowiki>General informations of instance</nowiki>|
<pre>
/* 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
 
</pre>
}}
 
{{SQWareRepositoryTables|tsqw_VolDb|
<nowiki>Volumetry of databases</nowiki>|
<pre>
/* 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
 
</pre>
}}
 
{{SQWareRepositoryTables|tsqw_Files|
<nowiki>Volumetry of files</nowiki>|
<pre>
/* 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
 
</pre>
}}
 
{{SQWareRepositoryTables|tsqw_GenHisto|
<nowiki>Monthly informations</nowiki>|
<pre>
/* 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
 
</pre>
}}
 
{{SQWareRepositoryTables|tsqw_DetailDb|
<nowiki>Detail databases</nowiki>|
<pre>
/* 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
 
</pre>
}}
 
{{SQWareRepositoryTables|tsqw_VolDbThreshold|
<nowiki>Specific dynamic threshold of databases</nowiki>|
<pre>
/* 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
 
</pre>
}}
 
{{SQWareRepositoryTables|tsqw_VolDbThresholdDef|
<nowiki>Default dynamic threshold of databases</nowiki>|
<pre>
/* 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
 
</pre>
}}
 
{{SQWareRepositoryTables|tsqw_AlertFile|
<nowiki>Alert log location</nowiki>|
<pre>
/* 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
 
</pre>
}}
 
{{SQWareRepositoryTables|tsqw_VolBackups|
<nowiki>Monitoring 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>Global exclude for 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>Global exclude of database for check backup db</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>Specific exclude of database for check backup db</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>
}}

Revision as of 19:04, 8 August 2015

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


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.


Specific database content for Mssql

Retrieved from "https://wiki.dbsqware.com/index.php?title=SQWareRepository:mssql&oldid=7255"