Difference between revisions of "SQWareRepository:mssql"

From Wiki_dbSQWare
Jump to: navigation, search
 
(One intermediate revision by the same user not shown)
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|
+
{{SQWareRepositoryTables|tsqwmsq_DetailDb|
−
<nowiki>Specific dynamic threshold of databases</nowiki>|
+
<nowiki>...</nowiki>|
 
<pre>
 
<pre>
−
/* Specific dynamic threshold of databases */
+
Field Type
−
create table tsqw_VolDbThreshold (
+
gather_date datetime
−
Alias varchar(30) not null,
+
dbalias varchar(80)
−
Database_name varchar(255) not null,
+
database_name varchar(255)
−
Threshold_c int,
+
size_db decimal(12,2)
−
Threshold_w int
+
used decimal(12,2)
−
+
free decimal(12,2)
−
)
+
log decimal(12,2)
−
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>
 
</pre>
 
}}
 
}}
  
−
{{SQWareRepositoryTables|tsqw_VolDbThresholdDef|
+
{{SQWareRepositoryTables|tsqwmsq_Files|
−
<nowiki>Default dynamic threshold of databases</nowiki>|
+
<nowiki>...</nowiki>|
 
<pre>
 
<pre>
−
/* Default dynamic threshold of databases */
+
Field Type
−
create table tsqw_VolDbThresholdDef (
+
gather_date datetime
−
Size_db int,
+
dbalias varchar(80)
−
Threshold_c int,
+
database_name varchar(255)
−
Threshold_w int
+
group_name varchar(128)
−
)
+
logical_file_name varchar(128)
−
go
+
physical_file_name varchar(256)
−
create unique clustered index isqw_VolDbThresholdDef_u on tsqw_VolDbThresholdDef(Size_db)
+
file_type varchar(10)
−
with ignore_dup_key
+
growth_property varchar(30)
−
go
+
maxsize_property varchar(30)
−
 
+
maxsize bigint(20)
−
grant select on tsqw_VolDbThresholdDef to public
+
total decimal(12,2)
−
go
+
used decimal(12,2)
−
 
+
free decimal(12,2)
−
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>
 
</pre>
 
}}
 
}}
  
