Consolidating Error Logs from multiple SQL Servers using SSIS

Problem

SQL Server stores all informational messages, warning messages, and errors in operating system and application log files. As a database administrator (DBA), it is our responsibility to review the information in the error log files on a daily basis for any errors that have occurred in our SQL Server environment. However, manually reviewing the log files daily is not an easy task in a busy SQL Server environment, and it can quickly consume a large part of your day. Therefore, most DBAs tend to skip this when they are busy with other production problems. In this tip, I'll show you how we can consolidate error log entries from multiple servers into a central location, and then report on them from central location.

Solution

Before we talk about our custom solution for consolidating SQL error log, I will first state the purpose of SQL Server error log and show different ways for viewing it on SQL Server.

Purpose of SQL Server ErrorLog

SQL Server keeps logs for Database Mail, SQL Server (database server) and SQL Server Agent (error log) in operating system and application log files. SQL Server maintains a current log and archive logs. By default, it keeps six archive logs. Only logins that are members of the sysadmin and securityadmin fixed server role are able to view SQL Server logs.

Viewing SQL Server Logs Using SQL Server Management Studio

To open the Log File Viewer in Management Studio:

1. Launch SQL Server Management Studio and then connect to a SQL Server instance.
2. In Object Explorer, expand Management, SQL Server Logs.
3. Right-click the current log or an archive log and choose View SQL Server Log.

To open the Log File Viewer from the SQL Server Agent Error Logs:

1. Expand SQL Server Agent, Error Logs.
2. Right-click the current log or an archive log and choose View Agent Log.

Log File Viewer

The Log File Viewer is shown below:

The Select logs window lets you choose from:

• SQL Server
• SQL Server Agent
• Database Mail
• Windows logs

You can view the Windows Application and System log  and with sufficient permissions the Security logs too. Log entry details display in the lower pane when you select a log entry. Most of the messages in the SQL Server logs are informational only and require no corrective action.

Viewing SQL Server Logs Using T-SQL

You can use sp_readerrorlog that is an undocumented stored procedure to view SQL Server error logs. It accepts log file number as a parameter. For example, to view the current log file, execute the sp_readerrorlog as follow:

EXEC [sp_readerrorlog] 0
GO

---

Solution for Consolidating SQL Server Logs

Now that we understand the purpose of SQL Server logs, let's begin with our solution. This solution is built using SSIS, and will help you consolidate and merge error logs from multiple SQL Servers.
For the purpose of this article, create a database called ErrorLogMonitor on a SQL Server instance, where you want to consolidate the error logs.
Execute the following T-SQL script to create this database:

CREATE DATABASE [ErrorLogMonitor]
 ON  PRIMARY 
( NAME = N'ErrorLogMonitor'
, FILENAME = N'\ErrorLogMonitor.mdf' 
, SIZE = 4160KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'ErrorLogMonitor_log'
, FILENAME = N'\ErrorLogMonitor_log.ldf' 
, SIZE = 1040KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Next, create the following two tables inside this database:

• SSISServersList– The table stores the names of each SQL Server for which you want to read SQL Server error log.
• ErrorLog– The table will store the error log data for all of the SQL Servers you are monitoring.

To create the tables, execute the following:

USE [ErrorLogMonitor]
GO
CREATE TABLE [dbo].[SSISServersList](
 [ServerName] [nvarchar](128) NOT NULL,
 [IsTest] [bit] NULL,
 [Active] [bit] NULL,
 [Port] [int] NULL) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ErrorLog](
 [Server] [nvarchar](128) NOT NULL,
 [Date] [datetime] NULL,
 [ProcessInfo] [varchar](50) NULL,
 [Message] [nvarchar](4000) NULL,
 [AuditDate] [smalldatetime] NOT NULL,
 [LoadID] [int] NOT NULL
) ON [PRIMARY]
GO

