Sqldotproject.files.wordpress.com
[pic]
Table of Contents
Table of Contents 2
1. Introduction: 4
System Architecture Diagram: 5
User Interface 6
Global.asax File 6
Web.config file 8
Site.Master 9
Site2.Master 11
About.aspx 14
Contacts.Aspx 15
Home.Aspx 16
Input.Aspx 17
Site.css 18
Sqldashboard.aspx 23
Xymon.aspx 24
Details.aspx 25
Edit.Aspx 27
Insert.Aspx 29
List.Aspx 32
ListDetails.Aspx 34
2. SQLDOT Database 37
3. Powershell Scripts: 60
4. Views: 81
5. Powershell Script Files: 147
6. Reports: 169
7. Known Issues 170
8. Potential Enhancements 171
Introduction:
The purpose of this document is to hand over all the source code associated with our project so that if someone wants to investigate our ideas or indeed make changes or futher develop the application, they will have a document to help them to do this. Below is a diagram to give a quick picture of how the system fits together.
[pic]
The types of technologies we have used fit into our licensing and support frameworks already in place in our organisation. The operating system of choice was therefore Microsoft windows 2008 R2 server and the flavour of database was Microsoft sql 2008 R1. Because we were using the Microsoft operating systems we decided to go with IIS 7.0 and use to develop our web front end. We also decided to use Microsoft sql reporting services to provide our reporting needs as we were fully licensed to use this product. There were other elements of information that we needed to provide to our database that required a level of automation, so to perform these tasks we used a combination of powershell scripts and sql code. Because the data we are querying and reporting on is of a sensitive nature, the environment will be held internally with no public facing element. To give a complete overview see the system architecture diagram on the next page.
System Architecture Diagram:
[pic]
User Interface
Global.asax File
Imports System.Web.Routing
Imports System.Web.DynamicData
Imports System.Web.UI
Imports System.Data.Entity.Infrastructure
Public Class Global_asax
Inherits HttpApplication
Private Shared s_defaultModel As New MetaModel
Public Shared ReadOnly Property DefaultModel() As MetaModel
Get
Return s_defaultModel
End Get
End Property
Public Shared Sub RegisterRoutes(ByVal routes As RouteCollection)
' IMPORTANT: DATA MODEL REGISTRATION
' Uncomment this line to register an Entity Framework model for Dynamic Data.
' Set ScaffoldAllTables = true only if you are sure that you want all tables in the
' data model to support a scaffold (i.e. templates) view. To control scaffolding for
' individual tables, create a partial class for the table and apply the
' attribute to the partial class.
' Note: Make sure that you change "YourDataContextType" to the name of the data context
' class in your application.
' See for more information on how to register Entity Data Model with Dynamic Data
DefaultModel.RegisterContext( _
New System.Func(Of Object)(Function() DirectCast(New SQLDOTEntities(), IObjectContextAdapter).ObjectContext), _
New ContextConfiguration() With {.ScaffoldAllTables = True} _
)
' The following registration should be used if YourDataContextType does not derive from DbContext
' DefaultModel.RegisterContext(GetType(YourDataContextType), New ContextConfiguration() With {.ScaffoldAllTables = False})
' The following statement supports separate-page mode, where the List, Detail, Insert, and
' Update tasks are performed by using separate pages. To enable this mode, uncomment the following
' route definition, and comment out the route definitions in the combined-page mode section that follows.
routes.Add(New DynamicDataRoute("{table}/{action}.aspx") With {
.Constraints = New RouteValueDictionary(New With {.Action = "List|Details|Edit|Insert"}),
.Model = DefaultModel})
' The following statements support combined-page mode, where the List, Detail, Insert, and
' Update tasks are performed by using the same page. To enable this mode, uncomment the
' following routes and comment out the route definition in the separate-page mode section above.
'routes.Add(New DynamicDataRoute("{table}/ListDetails.aspx") With {
' .Action = PageAction.List,
' .ViewName = "ListDetails",
' .Model = DefaultModel})
'routes.Add(New DynamicDataRoute("{table}/ListDetails.aspx") With {
' .Action = PageAction.Details,
' .ViewName = "ListDetails",
' .Model = DefaultModel})
End Sub
Private Shared Sub RegisterScripts()
ScriptManager.ScriptResourceMapping.AddDefinition("jquery", New ScriptResourceDefinition With {
.Path = "~/Scripts/jquery-1.7.1.min.js",
.DebugPath = "~/Scripts/jquery-1.7.1.js",
.CdnPath = "",
.CdnDebugPath = "",
.CdnSupportsSecureConnection = True,
.LoadSuccessExpression = "window.jQuery"
})
End Sub
Private Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
RegisterRoutes(RouteTable.Routes)
RegisterScripts()
End Sub
End Class
Web.config file
Site.Master
SqlDot Admin
SqlDoT Data Pages
Back to SQLDOT home-page
Site2.Master
SQL DoT - SQL Application Documentation Tool
About.aspx
About Page
The SQLDoT application provides a documentation resource for the 200+ SQL databases of Dublin City Council's SQL applications. It pulls all the essential information regarding these applications into a central location, accessible by relevant IS Department personnel.
We hope to extend this application to cater for all Dublin City Council applications in time. There has also been substantial interest expressed by other Local Authorities in SQLDoT.
Contacts.Aspx
Contacts Page
SQLDOT Team
Adam Murphy 01-2225466 adamm.murphy@dublincity.ie
Pat Roche 01-2222317 pat.roche@dublincity.ie
Eileen Dennan 01-2225466 eileen.dennan@dublincity.ie
General Information on SQLDoT :- sqldot@dublincity.ie
Postal Address
SQLDoT Team,
IS Department,
Dublin City Council,
Civic Offices,
Christchurch,
Dublin 8
Home.Aspx
SQLDoT Home-Page
Input.Aspx
My tables
Site.css
/* Global classes */
.DDMainHeader
{
font: small-caps bold 1.6em Trebuchet MS, Arial, sans-serif;
color:#0000FF;
padding-bottom: 12px;
border-bottom: 1px dotted #bbbbbb;
}
.DDSubHeader
{
margin-bottom: 10px;
font:1.2em Trebuchet MS, Arial, sans-serif;
color: #000000;
}
.DD
{
padding: 0px 8px;
font: .75em Tahoma, Arial, sans-serif;
color: #666;
}
.DDLightHeader
{
font-weight: bold;
background-color: #F7F7FF;
}
.DDTextBox, .DDDropDown
{
color: #666;
font: .95em Tahoma, Arial, Sans-Serif;
border: solid 1px #dcdcdc;
background-color: #FEFEFE;
}
.DDDropDown
{
padding:2px;
}
.DDFilter
{
font: .95em Tahoma, Arial, Sans-serif;
color:#666;
}
.DDControl
{
padding: 0px;
font: .95em Tahoma, Arial, Sans-Serif;
color: #666;
}
.DDFloatLeft
{
float:left;
}
.DDFloatRight
{
float:right;
}
.DDFooter
{
background-color: #dbddff;
}
.DDSelected
{
background-color: #fdffb8;
}
div.DDNoItem
{
color:Red;
font-family:Trebuchet MS, Arial, sans-serif;
}
.DDValidator
{
color:Red;
font-family:Trebuchet MS, Arial, sans-serif;
}
/* Navigation */
div.DDNavigation
{
font:.75em Tahoma, Arial, Sans-Serif;
position: relative;
top: -1px;
margin-bottom: -10px;
padding: 0px 5px;
left: 3px;
height: 39px;
}
div.DDNavigation img
{
position: relative;
margin-top: 0em;
padding: 0px 8px;
border: none;
background-color: #fff;
}
div.DDNavigation a
{
color: #839ce7;
text-decoration: none;
background-color: #fff;
padding-right: 8px;
top: -.2em;
left: .25em;
}
div.DDNavigation a:hover
{
color: #000099;
text-decoration: underline !important;
}
/* Details VIEW/EDIT */
table.DDDetailsTable
{
color: #666;
background-color: #FCFDFE;
border: 1px solid #dbddff;
border-bottom-width: 3px;
}
table.DDDetailsTable a
{
color:#718ABE;
text-decoration: none;
padding-right: 6px;
}
table.DDDetailsTable a:hover
{
color: #000099;
text-decoration: underline !important;
}
table.DDDetailsTable .td
{
padding: 3px 6px;
border: 1px solid #dbddff;
border-bottom-style: dotted;
font-size: .7em;
font-family:Tahoma, Arial, Sans-Serif;
}
/* DDGridView/DDListView */
table.DDGridView, table.DDListView
{
width: 100%;
color: #666;
border:solid 1px #dbddff;
font: .7em Tahoma, Arial, Sans-Serif;
}
table.DDGridView .th, table.DDGridView .td, table.DDListView .th, table.DDListView .td
{
white-space: nowrap;
}
table.DDGridView .th, table.DDListView .th
{
background: url(DynamicData/Content/Images/header_back.gif) repeat-x;
line-height:1.3em;
text-align: left;
font-size: 1em;
border:solid 1px #dbddff;
}
table.DDGridView .th a, table.DDListView .th a
{
color: #666;
text-decoration: none;
}
table.DDGridView .td, table.DDListView .td
{
border: dotted 1px #dbddff;
border-left-width:0px;
border-right-width:0px;
padding: 6px;
}
table.DDGridView .td a, table.DDListView .td a
{
color: #718ABE;
text-decoration: none;
margin-right: 6px;
}
table.DDGridView .DDSelected a, table.DDListView .DDSelected a
{
color: #718ABE;
text-decoration: none;
margin-right: 6px;
}
table.DDGridView .DDSelected a:hover, table.DDListView .DDSelected a:hover
{
color: #000099;
text-decoration: underline;
}
table.DDGridView .td a:hover, table.DDListView .td a:hover
{
color: #000099;
text-decoration: underline;
}
/* DataPager */
div.DDPager
{
padding: 0px 6px;
}
/* Bottom hyperlink design*/
div.DDBottomHyperLink
{
font: .75em Tahoma, Arial, Sans-Serif;
}
div.DDBottomHyperLink a
{
color: #839ce7;
text-decoration: none;
}
div.DDBottomHyperLink a:hover
{
color: #000099;
text-decoration: underline !important;
}
div.DDBottomHyperLink img
{
padding-right: 5px;
border: none;
}
Sqldashboard.aspx
SQL Dashboard
Xymon.aspx
XYMON Page
App Servers
SQL Servers
Dynamic Data – Page Templates
Details.aspx
Entry from table
No such item.
Show all items
Edit.Aspx
Edit entry from table
No such item.
Insert.Aspx
Edit entry from table
No such item.
Add new entry to table
List.Aspx
There are currently no items in this table.
Insert new item
ListDetails.Aspx
There are currently no items in this table.
SQLDOT Database
1. Create MS Database SQL called SQLDOT
2. Run Script below to create tables – Script files also attached in secondary table below
|USE [SQLDOT] |
|GO |
|/****** Object: Table [dbo].[Service_Company] Script Date: 08/20/2014 11:50:15 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|SET ANSI_PADDING ON |
|GO |
|CREATE TABLE [dbo].[Service_Company]( |
|[Company_ID] [int] IDENTITY(1,1) NOT NULL, |
|[Company_Name] [varchar](50) NOT NULL, |
|[Company_Department] [varchar](50) NULL, |
|[Company_AdsLn1] [varchar](100) NOT NULL, |
|[Company_AdsLn2] [varchar](100) NULL, |
|[Company_TownCity] [varchar](50) NULL, |
|[Company_County] [varchar](50) NULL, |
|[Company_Country] [varchar](50) NULL, |
|[Company_PostCode] [varchar](10) NULL, |
|[Company_MainTel] [varchar](50) NOT NULL, |
|[Company_MainEmail] [varchar](255) NOT NULL, |
|[Company_SuppTel] [varchar](50) NULL, |
|[Company_SuppEmail] [varchar](255) NULL, |
|[Company_Website] [varchar](255) NULL, |
|[Company_Notes] [varchar](max) NULL, |
|CONSTRAINT [PK_Service_Company] PRIMARY KEY CLUSTERED |
|( |
|[Company_ID] ASC |
|)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], |
|CONSTRAINT [UNIQUE_Service_Company_Name] UNIQUE NONCLUSTERED |
|( |
|[Company_Name] ASC |
|)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] |
|) ON [PRIMARY] |
|GO |
|SET ANSI_PADDING OFF |
|GO |
|/****** Object: Table [dbo].[PS_SQLVersionStrings] Script Date: 08/20/2014 11:50:15 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|SET ANSI_PADDING ON |
|GO |
|CREATE TABLE [dbo].[PS_SQLVersionStrings]( |
|[WindowsServerName] [char](40) NULL, |
|[SQLInstanceName] [char](40) NULL, |
|[SQLProductVersion] [char](40) NULL, |
|[SQLProductEdition] [char](40) NULL, |
|[SQLVersionNum] [char](40) NULL, |
|[SQLVersionMinor] [char](5) NULL, |
|[SQLVersionMajor] [char](5) NULL, |
|[SQLVersionBuild] [char](10) NULL, |
|[SQLProductLevel] [char](40) NULL, |
|[SQLInstanceRoot] [char](100) NULL, |
|[SampleDateTime] [datetime] NULL, |
|[TotalServerMemKB] [decimal](18, 0) NULL, |
|CONSTRAINT [IX_SQLVersionStrings] UNIQUE NONCLUSTERED |
|( |
|[WindowsServerName] ASC, |
|[SQLInstanceName] ASC, |
|[SampleDateTime] ASC |
|)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] |
|) ON [PRIMARY] |
|GO |
|SET ANSI_PADDING OFF |
|GO |
|/****** Object: Table [dbo].[PS_SQLPhysicalFilesUsed] Script Date: 08/20/2014 11:50:15 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|SET ANSI_PADDING ON |
|GO |
|CREATE TABLE [dbo].[PS_SQLPhysicalFilesUsed]( |
|[WindowsServerName] [char](40) NULL, |
|[SQLInstanceName] [char](40) NULL, |
|[SampleDateTime] [datetime] NOT NULL, |
|[DatabaseName] [nchar](128) NULL, |
|[Data_File_Size_MB] [numeric](28, 7) NULL, |
|[Log_File_Size_MB] [numeric](28, 7) NULL, |
|[Log_File_Used_MB] [numeric](28, 7) NULL, |
|[Log_File_Used_Percent] [bigint] NULL |
|) ON [PRIMARY] |
|GO |
|SET ANSI_PADDING OFF |
|GO |
|/****** Object: Table [dbo].[PS_SQLPhysicalFiles] Script Date: 08/20/2014 11:50:15 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|SET ANSI_PADDING ON |
|GO |
|CREATE TABLE [dbo].[PS_SQLPhysicalFiles]( |
|[WindowsServerName] [char](40) NULL, |
|[SQLInstanceName] [char](40) NULL, |
|[SampleDateTime] [datetime] NOT NULL, |
|[databaseName] [nvarchar](128) NULL, |
|[FileName] [sysname] NOT NULL, |
|[physical_name] [nvarchar](260) NOT NULL, |
|[type_desc] [nvarchar](60) NULL, |
|[state_desc] [nvarchar](60) NULL, |
|[size] [int] NOT NULL, |
|[max_size] [int] NOT NULL, |
|[growth] [int] NOT NULL, |
|[is_percent_growth] [bit] NOT NULL |
|) ON [PRIMARY] |
|GO |
|SET ANSI_PADDING OFF |
|GO |
|/****** Object: Table [dbo].[PS_SQLInstances] Script Date: 08/20/2014 11:50:15 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|SET ANSI_PADDING ON |
|GO |
|CREATE TABLE [dbo].[PS_SQLInstances]( |
|[WindowsServerName] [char](40) NULL, |
|[SQLInstanceName] [char](40) NULL, |
|[SampleDate] [date] NULL, |
|[SampleTime] [time](7) NULL, |
|[Process] [char](1) NULL, |
|[Type] [varchar](20) NULL |
|) ON [PRIMARY] |
|GO |
|SET ANSI_PADDING OFF |
|GO |
|/****** Object: Table [dbo].[PS_SQLDatabaseInfo] Script Date: 08/20/2014 11:50:15 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|SET ANSI_PADDING ON |
|GO |
|CREATE TABLE [dbo].[PS_SQLDatabaseInfo]( |
|[WindowsServerName] [char](40) NULL, |
|[SQLInstanceName] [char](40) NULL, |
|[DatabaseName] [sysname] NOT NULL, |
|[created] [datetime] NULL, |
|[SampleDateTime] [datetime] NOT NULL |
|) ON [PRIMARY] |
|GO |
|SET ANSI_PADDING OFF |
|GO |
|/****** Object: Table [dbo].[PS_SQLBackupsRun] Script Date: 08/20/2014 11:50:15 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|SET ANSI_PADDING ON |
|GO |
|CREATE TABLE [dbo].[PS_SQLBackupsRun]( |
|[WindowsServerName] [char](40) NULL, |
|[SQLInstanceName] [char](40) NULL, |
|[SampleDateTime] [datetime] NOT NULL, |
|[databaseName] [nvarchar](128) NULL, |
|[BackupFileName] [nvarchar](260) NULL, |
|[BackupSizeMB] [numeric](31, 0) NULL, |
|[TimeTaken] [varchar](12) NULL, |
|[BackupStartDate] [datetime] NULL, |
|[BackupFinishDate] [datetime] NULL, |
|[BackupType] [varchar](15) NULL, |
|[RecoveryModel] [nvarchar](60) NULL |
|) ON [PRIMARY] |
|GO |
|SET ANSI_PADDING OFF |
|GO |
|/****** Object: Table [dbo].[AddedRemovedDatabases] Script Date: 08/20/2014 11:50:15 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|SET ANSI_PADDING ON |
|GO |
|CREATE TABLE [dbo].[AddedRemovedDatabases]( |
|[WindowsServerName] [char](40) NULL, |
|[SQLInstanceName] [char](40) NULL, |
|[DatabaseName] [sysname] NOT NULL, |
|[Added/Removed] [char](10) NULL, |
|[created] [datetime] NULL, |
|[DateChanged] [date] NOT NULL |
|) ON [PRIMARY] |
|GO |
|SET ANSI_PADDING OFF |
|GO |
|/****** Object: Table [dbo].[Service_Dept] Script Date: 08/20/2014 11:50:15 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|CREATE TABLE [dbo].[Service_Dept]( |
|[Dept_ID] [int] IDENTITY(1,1) NOT NULL, |
|[Dept_Name] [nchar](50) NULL, |
|CONSTRAINT [PK_Service_REF_Dept] PRIMARY KEY CLUSTERED |
|( |
|[Dept_ID] ASC |
|)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] |
|) ON [PRIMARY] |
|GO |
|/****** Object: Table [dbo].[Service_DataClass] Script Date: 08/20/2014 11:50:15 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|SET ANSI_PADDING ON |
|GO |
|CREATE TABLE [dbo].[Service_DataClass]( |
|[DataClass_ID] [int] IDENTITY(1,1) NOT NULL, |
|[DataClassName] [varchar](15) NOT NULL, |
|CONSTRAINT [PK_Service_DataClass] PRIMARY KEY CLUSTERED |
|( |
|[DataClass_ID] ASC |
|)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], |
|CONSTRAINT [UI_Service_DataClass] UNIQUE NONCLUSTERED |
|( |
|[DataClassName] ASC |
|)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] |
|) ON [PRIMARY] |
|GO |
|SET ANSI_PADDING OFF |
|GO |
|/****** Object: Table [dbo].[Service_Component_Type] Script Date: 08/20/2014 11:50:15 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|SET ANSI_PADDING ON |
|GO |
|CREATE TABLE [dbo].[Service_Component_Type]( |
|[Component_Type_ID] [int] IDENTITY(1,1) NOT NULL, |
|[Component_Type] [varchar](30) NOT NULL, |
|CONSTRAINT [PK_Service_Component_Type] PRIMARY KEY CLUSTERED |
|( |
|[Component_Type_ID] ASC |
|)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], |
|CONSTRAINT [UI_Component_Type] UNIQUE NONCLUSTERED |
|( |
|[Component_Type] ASC |
|)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] |
|) ON [PRIMARY] |
|GO |
|SET ANSI_PADDING OFF |
|GO |
|/****** Object: Table [dbo].[Service_Server] Script Date: 08/20/2014 11:50:15 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|SET ANSI_PADDING ON |
|GO |
|CREATE TABLE [dbo].[Service_Server]( |
|[Server_ID] [int] IDENTITY(1,1) NOT NULL, |
|[Server_Name] [varchar](50) NOT NULL, |
|[Server_Type] [varchar](50) NOT NULL, |
|[Server_IP1] [varchar](15) NOT NULL, |
|[Server_IP2] [varchar](15) NULL, |
|[Server_IP3] [varchar](15) NULL, |
|[Server_OS] [varchar](50) NOT NULL, |
|[Server_Virtual] [char](1) NOT NULL, |
|[Server_RecoverPoint] [char](1) NOT NULL, |
|[Server_Notes] [varchar](max) NULL, |
|[Server_ShareAvailableInDR] [char](1) NULL, |
|[Server_ServiceName] [varchar](40) NULL, |
|CONSTRAINT [PK_Service_Server] PRIMARY KEY CLUSTERED |
|( |
|[Server_ID] ASC |
|)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] |
|) ON [PRIMARY] |
|GO |
|SET ANSI_PADDING OFF |
|GO |
|/****** Object: Table [dbo].[Service_Role] Script Date: 08/20/2014 11:50:15 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|SET ANSI_PADDING ON |
|GO |
|CREATE TABLE [dbo].[Service_Role]( |
|[Role_ID] [int] IDENTITY(1,1) NOT NULL, |
|[RoleName] [varchar](50) NOT NULL, |
|CONSTRAINT [PK_Service_Role] PRIMARY KEY CLUSTERED |
|( |
|[Role_ID] ASC |
|)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], |
|CONSTRAINT [UI_Service_Role] UNIQUE NONCLUSTERED |
|( |
|[RoleName] ASC |
|)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] |
|) ON [PRIMARY] |
|GO |
|SET ANSI_PADDING OFF |
|GO |
|/****** Object: Table [dbo].[Service_ServiceCrit] Script Date: 08/20/2014 11:50:15 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|SET ANSI_PADDING ON |
|GO |
|CREATE TABLE [dbo].[Service_ServiceCrit]( |
|[ServiceCrit_ID] [int] IDENTITY(1,1) NOT NULL, |
|[ServiceCritName] [varchar](15) NOT NULL, |
|CONSTRAINT [PK_Service_ServiceCrit] PRIMARY KEY CLUSTERED |
|( |
|[ServiceCrit_ID] ASC |
|)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], |
|CONSTRAINT [UI_Service_ServiceCrit] UNIQUE NONCLUSTERED |
|( |
|[ServiceCritName] ASC |
|)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] |
|) ON [PRIMARY] |
|GO |
|SET ANSI_PADDING OFF |
|GO |
|/****** Object: Table [dbo].[Service_Person] Script Date: 08/20/2014 11:50:15 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|SET ANSI_PADDING ON |
|GO |
|CREATE TABLE [dbo].[Service_Person]( |
|[Person_ID] [int] IDENTITY(1,1) NOT NULL, |
|[Name] [varchar](100) NULL, |
|[Tel] [varchar](20) NULL, |
|[Mob] [varchar](20) NULL, |
|[Email] [varchar](255) NULL, |
|[Dept_ID] [int] NOT NULL, |
|[Section] [varchar](50) NULL, |
|[Desk_Address] [varchar](100) NULL, |
|[Company_ID] [int] NOT NULL, |
|CONSTRAINT [PK_Service_Person] PRIMARY KEY CLUSTERED |
|( |
|[Person_ID] ASC |
|)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] |
|) ON [PRIMARY] |
|GO |
|SET ANSI_PADDING OFF |
|GO |
|/****** Object: Table [dbo].[Service_Service] Script Date: 08/20/2014 11:50:15 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|SET ANSI_PADDING ON |
|GO |
|CREATE TABLE [dbo].[Service_Service]( |
|[Service_ID] [char](10) NOT NULL, |
|[ServiceName] [varchar](100) NOT NULL, |
|[Service_Dept_ID] [int] NOT NULL, |
|[ThirdPartyCompany_ID] [int] NULL, |
|[DataClass_ID] [int] NOT NULL, |
|[ServiceCrit_ID] [int] NOT NULL, |
|[FuctionalSummary] [varchar](max) NULL, |
|[TechnicalSummary] [varchar](max) NULL, |
|[MiscInformation] [varchar](max) NULL, |
|[DataRetentionNote] [varchar](255) NULL, |
|[AvailibilityRequirements] [varchar](255) NULL, |
|[AppRepointingNote] [varchar](255) NULL, |
|[ServiceDescriptionDoc] [varchar](255) NULL, |
|[SharepointProjectPage] [varchar](255) NULL, |
|CONSTRAINT [PK_Service_Service] PRIMARY KEY CLUSTERED |
|( |
|[Service_ID] ASC |
|)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] |
|) ON [PRIMARY] |
|GO |
|SET ANSI_PADDING OFF |
|GO |
|/****** Object: Table [dbo].[Service_PersonRoles] Script Date: 08/20/2014 11:50:15 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|SET ANSI_PADDING ON |
|GO |
|CREATE TABLE [dbo].[Service_PersonRoles]( |
|[Person_ID] [int] NOT NULL, |
|[Service_ID] [char](10) NOT NULL, |
|[Role_ID] [int] NOT NULL, |
|CONSTRAINT [PK_Service_PersonRoles] PRIMARY KEY CLUSTERED |
|( |
|[Person_ID] ASC, |
|[Service_ID] ASC, |
|[Role_ID] ASC |
|)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] |
|) ON [PRIMARY] |
|GO |
|SET ANSI_PADDING OFF |
|GO |
|/****** Object: Table [dbo].[Service_Component] Script Date: 08/20/2014 11:50:15 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|SET ANSI_PADDING ON |
|GO |
|CREATE TABLE [dbo].[Service_Component]( |
|[Component_ID] [int] IDENTITY(1,1) NOT NULL, |
|[Service_ID] [char](10) NOT NULL, |
|[Component_FriendlyName] [varchar](50) NULL, |
|[Component_Type_ID] [int] NOT NULL, |
|[Component_EXE_ID] [int] NULL, |
|[Component_EXE_Dir] [varchar](255) NULL, |
|[Component_WEB_ID] [int] NULL, |
|[Component_WEB_URL] [varchar](255) NULL, |
|[Component_SQLInstance_ID] [int] NULL, |
|[Component_DB1] [sysname] NULL, |
|[Component_DB2] [sysname] NULL, |
|[Component_DB3] [sysname] NULL, |
|[Component_DB4] [sysname] NULL, |
|[Component_DB5] [sysname] NULL, |
|[Component_DB6] [sysname] NULL, |
|[Component_SSRS_ID] [int] NULL, |
|[Component_SSIS_ID] [int] NULL, |
|[Component_SSAS_ID] [int] NULL, |
|[Component_Live] [char](1) NOT NULL, |
|[Component_Notes] [varchar](max) NULL, |
|[Component_IO_Routines] [varchar](max) NULL, |
|CONSTRAINT [PK_Service_Component_ID] PRIMARY KEY CLUSTERED |
|( |
|[Component_ID] ASC |
|)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] |
|) ON [PRIMARY] |
|GO |
|SET ANSI_PADDING OFF |
|GO |
|/****** Object: Check [CHK_Service_Component_Live] Script Date: 08/20/2014 11:50:15 ******/ |
|ALTER TABLE [dbo].[Service_Component] WITH CHECK ADD CONSTRAINT [CHK_Service_Component_Live] CHECK (([Component_Live]='N' OR [Component_Live]='Y')) |
|GO |
|ALTER TABLE [dbo].[Service_Component] CHECK CONSTRAINT [CHK_Service_Component_Live] |
|GO |
|/****** Object: Check [CHK_Service_Server_RecoverPoint] Script Date: 08/20/2014 11:50:15 ******/ |
|ALTER TABLE [dbo].[Service_Server] WITH CHECK ADD CONSTRAINT [CHK_Service_Server_RecoverPoint] CHECK (([Server_RecoverPoint]='N' OR [Server_RecoverPoint]='Y')) |
|GO |
|ALTER TABLE [dbo].[Service_Server] CHECK CONSTRAINT [CHK_Service_Server_RecoverPoint] |
|GO |
|/****** Object: Check [CHK_Service_Server_Type] Script Date: 08/20/2014 11:50:15 ******/ |
|ALTER TABLE [dbo].[Service_Server] WITH CHECK ADD CONSTRAINT [CHK_Service_Server_Type] CHECK (([Server_Type]='SSAS' OR [Server_Type]='SSIS' OR [Server_Type]='SSRS' OR |
|[Server_Type]='DB' OR [Server_Type]='Apache' OR [Server_Type]='Tomcat' OR [Server_Type]='IIS' OR [Server_Type]='App')) |
|GO |
|ALTER TABLE [dbo].[Service_Server] CHECK CONSTRAINT [CHK_Service_Server_Type] |
|GO |
|/****** Object: ForeignKey [FK_Service_Component_EXE_ID] Script Date: 08/20/2014 11:50:15 ******/ |
|ALTER TABLE [dbo].[Service_Component] WITH CHECK ADD CONSTRAINT [FK_Service_Component_EXE_ID] FOREIGN KEY([Component_EXE_ID]) |
|REFERENCES [dbo].[Service_Server] ([Server_ID]) |
|GO |
|ALTER TABLE [dbo].[Service_Component] CHECK CONSTRAINT [FK_Service_Component_EXE_ID] |
|GO |
|/****** Object: ForeignKey [FK_Service_Component_Service_ID] Script Date: 08/20/2014 11:50:15 ******/ |
|ALTER TABLE [dbo].[Service_Component] WITH CHECK ADD CONSTRAINT [FK_Service_Component_Service_ID] FOREIGN KEY([Service_ID]) |
|REFERENCES [dbo].[Service_Service] ([Service_ID]) |
|GO |
|ALTER TABLE [dbo].[Service_Component] CHECK CONSTRAINT [FK_Service_Component_Service_ID] |
|GO |
|/****** Object: ForeignKey [FK_Service_Component_SQLInstance_ID] Script Date: 08/20/2014 11:50:15 ******/ |
|ALTER TABLE [dbo].[Service_Component] WITH CHECK ADD CONSTRAINT [FK_Service_Component_SQLInstance_ID] FOREIGN KEY([Component_SQLInstance_ID]) |
|REFERENCES [dbo].[Service_Server] ([Server_ID]) |
|GO |
|ALTER TABLE [dbo].[Service_Component] CHECK CONSTRAINT [FK_Service_Component_SQLInstance_ID] |
|GO |
|/****** Object: ForeignKey [FK_Service_Component_SSAS_ID] Script Date: 08/20/2014 11:50:15 ******/ |
|ALTER TABLE [dbo].[Service_Component] WITH CHECK ADD CONSTRAINT [FK_Service_Component_SSAS_ID] FOREIGN KEY([Component_SSAS_ID]) |
|REFERENCES [dbo].[Service_Server] ([Server_ID]) |
|GO |
|ALTER TABLE [dbo].[Service_Component] CHECK CONSTRAINT [FK_Service_Component_SSAS_ID] |
|GO |
|/****** Object: ForeignKey [FK_Service_Component_SSIS_ID] Script Date: 08/20/2014 11:50:15 ******/ |
|ALTER TABLE [dbo].[Service_Component] WITH CHECK ADD CONSTRAINT [FK_Service_Component_SSIS_ID] FOREIGN KEY([Component_SSIS_ID]) |
|REFERENCES [dbo].[Service_Server] ([Server_ID]) |
|GO |
|ALTER TABLE [dbo].[Service_Component] CHECK CONSTRAINT [FK_Service_Component_SSIS_ID] |
|GO |
|/****** Object: ForeignKey [FK_Service_Component_SSRS_ID] Script Date: 08/20/2014 11:50:15 ******/ |
|ALTER TABLE [dbo].[Service_Component] WITH CHECK ADD CONSTRAINT [FK_Service_Component_SSRS_ID] FOREIGN KEY([Component_SSRS_ID]) |
|REFERENCES [dbo].[Service_Server] ([Server_ID]) |
|GO |
|ALTER TABLE [dbo].[Service_Component] CHECK CONSTRAINT [FK_Service_Component_SSRS_ID] |
|GO |
|/****** Object: ForeignKey [FK_Service_Component_Type_ID] Script Date: 08/20/2014 11:50:15 ******/ |
|ALTER TABLE [dbo].[Service_Component] WITH CHECK ADD CONSTRAINT [FK_Service_Component_Type_ID] FOREIGN KEY([Component_Type_ID]) |
|REFERENCES [dbo].[Service_Component_Type] ([Component_Type_ID]) |
|GO |
|ALTER TABLE [dbo].[Service_Component] CHECK CONSTRAINT [FK_Service_Component_Type_ID] |
|GO |
|/****** Object: ForeignKey [FK_Service_Component_WEB_ID] Script Date: 08/20/2014 11:50:15 ******/ |
|ALTER TABLE [dbo].[Service_Component] WITH CHECK ADD CONSTRAINT [FK_Service_Component_WEB_ID] FOREIGN KEY([Component_WEB_ID]) |
|REFERENCES [dbo].[Service_Server] ([Server_ID]) |
|GO |
|ALTER TABLE [dbo].[Service_Component] CHECK CONSTRAINT [FK_Service_Component_WEB_ID] |
|GO |
|/****** Object: ForeignKey [FK_Service_Company_ID] Script Date: 08/20/2014 11:50:15 ******/ |
|ALTER TABLE [dbo].[Service_Person] WITH CHECK ADD CONSTRAINT [FK_Service_Company_ID] FOREIGN KEY([Company_ID]) |
|REFERENCES [dbo].[Service_Company] ([Company_ID]) |
|GO |
|ALTER TABLE [dbo].[Service_Person] CHECK CONSTRAINT [FK_Service_Company_ID] |
|GO |
|/****** Object: ForeignKey [FK_Service_Dept_ID] Script Date: 08/20/2014 11:50:15 ******/ |
|ALTER TABLE [dbo].[Service_Person] WITH CHECK ADD CONSTRAINT [FK_Service_Dept_ID] FOREIGN KEY([Dept_ID]) |
|REFERENCES [dbo].[Service_Dept] ([Dept_ID]) |
|GO |
|ALTER TABLE [dbo].[Service_Person] CHECK CONSTRAINT [FK_Service_Dept_ID] |
|GO |
|/****** Object: ForeignKey [FK_Service_PersonRoles_Person_ID] Script Date: 08/20/2014 11:50:15 ******/ |
|ALTER TABLE [dbo].[Service_PersonRoles] WITH CHECK ADD CONSTRAINT [FK_Service_PersonRoles_Person_ID] FOREIGN KEY([Person_ID]) |
|REFERENCES [dbo].[Service_Person] ([Person_ID]) |
|GO |
|ALTER TABLE [dbo].[Service_PersonRoles] CHECK CONSTRAINT [FK_Service_PersonRoles_Person_ID] |
|GO |
|/****** Object: ForeignKey [FK_Service_PersonRoles_Role_ID] Script Date: 08/20/2014 11:50:15 ******/ |
|ALTER TABLE [dbo].[Service_PersonRoles] WITH CHECK ADD CONSTRAINT [FK_Service_PersonRoles_Role_ID] FOREIGN KEY([Role_ID]) |
|REFERENCES [dbo].[Service_Role] ([Role_ID]) |
|GO |
|ALTER TABLE [dbo].[Service_PersonRoles] CHECK CONSTRAINT [FK_Service_PersonRoles_Role_ID] |
|GO |
|/****** Object: ForeignKey [FK_Service_PersonRoles_Service_ID] Script Date: 08/20/2014 11:50:15 ******/ |
|ALTER TABLE [dbo].[Service_PersonRoles] WITH CHECK ADD CONSTRAINT [FK_Service_PersonRoles_Service_ID] FOREIGN KEY([Service_ID]) |
|REFERENCES [dbo].[Service_Service] ([Service_ID]) |
|GO |
|ALTER TABLE [dbo].[Service_PersonRoles] CHECK CONSTRAINT [FK_Service_PersonRoles_Service_ID] |
|GO |
|/****** Object: ForeignKey [FK_Service_DataClass] Script Date: 08/20/2014 11:50:15 ******/ |
|ALTER TABLE [dbo].[Service_Service] WITH CHECK ADD CONSTRAINT [FK_Service_DataClass] FOREIGN KEY([DataClass_ID]) |
|REFERENCES [dbo].[Service_DataClass] ([DataClass_ID]) |
|GO |
|ALTER TABLE [dbo].[Service_Service] CHECK CONSTRAINT [FK_Service_DataClass] |
|GO |
|/****** Object: ForeignKey [FK_Service_Dept] Script Date: 08/20/2014 11:50:15 ******/ |
|ALTER TABLE [dbo].[Service_Service] WITH CHECK ADD CONSTRAINT [FK_Service_Dept] FOREIGN KEY([Service_Dept_ID]) |
|REFERENCES [dbo].[Service_Dept] ([Dept_ID]) |
|GO |
|ALTER TABLE [dbo].[Service_Service] CHECK CONSTRAINT [FK_Service_Dept] |
|GO |
|/****** Object: ForeignKey [FK_Service_Service_ThirdPartyCompany_ID] Script Date: 08/20/2014 11:50:15 ******/ |
|ALTER TABLE [dbo].[Service_Service] WITH CHECK ADD CONSTRAINT [FK_Service_Service_ThirdPartyCompany_ID] FOREIGN KEY([ThirdPartyCompany_ID]) |
|REFERENCES [dbo].[Service_Company] ([Company_ID]) |
|GO |
|ALTER TABLE [dbo].[Service_Service] CHECK CONSTRAINT [FK_Service_Service_ThirdPartyCompany_ID] |
|GO |
|/****** Object: ForeignKey [FK_Service_ServiceCrit] Script Date: 08/20/2014 11:50:15 ******/ |
|ALTER TABLE [dbo].[Service_Service] WITH CHECK ADD CONSTRAINT [FK_Service_ServiceCrit] FOREIGN KEY([ServiceCrit_ID]) |
|REFERENCES [dbo].[Service_ServiceCrit] ([ServiceCrit_ID]) |
|GO |
|ALTER TABLE [dbo].[Service_Service] CHECK CONSTRAINT [FK_Service_ServiceCrit] |
|GO |
Tables:
|[pic] |[pic] |
|[pic] |[pic] |
|[pic] |[pic] |
|[pic] |[pic] |
|[pic] |[pic] |
|[pic] |[pic] |
|[pic] |[pic] |
|[pic] |[pic] |
|[pic] |[pic] |
Powershell Scripts:
Create Powershell Script Files
|# |
|# Written by Team SQLDOT as part of MSC in NL (UCD)- July 2014 |
|# Team SQLDOT - Eileen Dennan, Pat Roche and Adam Murphy |
|# Dublin City Council - Information Systems Department |
|# |
|# |
| |
|# Snapins are required to avoid error messages. |
|# |
| |
|# build up the query you need to use |
| |
|Try |
| |
|{ |
|Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction Stop |
|Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction Stop |
|} |
| |
|Catch |
|{ |
|$ErrorMessage = $_.Exception.Message |
|$FailedItem = $_.Exception.ItemName |
|# just do someting as if there is an error it is because they are already loaded. |
|# break |
|# write-host $Error[0].Exception; |
|# Break |
|} |
| |
|$query1=@" |
|select ServerName from [dba_PS_SQLALL_SERVERS] order by 1 -- All SQL Versions |
|"@ |
| |
|# extract out the unique server names |
|$instanceNameList = invoke-sqlcmd -Query $query1 -serverinstance VM-SQLCMS -database SQLDOT | select-object ServerName -Unique |
|# Write-Host "DEBUG" $instanceNameList |
| |
|$query2=@" |
|SELECT |
|isnull(convert(char(40), SERVERPROPERTY ('ComputerNamePhysicalNetBIOS')), @@servername) as [WindowsServerName], |
|convert(char(40), isnull(SERVERPROPERTY ('InstanceName'), 'default')) as [SQLInstanceName], |
|convert(char(40), SERVERPROPERTY('productversion')) as [SQLProductVersion], |
|convert(char(40), SERVERPROPERTY ('productlevel')) as [SQLProductLevel], |
|convert(char(40), SERVERPROPERTY ('edition')) as [SQLProductEdition], |
|GETDATE() as [SampleDate], |
|cntr_value AS [TotalServerMemKB] |
|FROM sys.dm_os_performance_counters |
|WHERE counter_name = 'Total Server Memory (KB)' |
|"@ |
| |
|# iterate through the instances |
|foreach($ServerName in $instanceNameList) |
|{ |
|# Connect to each instance and return the name & version, then write that information into the VM-SQLCMS.SQLDOT.dbo.PS_SQLVersion table |
| |
|# Write-Host " [ServerName]: " $Servername |
| |
|Try |
|{ |
|$ErrorMessage = "OK" |
|[array]$PS_SQLVersionStrings = invoke-sqlcmd -Query $query2 -ServerInstance $($ServerName.Servername) -ErrorAction Stop |
|} |
|Catch |
|{ |
|$ErrorMessage = $_.Exception.Message |
|$FailedItem = $_.Exception.ItemName |
|# just do someting as if there is an error it is because they are already loaded. |
|# break |
|# write-host $Error[0].Exception; |
|# Break |
|} |
| |
|if ($ErrorMessage -eq "OK") { |
|foreach($objectitem in $PS_SQLVersionStrings) { |
|# Write-Host " [WindowsServerName]: " $ServerName.Servername |
|# Write-Host " [WindowsServerName]: " $objectitem.WindowsServerName |
|# Write-Host " [SQLInstanceName]: " $objectitem.SQLInstanceName |
|# Write-Host " [SQLProductVersion]: " $objectitem.SQLProductVersion |
|# Write-Host " [SQLProductLevel]: " $objectitem.SQLProductLevel |
|# Write-Host " [SQLProductEdition]: " $objectitem.SQLProductEdition |
|# Write-Host " [TotalServerMemKB]: " $objectitem.TotalServerMemKB |
|invoke-SqlCmd -serverinstance VM-SQLCMS -database SQLDOT -query ` |
|"insert into PS_SQLVersionStrings ` |
|( [WindowsServerName], [SQLInstanceName], [SQLProductVersion], [SQLProductLevel], [SQLProductEdition],[SampleDateTime], [TotalServerMemKB]) |
|Values ` |
|( '$($objectitem.WindowsServerName)', '$($objectitem.SQLInstanceName)', '$($objectitem.SQLProductVersion)', ` |
|'$($objectitem.SQLProductLevel)','$($objectitem.SQLProductEdition)', getdate(), $($objectitem.TotalServerMemKB))" -ErrorAction Stop |
|} |
| |
|} # end objectitem |
| |
|} # end Servername |
| |
| |
| |
|# |
|# Written by Team SQLDOT as part of MSC in NL (UCD)- July 2014 |
|# Team SQLDOT - Eileen Dennan, Pat Roche and Adam Murphy |
|# Dublin City Council - Information Systems Department |
|# |
|# |
| |
|# Snapins are required to avoid error messages. |
|# |
| |
|Try |
|{ |
|Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction Stop |
|Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction Stop |
|} |
|Catch |
|{ |
|$ErrorMessage = $_.Exception.Message |
|$FailedItem = $_.Exception.ItemName |
|# just do someting as if there is an error it is because they are already loaded. |
|# break |
|# write-host $Error[0].Exception; |
|# Break |
|} |
| |
|# build up the query you need to use |
|$query1=@" |
|select ServerName from [dba_PS_SQLALL_SERVERS] order by 1 -- All SQL Versions |
|"@ |
| |
|# extract out the unique server names |
|$instanceNameList = invoke-sqlcmd -Query $query1 -serverinstance VM-SQLCMS -database SQLDOT | select-object ServerName -Unique |
|Write-Output $instanceNameList |
| |
|$query2=@" |
|select |
|isnull(convert(char(40), SERVERPROPERTY ('ComputerNamePhysicalNetBIOS')), @@servername) as [WindowsServerName], |
|convert(char(40), isnull(SERVERPROPERTY ('InstanceName'), 'default')) as [SQLInstanceName], |
|GETDATE() as [SampleDateTime], |
|s.database_name as [databaseName], |
|m.physical_device_name as [BackupFileName], |
|ceiling (s.backup_size/1024/1024) as [BackupSizeMB], |
|CAST(DATEDIFF(second, s.backup_start_date, |
|s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' as [TimeTaken], |
|s.backup_start_date as [BackupStartDate], |
|s.backup_finish_date as [BackupFinishDate], |
|CASE s.[type] |
|WHEN 'D' THEN 'Full' |
|WHEN 'I' THEN 'Diff' |
|WHEN 'L' THEN 'TLog' |
|END AS [BackupType], |
|s.recovery_model as [RecoveryModel] |
|FROM |
|msdb.dbo.backupset s |
|INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id |
|left outer join sys.sysdatabases sdb on s.database_name = sdb.name |
|where |
|s.backup_start_date >= dateadd(day, -1, getdate()) and |
|-- s.backup_start_date >= '1 november 2013' and |
|m.physical_device_name not like '{%'and |
|m.physical_device_name not like 'RecoverPoint%' |
|ORDER BY |
|backup_start_date DESC, backup_finish_date |
|"@ |
| |
| |
|# iterate through the instances |
|foreach($ServerName in $instanceNameList) |
|{ |
|# Connect to each instance and return the backup information, then write that information into the VM-SQLCMS.SQLDOT.dbo.PS_SQLBackups table |
|Try |
|{ |
|$ErrorMessage = "OK" |
|[array]$PS_SQLBackupsRun = invoke-sqlcmd -Query $query2 -ServerInstance $($ServerName.Servername) -ErrorAction Stop |
|} |
|Catch |
|{ |
|$ErrorMessage = $_.Exception.Message |
|$FailedItem = $_.Exception.ItemName |
|# just do someting as if there is an error it is because they are already loaded. |
|# break |
|# write-host $Error[0].Exception; |
|# Break |
|} |
| |
|write-output " [ServerName]: $($ServerName.ServerName)" |
| |
|if ($ErrorMessage -eq "OK") { |
|foreach($objectitem in $PS_SQLBackupsRun) { |
| |
|if ($ServerName.ServerName -eq "VM-SQLTEST02\TEST2008") { |
| |
|# write-host -nonewline " [ServerName]: " $ServerName.ServerName |
|# write-host -nonewline " [WindowsServerName]: " $objectitem.WindowsServerName |
|# write-host -nonewline " [SQLInstanceName]: " $objectitem.SQLInstanceName |
|# write-host -nonewline " [SampleDateTime]: " $objectitem.SampleDateTime |
|# write-host -nonewline " [dbname]: " $objectitem.dbname |
|# write-host -nonewline " [BackupFileName]: " $objectitem.BackupFileName |
|# write-host -nonewline " [BackupSizeMB]: " $objectitem.BackupSizeMB |
|# write-host -nonewline " [TimeTaken]: " $objectitem.TimeTaken |
|# write-host -nonewline " [BackupStartDate]: " $objectitem.BackupStartDate |
|# write-host -nonewline " [BackupFinishDate]: " $objectitem.BackupFinishDate |
|# write-host -nonewline " [BackupType]: " $objectitem.BackupType |
|# write-host " [RecoveryMode]: " $objectitem.RecoveryModel |
|} |
| |
|invoke-SqlCmd -serverinstance VM-SQLCMS -database SQLDOT -query "insert into PS_SQLBackupsRun ( [WindowsServerName], [SQLInstanceName], [SampleDateTime], [databaseName], |
|[BackupFileName], [BackupSizeMB], [TimeTaken], [BackupStartDate], [BackupFinishDate], [BackupType], [RecoveryModel] ) Values ( '$($objectitem.WindowsServerName)', |
|'$($objectitem.SQLInstanceName)', '$($objectitem.SampleDateTime)', '$($objectitem.databaseName)','$($objectitem.BackupFileName)','$($objectitem.BackupSizeMB)', |
|'$($objectitem.TimeTaken)', '$($objectitem.BackupStartDate)', '$($objectitem.BackupFinishDate)', '$($objectitem.BackupType)', '$($objectitem.RecoveryModel)' )" |
|} # end of if |
|} # end objectitem |
| |
|} # end Servername |
|# |
|# Written by Team SQLDOT as part of MSC in NL (UCD)- July 2014 |
|# Team SQLDOT - Eileen Dennan, Pat Roche and Adam Murphy |
|# Dublin City Council - Information Systems Department |
|# |
|# |
| |
|# Snapins are required to avoid error messages. |
|# |
| |
|Try |
|{ |
|Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction Stop |
|Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction Stop |
|} |
|Catch |
|{ |
|$ErrorMessage = $_.Exception.Message |
|$FailedItem = $_.Exception.ItemName |
|# just do someting as if there is an error it is because they are already loaded. |
|# break |
|# write-host $Error[0].Exception; |
|# Break |
|} |
| |
|# build up the query you need to use |
|$query1=@" |
|select ServerName from dba_PS_SQLALL_SERVERS order by 1 -- All SQL Versions |
|"@ |
| |
|# extract out the unique server names |
|$instanceNameList = invoke-sqlcmd -Query $query1 -serverinstance VM-SQLCMS -database SQLDOT | select-object ServerName -Unique |
|# Write-Host "DEBUG" $instanceNameList |
|# this is the query to extract the data |
|$query3=@" |
|select |
|isnull(convert(char(40), SERVERPROPERTY ('ComputerNamePhysicalNetBIOS')), @@servername) as [WindowsServerName] |
|,convert(char(40), isnull(SERVERPROPERTY ('InstanceName'), 'default')) as [SQLInstanceName] |
|,name as [DatabaseName] |
|,crdate as [Created] |
|,GETDATE() as [SampleDateTime] |
|FROM |
|master.sys.sysdatabases |
|"@ |
| |
| |
|# iterate through the instances |
|foreach($ServerName in $instanceNameList) |
|{ |
|# Connect to each instance and return the name & version, then write that information into the VM-SQLCMS.SQLDOT.dbo.PS_SQLBackupsNotRun table |
| |
|# Write-Host " [ServerName]: " $ServerName.ServerName |
|Try |
|{ |
|$ErrorMessage = "OK" |
|[array]$PS_SQLPhysicalFiles = invoke-sqlcmd -Query $query3 -ServerInstance $($ServerName.Servername) -ErrorAction Stop |
|} |
|Catch |
|{ |
|$ErrorMessage = $_.Exception.Message |
|$FailedItem = $_.Exception.ItemName |
|# just do someting as if there is an error it is because they are already loaded. |
|# break |
|# write-host $Error[0].Exception; |
|# Break |
|} |
| |
|if ($ErrorMessage -eq "OK") { |
| |
|foreach($objectitem in $PS_SQLPhysicalFiles) { |
|# Write-Host " [ServerName]: " $objectitem.WindowsServerName |
|# Write-Host " [InstanceName]: " $objectitem.SQLInstanceName |
|# Write-Host " [databaseName]: " $objectitem.databaseName |
|# Write-Host " [Created]: " $objectitem.Created |
|# Write-Host " [SampleDateTime]: " $objectitem.SampleDateTime |
| |
| |
| |
|invoke-SqlCmd -serverinstance VM-SQLCMS -database SQLDOT -query ` |
|"insert into PS_SQLDatabaseInfo` |
|(WindowsServerName, SQLInstanceName, databaseName, Created, SampleDateTime)` |
|Values` |
|( '$($objectitem.WindowsServerName)',` |
|'$($objectitem.SQLInstanceName)',` |
|'$($objectitem.DatabaseName)',` |
|'$($objectitem.Created)',` |
|'$($objectitem.SampleDateTime)')" |
|} |
| |
|} # end objectitem |
| |
|} # end Servername |
| |
|# |
|# Written by Team SQLDOT as part of MSC in NL (UCD)- July 2014 |
|# Team SQLDOT - Eileen Dennan, Pat Roche and Adam Murphy |
|# Dublin City Council - Information Systems Department |
|# |
|# |
| |
|# Snapins are required to avoid error messages. |
|# |
| |
|Try |
|{ |
|Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction Stop |
|Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction Stop |
|} |
|Catch |
|{ |
|$ErrorMessage = $_.Exception.Message |
|$FailedItem = $_.Exception.ItemName |
|# just do someting as if there is an error it is because they are already loaded. |
|# break |
|# write-host $Error[0].Exception; |
|# Break |
|} |
| |
|# build up the query you need to use |
|$query1=@" |
|select ServerName from dba_PS_SQLALL_SERVERS order by 1 -- All SQL Versions |
|"@ |
| |
|# extract out the unique server names |
|$instanceNameList = invoke-sqlcmd -Query $query1 -serverinstance VM-SQLCMS -database SQLDOT | select-object ServerName -Unique |
|# Write-Host "DEBUG" $instanceNameList |
|# this is the query to extract the data |
|$query3=@" |
|select |
|isnull(convert(char(40), SERVERPROPERTY ('ComputerNamePhysicalNetBIOS')), @@servername) as [WindowsServerName] |
|,convert(char(40), isnull(SERVERPROPERTY ('InstanceName'), 'default')) as [SQLInstanceName] |
|,GETDATE() as [SampleDateTime] |
|,db_name(database_id) as [databaseName] |
|,name as [FileName] |
|,physical_name |
|,type_desc |
|,state_desc |
|,size |
|,max_size |
|,growth |
|,is_percent_growth |
|FROM |
|sys.master_files |
|"@ |
| |
| |
|# iterate through the instances |
|foreach($ServerName in $instanceNameList) |
|{ |
|# Connect to each instance and return the name & version, then write that information into the VM-SQLCMS.SQLDOT.dbo.PS_SQLBackupsNotRun table |
| |
|# Write-Host " [ServerName]: " $ServerName.ServerName |
|Try |
|{ |
|$ErrorMessage = "OK" |
|[array]$PS_SQLPhysicalFiles = invoke-sqlcmd -Query $query3 -ServerInstance $($ServerName.Servername) -ErrorAction Stop |
|} |
|Catch |
|{ |
|$ErrorMessage = $_.Exception.Message |
|$FailedItem = $_.Exception.ItemName |
|# just do someting as if there is an error it is because they are already loaded. |
|# break |
|# write-host $Error[0].Exception; |
|# Break |
|} |
| |
|if ($ErrorMessage -eq "OK") { |
| |
|foreach($objectitem in $PS_SQLPhysicalFiles) { |
|# Write-Host " [ServerName]: " $objectitem.WindowsServerName |
|# Write-Host " [InstanceName]: " $objectitem.SQLInstanceName |
|# Write-Host " [SampleDateTime]: " $objectitem.SampleDateTime |
|# Write-Host " [databaseName]: " $objectitem.databaseName |
|# Write-Host " [FileName]: " $objectitem.FileName |
|# Write-Host " [physical_name]: " $objectitem.physical_name |
|# Write-Host " [type_desc]: " $objectitem.type_desc |
|# Write-Host " [state_desc]: " $objectitem.state_desc |
|# Write-Host " [size]: " $objectitem.state_desc |
|# Write-Host " [size]: " $objectitem.size |
|# Write-Host " [maxsize]: " $objectitem.max_size |
|# Write-Host " [growth]: " $objectitem.growth |
|# Write-Host " [is_percent_growth]: " $objectitem.is_percent_growth |
| |
|invoke-SqlCmd -serverinstance VM-SQLCMS -database SQLDOT -query "insert into PS_SQLPhysicalFiles (WindowsServerName, SQLInstanceName, SampleDateTime, databaseName, |
|FileName, physical_name, type_desc, state_desc, size, max_size, growth, is_percent_growth) Values ( '$($objectitem.WindowsServerName)', '$($objectitem.SQLInstanceName)', |
|'$($objectitem.SampleDateTime)', '$($objectitem.databaseName)', '$($objectitem.FileName)','$($objectitem.physical_name)', '$($objectitem.type_desc)', |
|'$($objectitem.state_desc)', '$($objectitem.size)', '$($objectitem.max_size)', '$($objectitem.growth)', '$($objectitem.is_percent_growth)' )" |
|} |
| |
|} # end objectitem |
| |
|} # end Servername |
| |
|# |
|# Written by Team SQLDOT as part of MSC in NL (UCD)- July 2014 |
|# Team SQLDOT - Eileen Dennan, Pat Roche and Adam Murphy |
|# Dublin City Council - Information Systems Department |
|# |
|# |
| |
|# Snapins are required to avoid error messages. |
|# |
| |
|Try |
|{ |
|Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction Stop |
|Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction Stop |
|} |
|Catch |
|{ |
|$ErrorMessage = $_.Exception.Message |
|$FailedItem = $_.Exception.ItemName |
|# just do someting as if there is an error it is because they are already loaded. |
|# break |
|# write-host $Error[0].Exception; |
|# Break |
|} |
| |
|# build up the query you need to use |
|$query1=@" |
|-- select ServerName from dba_PS_SQL2005PLUS_SERVERS order by 1 -- Exclude SQL2000 |
|-- select WindowsServerName from PS_SQLInstances order by 1 -- All SQL Versions |
|select ServerName from [dba_PS_SQLALL_SERVERS] order by 1 -- All SQL Versions |
|"@ |
| |
|# extract out the unique server names |
|$instanceNameList = invoke-sqlcmd -Query $query1 -serverinstance VM-SQLCMS -database SQLDOT | select-object ServerName -Unique |
|# Write-Host "DEBUG" $instanceNameList |
|# this is the query to extract the data |
|$query3=@" |
|select |
|isnull(convert(char(40), SERVERPROPERTY ('ComputerNamePhysicalNetBIOS')), @@servername) as [WindowsServerName] |
|,convert(char(40), isnull(SERVERPROPERTY ('InstanceName'), 'default')) as [SQLInstanceName] |
|,GETDATE() as [SampleDateTime] |
|, instance_name AS 'DatabaseName', |
|MAX(CASE |
|WHEN counter_name = 'Data File(s) Size (KB)' |
|THEN cntr_value/1024.00 |
|ELSE 0 |
|END) AS 'Data_File_Size_MB', |
|MAX(CASE |
|WHEN counter_name = 'Log File(s) Size (KB)' |
|THEN cntr_value/1024.00 |
|ELSE 0 |
|END) AS 'Log_File_Size_MB', |
|MAX(CASE |
|WHEN counter_name = 'Log File(s) Used Size (KB)' |
|THEN cntr_value/1024.00 |
|ELSE 0 |
|END) AS 'Log_File_Used_MB', |
|MAX(CASE |
|WHEN counter_name = 'Percent Log Used' |
|THEN cntr_value |
|ELSE 0 |
|END) AS 'Log_File_Used_Percent' |
|FROM |
|master..sysperfinfo |
|WHERE |
|counter_name IN |
|( |
|'Data File(s) Size (KB)', |
|'Log File(s) Size (KB)', |
|'Log File(s) Used Size (KB)', |
|'Percent Log Used' |
|) |
|AND instance_name != '_total' |
|GROUP BY |
|instance_name |
|go |
|"@ |
| |
| |
|# iterate through the instances |
|foreach($ServerName in $instanceNameList) |
|{ |
|# Connect to each instance and return the name & version, then write that information into the VM-SQLCMS.SQLDOT.dbo.PS_SQLPhysicalFilesUsed table |
| |
|# Write-Host " [ServerName]: " $ServerName.ServerName |
|Try |
|{ |
|$ErrorMessage = "OK" |
|[array]$PS_SQLPhysicalFilesUsed = invoke-sqlcmd -Query $query3 -ServerInstance $($ServerName.Servername) -ErrorAction Stop |
|} |
|Catch |
|{ |
|$ErrorMessage = $_.Exception.Message |
|$FailedItem = $_.Exception.ItemName |
|# just do someting as if there is an error it is because they are already loaded. |
|# break |
|# write-host $Error[0].Exception; |
|# Break |
|} |
| |
|if ($ErrorMessage -eq "OK") { |
| |
|foreach($objectitem in $PS_SQLPhysicalFilesUsed) { |
|# Write-Host " [ServerName]: " $objectitem.WindowsServerName |
|# Write-Host " [InstanceName]: " $objectitem.SQLInstanceName |
|# Write-Host " [SampleDateTime]: " $objectitem.SampleDateTime |
|# Write-Host " [databaseName]: " $objectitem.databaseName |
|# Write-Host " [Data_File_Size_MB]: " $objectitem.Data_File_Size_MB |
|# Write-Host " [Log_File_Size_MB]: " $objectitem.Log_File_Size_MB |
|# Write-Host " [Log_File_Used_MB]: " $objectitem.Log_File_Used_MB |
|# Write-Host " [Log_File_Used_Percent]: " $objectitem.Log_File_Used_Percent |
| |
|invoke-SqlCmd -serverinstance VM-SQLCMS -database SQLDOT -query "insert into PS_SQLPhysicalFilesUsed (WindowsServerName, SQLInstanceName, SampleDateTime, DatabaseName, |
|Data_File_Size_MB, Log_File_Size_MB, Log_File_Used_MB, Log_File_Used_Percent) Values ( '$($objectitem.WindowsServerName)', '$($objectitem.SQLInstanceName)', |
|'$($objectitem.SampleDateTime)', '$($objectitem.databaseName)', '$($objectitem.Data_File_Size_MB)','$($objectitem.Log_File_Size_MB)', '$($objectitem.Log_File_Used_MB)', |
|'$($objectitem.Log_File_Used_Percent)')" |
|} |
| |
|} # end objectitem |
| |
|} # end Servername |
| |
Views:
|[pic] |[pic] |
|[pic] |[pic] |
|[pic] |[pic] |
|[pic] |[pic] |
|[pic] |[pic] |
|[pic] | |
|USE [SQLDOT] |
|GO |
|/****** Object: View [dbo].[dba_PS_SQLALL_SERVERS] Script Date: 08/20/2014 11:49:17 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|CREATE VIEW [dbo].[dba_PS_SQLALL_SERVERS] |
|AS |
|SELECT DISTINCT (rtrim(I.WindowsServerName) + '\' + rtrim(REPLACE(SQLInstanceName, 'MSSQL$', ''))) AS [ServerName] |
|FROM [SQLDOT].[dbo].PS_SQLInstances I |
|WHERE I.SQLInstanceName |
|NOT IN ('Access-Denied', 'MSSQLSERVER', 'SQLEXPRESS') |
|AND I.Process = 'Y' |
|AND I.Type = 'SQL' |
|UNION |
|SELECT DISTINCT (rtrim(I.WindowsServerName)) AS [ServerName] |
|FROM [SQLDOT].[dbo].PS_SQLInstances I |
|/* where I.SampleDate = (select MAX(SampleDate) from SQLInstances )*/ |
|WHERE I.SQLInstanceName IN ('MSSQLSERVER', 'SQLEXPRESS') |
|AND I.Process = 'Y' |
|AND I.Type = 'SQL' |
|GO |
|EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] |
|Begin DesignProperties = |
|Begin PaneConfigurations = |
|Begin PaneConfiguration = 0 |
|NumPanes = 4 |
|Configuration = "(H (1[40] 4[20] 2[20] 3) )" |
|End |
|Begin PaneConfiguration = 1 |
|NumPanes = 3 |
|Configuration = "(H (1 [50] 4 [25] 3))" |
|End |
|Begin PaneConfiguration = 2 |
|NumPanes = 3 |
|Configuration = "(H (1 [50] 2 [25] 3))" |
|End |
|Begin PaneConfiguration = 3 |
|NumPanes = 3 |
|Configuration = "(H (4 [30] 2 [40] 3))" |
|End |
|Begin PaneConfiguration = 4 |
|NumPanes = 2 |
|Configuration = "(H (1 [56] 3))" |
|End |
|Begin PaneConfiguration = 5 |
|NumPanes = 2 |
|Configuration = "(H (2 [66] 3))" |
|End |
|Begin PaneConfiguration = 6 |
|NumPanes = 2 |
|Configuration = "(H (4 [50] 3))" |
|End |
|Begin PaneConfiguration = 7 |
|NumPanes = 1 |
|Configuration = "(V (3))" |
|End |
|Begin PaneConfiguration = 8 |
|NumPanes = 3 |
|Configuration = "(H (1[56] 4[18] 2) )" |
|End |
|Begin PaneConfiguration = 9 |
|NumPanes = 2 |
|Configuration = "(H (1 [75] 4))" |
|End |
|Begin PaneConfiguration = 10 |
|NumPanes = 2 |
|Configuration = "(H (1[66] 2) )" |
|End |
|Begin PaneConfiguration = 11 |
|NumPanes = 2 |
|Configuration = "(H (4 [60] 2))" |
|End |
|Begin PaneConfiguration = 12 |
|NumPanes = 1 |
|Configuration = "(H (1) )" |
|End |
|Begin PaneConfiguration = 13 |
|NumPanes = 1 |
|Configuration = "(V (4))" |
|End |
|Begin PaneConfiguration = 14 |
|NumPanes = 1 |
|Configuration = "(V (2))" |
|End |
|ActivePaneConfig = 0 |
|End |
|Begin DiagramPane = |
|Begin Origin = |
|Top = 0 |
|Left = 0 |
|End |
|Begin Tables = |
|End |
|End |
|Begin SQLPane = |
|End |
|Begin DataPane = |
|Begin ParameterDefaults = "" |
|End |
|Begin ColumnWidths = 9 |
|Width = 284 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|End |
|End |
|Begin CriteriaPane = |
|Begin ColumnWidths = 11 |
|Column = 1440 |
|Alias = 900 |
|Table = 1170 |
|Output = 720 |
|Append = 1400 |
|NewValue = 1170 |
|SortType = 1350 |
|SortOrder = 1410 |
|GroupBy = 1350 |
|Filter = 1350 |
|Or = 1350 |
|Or = 1350 |
|Or = 1350 |
|End |
|End |
|End |
|' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'dba_PS_SQLALL_SERVERS' |
|GO |
|EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'dba_PS_SQLALL_SERVERS' |
|GO |
|/****** Object: View [dbo].[vw_DB_Usage] Script Date: 08/20/2014 11:49:17 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|CREATE VIEW [dbo].[vw_DB_Usage] |
|AS |
|SELECT TOP (100) PERCENT WindowsServerName, SQLInstanceName, SampleDateTime, DatabaseName, Data_File_Size_MB, Log_File_Size_MB, Log_File_Used_MB, |
|Log_File_Used_Percent |
|FROM dbo.PS_SQLPhysicalFilesUsed |
|ORDER BY WindowsServerName, SQLInstanceName, DatabaseName |
|GO |
|EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] |
|Begin DesignProperties = |
|Begin PaneConfigurations = |
|Begin PaneConfiguration = 0 |
|NumPanes = 4 |
|Configuration = "(H (1[41] 4[28] 2[13] 3) )" |
|End |
|Begin PaneConfiguration = 1 |
|NumPanes = 3 |
|Configuration = "(H (1 [50] 4 [25] 3))" |
|End |
|Begin PaneConfiguration = 2 |
|NumPanes = 3 |
|Configuration = "(H (1 [50] 2 [25] 3))" |
|End |
|Begin PaneConfiguration = 3 |
|NumPanes = 3 |
|Configuration = "(H (4 [30] 2 [40] 3))" |
|End |
|Begin PaneConfiguration = 4 |
|NumPanes = 2 |
|Configuration = "(H (1 [56] 3))" |
|End |
|Begin PaneConfiguration = 5 |
|NumPanes = 2 |
|Configuration = "(H (2 [66] 3))" |
|End |
|Begin PaneConfiguration = 6 |
|NumPanes = 2 |
|Configuration = "(H (4 [50] 3))" |
|End |
|Begin PaneConfiguration = 7 |
|NumPanes = 1 |
|Configuration = "(V (3))" |
|End |
|Begin PaneConfiguration = 8 |
|NumPanes = 3 |
|Configuration = "(H (1[56] 4[18] 2) )" |
|End |
|Begin PaneConfiguration = 9 |
|NumPanes = 2 |
|Configuration = "(H (1 [75] 4))" |
|End |
|Begin PaneConfiguration = 10 |
|NumPanes = 2 |
|Configuration = "(H (1[66] 2) )" |
|End |
|Begin PaneConfiguration = 11 |
|NumPanes = 2 |
|Configuration = "(H (4 [60] 2))" |
|End |
|Begin PaneConfiguration = 12 |
|NumPanes = 1 |
|Configuration = "(H (1) )" |
|End |
|Begin PaneConfiguration = 13 |
|NumPanes = 1 |
|Configuration = "(V (4))" |
|End |
|Begin PaneConfiguration = 14 |
|NumPanes = 1 |
|Configuration = "(V (2))" |
|End |
|ActivePaneConfig = 0 |
|End |
|Begin DiagramPane = |
|Begin Origin = |
|Top = 0 |
|Left = 0 |
|End |
|Begin Tables = |
|Begin Table = "PS_SQLPhysicalFilesUsed" |
|Begin Extent = |
|Top = 6 |
|Left = 38 |
|Bottom = 236 |
|Right = 239 |
|End |
|DisplayFlags = 280 |
|TopColumn = 0 |
|End |
|End |
|End |
|Begin SQLPane = |
|End |
|Begin DataPane = |
|Begin ParameterDefaults = "" |
|End |
|Begin ColumnWidths = 9 |
|Width = 284 |
|Width = 2490 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|End |
|End |
|Begin CriteriaPane = |
|Begin ColumnWidths = 11 |
|Column = 2025 |
|Alias = 900 |
|Table = 1170 |
|Output = 720 |
|Append = 1400 |
|NewValue = 1170 |
|SortType = 1350 |
|SortOrder = 1410 |
|GroupBy = 1350 |
|Filter = 1350 |
|Or = 1350 |
|Or = 1350 |
|Or = 1350 |
|End |
|End |
|End |
|' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_DB_Usage' |
|GO |
|EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_DB_Usage' |
|GO |
|/****** Object: View [dbo].[vw_DB_InstanceList] Script Date: 08/20/2014 11:49:17 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|CREATE VIEW [dbo].[vw_DB_InstanceList] |
|AS |
|SELECT Server_Name, Server_ID, Server_ServiceName |
|FROM dbo.Service_Server |
|WHERE (Server_Type = 'DB') |
|GO |
|EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] |
|Begin DesignProperties = |
|Begin PaneConfigurations = |
|Begin PaneConfiguration = 0 |
|NumPanes = 4 |
|Configuration = "(H (1[40] 4[20] 2[20] 3) )" |
|End |
|Begin PaneConfiguration = 1 |
|NumPanes = 3 |
|Configuration = "(H (1 [50] 4 [25] 3))" |
|End |
|Begin PaneConfiguration = 2 |
|NumPanes = 3 |
|Configuration = "(H (1 [50] 2 [25] 3))" |
|End |
|Begin PaneConfiguration = 3 |
|NumPanes = 3 |
|Configuration = "(H (4 [30] 2 [40] 3))" |
|End |
|Begin PaneConfiguration = 4 |
|NumPanes = 2 |
|Configuration = "(H (1 [56] 3))" |
|End |
|Begin PaneConfiguration = 5 |
|NumPanes = 2 |
|Configuration = "(H (2 [66] 3))" |
|End |
|Begin PaneConfiguration = 6 |
|NumPanes = 2 |
|Configuration = "(H (4 [50] 3))" |
|End |
|Begin PaneConfiguration = 7 |
|NumPanes = 1 |
|Configuration = "(V (3))" |
|End |
|Begin PaneConfiguration = 8 |
|NumPanes = 3 |
|Configuration = "(H (1[56] 4[18] 2) )" |
|End |
|Begin PaneConfiguration = 9 |
|NumPanes = 2 |
|Configuration = "(H (1 [75] 4))" |
|End |
|Begin PaneConfiguration = 10 |
|NumPanes = 2 |
|Configuration = "(H (1[66] 2) )" |
|End |
|Begin PaneConfiguration = 11 |
|NumPanes = 2 |
|Configuration = "(H (4 [60] 2))" |
|End |
|Begin PaneConfiguration = 12 |
|NumPanes = 1 |
|Configuration = "(H (1) )" |
|End |
|Begin PaneConfiguration = 13 |
|NumPanes = 1 |
|Configuration = "(V (4))" |
|End |
|Begin PaneConfiguration = 14 |
|NumPanes = 1 |
|Configuration = "(V (2))" |
|End |
|ActivePaneConfig = 0 |
|End |
|Begin DiagramPane = |
|Begin Origin = |
|Top = 0 |
|Left = 0 |
|End |
|Begin Tables = |
|Begin Table = "Service_Server" |
|Begin Extent = |
|Top = 6 |
|Left = 38 |
|Bottom = 219 |
|Right = 314 |
|End |
|DisplayFlags = 280 |
|TopColumn = 2 |
|End |
|End |
|End |
|Begin SQLPane = |
|End |
|Begin DataPane = |
|Begin ParameterDefaults = "" |
|End |
|Begin ColumnWidths = 9 |
|Width = 284 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|End |
|End |
|Begin CriteriaPane = |
|Begin ColumnWidths = 11 |
|Column = 1440 |
|Alias = 900 |
|Table = 1170 |
|Output = 720 |
|Append = 1400 |
|NewValue = 1170 |
|SortType = 1350 |
|SortOrder = 1410 |
|GroupBy = 1350 |
|Filter = 1350 |
|Or = 1350 |
|Or = 1350 |
|Or = 1350 |
|End |
|End |
|End |
|' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_DB_InstanceList' |
|GO |
|EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_DB_InstanceList' |
|GO |
|/****** Object: View [dbo].[vw_DB_DatabaseList] Script Date: 08/20/2014 11:49:17 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|CREATE VIEW [dbo].[vw_DB_DatabaseList] |
|AS |
|SELECT DatabaseName, WindowsServerName, SQLInstanceName |
|FROM dbo.PS_SQLDatabaseInfo |
|WHERE (SampleDateTime >= |
|(SELECT DATEADD(MINUTE, - 1, MAX(SampleDateTime)) AS Expr1 |
|FROM dbo.PS_SQLDatabaseInfo AS PS_SQLDatabaseInfo_1)) |
|GO |
|EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] |
|Begin DesignProperties = |
|Begin PaneConfigurations = |
|Begin PaneConfiguration = 0 |
|NumPanes = 4 |
|Configuration = "(H (1[40] 4[20] 2[20] 3) )" |
|End |
|Begin PaneConfiguration = 1 |
|NumPanes = 3 |
|Configuration = "(H (1 [50] 4 [25] 3))" |
|End |
|Begin PaneConfiguration = 2 |
|NumPanes = 3 |
|Configuration = "(H (1 [50] 2 [25] 3))" |
|End |
|Begin PaneConfiguration = 3 |
|NumPanes = 3 |
|Configuration = "(H (4 [30] 2 [40] 3))" |
|End |
|Begin PaneConfiguration = 4 |
|NumPanes = 2 |
|Configuration = "(H (1 [56] 3))" |
|End |
|Begin PaneConfiguration = 5 |
|NumPanes = 2 |
|Configuration = "(H (2 [66] 3))" |
|End |
|Begin PaneConfiguration = 6 |
|NumPanes = 2 |
|Configuration = "(H (4 [50] 3))" |
|End |
|Begin PaneConfiguration = 7 |
|NumPanes = 1 |
|Configuration = "(V (3))" |
|End |
|Begin PaneConfiguration = 8 |
|NumPanes = 3 |
|Configuration = "(H (1[56] 4[18] 2) )" |
|End |
|Begin PaneConfiguration = 9 |
|NumPanes = 2 |
|Configuration = "(H (1 [75] 4))" |
|End |
|Begin PaneConfiguration = 10 |
|NumPanes = 2 |
|Configuration = "(H (1[66] 2) )" |
|End |
|Begin PaneConfiguration = 11 |
|NumPanes = 2 |
|Configuration = "(H (4 [60] 2))" |
|End |
|Begin PaneConfiguration = 12 |
|NumPanes = 1 |
|Configuration = "(H (1) )" |
|End |
|Begin PaneConfiguration = 13 |
|NumPanes = 1 |
|Configuration = "(V (4))" |
|End |
|Begin PaneConfiguration = 14 |
|NumPanes = 1 |
|Configuration = "(V (2))" |
|End |
|ActivePaneConfig = 0 |
|End |
|Begin DiagramPane = |
|Begin Origin = |
|Top = 0 |
|Left = 0 |
|End |
|Begin Tables = |
|Begin Table = "PS_SQLDatabaseInfo" |
|Begin Extent = |
|Top = 6 |
|Left = 38 |
|Bottom = 125 |
|Right = 229 |
|End |
|DisplayFlags = 280 |
|TopColumn = 0 |
|End |
|End |
|End |
|Begin SQLPane = |
|End |
|Begin DataPane = |
|Begin ParameterDefaults = "" |
|End |
|Begin ColumnWidths = 9 |
|Width = 284 |
|Width = 1500 |
|Width = 1500 |
|Width = 2115 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|End |
|End |
|Begin CriteriaPane = |
|Begin ColumnWidths = 11 |
|Column = 1440 |
|Alias = 900 |
|Table = 1170 |
|Output = 720 |
|Append = 1400 |
|NewValue = 1170 |
|SortType = 1350 |
|SortOrder = 1410 |
|GroupBy = 1350 |
|Filter = 1350 |
|Or = 1350 |
|Or = 1350 |
|Or = 1350 |
|End |
|End |
|End |
|' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_DB_DatabaseList' |
|GO |
|EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_DB_DatabaseList' |
|GO |
|/****** Object: View [dbo].[vw_Service_List] Script Date: 08/20/2014 11:49:17 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|CREATE VIEW [dbo].[vw_Service_List] |
|AS |
|SELECT TOP (100) PERCENT ServiceName |
|FROM dbo.Service_Service |
|ORDER BY ServiceName |
|GO |
|EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] |
|Begin DesignProperties = |
|Begin PaneConfigurations = |
|Begin PaneConfiguration = 0 |
|NumPanes = 4 |
|Configuration = "(H (1[40] 4[20] 2[20] 3) )" |
|End |
|Begin PaneConfiguration = 1 |
|NumPanes = 3 |
|Configuration = "(H (1 [50] 4 [25] 3))" |
|End |
|Begin PaneConfiguration = 2 |
|NumPanes = 3 |
|Configuration = "(H (1 [50] 2 [25] 3))" |
|End |
|Begin PaneConfiguration = 3 |
|NumPanes = 3 |
|Configuration = "(H (4 [30] 2 [40] 3))" |
|End |
|Begin PaneConfiguration = 4 |
|NumPanes = 2 |
|Configuration = "(H (1 [56] 3))" |
|End |
|Begin PaneConfiguration = 5 |
|NumPanes = 2 |
|Configuration = "(H (2 [66] 3))" |
|End |
|Begin PaneConfiguration = 6 |
|NumPanes = 2 |
|Configuration = "(H (4 [50] 3))" |
|End |
|Begin PaneConfiguration = 7 |
|NumPanes = 1 |
|Configuration = "(V (3))" |
|End |
|Begin PaneConfiguration = 8 |
|NumPanes = 3 |
|Configuration = "(H (1[56] 4[18] 2) )" |
|End |
|Begin PaneConfiguration = 9 |
|NumPanes = 2 |
|Configuration = "(H (1 [75] 4))" |
|End |
|Begin PaneConfiguration = 10 |
|NumPanes = 2 |
|Configuration = "(H (1[66] 2) )" |
|End |
|Begin PaneConfiguration = 11 |
|NumPanes = 2 |
|Configuration = "(H (4 [60] 2))" |
|End |
|Begin PaneConfiguration = 12 |
|NumPanes = 1 |
|Configuration = "(H (1) )" |
|End |
|Begin PaneConfiguration = 13 |
|NumPanes = 1 |
|Configuration = "(V (4))" |
|End |
|Begin PaneConfiguration = 14 |
|NumPanes = 1 |
|Configuration = "(V (2))" |
|End |
|ActivePaneConfig = 0 |
|End |
|Begin DiagramPane = |
|Begin Origin = |
|Top = 0 |
|Left = 0 |
|End |
|Begin Tables = |
|Begin Table = "Service_Service" |
|Begin Extent = |
|Top = 6 |
|Left = 38 |
|Bottom = 114 |
|Right = 241 |
|End |
|DisplayFlags = 280 |
|TopColumn = 0 |
|End |
|End |
|End |
|Begin SQLPane = |
|End |
|Begin DataPane = |
|Begin ParameterDefaults = "" |
|End |
|Begin ColumnWidths = 9 |
|Width = 284 |
|Width = 2415 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|End |
|End |
|Begin CriteriaPane = |
|Begin ColumnWidths = 11 |
|Column = 1440 |
|Alias = 900 |
|Table = 1170 |
|Output = 720 |
|Append = 1400 |
|NewValue = 1170 |
|SortType = 1350 |
|SortOrder = 1410 |
|GroupBy = 1350 |
|Filter = 1350 |
|Or = 1350 |
|Or = 1350 |
|Or = 1350 |
|End |
|End |
|End |
|' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_Service_List' |
|GO |
|EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_Service_List' |
|GO |
|/****** Object: View [dbo].[vm_Business2] Script Date: 08/20/2014 11:49:17 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|CREATE VIEW [dbo].[vm_Business2] |
|AS |
|SELECT S.Service_ID, S.ServiceName, DP.Dept_Name, DC.DataClassName, SC.ServiceCritName, S.FuctionalSummary, S.TechnicalSummary, S.MiscInformation, |
|S.DataRetentionNote, S.AvailibilityRequirements, S.AppRepointingNote, S.ServiceDescriptionDoc, S.SharepointProjectPage |
|FROM dbo.Service_Service AS S INNER JOIN |
|dbo.Service_DataClass AS DC ON S.DataClass_ID = DC.DataClass_ID INNER JOIN |
|dbo.Service_Dept AS DP ON S.Service_Dept_ID = DP.Dept_ID INNER JOIN |
|dbo.Service_ServiceCrit AS SC ON S.ServiceCrit_ID = SC.ServiceCrit_ID |
|GO |
|EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] |
|Begin DesignProperties = |
|Begin PaneConfigurations = |
|Begin PaneConfiguration = 0 |
|NumPanes = 4 |
|Configuration = "(H (1[40] 4[20] 2[20] 3) )" |
|End |
|Begin PaneConfiguration = 1 |
|NumPanes = 3 |
|Configuration = "(H (1 [50] 4 [25] 3))" |
|End |
|Begin PaneConfiguration = 2 |
|NumPanes = 3 |
|Configuration = "(H (1 [50] 2 [25] 3))" |
|End |
|Begin PaneConfiguration = 3 |
|NumPanes = 3 |
|Configuration = "(H (4 [30] 2 [40] 3))" |
|End |
|Begin PaneConfiguration = 4 |
|NumPanes = 2 |
|Configuration = "(H (1 [56] 3))" |
|End |
|Begin PaneConfiguration = 5 |
|NumPanes = 2 |
|Configuration = "(H (2 [66] 3))" |
|End |
|Begin PaneConfiguration = 6 |
|NumPanes = 2 |
|Configuration = "(H (4 [50] 3))" |
|End |
|Begin PaneConfiguration = 7 |
|NumPanes = 1 |
|Configuration = "(V (3))" |
|End |
|Begin PaneConfiguration = 8 |
|NumPanes = 3 |
|Configuration = "(H (1[56] 4[18] 2) )" |
|End |
|Begin PaneConfiguration = 9 |
|NumPanes = 2 |
|Configuration = "(H (1 [75] 4))" |
|End |
|Begin PaneConfiguration = 10 |
|NumPanes = 2 |
|Configuration = "(H (1[66] 2) )" |
|End |
|Begin PaneConfiguration = 11 |
|NumPanes = 2 |
|Configuration = "(H (4 [60] 2))" |
|End |
|Begin PaneConfiguration = 12 |
|NumPanes = 1 |
|Configuration = "(H (1) )" |
|End |
|Begin PaneConfiguration = 13 |
|NumPanes = 1 |
|Configuration = "(V (4))" |
|End |
|Begin PaneConfiguration = 14 |
|NumPanes = 1 |
|Configuration = "(V (2))" |
|End |
|ActivePaneConfig = 0 |
|End |
|Begin DiagramPane = |
|Begin Origin = |
|Top = 0 |
|Left = 0 |
|End |
|Begin Tables = |
|Begin Table = "S" |
|Begin Extent = |
|Top = 6 |
|Left = 38 |
|Bottom = 128 |
|Right = 240 |
|End |
|DisplayFlags = 280 |
|TopColumn = 0 |
|End |
|Begin Table = "DC" |
|Begin Extent = |
|Top = 6 |
|Left = 278 |
|Bottom = 95 |
|Right = 442 |
|End |
|DisplayFlags = 280 |
|TopColumn = 0 |
|End |
|Begin Table = "DP" |
|Begin Extent = |
|Top = 96 |
|Left = 278 |
|Bottom = 185 |
|Right = 438 |
|End |
|DisplayFlags = 280 |
|TopColumn = 0 |
|End |
|Begin Table = "SC" |
|Begin Extent = |
|Top = 126 |
|Left = 38 |
|Bottom = 229 |
|Right = 206 |
|End |
|DisplayFlags = 280 |
|TopColumn = 0 |
|End |
|End |
|End |
|Begin SQLPane = |
|End |
|Begin DataPane = |
|Begin ParameterDefaults = "" |
|End |
|End |
|Begin CriteriaPane = |
|Begin ColumnWidths = 11 |
|Column = 1440 |
|Alias = 900 |
|Table = 1170 |
|Output = 720 |
|Append = 1400 |
|NewValue = 1170 |
|SortType = 1350 |
|SortOrder = 1410 |
|GroupBy = 1350 |
|Filter = 1350 |
|Or = 1350 |
|Or = 1350 |
|Or = 1350 |
|End |
|End |
|End |
|' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vm_Business2' |
|GO |
|EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vm_Business2' |
|GO |
|/****** Object: View [dbo].[vw_DB_ComponentDatabases] Script Date: 08/20/2014 11:49:17 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|CREATE view [dbo].[vw_DB_ComponentDatabases] |
|AS |
|select |
|(rtrim(SS.Server_Name) + '\' + rtrim(REPLACE(SS.Server_ServiceName, 'MSSQL$', ''))) AS [ServerName], |
|SS.Server_Name as [WindowsServerName], |
|SS.Server_ServiceName as [SQLInstanceName], |
|ponent_DB1, |
|ponent_DB2, |
|ponent_DB3, |
|ponent_DB4, |
|ponent_DB5, |
|ponent_DB6 |
|from |
|dbo.Service_Component SC |
|inner join |
|dbo.Service_Server SS on ponent_SQLInstance_ID = SS.Server_ID |
|where |
|ponent_SQLInstance_ID is not null |
|AND SS.Server_ServiceName |
|NOT IN ('Access-Denied', 'MSSQLSERVER', 'SQLEXPRESS', 'default') |
|UNION |
|select |
|rtrim(SS.Server_Name) AS [ServerName], |
|SS.Server_Name as [WindowsServerName], |
|SS.Server_ServiceName as [SQLInstanceName], |
|ponent_DB1, |
|ponent_DB2, |
|ponent_DB3, |
|ponent_DB4, |
|ponent_DB5, |
|ponent_DB6 |
|from |
|dbo.Service_Component SC |
|inner join |
|dbo.Service_Server SS on ponent_SQLInstance_ID = SS.Server_ID |
|where |
|ponent_SQLInstance_ID is not null |
|AND SS.Server_ServiceName IN ('MSSQLSERVER', 'SQLEXPRESS', 'default') |
|GO |
|/****** Object: View [dbo].[vw_Component_WEB] Script Date: 08/20/2014 11:49:17 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|CREATE VIEW [dbo].[vw_Component_WEB] |
|AS |
| |
|SELECT |
|Service_ID, |
|Server_ID, |
|Server_Name, |
|Component_WEB_Url |
|FROM Service_Component SC JOIN |
|Service_Server SS ON ponent_WEB_ID = SS.Server_ID |
|GO |
|/****** Object: View [dbo].[vw_Component_SSRS] Script Date: 08/20/2014 11:49:17 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|CREATE VIEW [dbo].[vw_Component_SSRS] |
|AS |
| |
|SELECT |
|Service_ID, |
|Server_ID, |
|Server_Name |
|FROM Service_Component SC JOIN |
|Service_Server SS ON ponent_SSRS_ID = SS.Server_ID |
|GO |
|/****** Object: View [dbo].[vw_Component_SSIS] Script Date: 08/20/2014 11:49:17 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|CREATE VIEW [dbo].[vw_Component_SSIS] |
|AS |
| |
|SELECT |
|Service_ID, |
|Server_ID, |
|Server_Name |
|FROM Service_Component SC JOIN |
|Service_Server SS ON ponent_SSIS_ID = SS.Server_ID |
|GO |
|/****** Object: View [dbo].[vw_Component_SSAS] Script Date: 08/20/2014 11:49:17 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|CREATE VIEW [dbo].[vw_Component_SSAS] |
|AS |
| |
|SELECT |
|Service_ID, |
|Server_ID, |
|Server_Name |
|FROM Service_Component SC JOIN |
|Service_Server SS ON ponent_SSAS_ID = SS.Server_ID |
|GO |
|/****** Object: View [dbo].[vw_Component_SQL] Script Date: 08/20/2014 11:49:17 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|CREATE VIEW [dbo].[vw_Component_SQL] |
|AS |
| |
|SELECT |
|Service_ID, |
|Server_ID, |
|Server_Name, |
|Component_DB1, |
|Component_DB2, |
|Component_DB3, |
|Component_DB4, |
|Component_DB5, |
|Component_DB6 |
|FROM Service_Component SC JOIN |
|Service_Server SS ON ponent_SQLInstance_ID = SS.Server_ID |
|GO |
|/****** Object: View [dbo].[vw_Component_EXE] Script Date: 08/20/2014 11:49:17 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|CREATE VIEW [dbo].[vw_Component_EXE] |
|AS |
| |
|SELECT |
|Service_ID, |
|Server_ID, |
|Server_Name, |
|Component_EXE_Dir |
|FROM Service_Component SC JOIN |
|Service_Server SS ON ponent_EXE_ID = SS.Server_ID |
|GO |
|/****** Object: View [dbo].[vw_Business] Script Date: 08/20/2014 11:49:17 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|CREATE VIEW [dbo].[vw_Business] |
|AS |
|SELECT dbo.Service_PersonRoles.Service_ID, |
|dbo.Service_Role.RoleName, |
|dbo.Service_Person.Name, |
|dbo.Service_Person.Tel, |
|dbo.Service_Person.Mob, |
|dbo.Service_Person.Email, |
|dbo.Service_Person.Section, |
|dbo.Service_Person.Desk_Address, |
|dbo.Service_Dept.Dept_Name, |
|dbo.Service_Service.ServiceName, |
|dbo.Service_pany_Name, |
|dbo.Service_pany_AdsLn1, |
|dbo.Service_pany_AdsLn2, |
|dbo.Service_pany_TownCity, |
|dbo.Service_pany_County, |
|dbo.Service_pany_Country, |
|dbo.Service_pany_PostCode, |
|dbo.Service_pany_MainTel, |
|dbo.Service_pany_MainEmail, |
|dbo.Service_pany_SuppTel, |
|dbo.Service_pany_SuppEmail, |
|dbo.Service_pany_Website, |
|dbo.Service_pany_Notes, |
|dbo.Service_ServiceCrit.ServiceCritName, |
|dbo.Service_DataClass.DataClassName, |
|dbo.Service_Service.FuctionalSummary, |
|dbo.Service_Service.TechnicalSummary, |
|dbo.Service_Service.MiscInformation, |
|dbo.Service_Service.DataRetentionNote, |
|dbo.Service_Service.AvailibilityRequirements, |
|dbo.Service_Service.AppRepointingNote |
| |
|FROM dbo.Service_PersonRoles INNER JOIN |
|dbo.Service_Person ON dbo.Service_PersonRoles.Person_ID = dbo.Service_Person.Person_ID INNER JOIN |
|dbo.Service_Service ON dbo.Service_PersonRoles.Service_ID = dbo.Service_Service.Service_ID INNER JOIN |
|dbo.Service_Role ON dbo.Service_PersonRoles.Role_ID = dbo.Service_Role.Role_ID INNER JOIN |
|dbo.Service_Dept ON dbo.Service_Dept.Dept_ID = dbo.Service_Person.Dept_ID INNER JOIN |
|dbo.Service_Company ON dbo.Service_pany_ID = dbo.Service_pany_ID AND |
|dbo.Service_Service.ThirdPartyCompany_ID = dbo.Service_pany_ID INNER JOIN |
|dbo.Service_DataClass ON dbo.Service_Service.DataClass_ID = dbo.Service_DataClass.DataClass_ID INNER JOIN |
|dbo.Service_ServiceCrit ON dbo.Service_Service.ServiceCrit_ID = dbo.Service_ServiceCrit.ServiceCrit_ID |
|GO |
|EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] |
|Begin DesignProperties = |
|Begin PaneConfigurations = |
|Begin PaneConfiguration = 0 |
|NumPanes = 4 |
|Configuration = "(H (1[12] 4[49] 2[20] 3) )" |
|End |
|Begin PaneConfiguration = 1 |
|NumPanes = 3 |
|Configuration = "(H (1 [50] 4 [25] 3))" |
|End |
|Begin PaneConfiguration = 2 |
|NumPanes = 3 |
|Configuration = "(H (1 [50] 2 [25] 3))" |
|End |
|Begin PaneConfiguration = 3 |
|NumPanes = 3 |
|Configuration = "(H (4 [30] 2 [40] 3))" |
|End |
|Begin PaneConfiguration = 4 |
|NumPanes = 2 |
|Configuration = "(H (1 [56] 3))" |
|End |
|Begin PaneConfiguration = 5 |
|NumPanes = 2 |
|Configuration = "(H (2 [66] 3))" |
|End |
|Begin PaneConfiguration = 6 |
|NumPanes = 2 |
|Configuration = "(H (4 [50] 3))" |
|End |
|Begin PaneConfiguration = 7 |
|NumPanes = 1 |
|Configuration = "(V (3))" |
|End |
|Begin PaneConfiguration = 8 |
|NumPanes = 3 |
|Configuration = "(H (1[56] 4[18] 2) )" |
|End |
|Begin PaneConfiguration = 9 |
|NumPanes = 2 |
|Configuration = "(H (1 [75] 4))" |
|End |
|Begin PaneConfiguration = 10 |
|NumPanes = 2 |
|Configuration = "(H (1[66] 2) )" |
|End |
|Begin PaneConfiguration = 11 |
|NumPanes = 2 |
|Configuration = "(H (4 [60] 2))" |
|End |
|Begin PaneConfiguration = 12 |
|NumPanes = 1 |
|Configuration = "(H (1) )" |
|End |
|Begin PaneConfiguration = 13 |
|NumPanes = 1 |
|Configuration = "(V (4))" |
|End |
|Begin PaneConfiguration = 14 |
|NumPanes = 1 |
|Configuration = "(V (2))" |
|End |
|ActivePaneConfig = 0 |
|End |
|Begin DiagramPane = |
|Begin Origin = |
|Top = 0 |
|Left = 0 |
|End |
|Begin Tables = |
|Begin Table = "Service_PersonRoles" |
|Begin Extent = |
|Top = 57 |
|Left = 338 |
|Bottom = 150 |
|Right = 489 |
|End |
|DisplayFlags = 280 |
|TopColumn = 0 |
|End |
|Begin Table = "Service_Person" |
|Begin Extent = |
|Top = 30 |
|Left = 526 |
|Bottom = 226 |
|Right = 677 |
|End |
|DisplayFlags = 280 |
|TopColumn = 0 |
|End |
|Begin Table = "Service_Service" |
|Begin Extent = |
|Top = 12 |
|Left = 94 |
|Bottom = 252 |
|Right = 287 |
|End |
|DisplayFlags = 280 |
|TopColumn = 0 |
|End |
|Begin Table = "Service_Role" |
|Begin Extent = |
|Top = 81 |
|Left = 748 |
|Bottom = 159 |
|Right = 899 |
|End |
|DisplayFlags = 280 |
|TopColumn = 0 |
|End |
|Begin Table = "Service_Dept" |
|Begin Extent = |
|Top = 169 |
|Left = 747 |
|Bottom = 247 |
|Right = 898 |
|End |
|DisplayFlags = 280 |
|TopColumn = 0 |
|End |
|Begin Table = "Service_Company" |
|Begin Extent = |
|Top = 158 |
|Left = 313 |
|Bottom = 266 |
|Right = 501 |
|End |
|DisplayFlags = 280 |
|TopColumn = 11 |
|End |
|Begin Table = "Service_DataClass" |
|Begin Extent = |
|Top = 228 |
|Left = 539 |
|Bottom = 306 |
|Rig' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_Business' |
|GO |
|EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'ht = 694 |
|End |
|DisplayFlags = 280 |
|TopColumn = 0 |
|End |
|Begin Table = "Service_ServiceCrit" |
|Begin Extent = |
|Top = 259 |
|Left = 726 |
|Bottom = 337 |
|Right = 885 |
|End |
|DisplayFlags = 280 |
|TopColumn = 0 |
|End |
|End |
|End |
|Begin SQLPane = |
|End |
|Begin DataPane = |
|Begin ParameterDefaults = "" |
|End |
|Begin ColumnWidths = 9 |
|Width = 284 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|End |
|End |
|Begin CriteriaPane = |
|Begin ColumnWidths = 11 |
|Column = 3405 |
|Alias = 900 |
|Table = 1170 |
|Output = 720 |
|Append = 1400 |
|NewValue = 1170 |
|SortType = 1350 |
|SortOrder = 1410 |
|GroupBy = 1350 |
|Filter = 1350 |
|Or = 1350 |
|Or = 1350 |
|Or = 1350 |
|End |
|End |
|End |
|' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_Business' |
|GO |
|EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_Business' |
|GO |
|/****** Object: View [dbo].[vm_Component_OLD] Script Date: 08/20/2014 11:49:17 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|CREATE VIEW [dbo].[vm_Component_OLD] |
|AS |
|SELECT TOP (100) PERCENT dbo.Service_Service.ServiceName, dbo.Service_ponent_FriendlyName, dbo.Service_Server.Server_Name, |
|dbo.Service_Server.Server_Type, dbo.Service_Server.Server_IP1, dbo.Service_Server.Server_IP2, dbo.Service_Server.Server_IP3, dbo.Service_Server.Server_OS, |
|dbo.Service_Server.Server_Virtual, dbo.Service_Server.Server_RecoverPoint, dbo.Service_Server.Server_Notes, dbo.Service_Server.Server_ShareAvailableInDR, |
|dbo.Service_Server.Server_ServiceName, dbo.Service_ponent_EXE_Dir, dbo.Service_ponent_WEB_URL, |
|dbo.Service_ponent_DB1, dbo.Service_ponent_DB2, dbo.Service_ponent_DB3, |
|dbo.Service_ponent_DB4, dbo.Service_ponent_DB5, dbo.Service_ponent_DB6, |
|dbo.Service_ponent_Live, dbo.Service_ponent_Notes, dbo.Service_ponent_IO_Routines |
|FROM dbo.Service_Component INNER JOIN |
|dbo.Service_Server ON dbo.Service_ponent_EXE_ID = dbo.Service_Server.Server_ID OR |
|dbo.Service_ponent_WEB_ID = dbo.Service_Server.Server_ID OR |
|dbo.Service_ponent_SQLInstance_ID = dbo.Service_Server.Server_ID OR |
|dbo.Service_ponent_SSRS_ID = dbo.Service_Server.Server_ID OR dbo.Service_ponent_SSIS_ID = dbo.Service_Server.Server_ID OR |
|dbo.Service_ponent_SSAS_ID = dbo.Service_Server.Server_ID INNER JOIN |
|dbo.Service_Service ON dbo.Service_Component.Service_ID = dbo.Service_Service.Service_ID |
|ORDER BY dbo.Service_Service.ServiceName, dbo.Service_ponent_FriendlyName |
|GO |
|EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] |
|Begin DesignProperties = |
|Begin PaneConfigurations = |
|Begin PaneConfiguration = 0 |
|NumPanes = 4 |
|Configuration = "(H (1[40] 4[20] 2[20] 3) )" |
|End |
|Begin PaneConfiguration = 1 |
|NumPanes = 3 |
|Configuration = "(H (1 [50] 4 [25] 3))" |
|End |
|Begin PaneConfiguration = 2 |
|NumPanes = 3 |
|Configuration = "(H (1 [50] 2 [25] 3))" |
|End |
|Begin PaneConfiguration = 3 |
|NumPanes = 3 |
|Configuration = "(H (4 [30] 2 [40] 3))" |
|End |
|Begin PaneConfiguration = 4 |
|NumPanes = 2 |
|Configuration = "(H (1 [56] 3))" |
|End |
|Begin PaneConfiguration = 5 |
|NumPanes = 2 |
|Configuration = "(H (2 [66] 3))" |
|End |
|Begin PaneConfiguration = 6 |
|NumPanes = 2 |
|Configuration = "(H (4 [50] 3))" |
|End |
|Begin PaneConfiguration = 7 |
|NumPanes = 1 |
|Configuration = "(V (3))" |
|End |
|Begin PaneConfiguration = 8 |
|NumPanes = 3 |
|Configuration = "(H (1[56] 4[18] 2) )" |
|End |
|Begin PaneConfiguration = 9 |
|NumPanes = 2 |
|Configuration = "(H (1 [75] 4))" |
|End |
|Begin PaneConfiguration = 10 |
|NumPanes = 2 |
|Configuration = "(H (1[66] 2) )" |
|End |
|Begin PaneConfiguration = 11 |
|NumPanes = 2 |
|Configuration = "(H (4 [60] 2))" |
|End |
|Begin PaneConfiguration = 12 |
|NumPanes = 1 |
|Configuration = "(H (1) )" |
|End |
|Begin PaneConfiguration = 13 |
|NumPanes = 1 |
|Configuration = "(V (4))" |
|End |
|Begin PaneConfiguration = 14 |
|NumPanes = 1 |
|Configuration = "(V (2))" |
|End |
|ActivePaneConfig = 0 |
|End |
|Begin DiagramPane = |
|Begin Origin = |
|Top = 0 |
|Left = 0 |
|End |
|Begin Tables = |
|Begin Table = "Service_Component" |
|Begin Extent = |
|Top = 16 |
|Left = 410 |
|Bottom = 207 |
|Right = 629 |
|End |
|DisplayFlags = 280 |
|TopColumn = 11 |
|End |
|Begin Table = "Service_Server" |
|Begin Extent = |
|Top = 14 |
|Left = 692 |
|Bottom = 197 |
|Right = 905 |
|End |
|DisplayFlags = 280 |
|TopColumn = 3 |
|End |
|Begin Table = "Service_Service" |
|Begin Extent = |
|Top = 15 |
|Left = 73 |
|Bottom = 185 |
|Right = 276 |
|End |
|DisplayFlags = 280 |
|TopColumn = 0 |
|End |
|End |
|End |
|Begin SQLPane = |
|End |
|Begin DataPane = |
|Begin ParameterDefaults = "" |
|End |
|Begin ColumnWidths = 25 |
|Width = 284 |
|Width = 3000 |
|Width = 2340 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|End |
|End |
|Begin CriteriaPane = |
|Begin ColumnWidths = 11 |
|Column = 1440 |
|Alias = 900 |
|Table = 1170 |
|Output = 720 |
|Append = 1400 |
|NewValue = 1170 |
|SortType = 1350 |
|SortOr' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vm_Component_OLD' |
|GO |
|EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'der = 1410 |
|GroupBy = 1350 |
|Filter = 1350 |
|Or = 1350 |
|Or = 1350 |
|Or = 1350 |
|End |
|End |
|End |
|' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vm_Component_OLD' |
|GO |
|EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vm_Component_OLD' |
|GO |
|/****** Object: View [dbo].[vm_Component] Script Date: 08/20/2014 11:49:17 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|CREATE VIEW [dbo].[vm_Component] |
|AS |
| |
|SELECT S.Service_ID, |
|S.ServiceName, |
|ponent_FriendlyName, |
|ponent_Live, |
|SS.Server_Name, |
|SS.Server_RecoverPoint, |
|ponent_EXE_Dir, |
|ponent_WEB_URL, |
|ponent_DB1, |
|ponent_DB2, |
|ponent_DB3, |
|ponent_DB4, |
|ponent_DB5, |
|ponent_DB6, |
|CASE WHEN ponent_SSRS_ID IS NOT NULL THEN SS.Server_Name ELSE NULL END as Component_SSRS, |
|CASE WHEN ponent_SSAS_ID IS NOT NULL THEN SS.Server_Name ELSE NULL END as Component_SSAS, |
|CASE WHEN ponent_SSIS_ID IS NOT NULL THEN SS.Server_Name ELSE NULL END as Component_SSIS |
|FROM Service_Service S |
|JOIN Service_Component SC ON S.Service_ID = SC.Service_ID |
|JOIN Service_Server SS ON SS.Server_ID = ponent_SQLInstance_ID OR |
|SS.Server_ID = ponent_EXE_ID OR |
|SS.Server_ID = ponent_WEB_ID OR |
|SS.Server_ID = ponent_SSRS_ID OR |
|SS.Server_ID = ponent_SSIS_ID OR |
|SS.Server_ID = ponent_SSAS_ID |
|GO |
|/****** Object: View [dbo].[vw_ServicePersonRole] Script Date: 08/20/2014 11:49:17 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|CREATE View [dbo].[vw_ServicePersonRole] |
|as |
|select |
|Service_PersonRoles.Service_ID, |
|Service_Service.ServiceName, |
|Service_Role.RoleName, |
|dbo.Service_Person.Name, |
|dbo.Service_Person.Tel, |
|dbo.Service_Person.Mob, |
|dbo.Service_Person.email, |
|dbo.Service_Person.Section, |
|dbo.Service_Person.Desk_Address, |
|dbo.Service_Dept.Dept_Name, |
|Service_pany_Name, |
|Service_pany_AdsLn1, |
|Service_pany_AdsLn2, |
|Service_pany_MainTel, |
|Service_pany_MainEmail |
|from |
|Service_PersonRoles |
|INNER JOIN |
|Service_Person |
|ON Service_PersonRoles.Person_ID = Service_Person.Person_ID |
|INNER JOIN |
|Service_Service |
|ON Service_PersonRoles.Service_ID = Service_Service.Service_ID |
|INNER JOIN |
|Service_Role |
|ON Service_PersonRoles.Role_ID = Service_Role.Role_ID |
|INNER JOIN Service_Dept |
|on Service_Dept.Dept_ID = Service_Person.Dept_ID |
|LEFT JOIN |
|Service_Company |
|ON Service_Service.ThirdPartyCompany_ID = Service_pany_ID |
|GO |
|/****** Object: View [dbo].[vw_Downtime] Script Date: 08/20/2014 11:49:17 ******/ |
|SET ANSI_NULLS ON |
|GO |
|SET QUOTED_IDENTIFIER ON |
|GO |
|CREATE VIEW [dbo].[vw_Downtime] |
|AS |
|SELECT dbo.Service_Server.Server_Name, dbo.Service_Service.ServiceName, dbo.Service_ponent_FriendlyName, dbo.Service_ponent_DB1, |
|ISNULL(dbo.Service_ponent_DB2, 'none') AS Component_DB2, dbo.Service_ponent_DB3, dbo.Service_ponent_DB4, |
|dbo.Service_ponent_DB5, dbo.Service_ponent_DB6, dbo.Service_Component.Service_ID, dbo.Service_ponent_Live, |
|dbo.vw_ServicePersonRole.RoleName, dbo.vw_ServicePersonRole.Name, dbo.vw_ServicePersonRole.Tel, dbo.vw_ServicePersonRole.Mob, |
|dbo.vw_ServicePersonRole.email, dbo.Service_Server.Server_Type |
|FROM dbo.Service_Server INNER JOIN |
|dbo.Service_Component ON dbo.Service_Server.Server_ID = dbo.Service_ponent_SQLInstance_ID OR |
|dbo.Service_Server.Server_ID = dbo.Service_ponent_EXE_ID OR dbo.Service_Server.Server_ID = dbo.Service_ponent_WEB_ID OR |
|dbo.Service_Server.Server_ID = dbo.Service_ponent_SSRS_ID OR dbo.Service_Server.Server_ID = dbo.Service_ponent_SSIS_ID OR |
|dbo.Service_Server.Server_ID = dbo.Service_ponent_SSAS_ID LEFT JOIN |
|dbo.vw_ServicePersonRole ON dbo.vw_ServicePersonRole.Service_ID = dbo.Service_Component.Service_ID INNER JOIN |
|dbo.Service_Service ON dbo.Service_Service.Service_ID = dbo.Service_Component.Service_ID |
|GO |
|EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] |
|Begin DesignProperties = |
|Begin PaneConfigurations = |
|Begin PaneConfiguration = 0 |
|NumPanes = 4 |
|Configuration = "(H (1[29] 4[17] 2[36] 3) )" |
|End |
|Begin PaneConfiguration = 1 |
|NumPanes = 3 |
|Configuration = "(H (1 [50] 4 [25] 3))" |
|End |
|Begin PaneConfiguration = 2 |
|NumPanes = 3 |
|Configuration = "(H (1 [50] 2 [25] 3))" |
|End |
|Begin PaneConfiguration = 3 |
|NumPanes = 3 |
|Configuration = "(H (4 [30] 2 [40] 3))" |
|End |
|Begin PaneConfiguration = 4 |
|NumPanes = 2 |
|Configuration = "(H (1 [56] 3))" |
|End |
|Begin PaneConfiguration = 5 |
|NumPanes = 2 |
|Configuration = "(H (2 [66] 3))" |
|End |
|Begin PaneConfiguration = 6 |
|NumPanes = 2 |
|Configuration = "(H (4 [50] 3))" |
|End |
|Begin PaneConfiguration = 7 |
|NumPanes = 1 |
|Configuration = "(V (3))" |
|End |
|Begin PaneConfiguration = 8 |
|NumPanes = 3 |
|Configuration = "(H (1[56] 4[18] 2) )" |
|End |
|Begin PaneConfiguration = 9 |
|NumPanes = 2 |
|Configuration = "(H (1 [75] 4))" |
|End |
|Begin PaneConfiguration = 10 |
|NumPanes = 2 |
|Configuration = "(H (1[66] 2) )" |
|End |
|Begin PaneConfiguration = 11 |
|NumPanes = 2 |
|Configuration = "(H (4 [60] 2))" |
|End |
|Begin PaneConfiguration = 12 |
|NumPanes = 1 |
|Configuration = "(H (1) )" |
|End |
|Begin PaneConfiguration = 13 |
|NumPanes = 1 |
|Configuration = "(V (4))" |
|End |
|Begin PaneConfiguration = 14 |
|NumPanes = 1 |
|Configuration = "(V (2))" |
|End |
|ActivePaneConfig = 0 |
|End |
|Begin DiagramPane = |
|Begin Origin = |
|Top = 0 |
|Left = 0 |
|End |
|Begin Tables = |
|Begin Table = "Service_Server" |
|Begin Extent = |
|Top = 6 |
|Left = 38 |
|Bottom = 125 |
|Right = 260 |
|End |
|DisplayFlags = 280 |
|TopColumn = 0 |
|End |
|Begin Table = "Service_Component" |
|Begin Extent = |
|Top = 126 |
|Left = 38 |
|Bottom = 245 |
|Right = 266 |
|End |
|DisplayFlags = 280 |
|TopColumn = 0 |
|End |
|Begin Table = "vw_ServicePersonRole" |
|Begin Extent = |
|Top = 6 |
|Left = 298 |
|Bottom = 125 |
|Right = 458 |
|End |
|DisplayFlags = 280 |
|TopColumn = 0 |
|End |
|Begin Table = "Service_Service" |
|Begin Extent = |
|Top = 246 |
|Left = 38 |
|Bottom = 365 |
|Right = 240 |
|End |
|DisplayFlags = 280 |
|TopColumn = 0 |
|End |
|End |
|End |
|Begin SQLPane = |
|End |
|Begin DataPane = |
|Begin ParameterDefaults = "" |
|End |
|Begin ColumnWidths = 17 |
|Width = 284 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|Width = 1500 |
|End |
|End |
|Begin CriteriaPane = |
|Begin ColumnWidths = 11 |
|Column = 1440 |
|Alias = 900 |
|Table = 1170 |
|Output = 720 |
|' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_Downtime' |
|GO |
|EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N' Append = 1400 |
|NewValue = 1170 |
|SortType = 1350 |
|SortOrder = 1410 |
|GroupBy = 1350 |
|Filter = 1350 |
|Or = 1350 |
|Or = 1350 |
|Or = 1350 |
|End |
|End |
|End |
|' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_Downtime' |
|GO |
|EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_Downtime' |
|GO |
Powershell Script Files:
|[pic] |[pic] |
|[pic] |[pic] |
|[pic] | |
|USE [msdb] |
|GO |
| |
|/****** Object: Job [SQLDOT_Update] Script Date: 08/20/2014 11:58:24 ******/ |
|BEGIN TRANSACTION |
|DECLARE @ReturnCode INT |
|SELECT @ReturnCode = 0 |
|/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 08/20/2014 11:58:24 ******/ |
|IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) |
|BEGIN |
|EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' |
|IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback |
| |
|END |
| |
|DECLARE @jobId BINARY(16) |
|EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'SQLDOT_Update', |
|@enabled=1, |
|@notify_level_eventlog=0, |
|@notify_level_email=0, |
|@notify_level_netsend=0, |
|@notify_level_page=0, |
|@delete_level=0, |
|@description=N'No description available.', |
|@category_name=N'[Uncategorized (Local)]', |
|@owner_login_name=N'sa', @job_id = @jobId OUTPUT |
|IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback |
|/****** Object: Step [PS_SQL-VersionStrings] Script Date: 08/20/2014 11:58:24 ******/ |
|EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'PS_SQL-VersionStrings', |
|@step_id=1, |
|@cmdexec_success_code=0, |
|@on_success_action=3, |
|@on_success_step_id=0, |
|@on_fail_action=3, |
|@on_fail_step_id=0, |
|@retry_attempts=0, |
|@retry_interval=0, |
|@os_run_priority=0, @subsystem=N'PowerShell', |
|@command=N'& "D:\SQLDOT\POWERSHELL\SqlServer-Version-Discovery.ps1"', |
|@database_name=N'master', |
|@flags=0 |
|IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback |
|/****** Object: Step [PS_SQL-DatabaseInfo] Script Date: 08/20/2014 11:58:24 ******/ |
|EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'PS_SQL-DatabaseInfo', |
|@step_id=2, |
|@cmdexec_success_code=0, |
|@on_success_action=3, |
|@on_success_step_id=0, |
|@on_fail_action=2, |
|@on_fail_step_id=0, |
|@retry_attempts=0, |
|@retry_interval=0, |
|@os_run_priority=0, @subsystem=N'PowerShell', |
|@command=N'& "D:\SQLDOT\POWERSHELL\SqlServer-DatabaseInfo.ps1"', |
|@database_name=N'master', |
|@flags=0 |
|IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback |
|/****** Object: Step [AddedRemovedDatabases] Script Date: 08/20/2014 11:58:24 ******/ |
|EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'AddedRemovedDatabases', |
|@step_id=3, |
|@cmdexec_success_code=0, |
|@on_success_action=3, |
|@on_success_step_id=0, |
|@on_fail_action=3, |
|@on_fail_step_id=0, |
|@retry_attempts=0, |
|@retry_interval=0, |
|@os_run_priority=0, @subsystem=N'TSQL', |
|@command=N'USE [SQLDOT] |
|GO |
| |
|-- Updating Table with Databases Added |
| |
|INSERT INTO [dbo].[AddedRemovedDatabases] |
|([WindowsServerName] |
|,[SQLInstanceName] |
|,[DatabaseName] |
|,[Added/Removed] |
|,[created] |
|,[DateChanged]) |
| |
|SELECT LastDate.WindowsServerName, |
|LastDate.SQLInstanceName, |
|LastDate.DatabaseName, |
|''Added'', |
|LastDate.created, |
|CONVERT(date,LastDate.SampleDateTime) as DateChanged |
| |
|FROM ( |
|SELECT [WindowsServerName] |
|,[SQLInstanceName] |
|,[DatabaseName] |
|,[created] |
|,MAX([SampleDateTime]) as SampleDateTime |
|FROM [dbo].[PS_SQLDatabaseInfo] |
|WHERE CONVERT(date,[SampleDateTime]) IN ( |
|SELECT TOP 1 CONVERT(date,[SampleDateTime]) |
|FROM [dbo].[PS_SQLDatabaseInfo] |
|GROUP BY CONVERT(date,[SampleDateTime]) |
|ORDER BY CONVERT(date,[SampleDateTime]) desc) |
|GROUP BY [WindowsServerName] |
|,[SQLInstanceName] |
|,[DatabaseName] |
|,[created] |
|) LastDate |
| |
|LEFT JOIN |
| |
|( |
|SELECT [WindowsServerName] |
|,[SQLInstanceName] |
|,[DatabaseName] |
|,[created] |
|,MAX([SampleDateTime]) as SampleDateTime |
|FROM [dbo].[PS_SQLDatabaseInfo] |
|WHERE CONVERT(date,[SampleDateTime]) IN ( |
|SELECT TOP 1 PrevDate |
|FROM ( |
|SELECT TOP 2 CONVERT(date,[SampleDateTime]) as PrevDate |
|FROM [dbo].[PS_SQLDatabaseInfo] |
|GROUP BY CONVERT(date,[SampleDateTime]) |
|ORDER BY CONVERT(date,[SampleDateTime]) desc) as T |
|ORDER BY PrevDate asc) |
|GROUP BY [WindowsServerName] |
|,[SQLInstanceName] |
|,[DatabaseName] |
|,[created] |
|) PrevDate |
| |
|ON LastDate.WindowsServerName = PrevDate.WindowsServerName |
|AND LastDate.SQLInstanceName = PrevDate.SQLInstanceName |
|AND LastDate.DatabaseName = PrevDate.DatabaseName |
|WHERE PrevDate.WindowsServerName IS NULL |
| |
|GO |
| |
|-- Updating Table with Databases Removed |
| |
|INSERT INTO [dbo].[AddedRemovedDatabases] |
|([WindowsServerName] |
|,[SQLInstanceName] |
|,[DatabaseName] |
|,[Added/Removed] |
|,[created] |
|,[DateChanged]) |
| |
| |
|SELECT PrevDate.WindowsServerName, |
|PrevDate.SQLInstanceName, |
|PrevDate.DatabaseName, |
|''Removed'', |
|PrevDate.created, |
|(SELECT TOP 1 CONVERT(date,[SampleDateTime]) |
|FROM [dbo].[PS_SQLDatabaseInfo] |
|GROUP BY CONVERT(date,[SampleDateTime]) |
|ORDER BY CONVERT(date,[SampleDateTime]) desc) as DateChanged |
| |
|FROM ( |
|SELECT [WindowsServerName] |
|,[SQLInstanceName] |
|,[DatabaseName] |
|,[created] |
|,MAX([SampleDateTime]) as SampleDateTime |
|FROM [dbo].[PS_SQLDatabaseInfo] |
|WHERE CONVERT(date,[SampleDateTime]) IN ( |
|SELECT TOP 1 CONVERT(date,[SampleDateTime]) |
|FROM [dbo].[PS_SQLDatabaseInfo] |
|GROUP BY CONVERT(date,[SampleDateTime]) |
|ORDER BY CONVERT(date,[SampleDateTime]) desc) |
|GROUP BY [WindowsServerName] |
|,[SQLInstanceName] |
|,[DatabaseName] |
|,[created] |
|) LastDate |
| |
|RIGHT JOIN |
| |
|( |
|SELECT [WindowsServerName] |
|,[SQLInstanceName] |
|,[DatabaseName] |
|,[created] |
|,MAX([SampleDateTime]) as SampleDateTime |
|FROM [dbo].[PS_SQLDatabaseInfo] |
|WHERE CONVERT(date,[SampleDateTime]) IN ( |
|SELECT TOP 1 PrevDate |
|FROM ( |
|SELECT TOP 2 CONVERT(date,[SampleDateTime]) as PrevDate |
|FROM [dbo].[PS_SQLDatabaseInfo] |
|GROUP BY CONVERT(date,[SampleDateTime]) |
|ORDER BY CONVERT(date,[SampleDateTime]) desc) as T |
|ORDER BY PrevDate asc) |
|GROUP BY [WindowsServerName] |
|,[SQLInstanceName] |
|,[DatabaseName] |
|,[created] |
|) PrevDate |
| |
|ON LastDate.WindowsServerName = PrevDate.WindowsServerName |
|AND LastDate.SQLInstanceName = PrevDate.SQLInstanceName |
|AND LastDate.DatabaseName = PrevDate.DatabaseName |
|WHERE LastDate.WindowsServerName IS NULL |
| |
|GO', |
|@database_name=N'SQLDOT', |
|@flags=0 |
|IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback |
|/****** Object: Step [PS_SQL-PhysicalFiles] Script Date: 08/20/2014 11:58:24 ******/ |
|EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'PS_SQL-PhysicalFiles', |
|@step_id=4, |
|@cmdexec_success_code=0, |
|@on_success_action=3, |
|@on_success_step_id=0, |
|@on_fail_action=3, |
|@on_fail_step_id=0, |
|@retry_attempts=0, |
|@retry_interval=0, |
|@os_run_priority=0, @subsystem=N'PowerShell', |
|@command=N'& "D:\SQLDOT\POWERSHELL\SqlServer-PhysicalFiles.ps1"', |
|@database_name=N'master', |
|@flags=0 |
|IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback |
|/****** Object: Step [PS_SQL-BackupsRun] Script Date: 08/20/2014 11:58:24 ******/ |
|EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'PS_SQL-BackupsRun', |
|@step_id=5, |
|@cmdexec_success_code=0, |
|@on_success_action=3, |
|@on_success_step_id=0, |
|@on_fail_action=3, |
|@on_fail_step_id=0, |
|@retry_attempts=0, |
|@retry_interval=0, |
|@os_run_priority=0, @subsystem=N'PowerShell', |
|@command=N'& "D:\SQLDOT\POWERSHELL\SqlServer-BackupsRun.ps1"', |
|@database_name=N'master', |
|@flags=0 |
|IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback |
|/****** Object: Step [PS_SQLDBFileSizeUsed] Script Date: 08/20/2014 11:58:24 ******/ |
|EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'PS_SQLDBFileSizeUsed', |
|@step_id=6, |
|@cmdexec_success_code=0, |
|@on_success_action=3, |
|@on_success_step_id=0, |
|@on_fail_action=3, |
|@on_fail_step_id=0, |
|@retry_attempts=0, |
|@retry_interval=0, |
|@os_run_priority=0, @subsystem=N'PowerShell', |
|@command=N'& "D:\SQLDOT\POWERSHELL\SqlServer-PhysicalFilesUsed.PS1"', |
|@database_name=N'master', |
|@flags=0 |
|IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback |
|/****** Object: Step [EmailReport] Script Date: 08/20/2014 11:58:24 ******/ |
|EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'EmailReport', |
|@step_id=7, |
|@cmdexec_success_code=0, |
|@on_success_action=1, |
|@on_success_step_id=0, |
|@on_fail_action=2, |
|@on_fail_step_id=0, |
|@retry_attempts=0, |
|@retry_interval=0, |
|@os_run_priority=0, @subsystem=N'TSQL', |
|@command=N'set nocount on |
|go |
|CREATE TABLE tempdb.dbo.backup_details ( |
|WindowsServerName VARCHAR(30), |
|SQLInstanceName VARCHAR(30), |
|DatabaseCount smallint, |
|BkpCount_1dFULL smallint NULL, |
|BkpCount_1wFULL smallint NULL, |
|BkpSizeFULL bigint NULL, |
|BkpCount_1dDIFF smallint NULL, |
|BkpCount_1wDIFF smallint NULL, |
|BkpSizeDIFF bigint NULL, |
|BkpCount_1dTLOG smallint NULL, |
|BkpCount_1wTLOG smallint NULL, |
|BkpSizeTLOG bigint NULL |
|) |
|go |
|DECLARE @WindowsServerName VARCHAR(30), |
|@SQLInstanceName VARCHAR(30), |
|@DatabaseCount smallint, |
|@yesterday date, |
|@last_week date |
| |
|DECLARE @db_BkpCount_1dFULL smallint, |
|@db_BkpCount_1wFULL smallint, |
|@db_bkpsizeFULL bigint |
| |
|DECLARE @db_BkpCount_1dDIFF smallint, |
|@db_BkpCount_1wDIFF smallint, |
|@db_bkpsizeDIFF bigint |
| |
|DECLARE @db_bkpcount_1dTLOG smallint, |
|@db_BkpCount_1wTLOG smallint, |
|@db_bkpsizeTLOG bigint |
| |
|select @yesterday = dateadd( DAY, -1, convert(date, getdate())) |
|select @last_week = dateadd( DAY, -8, convert(date, getdate())) |
| |
| |
|DECLARE svr_cursor CURSOR FOR |
|select |
|windowsservername, -- sqlinstancename |
|case sqlinstancename |
|when ''MSSQLSERVER'' then ''default'' |
|else sqlinstancename |
|end |
|from |
|SQLDOT.dbo.PS_SQLInstances |
|where |
|Process = ''Y'' |
|order by 1 |
| |
|-- select * FROM SQLDOT.dbo.PS_SQLBackupsRun |
| |
|open svr_cursor |
| |
|fetch next |
|from |
|svr_cursor |
|into |
|@WindowsServerName, |
|@SQLInstanceName |
| |
|-- ##################### Start of Backups Cursor ############################### |
|WHILE (@@FETCH_STATUS = 0) |
|BEGIN |
|Select @db_BkpCount_1dFULL = null, @db_BkpCount_1wFULL = null, @db_bkpsizeFULL = null, |
|@db_BkpCount_1dDIFF = null, @db_BkpCount_1wDIFF = null, @db_bkpsizeDIFF = null, |
|@db_BkpCount_1dTLOG = null, @db_BkpCount_1wTLOG = null, @db_bkpsizeTLOG = null |
| |
|select @DatabaseCount = count(distinct DatabaseName) |
|from |
|SQLDOT.dbo.PS_SQLBackupsRun |
|where |
|WindowsServerName = @WindowsServerName and |
|SQLInstanceName = @SQLInstanceName and |
|convert(DATE, BackupStartDate) = @yesterday |
| |
| |
|select @db_BkpCount_1dFULL = count(BackupStartDate) |
|from |
|SQLDOT.dbo.PS_SQLBackupsRun |
|where |
|WindowsServerName = @WindowsServerName and |
|SQLInstanceName = @SQLInstanceName and |
|BackupType = ''Full'' and |
|convert(DATE, BackupStartDate) = @yesterday |
| |
|select @db_BkpCount_1wFULL = count(BackupStartDate) |
|from |
|SQLDOT.dbo.PS_SQLBackupsRun |
|where |
|WindowsServerName = @WindowsServerName and |
|SQLInstanceName = @SQLInstanceName and |
|BackupType = ''Full'' and |
|convert(DATE, BackupStartDate) = @last_week |
| |
|select @db_bkpsizeFULL = sum(BackupSizeMB) |
|from |
|SQLDOT.dbo.PS_SQLBackupsRun |
|where |
|WindowsServerName = @WindowsServerName and |
|SQLInstanceName = @SQLInstanceName and |
|BackupType = ''Full'' and |
|convert(DATE, BackupStartDate) = @yesterday |
| |
|select @db_BkpCount_1dDIFF = count(BackupStartDate) |
|from |
|SQLDOT.dbo.PS_SQLBackupsRun |
|where |
|WindowsServerName = @WindowsServerName and |
|SQLInstanceName = @SQLInstanceName and |
|BackupType = ''Diff'' and |
|convert(DATE, BackupStartDate) = @yesterday |
| |
|select @db_BkpCount_1wDIFF = count(BackupStartDate) |
|from |
|SQLDOT.dbo.PS_SQLBackupsRun |
|where |
|WindowsServerName = @WindowsServerName and |
|SQLInstanceName = @SQLInstanceName and |
|BackupType =''Diff'' and |
|convert(DATE, BackupStartDate) = @last_week |
| |
|select @db_bkpsizeDIFF = sum(BackupSizeMB) |
|from |
|SQLDOT.dbo.PS_SQLBackupsRun |
|where |
|WindowsServerName = @WindowsServerName and |
|SQLInstanceName = @SQLInstanceName and |
|BackupType = ''Diff'' and |
|convert(DATE, BackupStartDate) = @yesterday |
| |
|select @db_BkpCount_1dTLOG = count(BackupStartDate) |
|from |
|SQLDOT.dbo.PS_SQLBackupsRun |
|where |
|WindowsServerName = @WindowsServerName and |
|SQLInstanceName = @SQLInstanceName and |
|BackupType =''TLog'' and |
|convert(DATE, BackupStartDate) = @yesterday |
| |
|select @db_BkpCount_1wTLOG = count(BackupStartDate) |
|from |
|SQLDOT.dbo.PS_SQLBackupsRun |
|where |
|WindowsServerName = @WindowsServerName and |
|SQLInstanceName = @SQLInstanceName and |
|BackupType =''TLog'' and |
|convert(DATE, BackupStartDate) = @last_week |
| |
|select @db_bkpsizeTLOG = sum(BackupSizeMB) |
|from |
|SQLDOT.dbo.PS_SQLBackupsRun |
|where |
|WindowsServerName = @WindowsServerName and |
|SQLInstanceName = @SQLInstanceName and |
|BackupType = ''TLog'' and |
|convert(DATE, BackupStartDate) = @yesterday |
| |
|insert into tempdb.dbo.backup_details |
|( |
|WindowsServerName, |
|SQLInstanceName, |
|DatabaseCount, |
|BkpCount_1dFULL, |
|BkpCount_1wFULL, |
|BkpSizeFULL, |
|BkpCount_1dDIFF, |
|BkpCount_1wDIFF, |
|BkpSizeDIFF, |
|BkpCount_1dTLOG, |
|BkpCount_1wTLOG, |
|BkpSizeTLOG |
|) |
| |
|select |
|@WindowsServerName, |
|@SQLInstanceName, |
|@DatabaseCount, |
|@db_BkpCount_1dFULL, |
|@db_BkpCount_1wFULL, |
|@db_bkpsizeFULL, |
|@db_BkpCount_1dDIFF, |
|@db_BkpCount_1wDIFF, |
|@db_bkpsizeDIFF, |
|@db_bkpcount_1dTLOG, |
|@db_BkpCount_1wTLOG, |
|@db_bkpsizeTLOG |
|fetch next |
|from |
|svr_cursor |
|into |
|@WindowsServerName, |
|@SQLInstanceName |
| |
|END |
|close svr_cursor |
|deallocate svr_cursor |
|go |
|-- ################## end of cursor ################### |
| |
|DECLARE @subject_line nvarchar(100) |
| |
|select @subject_line = ''SQLDOT:'' |
| |
|select @subject_line = @subject_line + |
|'' F/D/L (-1D) :'' + |
|cast(sum(BkpCount_1dFULL) as varchar(5)) + ''/'' + |
|cast(sum(BkpCount_1dDIFF) as varchar(5)) + ''/'' + |
|cast(sum(BkpCount_1dTLOG) as varchar(5)) + |
|'' F/D/L (-1W) :'' + |
|cast(sum(BkpCount_1wFULL) as varchar(5)) + ''/'' + |
|cast(sum(BkpCount_1wDIFF) as varchar(5)) + ''/'' + |
|cast(sum(BkpCount_1wTLOG) as varchar(5)) |
| |
|FROM tempdb.dbo.backup_details |
| |
| |
|EXEC msdb.dbo.sp_send_dbmail |
|@profile_name = ''VM-SQLCMS'', |
|@recipients = ''adamm.murphy@dublincity.ie'', |
|@query = ''SELECT |
|WindowsServerName, |
|SQLInstanceName, |
|DatabaseCount as [#DBs], |
|cast(BkpCount_1dFULL as varchar(3)) + ''''\'''' + |
|cast(BkpCount_1dDIFF as varchar(3)) + ''''\'''' + |
|cast(BkpCount_1dTLOG as varchar(3)) as [F/D/T-1d], |
|cast(BkpCount_1wFULL as varchar(3)) + ''''\'''' + |
|cast(BkpCount_1wDIFF as varchar(3)) + ''''\'''' + |
|cast(BkpCount_1wTLOG as varchar(3)) as [F/D/T-1w] |
|FROM tempdb.dbo.backup_details |
|order by WindowsServerName desc |
|go |
|'' , |
|@subject = @subject_line, |
|@attach_query_result_as_file = 1; |
|go |
|drop TABLE tempdb.dbo.backup_details |
|go |
|', |
|@database_name=N'SQLDOT', |
|@flags=0 |
|IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback |
|EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 |
|IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback |
|EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'WeekDay', |
|@enabled=1, |
|@freq_type=4, |
|@freq_interval=1, |
|@freq_subday_type=1, |
|@freq_subday_interval=0, |
|@freq_relative_interval=0, |
|@freq_recurrence_factor=0, |
|@active_start_date=20140710, |
|@active_end_date=99991231, |
|@active_start_time=80000, |
|@active_end_time=235959, |
|@schedule_uid=N'd9e20ce8-1ab8-4a39-b243-490cbd2b15b7' |
|IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback |
|EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' |
|IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback |
|COMMIT TRANSACTION |
|GOTO EndSave |
|QuitWithRollback: |
|IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION |
|EndSave: |
| |
|GO |
Job:
|[pic] |
Reports:
|[pic] |[pic] |
|[pic] |[pic] |
|[pic] |[pic] |
|[pic] |[pic] |
Known Issues
[pic]
Potential Enhancements
[pic]
-----------------------
Handover Document
Note: No customised Custom Pages, Entity Templates, filter or Field Templates used.
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- wordpress passing data between pages
- wordpress business templates
- wordpress rss feed not working
- wordpress jquery is not defined
- create wordpress blog
- wordpress roles editor
- wordpress full rss feed
- wordpress rss feed settings
- wordpress rss feed plugin
- wordpress display rss feed
- wordpress rss feed link
- wordpress rss feed to post