Data On Wheels – Merging Two Great Minds

I am very excited to announce that my daughter, Kristyna Hughes, will be joining the Data On Wheels team. Our fates and paths to this point seem parallel though not sequential. Both of us went to college and studied cultural anthropology. This turned out to be a great foundation for working with customers in our respective technology fields. If you have followed me for a while, you know that my career has focused on data and analytics for the past 20 years. During that time I worked for XATA Corporation which was a transportation management company. We were a cutting edge, SaaS technology company that specialized in electronic driver logs. I created my blog while working there, thus Data On Wheels. I have since went back into consulting but kept the name. Fast forward to today, Kristyna started her career in logistics which led her to her current employer, GlobalTranz. While there, she created a couple of reports which were well received and got noticed by the business intelligence team. She has since helped them grow their Power BI practice and deliver awesome reports. I am very proud of the work she has done and look forward to her joining Data On Wheels as a co-contributor. Now, let’s hear from her.

Kristyna and Steve at Kristyna’s Graduation from Biola University

Kristyna here, I am thrilled to be joining my dad in the BI world and in writing up the lessons learned along the way. Like he said, I have been deep in the world of Power BI from administration to building the executive morning report. I believe people in the business are our best assets as data analysts, so I appreciate learning my logistics knowledge through experience and continue to learn from my colleagues in the field. Our mutual background with cultural anthropology allows us to get to the heart of problems efficiently through discussions with stakeholders to bring them valuable insights from their data. While not all companies literally have their data on wheels like logistics, data can turn the wheels of profitable change for any company.

Buffalo Trace Distillery, Frankfort KY

One other thing my dad and I enjoy together is good whiskey from rye to bourbon to scotch. Look for some of our favorites in the Fun Info page as we sip and savor some soul-warming whiskeys.

Starting and Stopping SQL Server with PowerShell

Have you ever had this issue? You are trying to explore new features of SQL Server or you want to install the latest version for some testing, but SQL Server is a resource hog and kills your the performance of your PC. I have been evaluating SQL Server 2019 including both flavors of Analysis Services. That means I am installing the Developer edition of SQL Server on my laptop (a Yoga 900) running Windows 10.

See the source image

I have dealt with this issue in the past by opening the services window and setting the Startup Type to Manual and then turning it on when I wanted to work with them. In my current use case, I did not want to have to manage that with a lot of clicks. So, my thought was this should be easy with PowerShell. Of course, nothing is easy when it is not something you do everyday. So I started digging.

I found it is definitely possible but you need to know the steps that matter including making sure that PowerShell will execute the scripts on your PC. So here are the steps and code.

Empowering Your PC to Run Your PowerShell

The first issue is that PowerShell will not execute on your PC until you allow it. You will need to open PowerShell as an Administrator. You can leave the window open once we are done here. The following code will allow code created on your PC to be executed on your PC.

Set-ExecutionPolicy RemoteSigned

Shutting Down SQL Server with PowerShell

I chose to create two PowerShell files that will allow me to execute these steps for all the services I am managing in. In my examples, I have three instances of SQL Server – Data Engine Services, Analysis Services – Multidimensional Model, and Analysis Services – Tabular Mode.

The code required to startup SQL Server is noted below.

Set-Service 'MSSQL$DOWSQL2019' -StartupType Disabled
Stop-Service -Name 'MSSQL$DOWSQL2019' -Force

Let’s break it down. The first line will actually set the StartupType to Disabled. This will prevent it from restarting via a reboot. You will need to be intentional about restarting the service. The second line is the command to stop the service. The “Force” flag will shut down dependant services like SQL Agent if you have that running.

You will need to know the service name. SQL Server Data Engine Services are typically named “MSSQL$” followed by the instance name. If you are using Analysis Services, the naming using “MSOLAP$” as the prefix.

