Category Archives: SQL Server

Is There A Threat Inside?

Data-PrivacyIf you’ve been involved in technology for any length of time you are aware of outside threats to your network or databases. You read about some of these threats in the news such as hacking, breaches, etc.

All of these outside threats are pertinent and require our attention to detail as data professionals, but along with that threat are you considering any threats that could occur on the inside? Every shop should have some form of guidelines, documentation, regulations around their processes.

The risk from inside threats such as employees, ex-employees, and trusted partners. Some of these threats are accidental while others can be of a malicious nature. In either circumstance the consequences can be devastating for a company. Below are some things to think about within your own environment to prevent such actions from occurring.

Secure User Access

  • Stop unauthorized access – in all honesty this means button up the shop. If you have SA access across the board you are doing it wrong. Think about utilization of role based security, AD groups, etc. You are responsible for the data so don’t make this an afterthought.
  • Manage the threat of shared passwords – fifteen people shouldn’t have access to critical accounts. Check into secure user and password utility such as Secret Server; there are a number of companies out there that provide such products. Who is accessing these accounts and why?
  • Organizational Critical Assets – a companies assets such as data is one of the most important and integral pieces to the puzzle – it needs to be treated as such. This can mean many different things on many different levels. Do you know who is accessing your data and why?
  • Immediate Response to Suspicious Behavior – What do you do when you find activity going on that raises some concern? If you don’t have a process in place of reporting this then I suggest you think about getting one in place. Standards of such events are important; trust me on this. The time will come (and it will come) when threats become real. Procedures should be in place and gone over with all related data teams.

I ran across this article some time back from simple-talk and found it to be very fruitful in showing you How to Get SQL Server Security Horribly Wrong When you get time do check it out. In many cases I have run across security is an after thought – don’t let it be.

Define Areas of Vulnerability

This is a key component in getting started with taking your data seriously. Accessibility to information is a key deliverable in most shops; the data is the heartbeat. Face it; we live in a world today that is data driven; many decisions throughout every minute of the day are based on integrity of the data. Without addressing security in the design around the data it will leave you open to potential threats.

  • Network File Shares
  • Legacy Permissions
  • Logging and Monitoring
  • Change Control

These are just to name a few that could be potential vulnerabilities a shop can be exposed to.

Summary

We, as data professionals, need to take control and secure our data. But even more importantly we need to educate our end users on best practices and standards within the companies and shops we are associated with. Security can no longer be an afterthought.

If this means changing some things and rattling some cages then so be it; it may just save you in the end from a major security breach. We often are aware of external threats; what most people tend to over look are the threats from within the walls of a company.

It is imperative to take preventative measures and even the highest level of clearance should be monitored in some form or fashion. Think about the DBA for a second, and not just because I am one. They have the keys to the kingdom so to speak; same as a lot of sysadmins. There should be transparency in their actions; auditing should occur as to the what, when, and why.

Taking it a step further would be conducting data forensics (that would be a fun topic of discussion)

Bottom line I encourage you to start taking security around your data seriously if not someone else will.

How I Became A…SQL Server Data Professional

OopsLast night I saw a tweet from Matt Gordon (b|t); the topic caught my eye – “How I Became A…SQL Server Data Professional”. The original idea spawned from Kevin who is known as the SQL Cyclist (b|t) over here at this post

I’ll have to fill in the blank the same as Matt Gordon has with “How I Became A SQL Server Data Professional”

It definitely was not a bed of roses to get to where I am at now. My story is the same as many others across the world in that I became a DBA pretty much on accident at the time. I was a developer right out of college writing code in a language called Progress (think Visual Basic). I still remember to this today seeing variables in the code that had been passed down for a while with the Gilligan Island characters; you know skipper = minnow + Gilligan.

