Reporting - Default dates

When writing SSRS reports, we always have the need to set default dates for reports.  Usually prior week, or prior month.  Sometimes they are current week to date, or current week to month.  Whatever your default reporting needs are, here is a list that should get you started.



DECLARE @Date datetime = GETDATE()

-- Start of Month
SELECT DATEADD(m, DATEDIFF(m, 0, @Date), 0) [Start of Month]
-- End of Month
SELECT DATEADD(d,-1,DATEADD(m,1,DATEADD(m, DATEDIFF(m, 0, @Date), 0))) [End of Month]
-- Start of Week
SELECT DATEADD(d,-1,DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date), 0)) [Start of Week]
-- End of Week
SELECT DATEADD(d,-1,DATEADD(WEEK,1,DATEADD(d,-1,DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date), 0)))) [End of Week]

-- Start of Prior Month
SELECT DATEADD(m,-1,DATEADD(m, DATEDIFF(m, 0, @Date), 0)) [Start of Prior Month]
-- End of Prior Month
SELECT DATEADD(d,-1,DATEADD(m,-1,DATEADD(m,1,DATEADD(m, DATEDIFF(m, 0, @Date), 0)))) [End of Prior Month]
-- Start of Prior Week
SELECT DATEADD(WEEK,-1, DATEADD(d,-1,DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date), 0))) [Start of Prior Week]
-- End of Prior Week
SELECT DATEADD(d,-1, DATEADD(d,-1,DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date), 0))) [End of Prior Week]

List of all failed SQL Agent Jobs

Some SQL Agent Jobs run multiple time a day (I have see a job running every 10 seconds in Production).  SQL Agent only gives the the current state of all the jobs.  Here is how you can find all failures with their date and time.  From here, tracking down the root cause should just be a bit of investigative work.

SELECT  sysjobs.name
,sysjobhistory.run_date
,sysjobhistory.run_time
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.sysjobhistory
ON sysjobhistory.job_id = sysjobs.job_id
WHERE sysjobhistory.run_status = 0
AND sysjobhistory.step_id <> 0
ORDER BY sysjobhistory.run_date DESC, sysjobhistory.run_time  DESC

Redgate's SQL Search

I have been reluctant to use Redgate's SQL search.  Instead I have been scripting out the database and running a search.  I have used many clunky add-ins with little to no value add, which has put me off to trying many others.  Encouraged to give SQL Search a try by a friend, I quickly realized I had been missing out.

With a simple menu, I can quickly decide what I would like to search for.  Check a few boxes and start typing my search, right from SSMS.

Results are generated quickly, it didn't take long for me to see a time saving.  Gaining results quickly also kept me focused on the task at hand without loosing my concentration.  I highly recommend Redgate's SQL Search to ANYONE using SQL Server.

Custom Color for Production Connection

Have elevated permissions in production and want a visual aid to help quickly identify your production environments?

Select from all tables on an Instance

We have all inherited less than desirable SQL Server Instances.  DBAs and Developers will both need to go through the countless databases, tables, SPs, etc. to get a handle of the situation.  In such a fragile situation, we need to ensure we keep our data integrity and prevent our developers from accident updates while trying to trouble shoot an issue.   Here is a nice Server Level Permission set to allow Developers to select from any table in any database on the instance.

Restore Master Database

Recently I was tasked with migrating a SQL Server Instance to a new infrastructure.   Along with the migration, I also had to create a DEV, QA, UAT  environments as they were not part of the original infrastructure.  Instead of trying to move all the pieces separately, I migrated the databases, along with restoring the Master database.

To being we must make sure the new SQL Server Instance matches the existing instance.  Here is how we make sure the Instances match.

TDE - Transparent Data Encryption

Transparent Data Encryption (TDE) is a method of encrypting the database, data files and log files on the physical disk.  TDE is also known as encrypting data at rest.  TDE does real time encryption and decryption between the disk and memory.  We will be using the same Key Hierarchy and process as discussed in SQL SERVER Column Encryption.  We have one additional step, the encryption of the database itself, shown below.

SQL SERVER Column Encryption

Encryption has become a necessity when storing data.  There are many ways to encrypt data, here we are going to encrypt a column within the database using a key within the database.

Encryption Hierarchy - This is the encryption hierarchy within SQL SERVER

AES - starting with SQL Server 2016, we can only use AES_128, AES_192, and AES_256 encryption.

Looking at the standard encryption process, each layer of the hierarchy is encrypted by the layer above.  In this example we will use a symmetric key stored within our database.  We will encrypt our key with a certificate also within our database.  Our Certificate will be encrypted by the database master key DMK.  Continuing with standard SQL Server architecture, we also have a Service Master key SMK which is created when SQL SERVER is installed.  Our top level of encryption is Windows Data Protection API (DPAPI).  We could add additional layers.

VisualCV

Every professional must have a resume.  Most of the resumes I have reviewed in the IT world have been lack luster at best, and these are the ones which make it through HR.  One of the problems I had at the beginning of my career was that my resume would be spun up one night after work when I decided I wanted something more.  Each time would be a new resume, new format, complete rewrite from the ground up.  This method was error prone, time consuming and generally demoralizing.  Over time I have learned to continuously update my resume every few months with the latest projects I have been working on.  My resume is now ready to go at a moments notice.  Even though I am continuously tweaking my resume in its current format, I am always looking for ways to

Temporal Tables

Temporal tables, or system-versioned tables are new to SQL SERVER 2016 and are a way to have access to all of the history of a table.  In small databases or for tables where rows are rarely updated this feature is a beautiful thing and will soon find its way into many best practices.  To get started, simply right mouse on tables > New > Temporal Table > System-Versioned Table...

When creating a new Temporal Table, you are provided a template to generate the new table.  The first section of the given template is for dropping the table if it already exists.  Since this is a new table we can skip this part and go to the create table section.  *Remember, System versioning must be set to off in order to drop the table.
The second section of the template is for creating the system versioned temporal table. In order for a table to become a system versioned temporal table, we must have a Primary Key and 2 datetime2 colums.   The with clause of the create table statement turns system versioning on and defines the history table.