(4 intermediate revisions by the same user not shown) Line 1:
Line 1: {{SQWareRepositoryPresSgbd|Mssql|d'une database|alias FreeTds}}
{{SQWareRepositoryPresSgbd|Mssql|database|alias FreeTds|msq }}
== Contenu de la database ==
== 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 pour serveur hébergeant FreeTds</nowiki>|
<pre>
/* 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
</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>Informations générales sur l'instance</nowiki>|
<pre>
/* 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
</pre>
}}
{{SQWareRepositoryTables|tsqw_VolDb|
{{SQWareRepositoryTables|tsqwmsq_DetailDb |
<nowiki>Volumétrie des databases</nowiki>|
<nowiki>... </nowiki>|
<pre>
<pre>
/* Volumétrie des databases */
Field Type
create table tsqw_VolDb (
gather_date datetime
Gather_date smalldatetime not null,
dbalias varchar(80)
Alias varchar(80) not null,
database_name varchar(255 )
Database_name varchar(80) not null,
size_db decimal(12 ,2)
Size_db int,
used decimal (12,2 )
Owner varchar(30),
free decimal (12,2 )
Options varchar(600)
log decimal (12 ,2 )
)
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>
</pre>
}}
}}
{{SQWareRepositoryTables|tsqw_Files|
{{SQWareRepositoryTables|tsqwmsq_Files |
<nowiki>Volumétrie des fichiers</nowiki>|
<nowiki>... </nowiki>|
<pre>
<pre>
/* Volumétrie des fichiers */
Field Type
create table tsqw_Files (
gather_date datetime
Gather_date smalldatetime not null,
dbalias varchar(80)
Alias varchar(80) not null,
database_name varchar(255)
Database_name [sysname] NOT NULL,
group_name varchar (128)
group_name nvarchar(128) null,
logical_file_name varchar(128)
logical_file_name varchar(128),
physical_file_name varchar(256)
physical_file_name varchar(256),
file_type varchar(10)
[file_type] [varchar](10) NOT NULL,
growth_property varchar(30)
growth_property [varchar](30) NOT NULL,
maxsize_property varchar(30)
maxsize_property [varchar](30) NOT NULL,
maxsize bigint(20)
maxsize bigint null,
total decimal (12 ,2)
[total] [numeric](15, 2) NOT NULL,
used decimal (12 ,2)
[Used] [numeric](15, 2) NOT NULL,
free decimal (12 ,2)
[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>
</pre>
}}
}}
{{SQWareRepositoryTables|tsqw_GenHisto|
{{SQWareRepositoryTables|tsqwmsq_GenInfos |
<nowiki>Informations mensuelles</nowiki>|
<nowiki>... </nowiki>|
<pre>
<pre>
/* Informations mensuelles */
Field Type
create table tsqw_GenHisto (
gather_date datetime
Gather_date smalldatetime not null,
dbalias varchar(80)
Month smalldatetime not null,
nbdatabases int(11 )
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>
</pre>
}}
}}
{{SQWareRepositoryTables|tsqw_DetailDb|
{{SQWareRepositoryTables|tsqwmsq_Parameters |
<nowiki>détail des databases</nowiki>|
<nowiki>... </nowiki>|
<pre>
<pre>
/* détail des databases */
Field Type
create table tsqw_DetailDb (
dbalias varchar(80)
Gather_date smalldatetime not null,
gather_date datetime
Alias varchar(80) not null,
name varchar (255 )
Database_name [sysname] NOT NULL,
value varchar (255 )
[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>
</pre>
}}
}}
{{SQWareRepositoryTables|tsqw_VolDbThreshold|
{{SQWareRepositoryTables|tsqwmsq_RepositoryFreeTds |
<nowiki>Définition des seuils spécifiques pour les databases</nowiki>|
<nowiki>... </nowiki>|
<pre>
<pre>
/* Définition des seuils spécifiques pour les databases */
Field Type
create table tsqw_VolDbThreshold (
hostname varchar(255 )
Alias varchar(30) not null,
username varchar(255)
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>
</pre>
}}
}}
{{SQWareRepositoryTables|tsqw_VolDbThresholdDef|
{{SQWareRepositoryTables|tsqwmsq_VolDb |
<nowiki>Définition des seuils par défaut pour les databases</nowiki>|
<nowiki>... </nowiki>|
<pre>
<pre>
/* Définition des seuils par défaut pour les 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
size_db decimal (12 ,2 )
)
owner varchar (30 )
go
options varchar (4000 )
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>
</pre>
}}
}}
{{SQWareRepositoryTables|tsqw_AlertFile|
{{SQWareRepositoryTables|tsqwmsq_VolTables |
<nowiki>Alert log location</nowiki>|
<nowiki>... </nowiki>|
<pre>
<pre>
/* Alert log location*/
Field Type
create table tsqw_AlertFile (
dbalias varchar (80)
Alias varchar(30) not null,
gather_date datetime
Gather_date smalldatetime not null,
database_name varchar(255 )
Alert_file VARCHAR (800)
table_schema varchar(255)
)
name varchar (255 )
;
rows int(11 )
create unique clustered index isqw_AlertFile_u on tsqw_AlertFile(Alias)
reserved decimal(12,2)
with ignore_dup_key
data decimal (12,2 )
go
index_size decimal(12,2)
unused decimal(12,2)
grant select on tsqw_AlertFile to public
go
</pre>
</pre>
}}
}}
Presentation It is the repository module, and stores rdbms indicators for all RDBMS. 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 tsqwmsq_DetailDb This table contains :
...
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 :
...
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 :
...
Field Type
gather_date datetime
dbalias varchar(80)
nbdatabases int(11)
tsqwmsq_Parameters This table contains :
...
Field Type
dbalias varchar(80)
gather_date datetime
name varchar(255)
value varchar(255)
tsqwmsq_RepositoryFreeTds This table contains :
...
Field Type
hostname varchar(255)
username varchar(255)
tsqwmsq_VolDb This table contains :
...
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 :
...
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)