Needless to say I spent three years at that job and enjoyed it, but with growth and thinking I could take on the world I wanted to spread my wings and fly a little bit; which landed me the next 8 years at a place where my groundwork for getting into SQL would really flourish. I was doing SQL development work sprinkled in with some .Net and quickly realized that I wanted to stick with the core engine. Not sure why but SQL just stuck; it was intriguing to me. How to make queries run faster, how to get the most out of the engine,  why were queries taking such a long time to execute. All these things kept running through my head. Doing database work was building me into becoming the DBA that would bring me to my next job.

It was at this job where I got my feet wet with what SQL Community, PASS Summit, and Mentorship was all about. It’s been 5 solid years since getting involved and it has been one heck of a ride. As I sit back and look at where I started to where I am today I would never have guessed it. If you were to go back when I was younger people in my past would tell you that if it wasn’t sports oriented I would not have anything to do with it. Becoming a SQL Data Professional is not just a job to me; it’s a passion.

As I sit here and reflect back to all those memories I’m thankful for each one of them. No, it hasn’t always been easy. I believe the harder times have molded me and made me into a stronger more durable Data Professional. Whatever road you take to become what you are remember one thing – it is your journey; your story. You are the CEO of your destiny. Rise and grind – get it done.

Summary

I encourage you to take Kevin’s initial request to heart and think about when you became what you are now. How did you get there? What roads did you travel? Hope you had a good time reading this post; as it sparked a lot of memories for me. I wouldn’t trade any of them for anything in the world.

What Is SQL Saturday?

SQL SaturdayI was recently approached and asked, “What is SQL Saturday? I actually get asked that question more times than not from other people within and outside the community that have never had the pleasure of attending an event. I’d like to take a moment, and from my own perspective lay out the what, why, and how come you should familiarize yourself with such great events across the globe.

The What

You can read the official “What is SQL Saturday About” here

The Yates version is what you will find in this blog post and what it means to me as a data professional. Oddly enough I attended my first SQL Saturday event 4 yrs ago, and that stemmed from going to my first PASS Summit 6 yrs ago. Why the gap you ask? I don’t have a good reason; but what I can say is I was enamored by the fact I could go to this free event and learn from top-tier speakers. Usually there is a fee involved for lunch which is minimal and in some cases I’ve seen where you bring your own. These events usually consist of DBA, BI, Professional Development tracks split up across a full days time. In it you’ll find some speakers you would see at PASS Summit all the way to local and regional speakers. The good thing about these events are you can network and share experiences and knowledge with other data professionals from all walks of professions on top of the learning.

Depending on the size of the event you will have a chance to talk to various sponsors regarding their products that they offer. Not all events will have sponsors and that’s okay. The purpose of these events are to “help” people continue to learn and for others to pay forward the opportunities they have experienced.

The Why

The why is important. If you don’t get or read anything else on this post I want you to stop, open your eyes, take this in. I can sum it up in one word for you – PEOPLE.

I’ve found myself being a co-organizer for our local event in Louisville, KY – you can read about our upcoming one here. This is no easy undertaking, and I’ve seen the value; the difference it makes in data professionals. In speaking of giving back to what has been afforded to you; this is one way I believe that I can make an impact on the community. There is nothing like seeing a light bulb go off or seeing someone who has attended these events come up to you and say that something finally clicks. It’s about the attendees; the people.

Listen, it’s not a life about glitz and glamour. It’s hard work, it’s dedication, it’s requiring you to have a drive that when you are faced with adversity you overcome it. These events provide avenues for data professionals who can’t travel to the big conferences. Providing good quality learning is key to developing and cultivating our growing SQL Community ~ I’m a huge proponent that each one reach one. Stop and think about that for a second. Imagine how many people we have in our SQL Community. If each person reached one other person, my oh my, and if we help one at these said events then it is worth it in my eyes.

How Come

In my finite mind it started with a vision Andy Warren, Brian Knight, and Steve Jones had back in 2007. Knowing 2 of the 3 people listed I know that the mindset was geared toward  helping others learn because I know how much they have invested in me over the years. I look at SQL Saturday’s differently than I used to; over time it has grown from learning, soaking up all the knowledge I could; to speaking, volunteering, and helping at said events.