You can run these scripts directly in your PowerShell window. I did this while testing them. I wrote them in Visual Studio Code, but had issues executing them. You may be fine, I wanted to let you know my experience. Once I had my three instances scripted, I saved them in a file called SQLServerOff.ps1.

Turning Them on with PowerShell

The process and the code is similar. You first need to enable the services then start them up.

Set-Service 'MSSQL$DOWSQL2019' -StartupType Manual
Start-Service -Name 'MSSQL$DOWSQL2019' 

I chose the Startup Type “Manual” so I still need to turn them on. It is possible to set that to Automatic if you want it to start up on a reboot for instance. I then saved these in a file called SQLServerOn.ps1.

Running the PS1 Files

There are a couple of options to execute your file. While I was testing and I used the PowerShell window I had open. In order to execute your file replace “YOUR PATH” with the full path to the script.

PS C:\WINDOWS\system32> & "YOUR PATH\SQLServicesOn.ps1"

While a good pattern, I was still looking for the “one-click” solution. Some more digging resulted in me finding the way to execute PowerShell file from a shortcut. I did this by going to the desktop, right clicking an empty space and creating a new, blank short cut. Here is the command you can use for the shortcut:

powershell.exe -noexit -File “YOUR PATH\SQLServicesOn.ps1”

Add the snippet above with the proper path for your code to the shortcut location. Once you have named your shortcut and saved it, you need to open the properties on the shortcut. Go to the advanced settings and run this as Administrator (this will not work without that setting turned on). You can now add the shortcut to a place convenient for you such as your task bar.

While I focused on SQL Server services, this will work other services as well. Enjoy!

It’s Been a While …

Last Post – SQL Saturday MN 2018

When last I wrote, it was after I spoke at SQL Saturday in Minnesota. I co-presented a pre-con with Josh Owen. Prior to that was a few weeks of blogs on Azure and Azure Data Week. As Pragmatic Works moved to an Azure focused company, I, too, was learning much about Azure and what it takes to move workloads to the cloud.sqlsat796_header                                                  Post Linkden

The New Role – Director of Consulting

In October 2018, I was promoted to Director of Consulting at Pragmatic Works. Over the past 6 months or so, I have had the chance to lead and grow the consulting team at Pragmatic Works. This has been a great growth opportunity for me and my career. I am not perfect by any stretch, but the team is awesome! Pragmatic Works Consulting continues to grow and do awesome work. email-signature

The Beat Goes On …

As I look forward to the next few months, there are some great things happening at home. I get to see my youngest daughter graduate from high school, my second child and oldest son will be graduating from Liberty University next month, and then he will be getting married to his college sweetheart. It is will be a busy summer and into the fall at my house. But it will be great as well. Love watching the family grow up and move us all to the next stage all of life.

As Pragmatic Works continues to grow this year, I look forward to seeing new team members join our team and existing team members expand skills and capabilities. In the last two years, Pragmatic Works has transformed itself from a SQL Server based solution company focused on Microsoft Business Intelligence to an Azure solutions company specializing in bring customers to Azure and Azure Data Services. It is an exciting time in technology today and we are a part of that excitement.

Here’s to a great year!

 

Cosmos DB for the Data Professional

Cosmos DB LogoCosmos DB is one of the fastest growing Azure services in 2018. As its popularity grows, data professionals are faced with a changing reality in the world of data. Data is no longer contained in relational databases as general rule. We saw the start of this with Hadoop data storage, but no one ever referred to Hadoop as a database. Sure Hive and other Hadoop based technologies made the data look like a database, but we (data professionals) were able to keep our distance. What’s changed?

The Cloud, Data, and Databases

As cloud reaches more and more businesses, traditional data stores are being reconsidered. We now have data stored in Azure – Azure Data Lake, Azure Storage, Azure Database Services (SQL, PostgreSQL, MySQL), Azure Data Warehouse, and now Cosmos DB. Cosmos DB is the globalized version of Azure Document DB (more about that later). If we are to grow our skillset and careers to a cloud data professional, we need to know more about other ways the data is stored and used. I want to summarize some things that we need to be aware of about Cosmos DB. If your business uses it or plans to and you are a data pro, you will need to know this.

