|

SQL Server and Other Topics

rss

I got my start with SQL Server in 1997 with SQL Server 6.5 at BellSouth Cellular. From that point on I've had the opportunity to work on Versions 6.0 to 2014. I specialize in Performance Tuning, High Availability and SQL Development. Contact me through this site or through my Twitter handle @SQLDiver


Features I'm looking forward to:

  1. Always Encrypted

    Daily an enterprise organization has made a bad decision of not keeping their sensitive data protected. Why? Well, the excuse is its too expensive, too difficult, will take too long, will slow down the database, yada, yada. You're not going to get away with the whining any more. Always Encrypted is a solution that should make the process of encrypting sensitive data at rest and in motion at a point the much of the data enters the database (the application). Yes, of course, just like other encryption technologies, you need to plan the process of encrypting your data.

  2. Stretch Database

    One of the coolest ideas for "stretching" data rarely used to the cloud. There are lots of limitations currently as well as some cost issues that make it very difficult for the average Joe to afford the Stretch Database feature. I'm hoping they get this one fixed fairly quickly. Please?

  3. Query Data Store!!!!

    Ever since seeing Conor Cunnigham talk about this feature  at Summit 2014 my mouth has been watering. I started playing with it as soon as CTP 1 was released. The best article I found at the time was by Enrico van de Laar called The SQL Server 2016 Query Store: Overview and Architecture. This is going to make performance tuning more easier with more detail data available, the tools that monitor performance will begin to use the Query Store for its monitor data. Can't wait until all databases are on 2016... oh well... guess I'll be retired by then.

  4. Live Query Statistics

    I've been playing with this one a lot. Since it works with SQL Server 2014 (what? Yep!) as long as you're using SSMS 2016+ you will get Live Query Statistics for SQL Server 2014. I've been showing this one in my Performance Tuning for Mere Mortals sessions and pre-cons with Ed Watson. IT is a beautiful thing.

    Did you ever have someone bring you a @#%! stored procedure that takes forever to complete? You would have to wait hours for the graphical execution plan to complete in order to see where the bottleneck is. Not any more! Now you can see the operators percents, and information as the query is running.

  5. Dynamic Data Masking!

    Ok, there are a lot of issues in V1, you can easily get around the dynamic data masking, and can accidentally overwrite the actual data with the mask... but it is a good start. I won't use it in V1..until they have the issues worked out, but nice start.

  6. Row Level Security... woohoo.
    1. Now were getting somewhere with security. We can control what a user or group has access to at a row level. This would have been very useful a few years ago for several projects I had. Maybe. I still need to evaluate it closely to determine if it will be beneficial.
  7. Ok, Data Scientists... R, yes R is in 2016. It too has a long way to go to be fully usable (as I hear).

And it doesn't stop there... but I'm going to have to stop here for now. See you soon!


Save
Save
Save
Save
Save
Save
Save
Save

My current client has a monitoring "application" written by someone for SQL Server 2000. It has been added to for following version, 

but much of the code is old and written for SQL Server 2000 (i.e. not utilizing newer functionality and features).

One such function of monitoring the "application" is doing is gathering the ERRORLOG data from the SQL Server error log files every 5 minutes using sp_readerrorlog stored procedure. For those not in the know this stored procedure returns every record in the ERRORLOG file without the ability to provide a data range. It simply calls xp_readerrorlog which does have parameters for passing a date range to only retrieve records within that specific date range.

The current process builds a execution string which is then executed using osql which is a deprecated tool. The process loops through a table containing server names that have been added for monitoring then calls a stored procedure, passing the server name in as a parameter.

The current process is calling sp_readerrorlog every 5 minutes (every record), parsing the entire data set then filtering out the existing records before inserting into a log table in the database. Because he was using osql to execute, the parsing of the data was quite complex and unwieldy.

Instead of osql, I opted to write a stored procedure to select through a linked server to the servers remote servers to be monitored. I won't cover the creation of linked servers in this article, but it could be simply automated to check for the existence of a linked server with the name of the server you want to monitor and create it if it doesn't exist.