Believe it or not; speakers are people to. It is always encouraging when you see new speakers submit abstracts to these events. It allows for development and growth of upcoming rising stars to let their talents shine through.

Conclusion

If you haven’t attended a SQL Saturday then why not start? You can get a full listing of events here

I do request one thing of you if you attend. You will see volunteers at these events; remember one thing. They do this for free; we aren’t paid huge salary major league contracts; instead they (we) put in blood sweat and tears to put on a good event for you to come learn. I encourage you to seek one of them out and just say thank you; you will have no idea how much it will mean to them.

If you need help getting plugged into a SQL Saturday near you let me know. I’d love to talk to you and help you get started on your journey to further learning – just leave me a comment and I’ll reach out to you.

If you are a new and upcoming speaker, again give me a shout. I’ll be happy to provide some insights and tricks that has helped me over the years.

Let’s get after it and make it happen – each one reach one. Let’s Roll

Backup/Restore–What’s My Status?

WaitingThe day begins and you find yourself going through a list in your mind of things that need to be accomplished. You either make a mental note, jot it down on paper, or input notes into your mobile device. The day is getting off to a great start; you feel as though you have a sense of direction and purpose before you even open up shop for the day. It is then the phone rings or you get alerted to trouble; issues on the horizon.

The issue at hand requires a backup or restore to be completed and you find yourself dependent on the mercy of SQL processing the request. We’ve all been there; phone rings again and it is someone on the other line asking you:

  1. Are we done yet?
  2. What’s the status?
  3. How much longer will this take?

These are all legitimate questions that you will be asked, and it’s okay. Pressure situations are opportunities to make it happen. Over the years data professionals have built out their own script and document libraries. I’ve carried scripts around for years, one script I like to utilize regarding backups and restores is below. I have some variations to this script with what it pulls back, but the standard script hits the sys.dm_exec_requests  DMV.

SELECT  r.session_id ,
r.command ,
CONVERT(NUMERIC(6, 2), r.percent_complete) AS [Percent Complete] ,
CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time,
GETDATE()), 20) AS [ETA Completion Time] ,
CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min] ,
CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min] ,
CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0
/ 60.0) AS [ETA Hours] ,
CONVERT(VARCHAR(1000), ( SELECT SUBSTRING(text,
r.statement_start_offset / 2,
CASE WHEN r.statement_end_offset = -1
THEN 1000
ELSE ( r.statement_end_offset
– r.statement_start_offset )
/ 2
END)
FROM   sys.dm_exec_sql_text(sql_handle)
))
FROM    sys.dm_exec_requests r
WHERE   command IN ( ‘RESTORE DATABASE’, ‘BACKUP DATABASE’ );

By checking only for the restore and backup command lines you will be able to quickly identify your session id and get an  approximate ETA and percentage complete. you can tinker of course with the estimations if you’d like or pull back more fields. This is just a simple technique in utilizing a helpful DMV to provide info quickly.


Additional information

Aaron Bertrand (b|t) wrote a post several years ago around sys.dm_exec_requests that has good information in it that you can find here.

You can find the MSDN listing for sys.dm_exec_requests here.

What are DMV’s?

DMV’s are Dynamic Management Views within SQL that can help with a myriad of troubleshooting, performance tuning, and overall health of a system. You can find all the categories for DMV’s and learn more about them here.

Wrapping Up

I’ve been on both sides of the fence in the past where third-party tools are not always possible to have. These DMV’s can be life savers in certain situations; you can parachute in and parachute out gathering knowledge on any given situation. I urge you to explore and learn new things that may help you in your future.

**DISCLAIMER – Do not take code blindly from the internet because you found it on a blog and execute it without first testing it yourself.**

Why Use Red Gate’s SQL Multi Script?