Finally, we are ready to design our SSIS package. To do that launch SQL Server Data tools and create a new Integration Services project. Once the package is created, it will create an empty package. Rename this package to ConsolidateErrorLog_Package.dtsx.
Follow these steps to configure the package.

1: Defining Package Variables

Add the following two variables for the SSIS package:

• Variable 1: SQLServer_Connection
  • Scope: ConsolidateErrorLog_Package
  • Data Type: String
  • Value:<You SQL Server instance name> (Specify the instance name where ErrorLogMonitor database is located).
• Variable 2: SQLServer_ResultSet
  • Scope: ConsolidateErrorLog_Package
  • Data Type: Object
  • Value: System.Object

To do that, right-click anywhere on the empty panel on the Control Flow Tab, then select Variables and then click Add Variables icon (see below).

2: Defining Package Connections

First create a new ADO.NET connection to the ErrorLogMonitor database on your SQL Server. To do this, click anywhere on the Connection Managers, and choose New ADO.NET Connection. Configure the ADO.NET connection as follow:

Rename the connection to ADO_ErrorLogMonitor.
Next, create the dynamic OLE-DB connection, this connection allows us to connect to the different SQL Server instances. This connection is passed with SQLServer_Connection variable that contains SQL Server instance name from the SSISServerList table, which was created earlier. Rename the connection to Dynamic_ErrorLogMonitor.
Now right-click the Dynamic_ErrorLogMonitor connection and then choose Properties. First change the initial catalog to master, and then click (…) box next to expression and specify the following expression for ServerName property:

@[User::SQLServer_Connection]

3: Defining Package Tasks

3.1: Configuring “Truncate ErrorLog table” – Execute SQL Task

Add an “Execute SQL Task” to the control flow. Double-click the task and configure properties in the “General” page of the task as follows:

• Rename “Execute SQL Task” to “Get SQL Server ErrorLog”.
• Set “ResultSet” property to “None”.
• Set “Connection Type” property to “ADO.NET”.
• Set “Connection” property to “ADO_ErrorLogMonitor”.
• Set “BypassPrepare” to “True”.
• Set “SQLStatement” property with the error log collection script below:

TRUNCATE TABLE [dbo].[ErrorLog]

3.2: Configuring “Get Server Names” – Execute SQL Task

Add “Execute SQL Task” to control flow. Double-click the task and configure properties in the “General” page of the task as follows:

• Rename “Execute SQL Task” to “Get SQL Server Names”.
• Set “ResultSet” property to “Full result set”.
• Set “Connection Type” property to “ADO.NET”.
• Set “Connection” property to “ADO_ErrorLogMonitor”.
• Set “BypassPrepare” to “False”.
• Set “SQLStatement” to the following:

SELECT [ServerName] 
  + CASE ISNULL(CAST([Port] AS [varchar](10)), '')
   WHEN ''
    THEN ''
   ELSE ',' + CAST([Port] AS [varchar](10))
    END AS [ServerName]
FROM [dbo].[SSISServersList]
WHERE [Active] = 1
GO

Click ResultSet in left pane and then click Add to add the "User::SQLServer_ResultSet" variable as shown below. Change Result Name value to 0, and then click OK to save the configuration for the task.

Then in the SSIS package, connect the “Truncate ErrorLog table” task to the “Get Server Names” task.

3.3: Configuring – ForEach loop container

Drag and drop a ForEach loop container to the package control flow. Double-click the ForEach loop container, and then click Collection in left pane. Here configure the properties of this window as follows:

• Change “Enumerator” property to “Foreach ADO Enumerator”.
• Set “Enumeration Mode” to “Rows in the first table”.
• Specify “ADO object source variable” to the “User::SQLServer_ResultSet” package variable. 

Now click “Variable Mappings” and select “User::SQLServer_Connection” package variable to map to the collection value.

Then in the SSIS package, connect the “Get Server Names” task with the “ForEach loop” container.

3.4: Configuring “Get SQL Server ErrorLog” – Execute SQL Task

