|
|
| 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>
| |
| − | }}
| |