Someone once told me at the shop that we have plenty of software utilities laying around that could be very useful that no one is utilizing. I got to thinking about that from a DBA standpoint; we are fortunate enough to have the Tool Belt from Red Gate and with that comes a plethora of utilities such as SQL Data Generator, SQL Document Manager, SQL Compare, SQL Data Compare, and the list could go on (full listing).

One of those said utilities is a little gem called SQL Multi Script. For me I have found this utility most useful. First of all I’m a heavy SQL Compare (why use SQL Compare) user. We utilize it here in the shop daily for the most part and I like the ease of how to call all of Red Gate’s apps from one location:

By clicking on the icon in top left you will be presented with a list of utilities in your tool belt (pending on your licenses and I’m using version 12.1)

RedGateIcon

As you can see the SQL Multi Script utility is the last on my drop down menu:

RedGateIcon2

The Why?

So, now that we’ve established how easy it is to call the utilities from any said location within the product line; exactly what does multi script mean for me? Glad you asked; I’m going to try to take you on a journey inside a utility while painting an example of how to use to tool in a real life example.

When you first come into the utility you will see a screen similar to the one below:

RedGateIcon3

What I like about the utility is the ease and flow of the product. It didn’t take long to get up and running with it. If you notice on the left hand side you have the option of adding existing or new scripts to the template. In my case for releases this allows us to save multiple schema changes for various databases and incorporate them into one setting for a quick review and execution. One thing that I have ran across, that is no fault to the utility, is static data scripts that may need to be run in a certain order, but that is up to me to put in the correct order of execution once I get the scripts into multi script. I will show an example below of what multiple scripts look like within the utility.

On the right hand side you will see  a distribution list. By clicking on configure you can set up a list of servers that you normally deploy or execute to in one location. One thing to keep in mind is to execute the scripts according to which server (databases) you select. Be cognizant of which ones you are selecting within the utility.

Example of adding existing scripts:

RedGateIcon4

Example of configuration set up:

RedGateIcon5

At the far right you can see I have a test distribution list set up. This affords me the ability to add servers from the list on the left or I can add any SQL server into my listing. From there I can pull down exact databases on that server to execute scripts against. For this purpose I’ll pick a couple of databases from the local server for review:

RedGateIcon6

I have two scripts I want to execute against two databases on my local. I can execute both scripts if I want at the same time or I can execute one script at a time. I also have the ability to select only one database I want to run the scripts against or I can select both databases to run the script against (think of syntax and proper use of database names if you go this route in the script). This is just flexible functionality in which the utility presents.

Back to my case at hand; since we deploy to multiple servers against multiple databases also think about the big picture. I can include databases from all over the enterprise environment if needed and let the Multi Script utility control when to run what, where, and in what order. If you look closely you will notice a blue arrow pointing down in the “Scripts to Execute” section. This arrow, along with the up error next to it, allows me to move my scripts up and down in the order I want to. So I can add all the scripts I want at one time and then come back through and organize them when I am ready.

The Results

Now, after the script execution is complete you can review the results in the lower window pane of the Multi Script utility. Will look similar to results window below:

MultiScriptResults

A feature I like is the ability to save multiple script executions in different formats on work that was completed; this comes in handy when supplying back a summary report of what was changed to a:

  • Change Management Team
  • QA Team
  • Dev Team

MultiScriptSaveResults

Said All That To Say This

There are various utilities available to us that we may not even have explored yet. This little utility by Red Gate has come in real handy for myself and my team. Take the time to look at what is available to you and see what may fit your needs. This concept does not just pertain to Red Gate utilities. Look at various things in your everyday routine and ask yourself:

  1. What can I utilize to become more efficient?
  2. Why am I not utilizing the utility?
  3. Is there a lack of knowledge?
  4. Where are my gaps that I can improve on and how do I get there?
  5. Is there a utility out there where I won’t have to reinvent the wheel?

Look for the hidden gems and continue to provide “Leadership Through Service”

Hold The Fort

