Using Database Trigger to Enforce Naming Convention Standard

When setting up a SQL data warehouse that will be used by multiple developers, it is important that the naming of objects is consistent. This allows the database to be searchable and well organized so that new users can pick up on the data quickly and know how to navigate the database. To enforce a naming convention, you have a few options such as audit specifications, server events, and triggers.

I chose to use a database trigger for enforcing a naming convention because the maintenance i felt was easier. Below is the code. It checks based on the object if a prefix is there, and if not, it rolls back the transaction and alerts the user with an error:

 

CREATE TRIGGER [trg_naming_compliance]
ON DATABASE
FOR
DDL_FUNCTION_EVENTS, DDL_PROCEDURE_EVENTS, DDL_TABLE_VIEW_EVENTS

AS
BEGIN

SET NOCOUNT ON;

DECLARE @data XML = EVENTDATA()

DECLARE @db_nm VARCHAR(256) = @data.value(‘(/EVENT_INSTANCE/DatabaseName)[1]’, ‘varchar(256)’)
DECLARE @evnt_typ VARCHAR(250) = @data.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘varchar(250)’)
DECLARE @obj_nm VARCHAR(256) = @data.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘varchar(256)’)
DECLARE @obj_typ VARCHAR(256) = @data.value(‘(/EVENT_INSTANCE/ObjectType)[1]’, ‘varchar(250)’)
DECLARE @tsql_cmd VARCHAR(MAX) = @data.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘varchar(max)’)
DECLARE @usr_id VARCHAR(256) = @data.value(‘(/EVENT_INSTANCE/LoginName)[1]’, ‘varchar(256)’)
DECLARE @obj_schema VARCHAR(256) = @data.value(‘(/EVENT_INSTANCE/SchemaName)[1]’, ‘varchar(256)’)

— Compliance Check

— Set your compliance prefixes here
DECLARE @TBL_PREFIX VARCHAR(5) = ‘tb_’
DECLARE @VIEW_PREFIX char(2) = ‘v_’
DECLARE @sp_prefix varchar(5) = ‘usp_’
DECLARE @FN_prefix varchar(5) = ‘ufn_’
DECLARE @COMP_VIOLATION_RULE VARCHAR(1000)

DECLARE @is_compliant BIT = 1

IF @evnt_typ IN (‘CREATE_TABLE’,’ALTER_TABLE’)
BEGIN
IF LEFT(@obj_nm,3) <> @TBL_PREFIX
SET @COMP_VIOLATION_RULE = ‘Table names must be prefixed with “tb_”.’
END
ELSE IF @evnt_typ IN (‘CREATE_VIEW’,’ALTER_VIEW’)
BEGIN
IF LEFT(@obj_nm,2) <> @VIEW_PREFIX
SET @COMP_VIOLATION_RULE = ‘View names must be prefixed with “v_”.’
END

ELSE IF @evnt_typ IN (‘CREATE_PROCEDURE’,’ALTER_PROCEDURE’)
BEGIN
IF LEFT(@obj_nm,4) <> @sp_prefix
SET @COMP_VIOLATION_RULE = ‘Stored procedure names must be prefixed with “usp_”.’
END

ELSE IF @evnt_typ IN (‘CREATE_FUNCTION’,’ALTER_FUNCTION’)
BEGIN
IF LEFT(@obj_nm,4) <> @FN_prefix
SET @COMP_VIOLATION_RULE = ‘Function names must be prefixed with “ufn_”.’
END
IF @COMP_VIOLATION_RULE IS NOT NULL
BEGIN
DECLARE @err_msg VARCHAR(8000) = CONCAT(‘** Compliance Violation ** : ‘,@COMP_VIOLATION_RULE,’ Please consult dev standards before resubmitting’)
SET @is_compliant = 1
RAISERROR(@err_msg,16,16)
ROLLBACK
END
END

Using Database Trigger to Enforce Naming Convention Standard

Converting Users from SSIS to T-SQL For ETL

I’ve spent a little over 9 years now working with T-SQL and SSIS, and I’ve lived through the following headaches known with SSIS:

  • Upgrading hundreds of packages when we upgrade SQL Server
  • Updating connection passwords when we have to refresh for security reasons
  • Dealing with user key encrypted packages and having to get those solved so that we can use and diagnose a problematic package
  • Documenting the contents of an SSIS package when a business user requests information

I could go on about listing all the other known issues with SSIS. I’m not bashing the product. It is by far one of the most fascinating and creative pieces of software I have ever seen. The internals of it that deal with memory buffers and multi-threading make it world class ETL software. But with all the headaches I had dealt with in the past, I knew there could be something easier and faster to work with.

At the end of 2013, I started building out a concept plug-n-play software for ETL that allows the end user to just use T-SQL to automate the creation and execution of an SSIS package. This would later evolve into EDIS, but at the time, I was just trying to come up with a solution that allowed me and my co-workers to build and deploy ETL faster and easier. After working through several revisions, I finally had a solution that used just parameters to build an SSIS package on the fly without the end user ever needing to open SSIS. Once I put this in front of some die-hard SSIS users, they were shocked and fascinated by how easy it was to use this new approach (in just 4 parameters, you could have a data flow package built and executed). One of them had been using SSIS for over 12 years (back when it was known as DTS from the SQL 2000 days). He said at first that he could never get used to this approach of building ETL workflows from T-SQL, but once he used the program for a couple days, he came back to me and said I needed to expand the capabilities further so that we didn’t have to create SSIS packages ever again.

