SQLTimer: a SQL Server Timer

the-science-of-timing

thinking_animated_smiley_emoticon

So…you’ve got a fancy Business Intelligence (BI) solution? Predictive Analytics or perhaps Actionable Analytics? Ever thought about the right time to contact your (potential) customers?



SQLTimer might be a handy addition to your BI Suite!
Like a timer for your lighting, it checks every hour whether it’s the right moment to execute jobs for categories like:

BusinessHour: Indicates whether or not it’s the right time to contact your customer during business hours (included in the SQLTimer script)

Other additional time schedules which are downloadable:
Twitter: Indicates whether or not it’s the right time to contact your Twitter users
FaceBook: Indicates whether or not it’s the right time to contact your FaceBook users
LinkedIn: Indicates whether or not it’s the right time to contact your LinkedIn users
GooglePlus: Indicates whether or not it’s the right time to contact your Google+ users
Pinterest: Indicates whether or not it’s the right time to contact your Pinterest users

In this blogpost, I’ll be diving into the SQLTimer itself and the free time schedule called ‘BusinessHour’ (included in the script).

Download the complete script free on:
SQLTimer – How to install


How it works:
Just like the timer for your lightbulb, it is configured on an hourly basis:

SQL-Timer-Execute-TimeSchedule

The values indicate whether it is a bad, better or even the best time to contact your business on a weekly basis:

BusinessHour values:
0) = Outside of business hours
1) = Inside of business hours
2) = Preferred hours

Important note: SQLTimer is setup on a day-to-day basis and thus you might tweak the time schedule a bit for local holidays or other important days in the year:

SQL-Timer-TimeSchedule

SQLTimer objects:
(An existing database is required)
Let’s have a look at all the SQLTimer objects:

SQL-Timer-Objects

*** Tables ***

timer.TimerLogging
This table contains the logging (when a time schedule was executed (StartTime/EndTime) and what value the time schedule had)

SQLTimer’s logging in timer.TimerLogging:

SQL-Timer-Logging

timer.TimeSchedule
This table actually contains the time schedule itself

timer.TimeScheduleConfiguration
This table is all about the configuration

SQLTimer’s configuration in timer.TimeScheduleConfiguration
In case you would like to change the name of the schema, don’t forget to change the configuration settings:

SQL-Timer-TimeScheduleConfiguration


*** Stored Procedures ***

timer.usp_Hourly_Timer
This SP is the timer itself and needs to be scheduled in SQL Agent to be run on an hourly basis

timer.usp_Select_TimeSchedule
You might want to use the timer.usp_Select_TimeSchedule() stored procedure to analyze the time schedule for the coming week:

SQL-Timer-Execute-Select-TimeSchedule

timer.usp_Execute_BusinessHour_Jobs
This SP will execute the preferred jobs (for example: T-SQL code, SP’s or SQL Agent Jobs) for the time schedule ‘BusinessHour’

Here’s how the timer.usp_Execute_BusinessHour_Jobs looks like.
You need to add your T-SQL code, SP’s or SQL Agent Jobs here that need to be executed if the @TimeScheduleValue reaches a certain value.

SQL-Timer-Execute-BusinessHour-Jobs


*** Install the SQLTimer ***

So far about the SQLTimer itself, it’s time to install it!
1) Download the T-SQL script here: SQLTimer – How to install

2) Unzip the ZIP-file and open the *.sql file in SSMS or SSDT

3) Change the database name and uncomment the T-SQL code:

SQL-Timer-T-SQL-Script

4) Execute the T-SQL script
Output of the script:

SQL-Timer-T-SQL-Script-executed

5) Create a SQL Agent Job (Execute T-SQL statements) which executes the timer.usp_Hourly_Timer stored procedure every hour


6) Don’t forget to update the time schedule with your local (company) holidays 🙂
Table: timer.TimeSchedule


Websites:
SQLTimer
BumbleBI IT


Advertisements

DateTimeOffset to Date Time Offset (Dimension) Identities

These SQL functions might come in handy for your ETL and DWH.
I’m currently designing a DWH which will have three ‘TimeDimensions’:
DateDimension (grain = day)
TimeDimension (grain = second)
TimeZoneDimension (grain = offset in minutes)

Simply said, these functions convert a DATETIMEOFFSET value into three types of integer identity values (which can be used in your dimensions).

DateTimeOffset to Date Time Offset Identities - Results

Want it? 🙂
Download it here:
DateTimeOffset to Date Time Offset Identities – SQL Code

SQL Server 2012 Encryption (Security)

SQL Server database encryption the 2012 way…
How to fully secure your sensitive data? Have a look!

Am I still missing anything? Reply!

Query:

Results:


SET NOCOUNT ON;

DECLARE
@Pass1 NVARCHAR(25),
@Pass2 NVARCHAR(25),
@Salt NVARCHAR(25)

SET @Pass1 = '23543'
SET @Pass2 = '45663'
SET @Salt = 'R@nd0mS!a6lTValue'

SELECT TOP 10
FirstName
--Encrypted, 2 times to prevent the use of rainbow tables:
,EncryptByPassPhrase(@Pass2,
EncryptByPassPhrase(@Pass1,FirstName)
) AS Encrypted
--Decrypted:
,Convert(NVARCHAR(50),
DecryptByPassPhrase (@Pass1,
DecryptByPassPhrase (@Pass2,
EncryptByPassPhrase(@Pass2,EncryptByPassPhrase(@Pass1,FirstName))
)
)
) AS Decrypted
--One-way hashing on multiple columns:
,HashedMultipleValues =
HashBytes('SHA2_512',
IsNull(FirstName,'NA')+'|'+IsNull(LastName,'NA') --'|' = seperator
)
--One-way hashing on a single column (like creditcard numbers of passwords):
--Don't forget to use HashBytes() multiple times using the same @Salt value!
,HashedSingleValue =
HashBytes('SHA2_512',
IsNull(FirstName,'NA')+@Salt --Added @Salt to prevent the use of rainbow tables
)
FROM AdventureWorksDW2008R2.dbo.DimCustomer


 

—>>> ToDo’s (don’t forget!):
– Save all encrypt/decrypt coding (SP/FN) with the ‘WITH ENCRYPTION’-statement,
or you can also use a certificate and key to encrypt/decrypt data:
http://www.mssqltips.com/sqlservertip/2431/sql-server-column-level-encryption-example-using-symmetric-keys/
– Enable TDE (Enterprise Edition of SQL Server 2012) to prevent any user to access the data on disk level:
http://msdn.microsoft.com/en-us/library/bb934049.aspx
– Make sure to secure all inbound and outbound connections by using Certificates (for example by Symantec / Verisign):
http://www.symantec.com/products-solutions/families/?fid=ssl-certificates#tabs
– More info on SQL Server 2012 Encryption:
http://msdn.microsoft.com/en-us/library/bb510663.aspx
– DO NOT store your certificates on the same server….use an encrypted USB disk and store it in a physical safe for example
– Are there any applications using your SQL Server? Hire a hacker to perform a security check (like SQL Injection)