RipatDBA, database

Ripat DBA

Contact

Server viewer

Database

Download

Change menu

Extra tables

Examples

Q & A

License info

Version history

SQL articles

Database needed for Server viewer.
Last changed: 05 January 2025

Five steps

There are five steps that needs to be done to get the program to work.

- Create the database ServerViewer (see script below) with the table Servers on a SQL server = your monitor server.
- Insert the monitor servername into the table Servers (see script below).
- Download and unzip the zipfile.
- Open the config file and update the domain name and servername.
- Start the program.

The database needed for the program Server viewer can be created with the script below.
After execution of the script the database 'ServerViewer' will be created. It will contain one table called 'Servers'.
In this table the SQL servers should be added in order to be able to run queries on them.

The table 'Servers' could look like this after you have inserted six servers. The first record is our monitor server:

Script for creating the database 'ServerViewer'

-- create the database on the monitor server, change to the correct paths
USE [master]
GO
CREATE DATABASE [ServerViewer]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'ServerViewer', FILENAME = N'C:\Data\ServerViewer.mdf' , SIZE = 9020736KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ServerViewer_log', FILENAME = N'C:\Log\ServerViewer.ldf' , SIZE = 1623488KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
USE [ServerViewer]
GO
EXEC [ServerViewer].sys.sp_addextendedproperty @name=N'Purpose', @value=N'Used for monitoring activities done by the DBA.'
GO

-- create the table Servers
USE [ServerViewer]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Servers](
    [ServerId] [int] IDENTITY(1,1) NOT NULL,
    [ServerName] [varchar](40) NOT NULL,
    [Domain] [varchar](10) NULL, -- determines if Windows authentication is used to connect to the server
    [ServerIP] [varchar](40) NULL, -- the server ip could be used to connect to the server instead of the servername, to use this: type a space behind the domain
    [Version] [varchar](6) NULL, -- holds the SQL version
    [SkipServer] [bit] NOT NULL, -- determines whether or not the server will be visible in the server tree
    [ScanFreeDiskSpace] [bit] NOT NULL,-- not used by this program but could be used for other purposes
    [ScanHardening] [bit] NULL,-- not used by this program but could be used for other purposes
    [ReadErrorLog] [bit] NOT NULL,-- not used by this program but could be used for other purposes
    [ReadSQLCounter] [bit] NOT NULL, -- not used by this program but could be used for other purposes
    [ReadCPU] [bit] NULL, -- not used by this program but could be used for other purposes
    [SortOrder] [smallint] NULL, -- determines the order for sorting the servers in the server tree
    [Department] [varchar](50) NULL, -- extra field for information
    [Environment] [varchar](20) NULL, -- this field determines the environment in the tree, i.e Dev, Tst, Acc of Prd
    [EnvironmentSortOrder] [smallint] NULL, -- could be used in queries for extra sort order
    [Remark] [varchar](max) NULL, -- extra field for information
    [GUI] [bit] NULL, -- true if the server has a gui
    [DateCreated] [datetime] NULL,
    [ToolTip] [varchar](50) NULL, -- this field will show a tooltip in the server tree
    [Login] [varchar](50) NULL, -- when SQL authentication is used place your (encrypted) login here
    [Password] [varchar](50) NULL -- when SQL authentication is used place your (encrypted) password here
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Servers] ADD CONSTRAINT [DF_Servers_ServerIP] DEFAULT (NULL) FOR [ServerIP]
ALTER TABLE [dbo].[Servers] ADD CONSTRAINT [DF_Servers_SkipServer] DEFAULT ((0)) FOR [SkipServer]
ALTER TABLE [dbo].[Servers] ADD CONSTRAINT [DF_Servers_ScanFreeDiskSpace] DEFAULT ((0)) FOR [ScanFreeDiskSpace]
ALTER TABLE [dbo].[Servers] ADD CONSTRAINT [DF_Servers_ReadErrorLog] DEFAULT ((0)) FOR [ReadErrorLog]
ALTER TABLE [dbo].[Servers] ADD CONSTRAINT [DF_Servers_ReadSQLCounter] DEFAULT ((0)) FOR [ReadSQLCounter]
ALTER TABLE [dbo].[Servers] ADD CONSTRAINT [DF_Servers_ReadCPU] DEFAULT ((0)) FOR [ReadCPU]
ALTER TABLE [dbo].[Servers] ADD CONSTRAINT [DF_Servers_DateCreated] DEFAULT (getdate()) FOR [DateCreated]

-- create some indexes on the table
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IDX_Servers_ServerName_2] ON [dbo].[Servers]
(
    [ServerName] ASC
)
INCLUDE
(
    [ScanFreeDiskSpace],
    [SkipServer]
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IDX_Servers_ServerName_Domain] ON [dbo].[Servers]
(
    [ServerName] ASC,
    [Domain] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Here is an example where the monitor server is called: Server1. Replace this by your own monitor servername.

Add 1 server now (the monitor server = server that contains the previously created database 'ServerViewer') to the table 'Servers'.
If the domain is the same as in the config file (see blue marked above and also button Download), then Windows authentication will be used to connect to this server
if you execute a query. This is the account which is used for starting the program.
With this account a connection to others servers is made if the domain is the same. Is the domain not the same then SQL authentication will be used.

Add the monitor server 'Server1' by running this script: (modify it according your preferences).

Some fields like [ScanFreeDiskSpace], [ScanHardening], [ReadErrorLog] and [ReadSQLCounter] can be used for other purposes.

Now go to Download and download the program with lots of SQL queries.

Ripat DBA

Contact