Now, I feel that EDIS has matured to a product that is starting the optimization phase. It now covers data flows, FTP and S-FTP, web requests, local directory operations, zipping and unzipping files, and the capabilities continue to grow over time.

Another added benefit of EDIS is the self-documenting audit trail. For once, a DBA can go to just a single table to see all ETL that is being done on their server, and the mysterious black box of an SSIS package is now a thing of the past. This has been a huge win for large organizations that have ETL pulling from hundreds of sources. The DBA now has the ability to quickly answer the WHO, WHAT, WHEN, and WHERE.

All in all, this shift of moving developers from SSIS to EDIS is not easy. At first, most will look at the tool and say “so what? I can do this in SSIS. Why should I change?”. Once they realize how much faster they can build and update ETL, the fact that they only have to set credentials once and ditch config files, and that they only need to use SSMS to develop new ETL and not have to install SSDT/Visual Studio, that’s when they get the “ah-ha” moment, and say they don’t want to go back to SSIS ever again.

I’m not trying to use this blog as a sales pitch; this was more to just get down on paper the challenges and why I built EDIS. It has made managing my ETL team’s portfolio much easier and has empowered both small and large organizations the ability to train users quickly and easily so that they can get data into SQL Server much faster than they are used to.

 

 

Converting Users from SSIS to T-SQL For ETL

Get Real Time Stock Quote with T-SQL

 

Recently we released an update to EDIS to do a web service call and get the results back to a table in XML or JSON format. I didn’t realize at the time that this feature could evolve to do so much, but basically any website that has an OData API was fair game to try the web service against. As a test, I created a stored proc that leveraged the web service tool to get a stock price quote from yahoo finance. Below is the code. You just pass in the stock ticker symbol, and EDIS does the rest quick and easy. To try this, there is a free demo version of EDIS at http://www.sqletl.com for download.

 

CREATE proc [dbo].[usp_get_stock_price]
@stock_ticker varchar(4)
as
declare @url varchar(max) = ‘http://finance.yahoo.com/webservice/v1/symbols/’+@stock_ticker+’/quote?format=xml&#8217;

exec SSISDB.MDDataTech.usp_run_web_request
@url = @url
,@output_tbl_nm = ‘##quote_price’
;

declare @res xml = cast((select * from ##quote_price) as xml);
declare @curr_price varchar(50) = @res.value(‘(list/resources/resource/field[@name=”price”]) [1]’,’varchar(50)’)
print @curr_price

 

 

 

Get Real Time Stock Quote with T-SQL

Get notified when a user is elevated to sysadmin

I recently came across an issue where a co-worker reached out to me about their SQL Server having issues with users getting added to the sysadmin role without their knowledge. After taking to him more about it, I figured the easiest way for him to stay in the loop when users are added to this role was to setup a server trigger.

Server triggers are great for tracking logins and when server wide events occur. To get notified, you simply setup the server trigger with a call to the msdb.dbo.sp_send_dbmail proc and that’s it. Below is the code for this. This code assumes you have already configured database mail. If you haven’t, you must do this for the trigger to fire.

USE [master]
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER trg_server_sec_notification
ON
ALL SERVER WITH EXECUTE AS ‘sa’
FOR DDL_SERVER_SECURITY_EVENTS

AS
BEGIN
BEGIN TRY

SET NOCOUNT ON;

DECLARE @data XML = EVENTDATA();

DECLARE @login_nm NVARCHAR(250) = @data.value(‘(/EVENT_INSTANCE/LoginName)[1]’,’NVARCHAR(255)’)
DECLARE @server_role NVARCHAR(255) = @data.value(‘(/EVENT_INSTANCE/RoleName)[1]’,’NVARCHAR(255)’)
DECLARE @tsql NVARCHAR(MAX) = @data.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’,’NVARCHAR(max)’)
DECLARE @login_that_was_elevated NVARCHAR(255) = @data.value(‘(/EVENT_INSTANCE/ObjectName)[1]’,’NVARCHAR(255)’)
DECLARE @event NVARCHAR(255) = @data.value(‘(/EVENT_INSTANCE/EventType)[1]’,’NVARCHAR(255)’)

— No notification necessary if no add event occurred
IF @event <> ‘ADD_SERVER_ROLE_MEMBER’ RETURN;

DECLARE @change_type VARCHAR(10)
IF CHARINDEX(‘ADD’,@tsql) > 0
SET @change_type = ‘added’
ELSE
SET @change_type = ‘removed’

DECLARE @msg VARCHAR(8000) = CONCAT(
‘Note: Login [‘,@login_that_was_elevated,’] has been ‘,@change_type,’ to server security group <b>[‘,@server_role,’]</b> by user [‘,@login_nm,’].<br><br>’
)
;

DECLARE @sub VARCHAR(500) = CONCAT( ‘Server Security Notification: [‘,@@SERVERNAME,’]’)

EXEC msdb.dbo.sp_send_dbmail @recipients = ‘YOUR_EMAIL@YOUR_DOMAIN.com’
,@body = @msg
,@body_format = ‘HTML’
,@subject = @sub
,@exclude_query_output = 1

END TRY
BEGIN CATCH
END CATCH

END
GO

ENABLE TRIGGER trg_server_sec_notification ON ALL SERVER
GO

 

Get notified when a user is elevated to sysadmin