Introduction to DataCamp Projects

Introduction to DataCamp Projects is the first project given by DataCamp.  The intro goes on to explain the projects are a place to apply concepts learned in the courses.  The main focus is on Jupyter notebook.  Jupyter notebook allows code anywhere.  This is a web based app which is built to handle large amounts of data to create visualizations such as plotting points.  This project was part of the free course.  There wasn't much substance the project narrative didn't seem to flow very well.  I hope this is a low point of the career track.

project information:

Rasmus Bååth
Instructor at DataCamp
6 tasks
1,500 XP

Intro to Python for Data Science

I have recently decided to expand my knowledge of data science.  To accomplish this goal, I have enlisted myself in the career track- Data Scientist with Python by DataCamp.  I have just finished the first course, Intro to python for data science.  Data Camp's method for teaching combines videos, light reading and interactive exercises to move through the course.  The course had and estimated time of 4 hours to complete.  The intro course was just that, an introduction that did not need a coding background.  We began with the very basics of coding, including types, variables and functions.  We covered how to import the numpy package, and worked through some examples on how to use numpy with given data sets. I really enjoyed the delivery method of the course and will continue taking other courses from DataCamp. Each course gives you a Statement of accomplishment  which can be downloaded or shared directly to LinkedIn.  Anyone can try the introduction course for free.

Course Information:
Filip Schouwenaars
Data Science Instructor at DataCamp
Intro to Python for Data Science
https://www.datacamp.com/courses/intro-to-python-for-data-science
4 hours
11 Videos
57 Exercises
4,700 XP

You can find my profile here: https://www.datacamp.com/profile/joelcottage

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]

SQL Agent Jobs without notifications

All of our SQL Agent Jobs should notify someone if a failure occurs.  Below is a query to find all SQL Agent Jobs that are enabled, and do NOT have an email notification.  Quickly dissecting the script below, we are looking for [notify_level_email] NOT IN (1, 2, 3).

1 = When the job succeeds
2 = When the job fails
3 = When the job completes

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

Where to find Deadlocks

All deadlock information is logged under the system_health extended event by default.  simply open system_health, right click package0.event_file > View Target Data...  At this point we can right click the grid, and select filter by this value.

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?

Backup Solution

New Year, Same old story.  SQL Server backups are a necessity for every SQL Server Instance.  While there are many ways to accomplish this task, my favorite by far is Ola Hallengren's SQL Server Maintenance Solution.  The script comes complete with Database backups, rebuild indexes, integrity checks and even log cleanup.

Show Desktop in Windows 10

I am always flooding my desktop with open windows while developing.  Showing the desktop is a must have in a situation where you need to switch gears for a few minutes.  Windows 10 has the show desktop built in right out of the box.  Simply click the small button in the lower right hand corner.

Auto Fix Orphaned User

When restoring a database to a new or different instance, logins at the instance level are not mapped to the database users.  SQL SERVER has a built in stored procedure to help us identify orphaned users, sp_change_users_login.  We can also use this stored procedure to fix the mapping if the login exists at the instance level.  Finally, if we need to create a new login for the existing user, we can create a login and password.  See the below examples.

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.

db_executer

SQL Server comes with several predefined database roles which fit the needs of most day to day user needs.  A big miss that I have found is the ability to execute stored procedures.  Developers need the right to execute the SPs they are creating in the DEV and QA environment.  Many service accounts also need execute rights.  All that needs done is to create the Role, grant execute rights, and add members to a role.

CREATE ROLE db_executor
GO
GRANT EXECUTE TO db_executor
GO
EXEC sp_addrolemember 'db_executor','User or Group'

 

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.

Screen Capture Blurs

When I first started writing technical blog posts, I found an immediate need to be able to blur parts of my screen captures.  I have a habit of using the built in windows snipping tool (actually anchored to my task bar) and wanted to use a tool in addition to my snipping tool.  I quickly found awesome screenshot https://www.awesomescreenshot.com/

Awesome Screenshot is a browser extension.  I can, however, take my snips from day to day work, simply select Awesome Screenshot, click Select a local Image, drop it in my browser, and blur my work from there.

This is a great tool, simple to use, and great for blogging!

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.

TFS - SSRS

The last 2 posts have seen use use TFS to build and deploy database models and SSIS packages.  http://sqljoel.blogspot.com/2017/07/tfs-ssis.html

We are now going to turn our focus to SSRS.  Just like applications, database models and SSIS our reports should get the same attention to detail when it comes to version control.  Here we will build and deploy our SSRS reports through TFS.  For ease of deployment, I have build a seperate project for each folder I want to see on our SSRS Server.  Just as we had done with SSIS, when building our solution, we will need to use a command line task.  We will call the devenv.com with the switches to /rebuild our solution.