Add an “Execute SQL Task” inside the ForEach loop container. Double-click the task and configure properties in the “General” page of the task as follows:

• Rename “Execute SQL Task” to “Get SQL Server ErrorLog”.
• Set “ResultSet” property to “None”.
• Set “Connection Type” property to “OLE DB”.
• Set “Connection” property to “Dynamic_ErrorLogMonitor”.
• Set “BypassPrepare” to “True”.
• Set “SQLStatement” property with the error log collection script below:

SET NOCOUNT ON;
SET DATEFORMAT MDY; 
DECLARE @LoadID   [int]
    ,@FileNumber  [tinyint]
    ,@ErrorLogSize [bigint]
    ,@ExtractDate [datetime]
    ,@SQLServer  [nvarchar](128)
IF NOT EXISTS (SELECT * FROM [tempdb].[sys].[objects] 
    WHERE [name] = 'ERROR_LOG' AND [type] IN (N'U'))
BEGIN
 CREATE TABLE [tempdb].[dbo].[ERROR_LOG]
  ([Server]  [nvarchar](128) NULL
  ,[LogDate]  [datetime] NULL
  ,[ProcessInfo]  [varchar](50) NULL
  ,[Text]   [nvarchar](4000) NULL)
END
ELSE 
BEGIN
 TRUNCATE TABLE [tempdb].[dbo].[ERROR_LOG]
END
IF OBJECT_ID('tempdb..#ErrorLogs') IS NOT NULL
    DROP TABLE #ErrorLogs
CREATE TABLE #ErrorLogs 
 ([ArchiveNumber]  [int]
 ,[DateLastChanged]  [datetime] NOT NULL
 ,[LogFileSizeInBytes]  [bigint])
-- Reading the error logs files details
INSERT INTO #ErrorLogs 
EXEC [master]..[xp_enumerrorlogs]
-- Assigning values to variables
SELECT @FileNumber = [ArchiveNumber]
   ,@ErrorLogSize = [LogFileSizeInBytes]
FROM #ErrorLogs
WHERE [ArchiveNumber] = 0
-- Set extract date for 1 day
SET @ExtractDate = DATEADD(dd, -1, CURRENT_TIMESTAMP)
IF (@ErrorLogSize <= 536870912)
BEGIN
 -- Loading the latest errorlog information inside table
 INSERT INTO [tempdb].[dbo].[ERROR_LOG]
      ([LogDate]
      ,[ProcessInfo]
      ,[Text])
 EXEC [sp_readerrorlog] 0
END 
ELSE 
BEGIN
 INSERT INTO [tempdb].[dbo].[ERROR_LOG]
   ([LogDate]
   ,[ProcessInfo]
   ,[Text])
 VALUES (CURRENT_TIMESTAMP
     ,'Server'
     ,'ErrorLog is two big to load.')
END
DELETE FROM [tempdb].[dbo].[ERROR_LOG]
WHERE [LogDate] < @ExtractDate
DELETE FROM [tempdb].[dbo].[ERROR_LOG]
WHERE [ProcessInfo] = 'Backup'
DELETE FROM [tempdb].[dbo].[ERROR_LOG]
WHERE [ProcessInfo] = 'Logon'
UPDATE [tempdb].[dbo].[ERROR_LOG]
SET  [Server] = CAST(SERVERPROPERTY('ServerName') AS [nvarchar](128))
SET NOCOUNT OFF;
GO

3.5: Configuring “Process ErrorLog” – Data Flow Task

Add a “Data Flow Task” inside the ForEach loop container and rename it to “Process ErrorLog”. Connect the “Data Flow Task” with the “Get SQL Server ErrorLog”.
Now, double-click the “Data Flow Task”, and drag the “OLE DB Source” and “ADO NET Destination” to the “Data Flow Task”. Next, connect the “OLE DB Source” with the “ADO NET Destination. Then double-click “OLE DB Source” and specify the settings as follows:

• Set “OLE DB Connection Manager” to “Dynamic_ErrorLogMonitor”.
• Change “Data access mode” to “SQL command”
• Specify the following query in “SQL command text”:

SELECT [Server]
      ,[LogDate]
      ,[ProcessInfo]
      ,[Text]
      ,CURRENT_TIMESTAMP AS [AuditDate]
FROM [tempdb].[dbo].[ERROR_LOG]
GO

Once done, click OK to save the settings and return to Data Flow.

Finally, double-click “ADO NET Destination” and configure as follow:

• Set “Connection manager” to “ADO_ErrorLogMonitor”.
• Select “dbo.ErrorLog” table in our “ErrorLogMonitor” database, where data will be copied.
• Then map source table to destination table. 

Click OK to save settings.

---

Package Review

All done, our package is fully configured, and it is now ready for us to execute the package. The package control flow and data flow look similar to the following figures:

Control Flow - ConsolidateErrorLog_Package.dtsx

Data Flow - ConsolidateErrorLog_Package.dtsx

---

Testing

To test the package, simply add a few servers in the SSISServerList table and then execute the package using the play button in SQL Server Data Tools (see below):

Once successfully executed, query the data of dbo.ErrorLog inside our ErrorLogMonitor database to retrieve all SQL servers error logs (see below):

Next Steps

• Download this SSIS solution
• No matter how well you have designed and tested a database, errors will occur. You can use SQL Server error log files to view information about errors that have occurred because SQL Server stores all informational messages, warning messages, and errors in operating system and application log files.
• Take the next steps and write some queries that can then pull key errors from this consolidated table.
• Create a job that finds key errors and sends you emails notifications.
• Check out these related tips:
  • Increase the Number of SQL Server Error Logs
  • Reading the SQL Server log files using TSQL
  • Best Practice - Increase the Number of SQL Server Error Logs
  • Simple way to find errors in SQL Server error log

THANK YOU & Visit Again (Give me your precious comments/queries)
ready to help.

Problem

Have you ever had to write a bunch of TSQL to do some data analysis on the table data in your database. If you have you'll know that this can become a fairly time consuming and tedious task? SQL Server 2012 Integration Services has a feature called the Data Profiling task that can help you perform this analysis much easier and faster (this feature is also available in SQL Server 2008). This task can help you find potential issues with your existing data as well as help you become more familiar with the data in a database that you have just started managing.

Solution

To use the Data Profiling SSIS task in SQL Server 2012 we first need to create a new Integration Services project in SQL Server Data Tools (formerly Business Intelligence Development Studio). Once you've created the project and opened it, your screen should look as follows with an empty package design tab.

Now that we have a package open we need to add a connection manager object for the database we want to run the profile against. To do this we right click "Connection Managers" in the Solution Explorer and select "New Connection Manager". Then select "ADO.NET" and click "Add...", then click "New..." and then fill in dialog box as show below. Once everything is filled in I like to click "Test Connection" just to make sure it's configured correctly. After this is successful you can click "OK" to complete the setup.

Next we can add the Data Profiling task to the package by clicking and dragging it from the SSIS Toolbox onto the Control Flow panel.

We can start to configure the task by double clicking on the task in the Control Flow panel. A dialog box opens to the General Tab where we can setup our Profiler Output File connection. Click on the Destination property and select "<New File connection...>" and complete the dialog box as shown below. Once complete we can click "OK".

The complete General tab should now look like this.

From this panel we have two different methods we can use to configure what we are going to profile. First we can click on "Quick Profile" and we are presented with a form we can use to configure one or more tables to be profiled. For this example I am just going to select a single table and check all the profiles to be computed. A description of each profile can be found here. Once completed the form should look like the one below.

