I’m a data geek :) In fact, I like data so much that I have made it my career! I work with Azure Data and the Microsoft Data Platform, focusing on Data Integration using Azure Data Factory (ADF) and SQL Server Integration Services (SSIS).
In this category, I write technical posts and guides, and share my experiences with certification exams. You can also find a few interviews with Azure and SQL Server experts!
Azure Data posts cover topics like Azure Data Factory, Azure SQL Databases, Azure Data Lake Storage, and Azure Synapse Analytics. Microsoft Data Platform posts may cover topics like SQL Server, T-SQL, and SQL Server Management Studio (SSMS). You may even find the occasional Power BI post in here!
I know Bob had a very busy schedule at Microsoft Ignite, so I’m very thankful he was able to spend a few minutes with me! I hope you find this as interesting as I did :) If you want to learn even more, you can watch his session Experience Microsoft SQL Server 2017: The fast and the furious from Microsoft Ignite 2017:
I got to interview Buck Woody about Data Science at Microsoft Ignite 2017! :)
In this interview, we chat about Microsoft Business Analytics and AI (formerly known as Cortana Intelligence Suite), Artificial Intelligence in Excel, intent-based programming, Predictive Analytics, DevOps for Data Scientists and life-long learning.
Data Science with Buck Woody – Microsoft Ignite 2017
I’m so thankful that Buck took some time out of his busy schedule to chat with me, and I hope all of you enjoy this interview. If you want to learn more, watch his full session DevOps for data science from Microsoft Ignite 2017:
T-SQL Tuesday #68 is hosted by Andy Yun (@SQLBek). Many SQL Server defaults are not ideal, and most of us have a list of defaults we always change. Andy wants us to Just Say No to Defaults and blog about what, why or how we change defaults.
If you are an SSIS developer like me, there is a big chance that the ProtectionLevel in SSIS Packages is on top of your list of defaults to change. The default ProtectionLevel is EncryptSensitiveWithUserKey (ugh), but most of the time it is not the best option. Raise your hand if you have ever asked your favorite search engine for advice on issues like “SSIS package fails in SQL Server Agent job” or if you have ever heard someone exclaim “but it works on my machine!?” :)
There are many great blog posts about the different ProtectionLevels, why you probably want to change to DontSaveSensitive as your default, and how to use configurations and parameters instead of encrypted SSIS packages. I will not go into details about any of that in this post, but I will use ProtectionLevel as an example default property you want to change in many SSIS packages at the same time.
How do you batch update properties in existing SSIS packages? You probably don’t want to open up every single package and change them manually?
T-SQL Tuesday #66 was hosted by me on May 12th. The topic of the month was Monitoring. We all monitor something while working with SQL Server, and this month’s blog posts covers all kinds of topics by DBAs, developers and BI professionals.
Monitoring is a very wide, but also very important topic for all of us. Be proactive and monitor your databases, servers and environments (even test and development), and make it a goal to discover issues before your end users alert you. Invest in vendor solutions, use the tools available in SQL Server or write your own custom solutions, just make sure you monitor what is critical in your company. Capture data to see trends over time and use PowerShell to automate tasks. Also don’t forget to monitor the monitoring solutions!
Want to participate or read more?
Monitor the #tsql2sday hastag on Twitter the first week each month for the invitations. Steve Jones (@way0utwest) keeps an updated list of all previous T-SQL Tuesday Topics. If you ever need inspiration to write a blog post, just pick a topic and write away.
Thank you again to everyone who participated in T-SQL Tuesday #66! :)
T-SQL Tuesday #66 is hosted by me (yay, fun!) and is all about monitoring. We all monitor something while working with SQL Server, and there are so many topics to choose from. As a Data Warehouse developer I use Adam Machanic’s sp_WhoIsActive all the time, I look at the Integration Services Dashboard in SSMS to monitor SSIS package executions and I check the Job Activity Monitor for a quick overview of what’s currently running on our servers.
However, I decided to write about a custom Real Time Monitoring solution we use in my company (Storebrand) that my coworkers in Lithuania created. I couldn’t resist this opportunity to show one of our cool solutions that I get to use and to brag about how talented my coworkers are :)
Storebrand Real Time Monitoring
There are many great monitoring solutions available out there. In my department we actually use several solutions from vendors to monitor our SQL Servers and Business Intelligence environments, but we also had some very specific monitoring requirements. None of the vendor solutions available at the time were able to provide all the functionality we needed, so we built a custom real time monitoring solution:
Easily create and edit personal projects, or collaborate on shared projects
Web-based interface to access projects from any computer in the network
Pick and choose specific files, databases, cubes, jobs and even job steps to monitor in each project
Create status and quality checks, and subscribe to get alerts via e-mail or SMS if the checks fail
Welcome to T-SQL Tuesday #66! My name is Cathrine Wilhelmsen (@cathrinew) and I’m very happy to be your host for the T-SQL Tuesday coming up on May 12th.
Much Monitoring in the Month of May
The topic of the month is monitoring. We all monitor something while working with SQL Server: Maybe you’re a DBA who monitors database performance, an SSIS developer who monitors job and package execution, an application developer who monitors queries or a BI analyst who monitors report usage? Do you use T-SQL or PowerShell scripts? Have you created your own monitoring reports or applications? Have you invested in a complete monitoring solution from a vendor? What is monitored manually and what is automated with alerts? If a brand new SQL Server professional in your role asks you for advice, what do you say is the number one most important thing to monitor? Have you ever stayed awake for a week solving a problem that could have been avoided with proper monitoring? Did you implement a monitoring solution that saved your company?
Be creative! There are so many topics to choose from, and you can of course write about anything you want as long as it is related to SQL Server and monitoring. It doesn’t matter if you’re new to SQL Server or have decades of experience, whether your blog post is short or long, an introduction or a deep dive, if you include scripts or screen shots… Just join the party and have fun while you share your knowledge. (And if you joined the #SQLNewBlogger challenge in April, this is a great way to keep blogging!)
What is this T-SQL Tuesday thing?
T-SQL Tuesday is a monthly blog party started by Adam Machanic (@AdamMachanic) back in 2009. The concept is simple: The host of the month picks a SQL-related topic and writes an invitation on the first Tuesday of the month. All bloggers are welcome to join in, they have one week to write a blog post about the chosen topic and publish their post on the second Tuesday of the month. The host then writes a summary of all the participating blog posts.
Inserts, updates and deletes on large tables can be very slow and expensive, cause locking and blocking, and even fill up the transaction log. One of the main benefits of table partitioning is that you can speed up loading and archiving of data by using partition switching.
Partition switching moves entire partitions between tables almost instantly. It is extremely fast because it is a metadata-only operation that updates the location of the data, no data is physically moved. New data can be loaded to separate tables and then switched in, old data can be switched out to separate tables and then archived or purged. All data preparation and manipulation can be done in separate tables without affecting the partitioned table.
T-SQL Tuesday #65 is hosted by Mike Donnelly (@SQLMD). There is no specific topic to write about this month, Mike simply wants us to learn something new and then write a blog post to teach it to others. I want to share something that I only recently learned, something I wish I had known about years ago, something that became part of my toolbox as soon as I discovered it: the Numbers Table. It is so simple and solves so many problems that everyone should know about it :)
A Numbers Table (perhaps most known as a Tally Table, sometimes called an Auxiliary Table of Numbers and even referred to as the Swiss Army Knife of SQL Server) is a one-column helper table that contains the numbers 1, 2, 3, 4, 5 and so on all the way up to the-highest-number-you-could-possibly-need.
It can be used to replace slower loops and row-by-row operations with faster set-based operations, generate dates, split strings, find gaps in data sets, expand data sets, insert test data and probably hundreds of other things. There are so many great and detailed articles already published about this topic, so I will stick to the T-SQL Tuesday topic of “Teach Something New” and share the two most recent things I learned: different ways to create a numbers table, and different ways to quickly insert test data by using a numbers table.
There are many benefits of partitioning large tables. You can speed up loading and archiving of data, you can perform maintenance operations on individual partitions instead of the whole table, and you may be able to improve query performance. However, implementing table partitioning is not a trivial task and you need a good understanding of how it works to implement and use it correctly.
Being a business intelligence and data warehouse developer, not a DBA, it took me a while to understand table partitioning. I had to read a lot, get plenty of hands-on experience and make some mistakes along the way. (The illustration to the left is my Table Partitioning Cheat Sheet.) One of my favorite ways to learn something is to figure out how to explain it to others, so I recently did a webinar about table partitioning. (Update in 2020: The webinar has now been archived. Please contact Pragmatic Works if you would like to watch it, as they are the owners and publishers.) I wanted to follow that up with focused blog posts that included answers to questions I received during the webinar. This post covers the basics of partitioned tables, partition columns, partition functions and partition schemes.
In 2015, I decided that I will not try to take a Microsoft Online Exam again (until they fix or change their exam software). In 2021, I still stand by my decision. Here’s why.
I heard about Microsoft Online Exams in 2015 and found out they were available in Norway. I immediately wanted to try it! Taking an exam online would allow me to schedule it during the weekend so I didn’t have to take time off work. It also meant I saved 2-3 hours of travel time on a day.
Reviews of the exam process were both good and bad. Some had technical difficulties and had to reschedule, some had technical difficulties and failed their exam, while others had no problems at all and were very happy. I decided to give it a go.