stressHave you ever heard the phrase “Hold The Fort”? Long ago in battle supply routes were targeted by enemy regiments that would cut off rations to fighting units. As the battle ensued the enemy had driven back their counterparts to a small area on a hill. They were being overwhelmed with many wounded and dying; that is until in the distance they noticed reinforcements were on the way.

How many times have you, as a data professional, been stuck wondering the same thing? Battered, worn down, and flat-out exhausted until you look and see reinforcements on the horizon. Sure, we’ve all been there, but to some it is a question of how do I call in re-enforcement’s or how do I even go about sending out a help signal? Below are some various ways you can tackle problems:

Forums

There are several forums out there that can provide some great insights into similar issues that you may be experiencing. To a certain extent I think forums are a great avenue to explore potential solutions. With that said however you cannot take every answer verbatim. There can be some off the wall answers out there that shouldn’t be followed. It is important to test anything you find on the web regardless of who it comes from.

You can find some of the forums I frequently attend here.

#sqlhelp

One thing that I’ve learned about the community is the willingness to help each other out. Remember above when I stated “Hold the fort, and reinforcements are on the way”; this is a good twitter hashtag to be familiar with. Professionals from all over the world take this seriously and it is not to be mistreated on how it is utilized. I have received great assistance in the past on issues I’ve been stuck with.

Phone a friend

We all have our “go to group” we bounce ideas off of. Sometimes it is helpful to bounce ideas off of another data professional. You may surprise yourself and actually start talking through your problem and come to an answer you may not have arrived to had you not initiated the conversation.

Old Fashion Testing

As a data professional you cannot be afraid to get your hands dirty. Prove your theories and test the scenarios as you run across them. One should be doing this anyway, but in doing so a wealth of knowledge gained is at your fingertips. We stop learning when we stop trying; just because you encounter one failure doesn’t mean you stop. You keep fighting and beating down that door until you make it through the other side. How bad do you want it?

Is It A Vendor Product?

I have the opportunity to work with a plethora of vendors. Maybe your issue revolves around a vendor product and you are not able to make any changes. Great, this happens all the time to data professionals. Some questions I’ll throw out there are:

  1. Have you opened a dialogue with the vendor about the issue?
  2. Have you documented the issue; not just call and say I have a problem?
  3. Have you tried to reproduce the error?
  4. It it already a known bug?
  5. Is it something that we can address internally before we talk with the vendor?

I’ve worked with a couple great vendors in Red Gate and SentryOne over the years. As a data professional I’ve seen vendors (not all) take pride in a quick response time. If there is an issue with a product they will want to take care of it in a swift manner or provide feedback as to why, when, and what to expect.

Internal Resources

I’m going to say this here, and it may shock some of you. No one knows everything; there may be some who elude or perpetuate appearances that they know everything there is to know,  but that is not the case. Whether you are in a shop or maybe a consultant (who can tap into their contact base) there are internal methods to maybe approach a different business unit or tech unit on an issue to get an outside perspective. Sometimes a fresh set of eyes goes a long way.

Technical Blogs

There are many great technical blogs out there; I have listed some in my DBA Blogs section to the right of this page. Each person is different and has their own go to Blog for review. I receive questions all the time from readers and other data professionals; being respectful is key but I have not seen one data professional out there who would not extend a helping hand if presented in a respectful manner. DO NOT TAKE this as one will provide an answer. We may show you how to connect the dots, but it is important one takes the necessary steps to get to the answer on their own. Nothing is given….work hard for it.

User Groups

Maybe your issue is not a hot ticket item and it can wait to a user group meeting. These are great places to bring up questions and issues in a local environment with some pretty stellar professionals. I guarantee that if you ask a question and no one knows the answer then someone will do their best to find out that answer and further the discussion. An example of a user group can be found here.

PASS Summit

Perhaps your issue is ongoing and you are at the PASS Summit. Great, besides being at a conference with over 6k of our closest friends I would take advantage of the SQLCat and AzureCat Team’s hours. It is a great way to tell Microsoft of your issues and get advice from some of the top-tier people in our industry.