After clicking "OK" on the form above we are taken to the Profile Requests panel which is the other method of configuring this task and could have been selected when the initial form was opened. Looking at the screenshot below we can see all of the Profile Types we selected in the last form are configured here. Looking under the Data subsection of the Request Properties section we can see that we also have the ability to profile one or more columns within the table. As an example, if we were looking for a candidate key we may exclude some columns from the profile that we know for sure would not be part of the key.

Let's add one more table/column by clicking on the next empty space in the "Profile Type" column. After selecting the profile type we can fill in the "Request Properties" section as shown below by select a connection manager, table/view and columns. In this case we are just going to select a single column. After clicking "OK" to complete the configuration we are taken back to the Control Flow tab of our package.

The configuration of our data profiling task is now complete and we can run it by selecting "Start Debugging" from the Debug menu or by using the F5 shortcut. Once it has completed successfully we have to select "Stop Debugging" from the Debug menu or use the Shift+F5 shortcut to be able to view the output.

The output file is just a regular xml file, here is a sample, and Microsoft provides two options for launching the Data Profile Viewer which displays this xml file in a nice formatted output. The first way is to double click on the Data Profiling task on the Control Flow tab and it will open up the General tab as it did when you were configuring the task. Once here we can click "Open Profile Viewer". Alternatively we can start the Data Profile Viewer as a stand-alone application. It can be found under the Start Menu -> Microsoft SQL Server 2012 -> Integration Services -> Data Profile Viewer. Once there simply open the output file that was created by the Data Profiling task. Regardless of the method you choose you will get the following window to display the contents of the xml file.

Next Steps

• Take data quality a step further with SQL Server 2012 Data Quality Services
• Use Master Data Services to provide a centralized location for clean, up-to-date data
• More information on SQL Server Data Tools

How to Install SQL Server 2012 Denali CTP3

Microsoft released SQL Server 2012, Denali CTP3 (Community Technical Preview) version of next version of Microsoft's Data Platform application SQL Server. I hope installation guide will help SQL Server administrators and SQL Server developers during their SQL Server 2012 installation process. At least, IT professionals can have a rough idea what is going on with SQL Server 2012 by just looking at the setup screenshots.
Before going over the SQL Server 2012 installation steps, developers and administrators who want to download latest SQL Server 2012 release can check the download links from www.Mircosoft.com

After you download SQL Server 2012 setup files and save them in a folder on server, start unpacking install files by running SQLFULL_x86_ENU_Install.exe or SQLFULL_x64_ENU_Install.exe files. Please note that if you have problems with data unpacking, please refer to SQL Server 2012 CTP3 installation error

After the data is extracted into a new folder SQLFULL_x86_ENU or SQLFULL_x64_ENU, you can check the existency of SETUP.EXE IF you can not see SETUP.EXE, please control if data is extracted correctly. Perhaps the SQL Server installation files might be corrupt.
Start SQL Server 2012 installation wizard by running SETUP.EXE application
If everything goes smoothly, SQL Server Installation Center screen will be displayed.

Just as I did during my first SQL12 CTP3 installation, you can run System Configuration Checker SQL Server tool in order to see if there are some conditions that can prevent successful SQL Server 2012 installation.
You can also try to run SQL Server Upgrade Advisor tool, but before make sure that scriptdom is already downloaded and installed on the server.
In order to install a SQL Server instance, or upgrade existing SQL Server instances go to SQL Server Installation Center Installation tab. You will see "New SQL Server stand-alone installation or add features to an existing instllation" link buton. Press this link and launch a fresh new SQL Server 2012 Denali CTP3 setup process here.

At this step of installation of SQL Server 2012, the wizard will check the setup support rules.

In the next step, you can identify the edition of the free SQL Server installation. You can choose Evaluation or Express from the free edition specification combobox.
It is possible to enter a valid SQL Server 2012 product key to validate your SQL Server 2012 installation

Accept SQL Server 2012 license terms

Install SQL Server Setup Support files rules are checked at this step. The installation will not continue if you get an error. Check details for the warnings.

