Friends Of Red Gate 2015

forg-logoI’m excited that I’ve been asked to be back involved for another year in the Friends of Red Gate program. This program allows influential community members, community bloggers,  MVP’s and intuitive minds an inside track on helping with pre-existing and existing utilities that Red Gate has in their arsenal.

I am a Red Gate user and thankful that my shop is a Red Gate shop. The tools that they provided are second to none in the life of a data professional. I always look forward to doing some sessions on some of their products at various events so if you are eager to learn then send me a note.

It’s an honor to be considered for this program and I’m humbled to be able to continue to be associated with a company as Red Gate.

Let’s get 2015 started and start making that sweet SQL music.

Impact Player Series – Part 1

ImpactI wanted to start this series in regards to impact players that go above and beyond in the SQL Community. Coming from a sports background this resonates well with me and by the end of the year will have a 12 person roster.

I get asked a lot when I go to events, conferences, talks, groups who inspires me. Or I get asked where do I start. There are many fine folks in the SQL Community and I cannot encompass them all; believe me this 12 person team could easily become much more.

So, who the heck is the first impact player? Being that I am from Kentucky it pains me just a little to write this knowing that this person is an SEC rival (college sports) and is a true orange fan in Florida ~ Ed Watson (B|T).

Ed is a Microsoft SQL Server MVP who I have gotten to know over the past year. His tenacity alone has spoke volumes in how he approaches speaking engagements, obstacles, issues, SQL life, basically the things that make up a data professionals life. I would classify Ed as one of those exceptional data professionals that you hear about. If you not ever had the chance to hear him speak at an event I suggest that you keep your ears open and if he comes to a town near you attend ~ you would not be disappointed.

I’ve never one time have come to Ed and him tell me not today; he is always eager to assist and help and has been a strong voice in the community.

He is a frequent blogger over at the SQL Swampland and can be found on twitter; although don’t interrupt him while a University Florida football or basketball game is on. If you choose to do so then you have been warned up front.

Check out Ed’s blog or give him a shout on the twitter feed. Truly a standup guy and one worth following in the community.

While having a Fab Five intact I’m excited to build this out and see where it leads.  Check back next month for Part 2 in the series.

A Healthy SQL Server

fitnessT-SQL Tuesday has come and gone and I missed the boat due to some ongoing work constraints. With that said I liked the topic and wanted to do a separate post as I hated to miss it.

Robert Pearl (B|T) was the host for January and I think the topic he chose was spot on with the time of year and well, why not just jump in and get started?

What can you do to make your SQL server healthy?

The theme is broad, and there are plenty of tips and tricks that can be said. I’ll only touch on a few that may be of some use in this upcoming year and hope they can resonate with someone in the community.

PBM\CMS

Policy Based Management and Central Management Server are two useful resources at the data professionals disposal that can aid in a multiple SQL server configuration shop.

PBM allows you to execute a set of standard and custom policies against one or a set of servers allowing you to receive custom daily automated reports. Why not have this at your disposal to see what is going on with your servers before you even get into the office.

CMS allows for a one stop shop of all your servers. One thing I like about CMS is execution of scripts against multiple servers at one time; with that said with much access such as this comes great responsibility and not for the faint of heart. It’s imperative you truly understand what you are working with before getting involved with this but is a great resource to have.

Monitoring

If you aren’t monitoring your servers then why not start today. Some ideas you can take into consideration but not limited to are:

  1. Job notifications on event of failure
  2. Space limitations
  3. Wait Stats
  4. Index Fragmentation
  5. Statistics
  6. User\Login information
  7. General baselines
  8. Backups
  9. New servers brought online
  10. Blocking

Don’t end with these; the intent is to get you to think about what might work for you at your shop.

Backups

I put this topic in here because I wonder how many people are testing their restores? Do you receive notifications in event of backup failures? Trust me on this; don’t be the one to get caught not having a backup or not knowing if your backup works.

Automate, Automate, and Automate

Look at your day to day activities and then ask yourself; can any of these tasks be automated? The idea is to become more efficient and be pro-active instead of re-active.

DBA Standard Database

Do you have a standard DBA database on all the servers that can house your maintenance stored procedures, tasks, server info (yes you need to know what is in your environment), any other pertinent documentation.

Source Control

Is your code source controlled? If not time to get in the game. One good place to start is Red Gate’s Source Control utility

Conclusion

Listen, these are just ideas and not even the tip of the iceberg. The intent is to jump start your mind and think of some possibilities that  you may not already be utilizing.

I sure hated to miss this month’s block party, but that is okay. Time doesn’t always work out in our favor, but we pick ourselves up and move on. Nothing is handed to you; work hard for it. Look at your environment and be that impact player or game changer. You be the one to make the difference.

**Always always always test new things you find on a test environment. Do not put anything straight into production.

Impact Players – SQL Style

MakeADiffAwhile back I did a post on my Fab Five – was one of my favorite ones I’ve done to date because it dealt with individuals who have made a tremendous impact in my professional career in some form or fashion. I am not ashamed to admit I am a sports fanatic; lived it my whole life both playing basketball at a high collegiate level, soccer, football, baseball, and golf. Sports has been good to me and some of the lessons I learned early on has carried over into my data professional career.