A few things before getting started: you will want to make sure your security is correct for your linked servers (meaning you probably want to make sure your SPNs are configured correctly: Register a Service Principal Name for Kerberos Connections. And you might have issues with MSDTC if not correctly configured: Troubleshooting Problems with MSDTC.

So let's get into the meat of the process. A table called sqlErrorLog exists with the following schema:

 
   
SET ANSI_NULLS ON 
GO 
 
SET QUOTED_IDENTIFIER ON 
GO 
 
SET ANSI_PADDING ON 
GO 
 
CREATE TABLE [dbo].[SQLErrorLog]( 
   [SQLErrorLogID] [int] IDENTITY(1,1) NOT NULL, 
   [LogDate] [datetime] NOT NULL, 
   [ServerName] [varchar](255) NOT NULL, 
   [ProcessInfo] [varchar](100) NULL, 
   [Text] [varchar](8000) NULL, 
 CONSTRAINT [PK_SQLErrorLog] PRIMARY KEY CLUSTERED  
( 
   [LogDate] ASC, 
   [ServerName] ASC, 
   [SQLErrorLogID] 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
   
 

 

Frankly I hate the design, no foreign key, bad column names. I recommend creating a primary key on ymd, serverName and ukey in that order. I didn't have that luxury as this was a third party application. If you would like to change the column names 

 

 
   
-- SELECT TOP 10000 * from sqlErrorLog el ORDER BY LogDate desc 
-- exec dbo.PopulateErrorLogByServerName 'LOCAL' 
ALTER PROCEDURE dbo.PopulateErrorLogByServerName ( 
   @servername varchar(100) 
   ) 
   
AS 
   
SET NOCOUNT ON; 
BEGIN 
   --IF OBJECT_ID(N'tempdb..#ErrorLogs') IS NOT NULL 
   --   DROP TABLE #ErrorLogs 
   
   --IF OBJECT_ID(N'tempdb..#ErrorLog') IS NOT NULL 
   --   DROP TABLE #ErrorLog 
   
   -- Create Temp Tables used for processing errorlog records 
   CREATE TABLE #ErrorLogs ([Archive #] smallint, [Date] datetime, [Log File Size (Byte] int) 
   CREATE TABLE #ErrorLog (ID int IDENTITY(1,1), LogDate datetime, ProcessInfo varchar(25), [Text] varchar(max), [ServerName] varchar(256)) 
   
   -- Variable declaration 
   DECLARE @ROWS int 
   DECLARE @curDate datetime = getdate()    
   DECLARE @date nvarchar(20) = convert(varchar(20),@curDate,20) 
   DECLARE @datestart nvarchar(20)  
   DECLARE @maxdate datetime 
   DECLARE @FileNum tinyint 
   DECLARE @ExecString nvarchar(1000) 
   DECLARE @ParameterDefinition nvarchar(2000) = N'@FileNum tinyint, @datestart nvarchar(20), @date nvarchar(20)' 
   PRINT @ExecString 
  
  
    SET @ExecString = N'[' + @servername + '].[master].[sys].[xp_enumerrorlogs]' 
 
   -- Get list of errorlogs 
   INSERT INTO #ErrorLogs 
   EXECUTE sp_executesql @ExecString 
     
   SELECT @maxdate = max(LogDate) 
   FROM dbo.sqlErrorLog sel 
   WHERE serverName = @servername 
 
   IF @maxdate IS NULL 
   SELECT @maxdate = min(Date) FROM #ErrorLogs sel 
      
   SELECT @datestart = convert(varchar(20),@maxdate,20) -- format for execution 
 
  
   SELECT TOP 1 @FileNum = [Archive #] 
   FROM #ErrorLogs  
   WHERE Date <= @maxdate 
   ORDER BY Date desc 
 
   -- Loop through files if errorlog has been cycled (or missed) 
   WHILE @FileNum Is NOT NULL 
   BEGIN 
   
   set @datestart = @maxdate 
   
   SET @ExecString = N'[' + @servername + '].[master].[sys].[xp_readerrorlog] @FileNum,1,null,null, @datestart,@date,''asc''' 
 
   INSERT INTO #ErrorLog (LogDate, ProcessInfo, [Text]) 
   EXECUTE sp_executesql @ExecString, @ParameterDefinition, @FileNum, @datestart, @date  
   
   UPDATE el 
   SET [ServerName] = @servername 
   FROM #ErrorLog el 
   WHERE [ServerName] IS NULL 
   
   SELECT @FileNum = MAX([Archive #]) 
   FROM #ErrorLogs  
   WHERE [Archive #] < @FileNum 
   
   END 
   
   -- Populate sqlErrorLog with records that do not exist 
   INSERT INTO sqlErrorLog ( LogDate  , ProcessInfo  ,Text,ServerName) 
   SELECT el.LogDate, el.ProcessInfo, LEFT(el.Text,8000), el.ServerName 
   FROM #ErrorLog el  
   LEFT JOIN dbo.sqlErrorLog sel 
   ON el.LogDate = sel.LogDate 
   AND el.ServerName = sel.ServerName 
   AND el.ProcessInfo = sel.ProcessInfo 
   AND el.Text = sel.Text 
   WHERE sel.LogDate IS NULL 
 
   SET @ROWS = @@ROWCOUNT 
 
   IF @ROWS > 0 
   PRINT CAST(@ROWS as varchar(8)) + ' Records Added' 
   ELSE 
   PRINT '0 Records Added' 
   
END