Introducing Cosmos DB

Azure Cosmos DB is Microsoft’s globally distributed, multi-model database.

Cosmos DB Overview 201804

Source: https://docs.microsoft.com/en-us/azure/cosmos-db/introduction 

I will break down key components of Cosmos DB with a data professional in mind. There are a lot of aspects of Cosmos DB that make it very cool, but you will want to understand this when you get the call to fix the database.

Multi-model Database Service

Currently Cosmos DB supports four database models. This is like having for different database servers in one. I liken it to having SQL Server Database Engine and SQL Server Analysis Services using the same underlying engine and it only “looks different.” Cosmos DB refers to these as APIs. The API is chosen when the database is created. This optimizes the portal and database for use with that API. Other APIs can be used to query the data, but it is not optimal. Here are the four models supported and the APIs that support them.

Cosmos DB models

  • Key Value Pair: This is exactly as it sounds. The API is implemented with the Azure Table Storage APIs.
  • Wide Column or Column Family: This stores data similar to relational, but there is no row consistency (each row can look different). Cosmos DB uses the Cassandra API to support this model. (For more information on Cassandra click here.)
  • Documents: This model is based on JSON document storage. Cosmos DB currently supports two APIs for this model: SQL which is the Document DB API and Mongo DB. These are the most common models used in Cosmos DB today. Document DB is the “parent” to Cosmos DB which was rebranded.
  • Graph: Graph databases are used to map relationships in data and were made popular with Facebook for instance. Microsoft uses the open source Gremlin API to support the Graph Database Model.

None of these databases are traditional row/column stores. They are all variations of NoSQL databases.

Turnkey Global Distribution

This is a key attribute for Cosmos DB. Cosmos DB can be easily distributed around the world. Click the data center you want to replicate to and Cosmos DB takes care of the rest. Cosmos DB uses a single write node and multiple read nodes. However, because Cosmos DB was built with global distribution in mind, you can easily and safely move the write node as well. This allows you to “chase the sun” and keep write operations happening “locally”.

Data Consistency

Data consistency is a primary concern of any data professional. The following tables compare Cosmos DB Consistency Levels with SQL Server Isolation Levels. These are not a one for one match, but demonstrate the different concerns between the systems.

 

Cosmos DB

SQL Server

Consistency Level Guarantees Isolation Level Dirty Read Non- repeatable Read Phantom
Strong Reads are guaranteed to return the most recent version of an item. Serializable No No No
Bounded Staleness Consistent Prefix or read order. Reads lag behind writes by prefixes (K versions) or time (t) interval. Snapshot No No No
Session Consistent Prefix. Monotonic reads, monotonic writes, read-your-writes, write-follows-reads. Repeatable Read No No Yes
Consistent Prefix Updates returned are some prefix of all the updates, with no gaps. Reads are not read out of order. Read Committed No Yes Yes
Eventual Out of order reads. Read Uncommitted Yes Yes Yes

As you can see, there are some similarities. These options are important to understand. In the Cosmos DB, the more consistent you need the data, the higher the latency in the distributed data. As a result, most Cosmos DB solutions usually start with Session Consistency as this gives a good, consistent user experience while reducing latency in the read replicas.

Throughput

I am not going to dig into this much. But you need to understand that Request Units (RU) are used to guarantee throughput in Cosmos DB. As a baseline, Microsoft recommends thinking that a 1 KB JSON file will require 1 RU. The capacity is reserved for each second. You will pay for what you reserve, not what you use. If you exceed capacity in a second your request will be throttled. RUs are provisioned by region and can vary by region as a result. But they are not shared between regions. This will require you to understand usage patterns in each region you have a replica.

Scaling and Partitions