SQL Server 2012 Installation Type setup step will request from you to define if a new installation of SQL Server Denali CTP3 will be executed or new features will be added to an existing instance of Denali CTP3. Since there was not any installed SQL11 CTP3 instance on my computer, the second option is read-only. I'll continue to setup SQL Server 2012 with first option.
In the screenshot you will see an SQL Server 2012 instance named Denali which is SQL Server 2012 CTP1 version. So this CTP3 setup files can not update previous CTP1 installation.

Choose the appropriate Setup Role from list

Feature Selection screen enables users to choose which SQL Server features to install on the target database server. As you can see in the below wizard screenshot, there is a long list of Microsoft SQL Server 2012 features and components to choose from. If you plan to install SQL Server Denali for training purposes, you can select all features using Select All buton and continue just as I did.

Installation Rules check

SQL Server 2012 Denali CTP3 Instance Configuration screen
I used "CTP3" as new SQL Server instance name and change the instance root directory from C to D drive

The following step in the setup wizard is checking the disk space requirements on the server where you want to install SQL Server.

Server Configuration screen is used to set service accounts and collation configuration
What is new in this Server Service accounts selection screen is the Setup Wizard suggests default service acount users. This is one of the changes in the SQL Server 2012 Setup process with Denali CTP3.

In the Collation tab of the Server Configuration screen, you can choose the default collation (or server collation) among numerous collation definitions.

Database Engine Configuration :
Using this screen the setup user can identify database engine authentication security mode and SQL Server 2012 instance administrators.
As usual setup administrator can choose Windows Authentication mode or Mixed mode with SQL Login.
Do not forget to add your current user as admin for the SQL Server 2012 Denali CTP3 instance.

The other tab enables setup admins configure data directories where system databases, user databases, user database logs, temp db and temp db logs and backup files will be stored.

After SQL Server Database Engine configuration is completed, the next step enables administrators to configure Analysis Services, SSAS 2012. The Analysis Services settings includes Server Configuration and Data Directories settings for the SQL Server Analysis Services, SSAS instance.

This step is used to configure another SQL Server 2012 Data Platform component, SQL Server 2012 Reporting Services which is an important part of the Microsoft SQL Server Business Intelligence solutions.
Reporting Services settings within the setup wizard specifies the Reporting Services configuration mode:
Install and configure,
Install only, and
Reporting Services SharePoint Integrated Mode.

One of the new SQL Server components is Distributed Replay Controller which is now being considered as one of the SQL Server installation and configuration steps. At this step, you will specify the users who will have permissons for the SQL Server Distributed Replay Controller service. These users will have unlimited access to the Distributed Replay Controller service.
You can add your user as admin for the DPC service by using the Add Current User buton

After the accounts which can access controller service, administrators can now configure Distributed Replay Client. Settings to be configured include controller name, and data directories like working directory and result directory for the client.

Error Reporting step is where you can contribute to the effort that Microsoft SQL Server teams are performing by sending the error reports to Microsoft in case an interruption during SQL Server Denali CTP3 installation
You can disable this option by clearing the checkbox field.

Database administrators are near to the end of configuring SQL Server Denali instance. If the database server and SQL Server configuration can pass the Installation Configuration Rules, the setup process will start.

If the installation configuration rules are successfully applied to the current instance setup, you can proceed to the next step where one last chance to overview the overall configuration settings is enabled.

After you controlled the configured SQL Server 2012 Denali features for setup, click on Install buton to start SQL Server 2012 Denali CTP3 installation
If the setup process successfully creates a new instance of SQL Server 2012, it will request from you to restart the server. I'll advise you restart your computer in order to complete installation successfully.

To summarize, this tutorial is showing how to insall SQL Server 2012 Denali CTP3 on a database server. The above described setup process may vary according to the SQL Server feature selection you configured for the instance. I hope the setup guide will be helpful for developers and SQL Server database administrators.