Teamwork

Have you ever been part of a team at work? Even if you are your own department there are other vendors or departments that you have to work with most likely in order to achieve a goal. One thing that always stuck with me and humbled me at an early age is that “there is no I in team”. Being a past point guard one of my jobs was to facilitate the rock and get people involved in the offense. Little did I know how much those lessons on the court would carry over to my day to day work.

Worth Ethic

I was fortunate enough to be surrounded by some stellar people early on who “invested” (there is that key word again) time in me. My normal routine in high school was waking up at 3 a.m. to get to the gym; meeting my assistance coach. Working out and shooting 800 threes, 200 free throws, countless conditioning drills and the list goes on. Running wind sprints in the street with strength shoes on to gain speed; wearing blind folds as I dribbled the ball to believe in my skill set and handles so come game time I would be sure and capable.

As I sat at my locker I could see a saying that said, “Somewhere somebody is improving getting better each day and one day you will meet that person; what will you do to get ready?”

You know what? All that has carried over into my SQL profession. Don’t get me wrong, a balance has been drawn. Things are different now; I have a family who depends on me and my time is important, but that fire and work ethic is still there – always will be.

Accepting New Ideas

Do you think I walked into college and knew everything that was going on? Ha, back then I thought I did, but quickly realized the level I had to play was nothing I’ve ever seen before. See in high school you could get away with taking a play off, scoring at will. However at this level everyone can do what you do; so what can you do to stand out?

Each day I try to learn something new from someone. The community is filled with brilliant individuals, and so is most shops for that matter. Don’t ever reach the point that you feel you have arrived? If you do than it is then you will start to fail.

Impact Player Series

So, to the point at hand, how do you become an impact player? My Fab Five are top tier individuals, but there are more out there who has helped me with their encouragement and their knowledge. Each month I plan on picking a community member who is just that – an impact player in the community and who goes above and beyond the call of duty.

Individuals who get the job done, not afraid to fail, and continuously work to hone their craft.

At the end of the year I will have a 12 person team assembled of impact players that hopefully will benefit others in the SQL Community that they to can learn from.

Until then…

Reports In SSISDB

I was approached with an in shop issue where a group could not view the execution reports in the SSISDB. The reason for this was due to the security standards in place at the shop; the user could not be allowed to have the SSIS_Admin role.

In order to allow the user group access to the review Integration Services reports on package executions the SSIS_Admin role had to be given. This role would allow the following capabilities:

· Import Packages

· Delete own packages

· Delete all packages

· Change own package roles

· Change all package roles

This role also elevates privileges to sysadmin. This elevation of privilege can occur because these roles can modify Integration Services packages and Integration Services packages can be executed by SQL Server using the sysadmin security context of SQL Server Agent.

What this document will provide is a solution around not granting the SSIS_Admin role, but still allow the necessary entities access to the SSIS execution reports without granting any write, execute, create, update, or insert access.

The Catalog Views

Microsoft has two catalog views that make up the report access for SSIS packages (2012 and later) that I have found. We will look at both of these views and alter them to comment out the where clause. In doing so this will negate only SSIS_admin and sysadmins from having access to the reports, but will allow other users access to these reports.

Catalog.Event_Messages

The first catalog view we will look at is catalog.event_messages. This view is simply utilized for displaying information about messages that were logged during operations. The way Microsoft has configured this view is to only allow the SSIS_Admin or sysadmin privilege to view. In our case we would like to have other groups the ability to review the messages only. In order to do this we need to alter the catalog view.

To access the catalog view navigate to the SSISDB on the SQL Instance. Right click the catalog view and say alter:

clip_image002

Next comment out the where clause and execute the alter statement updating the catalog view appropriately

clip_image004

Catalog.executions

The second catalog view can be accessed in the same manner as the first catalog by repeating the same methodology of altering the view. Catalog.executions displays the instances of the package execution in the Integration Services catalog. Packages that are executed with the Execute Package task run the same instance of execution as the parent package.

After right clicking and altering the view, the where clause will need to be commented out as shown below:

clip_image006

After the where clause is commented out execute the alter command to update the catalog view.

Granting Catalog Access

After altering the catalog views we are left with a manual but needed process. Within the Integration Services Catalogs the following steps will need to be completed:

1. Right click on the package and go to properties

2. Once the Folder Properties dialog box is initiated go to Browse in the upper right hand corner

image

3. Once the Browse All Principals dialog box is initiated select the public database role and click ok

image

4. Ensure the grant read access is then given to the database role and click ok

image

Conclusion

By taking the above steps the users can now see the reports and that is it. In testing I’ve found that users cannot do the following:

· Create new environmental variables

· Create new packages

· Create new folders

· Cannot add, insert, update, or delete anything in the SSISDB or the Catalog Folders

· Cannot initiate any SSIS SQL Agent jobs

· Cannot execute any queries against the SSISDB

What I’ve found users can do the following:

· See package names

· Right click and select all executions

· View the reports

**As with anything do not take code from the web and blindly implement into your production environment.