Within Cosmos DB, partitions are used to distribute your data for optimal read and write operations. It is recommended to create a granular key with highly distinct values. The partitions are managed for you. Cosmos DB will split or merge partitions to keep the data properly distributed. Keep in mind your key needs to support distributed writes and distributed reads.

Indexing

By default, everything is indexed. It is possible to use index policies to influence the index operations. Index policies are modified for storage, write performance, and read or query performance. You need to understand your data very well to make these adjustments. You can include or exclude documents or paths, configure the index type, and configure the index update mode.  You do not have the same level of flexibility in indexes found in traditional relational database solutions.

Security

Cosmos DB is an Azure data storage solution which means that the data at rest is encrypted by default and data is encrypted in transit. If you need RBAC, Azure Active Directory (AAD) is supported in Cosmos DB.

SLAs

I think that the SLAs Microsoft provides with Cosmos DB are a key differentiator for them. Here is the short summary of guarantees Microsoft provides:

  • Latency: 99.99% of P99 Latency Attainment (based on hours over the guarantee)
    • Reads under 10 ms
    • Writes under 15 ms
  • Availability
    • All up – 99.99% by month
    • Read – 99.999% by month
  • Throughput – 99.99% based on reserved RUs (number of failures to meet reserved amount)
  • Consistency – 99.99% based on setting

These are financially backed SLAs from Microsoft. Imagine you providing these SLAs for your databases. This is very impressive.

Wrap Up

For more information, check out Microsoft’s online documentation on Cosmos DB.

I presented this material at the April 2018 PASS MN User Group Meeting. The presentation can be found here.

Five Years, A Quiet Quarter, A Look Ahead to 2016

Five Years of Blogging

From DataOnWheels Logo Original to data-on-wheels-1

My blogging story started on December 7, 2010. I have now had a blog for over 5 years. I want to thank all of you who have read my blog and interacted with me through it. You have seen me change the theme once and do a number of series. Here are some highlights from the past five years:

Top 5 Posts All Time

  1. Adding Top 10 Charts to Power View Which Honor Filters
  2. Simple batch script to generate XMLA and deploy SSAS DB
  3. T-SQL Window Functions – Part 1- The OVER() Clause
  4. Exploring Excel 2013 for BI Tip #14- Sparklines and Pivot Tables
  5. O, There’s the Data- Using OData in SSIS

Top Series All Time

The Excel BI Tips series has changed it name a couple of times. However, this tip series still rings true even today even as Microsoft invests in other tools. Look for some more Power BI content this year, but this series will continue to have updates. Also, look to see some Excel 2016 topics added to the list as that release becomes available. Here are the top ten tips from the series:

Tributes

A tribute is an expression of gratitude or praise. A couple of years ago, I started a series about individuals who have impacted my career. I do this as a tribute to my father-in-law, Ed Jankowski who passed away in December 2009. Check out my original post about him and his impact on me being in software development today.

Some Stats

I want to thank everyone again for taking time to check out my blog. Here are some stats that I thought were cool and decided to brag about here:

  • 2011 daily average: 9 – 2015 daily average: 162
  • 156 posts
  • 135,000 views
  • Best ever views in a day: 584

Thanks again for checking out my “help” library. As I noted in one of my posts, I blog to not forget and to pass along what I have learned. The key for me is that I do it when I can about topics that interest me.

A Quiet Quarter

The last statement holds true here. I have had a very quiet end of the year. I had blogs which followed up sessions, a practice that I intend to continue, and one BI Tip. November and December were quiet as my job and family took precedence as Pragmatic Works closed out the year strong and we had holiday activities at home including getting my two kids in college home. Well, the dust has settled so I am getting a few more posts published now. Look for the Minnesota SQL Server User Group and Minnesota BI User Group follow up posts this week.

Looking ahead to 2016

After a busy year last year, I am looking forward to having some new opportunities to write about Azure, SQL Server 2016, and other technologies I have not even seen yet. Are you excited for what is coming? Let’s have a great year working with data and analytics.