−
{{SQWareRepositoryTables|tsqw_AlertFile|
+
{{SQWareRepositoryTables|tsqwmsq_GenInfos|
−
<nowiki>Alert log location</nowiki>|
+
<nowiki>...</nowiki>|
 
<pre>
 
<pre>
−
/* Alert log location*/
+
Field Type
−
create table tsqw_AlertFile  (
+
gather_date datetime
−
Alias varchar(30) not null,
+
dbalias varchar(80)
−
Gather_date smalldatetime not null,
+
nbdatabases int(11)
−
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>
 
</pre>
 
}}
 
}}
  
−
{{SQWareRepositoryTables|tsqw_VolBackups|
+
{{SQWareRepositoryTables|tsqwmsq_Parameters|
−
<nowiki>Monitoring backups</nowiki>|
+
<nowiki>...</nowiki>|
 
<pre>
 
<pre>
−
create table tsqw_VolBackups (
+
Field Type
−
  Alias varchar(30) not null,
+
dbalias varchar(80)
−
  Database_name varchar(255) not null,
+
gather_date datetime
−
  Beginning smalldatetime  not null,
+
name varchar(255)
−
  Bck_type varchar(50)     not null,
+
value varchar(255)
−
  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>
 
</pre>
 
}}
 
}}
  
−
{{SQWareRepositoryTables|tsqw_VolBackupsExclude|
+
{{SQWareRepositoryTables|tsqwmsq_RepositoryFreeTds|
−
<nowiki>Global exclude for check backup</nowiki>|
+
<nowiki>...</nowiki>|
 
<pre>
 
<pre>
−
create table tsqw_VolBackupsExclude (
+
Field Type
−
  Alias varchar(30) not null,
+
hostname varchar(255)
−
  DbaName varchar(30) not null,
+
username varchar(255)
−
  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>
 
</pre>
 
}}
 
}}
  
−
 
+
{{SQWareRepositoryTables|tsqwmsq_VolDb|
−
{{SQWareRepositoryTables|tsqw_VolBackupsExcludeGlobalDb|
+
<nowiki>...</nowiki>|
−
<nowiki>Global exclude of database for check backup db</nowiki>|
 
 
<pre>
 
<pre>
−
create table tsqw_VolBackupsExcludeGlobalDb (
+
Field Type
−
  Database_name varchar(30)     not null,
+
gather_date datetime
−
  DbaName      varchar(50)     not null,
+
dbalias varchar(80)
−
  Comments      varchar(50)    not null,
+
database_name varchar(255)
−
  Upd_date      smalldatetime  default getdate()
+
size_db decimal(12,2)
−
)
+
owner varchar(30)
−
go
+
options varchar(4000)
−
 
 
−
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>
 
</pre>
 
}}
 
}}
  
−
{{SQWareRepositoryTables|tsqw_VolBackupsExcludeDb|
+
{{SQWareRepositoryTables|tsqwmsq_VolTables|
−
<nowiki>Specific exclude of database for check backup db</nowiki>|
+
<nowiki>...</nowiki>|
 
<pre>
 
<pre>
−
create table tsqw_VolBackupsExcludeDb (
+
Field Type
−
  DataServer    varchar(30)     not null,
+
dbalias varchar(80)
−
  Database_name varchar(30)     not null,
+
gather_date datetime
−
  DbaName      varchar(50)     not null,
+
database_name varchar(255)
−
  Comments      varchar(50)     not null,
+
table_schema varchar(255)
−
  Upd_date      smalldatetime  default getdate()
+
name varchar(255)
−
)
+
rows int(11)
−
go
+
reserved decimal(12,2)
−
 
+
data decimal(12,2)
−
create unique clustered index isqw_VolBackupsExcludeDb_u on tsqw_VolBackupsExcludeDb(DataServer,Database_name)
+
index_size decimal(12,2)
−
with ignore_dup_key
+
unused decimal(12,2)
−
go
 
−
 
 
−
grant select on tsqw_VolBackupsExcludeDb to public
 
−
go
 
 
</pre>
 
</pre>
 
}}
 
}}

Latest revision as of 19:12, 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


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.


Specific database content for Mssql

tsqwmsq_DetailDb

This table contains : ...
Desc of tsqwmsq_DetailDb :

Field Type
gather_date datetime
dbalias varchar(80)
database_name varchar(255)
size_db decimal(12,2)
used decimal(12,2)
free decimal(12,2)
log decimal(12,2)


tsqwmsq_Files

This table contains : ...
Desc of tsqwmsq_Files :

Field Type
gather_date datetime
dbalias varchar(80)
database_name varchar(255)
group_name varchar(128)
logical_file_name varchar(128)
physical_file_name varchar(256)
file_type varchar(10)
growth_property varchar(30)
maxsize_property varchar(30)
maxsize bigint(20)
total decimal(12,2)
used decimal(12,2)
free decimal(12,2)


tsqwmsq_GenInfos

This table contains : ...
Desc of tsqwmsq_GenInfos :

Field Type
gather_date datetime
dbalias varchar(80)
nbdatabases int(11)


tsqwmsq_Parameters

This table contains : ...
Desc of tsqwmsq_Parameters :

Field Type
dbalias varchar(80)
gather_date datetime
name varchar(255)
value varchar(255)


tsqwmsq_RepositoryFreeTds

This table contains : ...
Desc of tsqwmsq_RepositoryFreeTds :

Field Type
hostname varchar(255)
username varchar(255)


tsqwmsq_VolDb

This table contains : ...
Desc of tsqwmsq_VolDb :

Field Type
gather_date datetime
dbalias varchar(80)
database_name varchar(255)
size_db decimal(12,2)
owner varchar(30)
options varchar(4000)


tsqwmsq_VolTables

This table contains : ...
Desc of tsqwmsq_VolTables :

Field Type
dbalias varchar(80)
gather_date datetime
database_name varchar(255)
table_schema varchar(255)
name varchar(255)
rows int(11)
reserved decimal(12,2)
data decimal(12,2)
index_size decimal(12,2)
unused decimal(12,2)
Retrieved from "https://wiki.dbsqware.com/index.php?title=SQWareRepository:mssql&oldid=7265"