Conclusion

I don’t know what your situation is or will be. Obstacles will come from all angles this year; it is up to us on how we deal with them. I would be amiss if I sat here and didn’t tell you that it won’t always be easy. Nothing in life is easy; some days you will have to work and grind your way through until you get to an answer for an issue. In the end though, when you do find that answer (and you will) you will be able to learn from it and move on. It is part of a data professionals journey.

Keep fighting and keep working toward your end goals. Never stop learning; never stop gaining knowledge.

PASS Summit Keynote 2 – Release 2

pass_2016_website

Dr. David DeWitt is getting ready to take the stage to a huge ovation and is thrilled to be back at PASS Summit.

Haha, bring back #BringBackRimma is a twitter tag that he has put in place….looking forward to hearing Dr. David DeWitt speak today…..

  1. Why data warehousing in the cloud
  2. Scalable DW fundamentals
  3. A look at 3 leading competitors

Why Data Warehousing in the Cloud

Reduce time to insights

Dynamic adjust capacity

Appliances will always be the gold standard and cloud is somewhere in between, but there is no “free lunch”

No CapEx and low OpEx

Go from conception to nsight in hours

Rock bottom storage prices

Simple upgrade process

Flexibility to scale up/down compute capacity

Scalable DW Fundamentals

Interesting to see partitioned parallelism as a point that discusses software building blocks.

Hardware failures carrying over into the cloud…..yes this does happen.

What are some alternatives to scalable DW designs? Shared-Nothing and Shared-Storage.

Shared-Nothing is the gold standard; can be designed and scaled indefinitely.

*Storage and compute are co-located*

Shared-Storage is stored on remote storage (SAN, Azure, S3)

Demos occurring………………….

PASS Summit Keynote 2 – Release 1

pass_2016_website

Grant Fritchey taking the stage to talk about the financial health of PASS.

250k members , 170 countries, and 87% global representation – outstanding.

73% return into the community; focused on being a one global community

119 SQL Saturday Events held in 2016; have had pretty solid growth over the course of 2012-2016

Six 24 hours of pass in 5 different languages

A new PASS BA Event in Chicago coming up; be on the lookout for details.


Denise talks about what does PASS mean to you? Interesting question so ask yourself; what does PASS mean to you?

Professionals discover, create, and network those are some of the topics that have been addressed.

Connect….ShareLearn > but as technology progresses and grows so do we.

PASS with a new BRAND……yep that’s right. New shirts are flying out for the new brand.

A new website is on it’s way (work in progress) with a launch date of early next year.


11:45 WIT luncheon today sponsored by SentryOne (ticket required – check with the registration desk)

Speaker idol at 4:45 round 2


 

 

 

PASS Keynote Part 2 – Live Coverage

pass_2016_website

 

Good morning from PASS Summit in Seattle, Washington. Like yesterday I will be live blogging the event. The same format of the blog will apply with up to 5 releases every 15 minutes.

Yesterday was a great first keynote, and from what it appears attendees are pretty excited to see what the second keynote will hold.

If you get a chance check out the blog and look forward to keeping you informed.

 

PASS Summit Live Keynote – Release 5

pass_2016_website

Julie Koesmarno, Senior Project Manager at Microsoft, takes the stage to talk about business analytics utilizing War and Peace.

Tracing emotions of each key characters via a heat map…..pretty cool to see application of SQL Light language.

Cognitive capabilities managed with big data……


Deep learning is everywhere; this means that SQL Server 2016 falls into that category. Seeing a demo on drone functionality and feeling the pulse of the power grid


Jen Stirrup (Data “Whisperer”) takes the stage and talks Pokemon…..yes folks Pokemon but there is more than that.

Utilizing Power BI for the activity for each Pokemon sprinkled in with some “R”. The data itself is stored in the Azure SQL Database with utilizing the Azure Services.