Skip to content

posts🔗

  • 2 min read

SQL 2016 - Brief Overview on some new features

These are notes taken from the Houston SQL Pass User group from July. This presentation was given by John Cook, (Data Platform Solution Architect Microsoft) who did a great job with limited time on providing some great details on the new functionality with SQL 2016. To follow him, take a look at sqlblog.com where he posts or follow him on twitter. Thanks to him for the overview.

JohnPaulCook (@JohnPaulCook) on Twitter

Microsoft Data Platform specialist and Registered Nurse

John Paul Cook Sql Blog (No Link)

SQL Blog - Blogs about SQL Server, T-SQL, CLR, Service Broker, Integration Services, Reporting, Analysis Services, Business Intelligence, XML, SQL Scripts, best practices, database development, database administration, and programming

cloud first

Most of the new features included in 2016 have been tested in the cloud. They are implementing cloud-first with features. Therefore, most on prem features have been throughly tested in the new world, sometimes even up to months.

dynamic data masking

  1. If you know a specific value you could get the results back in a specific query by putting in where clause. This would retrieve the row masked, but you still knew the results due to this "brute force attack". Dealing with security means you'd prevent adhoc queries anyway. You want to ensure that that scenario doesn't happen.
  2. This would be categorized more as obfuscation. This is not the same as encryption.
  3. New grant permission for UNMASK

encryption

  1. Encryption at column level
  2. Deterministic: need this for being able to search/join among different tables
  3. Random: Good for increasing the difficulty of breaking the encryption.
  4. Encryption increases to the size of the data, taking up more size
  5. Has some limitations on Collation. The example was COLLATE Latin_General_BIN2
  6. This is offloaded to the client which converts the value with ado.net 4.6.1. This means a certain compatibility would need to be maintained to use this with legacy applications. This is done on the client. Unless you give the key to the dba, they can't see the information.
  7. Additional connection string value is required, per SSMS has to convert and interpret this value.

column encryption setting=enabled

encryption

stretch

Stretch is more "stretch table" to Azure. This means you'd bind a function to your sql server with the logic to archive. This would let you store very cold data without having to maintain locally. Another positive to this is that each table is contained as it's own "database" in Azure. They maintain the backups for you, so your backup windows are not impacted. You only have to backup the local data.

Temporal Database

Microsoft keeps track of all your changes in a table. You have to enable on each table individually. This functionality stores the history of all changes to ensure this history is tracked. This used to require a lot of coding.

  • 1 min read

Install ReadyRoll via Command Line

command line install options

ReadyRoll has some great features, including the ability to use without cost on a build server. If you want to ease setup on multiple build servers you could create a simple command line install step against the EXE.

future changes

ReadyRoll was recently acquired by Redgate, so the installer options may change in the future to be more inline with the standard Redgate installer. For now, this is a way to automate an install/updates.

autoupdating via Ketarin

I personally use Ketarin to help me manage automatically updating apps like SQL Server Management Studio. I've uploaded a public entry for ReadyRoll to automate download and install of the latest ReadyRoll version when available. For more detail on how to use Ketarin see my earlier post on Automating SSMS Upgrades

command line options

  1. Find the path of the installer
  2. Run ReadyRoll.msi /exenoui /qn for a silent install.

2016-08-16_11-05-53 - For automated setup and install use the following code with Ketarin

  • 1 min read

Does sp_rename on a column preserve the ms_description?

Did some checking as couldn't find help in the MSDN documentation. My test on SQL 2016 shows that since the column_id isn't changing, the existing mapping of the description for the column is preserved.

I know it's probably pretty obvious, but I had someone ask me, so figured proving the mapping for ms_description is maintained would be a good thing to walk through. Score another point for Microsoft, for design practices

  • 3 min read

Regex With SQL Server - SQLSharp

In the context of my developer machine, I had log files I wanted to parse through. I setup a log library to output the results on a test server to a sql table instead of text files. However, this meant that my "log viewers" that handled regex parsing weren't in the picture at this point. I wanted to parse out some columns from a section of message text, and thought about CLR as a possible tool to help this.Ideally, I wanted to feed the results for analysis easily into power bi, and avoid the need to create code to import and parse out fields. Since I knew the regex values I wanted, I thought this would be a good chance to try out some CLR functionality for the first time with SQL Server 2016 + CLR Regex parsing. I ran across SQL# and installed. The install was very simple, just downloaded a SQL script and ran it, adding a final "reconfigure" statement to ensure everything was good to go.

SQLSharp (SQL#)

I used the free version which provided great regex parsing functionality.

SQLSharp (SQL#)

Simple to use

Constructing the following query parsed the results easily, with no extract coding/import process required.

Simple to use

Performance

This was just an isolated 1000 record test, so nothing exhaustive. I compared it to a table function that parsing strings (could probably be optimized more). For the purpose of running a simple log parsing search on 1000 rows it did pretty good! For better work on parsing of strings, there are detailed postings out there by Aaron Bertrand, Jeff Moden, and others. My scope was specifically focused on the benefit for a dba/developer doing adhoc-type work with Regex parsing, not splitting delimited strings. The focus of most of the articles I found was more on parsing delimited string. However, I'm linking to them so if you are researching, you can be pointed towards so much more in-depth research on a related topic.

Performance

Thoughts

The scope of my review is not covering the proper security setup for CLR with production, CLR performance at high scale, or anything that detailed. This was primarily focused on a first look at it. As much as I love creative SQL solutions, there are certain things that fit better in code, not SQL. (heresy?) I believe Regex/advanced string parsing can often be better handled in the application, powershell, or other code with access to regex libraries. In the case of string parsing for complex patterns that are difficult to match with LIKE pattern matching, this might be a good resource to help someone write a few SQL statements to parse out some log files, adhoc ETL text manipulation, or other text querying on their machine without having to add additional work on importing and setup.

  • 2 min read

Improvements with SSMS 2016

{{< admonition type="info" title="Updated: 2019-01-24" >}} Improved options to install through Chocolatey package. Use command choco upgrade sql-server-management-studio and you'll simplify the installation process greatly. Also for servers, consider Azure Data Studio as much smaller download and might provide what you need to do basic management without a length install and download. {{< /admonition >}}

{{< admonition type="info" title="Updated: 2018-03-30" >}} Use SSMS 2017 when possible now. It has continued to be improved. Current download page for SSMS 2017 If you want a shortcut to install, check out this post: Update SSMS With PS1 {{< /admonition >}}

The staple of every SQL Server developer's world, SSMS has been impossible to usurp for the majority of us. However, it's also been behind the development cycle of visual studio, and didn't have continual updates. That changed recently, as I've previously posted on. SSMS (Sql Server Management Studio) now being decoupled from the SQL Server database releases.I've been enjoying some of the improvements, especially as relatest to the built in execution plan viewer. I use SQL Sentry Pro, but for a quick review, any improvements to the default viewer is a welcome addition!

Live Statistics View

You can see the statistics update as it's running.

Live Statistics View

Side by Side Comparison of Plans

This is something that is fantastic. A good step in the right direction for helping compare plans quickly. This is a feature I'd love to see added to other tools like SQL Sentry Plan Explorer. When plans don't vary significantly in their structure, this type of view is great for quickly viewing variances.

Side by Side Comparison of Plans

Usability

You can actually drag your mouse to pan a plan... enough said. This should have been there a long time ago.

comparison of properties

The properties pane also has an overhaul with some really useful comparison information, helping you identify what is now different.

comparison of properties

overall

Really liking the improvements I've seen. There are a lot of things about SSMS I'd like to see improved, and with a regular release cycle the future for SSMS looks promising! I'll be really happy once the Visual Studio dark theme has made it's way over... I swear everything just runs faster with a dark theme ;-)

  • 7 min read

Glasswire: (Giveaway Included) Networking Monitoring even a caveman could

Giveaway details at the bottom for those interestedDealing with development & sql servers, I like to know what type of network traffic is happening on my machine. What is the overhead of monitoring on network bandwidth, what is communicating across servers or even externally?

What is phoning home?

You can create perfmon counters, but realistically sometimes I just want a easy quick overview of network traffic with minimal overhead. I have been using a utility I came across called Glasswire, and found this tremendously helpful. I'd highly recommend taking a look at this. I've installed on a couple of the sandbox sql servers I have worked with and found it really great for evaluating the network traffic occurring from the monitoring services running on them. Disclaimer: I have been provided with a free license to allow me to review the product in detail. This doesn't impact my assessment of the tool. I just love great tools and try to help other developers find them.

Network traffic monitoring

Sounds boring, but Glasswire changed my perspective on this. I always want to know what's "phoning home" and using up my bandwidth, but until I tried this app, I never have found something that tracked and reported on it in a clean user friendly way. Process Hacker is my preferred task manager, and it can provide some metrics when running, but not a long term history, and not in a user friendly format for analysis.

Comparing timeline

I found this great as a simple way to compare two SQL server monitor tools traffic against each other. I wanted to know the network traffic load they were generating, and this was a great way to get some quick transparency on the network impact. In this example, the test wasn't perfect as they had slightly different detail level tracking configuration, so just take this as an example.

Comparing timeline

Comparing Usage Metrics

Comparing Usage Metrics

See activity

I really like the transparency of seeing what network activity is occurring on my system. I've found myself evaluating why apps would need to connect rather than just allowing everything through.

See activity

Easiest firewall tweaking I've seen

Easiest firewall tweaking I

Would I recommend

Completely! I've found this to give me a transparency into the network ativity in a great way. PROS

  • Beautifully thought out design
  • Creates a great awareness of network activity, allowing you to be more proactive on what you allow to send data out CONS
  • Some "power user" functionality would be nice, such as being able to customize or get details from the desktop widget, add special alerting on specific apps whenever they request network access, etc. These are small things though. I think the overall design is very elegant and well thought out
  • Price. For normal users this is really expensive for the pro version. However, if you are just interested in some basic monitoring without a long history and desktop toast alerts, you can get the free version and still get great value from it.

Interview with Developer

I thought the app was unique enough in design and function that it would be great to get a short interview with the owner to share a bit about his development approach, goals, and overall story.

Tell me a little bit about yourself and your company.

Before GlassWire we made a webcam virtual driver software that allowed you to use your webcam with multiple applications simultaneously. That company was acquired a couple years ago. Making a sophisticated driver gave us experience with making drivers, so we used that experience to make our network monitoring driver. Since launch we have been surprised by how many people use GlassWire to keep their data usage low and save money and resources. For example some of our customers have boats and they are on very strict data plans out on the ocean, so they use GlassWire to see what's wasting their data and also block apps they don't want to use while out at sea. After we launched we were surprised by how feedback was so positive right away. I was worried that nobody would want the software at all, but people seem to like it and we have now had close to 4 million downloads.

What made you want to build a network application like Glasswire?

I always felt that I couldn't see what was happening with my PC's network usage so I built GlassWire for myself so I could instantly see what was happening. I also had some relatives who lived in a remote area and could only use Satellite Internet access. Satellite only gives you a little data, then throttles you so it's very useful to see what apps are wasting your data. GlassWire also has a built in bandwidth overage monitor to help with that.

Why was QT chosen as your graphs/design?

I tested a lot of different tools to see what was happening on the network but I found them difficult to use, plus I couldn't find any that could go back in time and show me past network usage so our team worked together to build GlassWire. QT allows us to build a beautiful UI and make changes easier over time.

What's been some of your hardest decisions in designing the application?

After launching we found that Bittorrent users were causing GlassWire to use too much memory/resources on their PC because Bittorrent communicates with so many hosts simultaneously in such a short time period. We had to redesign GlassWire to use less resources for these users, and I blogged about it here https://blog.glasswire.com/2016/03/29/how-glasswire-1-2-saves-your-memory-and-resources/. There were a lot of different hard decisions we had to make, like adding "loading..." in some places in the UI to take the load off of GlassWire for users who had too many hosts. I was worried users may find these short delays annoying bit fortunately nobody seemed to be upset about it and we are continuing to grow, and GlassWire now uses significantly less resources for everyone.

Do you have a design philosophy that helps balance more features with simplicity?

I try to look at other popular applications and study what makes them successful. Currently we are working on our Android application and the work on Android has helped me come up with some ideas on how to improve yet simplify our GlassWire desktop software.

What's your thoughts on implementing user feedback vs bringing design choices that no one even thought about?

We love to get user feedback in the forum and on Twitter, etc... For me it's easy to come up with feature/design choices because I want GlassWire for myself. I think I'm a pretty normal person and usually the things that I want others want too.

Any future projects you want to accomplish (not a roadmap, but general things you'd love to tackle)

We're excited about bringing GlassWire to mobile and Mac, but it's not easy so we're trying to make sure our Mac/mobile versions have the same high quality as our desktop software. We don't want to cut corners, so I hope our fans will be patient!

Can you tell me a few of your design philosophy decisions that drove some of those simple UI differences that are a bit uncommon? (like the graph refreshing smoothly without 1 second gaps)

Since we built the software for ourselves we tried to create a simple layout that we preferred, kind of like a web browser. We tried some 1 second intervals but it made the graph look jerky and it hurt the eyes so the team spent a lot of time making the smooth graph we ended up with. One of the main things I wanted myself was to be able to see what caused a spike on the graph and with GlassWire you can just click the spike, then see what hosts/apps were involved in the spike.

Giveaway Details

Glasswire was kind enough to provide me with a license for their Elite version (currently $199) which is a onetime fee. You can get the hookup! To get the hookup on this... I'm making it technical since this is not for sweepstake surfers but my techy SQL friends.... Reminder: This is a Windows application, not Mac. Drawing will occur end of July and winner name will be posted here and notified in Twitter direct message

Giveaway Result 2016-08-11

Congrats to Tim! I'll be sending you the license details. Hope you enjoy and thanks to Glasswire for sponsoring this. Give the free version a shot, even if you aren't planning on going pro. It's a great tool for anyone to increase transparency on what's really happening with their system.

  • 2 min read

SQL Compare 12: Initial Look

I know there have been a few other folks going into more detail on SQL Compare 12 (beta), but I thought I'd share just a few looks at the new design. Looks pretty slick, and I like where the design is going. Just a quick look, as I'm sure there will be more to cover when it's finally released. Until then....

Some GUI Improvements

Very clean. Not too drastic of a change to mess with current workflows. Still, nice to see some clean design like this.

Some GUI Improvements

Options dialogue

Same options, just slightly different navigation/ui design to get there.

Options dialogue

Comparing databases

Comparing running... saving me a massive amount of work in not creating scripts by hand :-)

Comparing databases

Comparison matches

Comparison matches

Final thoughts

Again, this was just a quick peek at what I've looked at. Overall, I like the design choices. UI Design improvements are nice. I would say the next stage would be seeing this better design roll-out into better report styling & formats, making it consistent. Additionally, while the UI improvements are nice, the code diff viewer at the bottom looks pretty similar, and I'd love the ability to have this improved a little. Perhaps even the option to use "external diff viewer" and pipe it into my preferred diff viewer (Araxis Merge for me). Officially, I'm part of the Friend's of Red Gate community now, so I'll probably be posting a bit more on their tools as I've got the chance to work with more of them now. I've always been a big fan of their products, and now I get a chance to be part of their community and help with testing, feedback, and advocating their great stuff when it would help folks out. Final thought... why can't all great tools have a full Solarized Dark theme? ;-)

  • 4 min read

SSMS Tools Pack - A Handy Tool for generating CRUD

So I've had this tool around for a while, but never found much usage out of it to be honest. I didn't end up writing a review as I had other tools that did text replacements, and history/session saving. I've always considered this tools implementation of SQL History/Tabs saver the best period, even over Red Gate Tab History, SSMSBoost, etc. However, recommending the tool solely based on it's fantastic history saver wasn't really something I was going to do.However, having to generate some CRUD procs lately I found a new reason to appreciate this tool. I dusted it off, updated to the latest, and looked for the CRUD option I remember it having. Sure enough I ended up saving myself a lot of time and generated procs that were all standard with what I wanted to create. This gets my hearty approval to avoid tedious grunt work on creating procs. Since the tool throws in a great history/session saver to avoid losing work, it's even more of a recommended tool!

CRUD Generator

Disclaimer: I was provided with a license to give me time to fully review. This doesn't impact my assessment of the tool. I don't recommend tools without actually using them and seeing if they'd actually benefit me in my work

SSMS Tools Pack First, I know there are some great stored procs/scripts that people have written to do this. I appreciated those, but found I was going to spend a lot of time trying to customize to get the error handling and other scripted pieces in, so I revisted SMSS Tool Pack.

create CRUD from context menu for entire database

create CRUD from context menu for entire database

general options

general options

replacement text options

This has some potential to be very helpful! You could generate the user, date and time, and more to generate some comment headers and more.

replacement text options

replacement text example

replacement text example

select template

select template

insert template

I prefer a begin try and error catch output syntax. I was able to encapsulate the CRUD generator statements with the syntax I preferred, and no longer had to manually manipulate each file to get it where i wanted it. This was a lot of time saved!

insert template

update template

update template

warning - case sensitive parameters

Make sure to keep the case correct on the variables. This is case sensitive.

warning - case sensitive parameters

saving you a lot of work...

Once you've clicked the generate CRUD the magic happens. The results were a large list of prebuilt stored procedures for doing all the CRUD operations needed, with no extra work required. Win!

saving you a lot of work...

Other

Searching the history of previously executed queries, versions of files edited, and sessions of tabs is all excellently handled in the SQL History Search extension. My favorite part is the useful status messages such as

Other

History of Execution

History of Execution

updates

Some recent updates were released with version 4 that might be beneficial for your workflow such as support for SSMS 2016, renaming of tabs, better insert generator, and some other things. Check out the website for more details.

its not CRUD.... it's quite nice!

While it doesn't really do full formatting or other things, the organized query execution, CRUD generator, and some other features make it a nice tool if you have the budget to purchase. It's a especially a good tool for those who want to generate CRUD procs easily.

  • 3 min read

OmniCompare: A free tool to compare SQL Instances

comparing instances

When working with a variety of instances, you can often deal with variances in configuration that might impact the performance. Without digging into each instance you wouldn't know immediately that this had happened. There are fantastic tools, like Brent Ozar's SP_Blitz, but this doesn't focus on every single configuration value and cross instance comparison. To supplement great material like that a tool like OmniCompare is great.

side by side comparison

Initial View of Comparison I am definitely adding to my list of great sql tools. OmniCompare provides a side by side comparison of various configuration and system related values in a side by side format so you can easily see variances in basic configuration.

release post

I read the post about this by Phil Grayson here . He's got some great examples of what you could use it for, such as auditing, performancing tuning, synchronizing server settings, and more. The link for OmiCompare to try it out

synchronization

Apparently, it's got the ability to synchronize some of the configuration settings so you could use a template to help setup a new configuration of sql server quickly. I haven't had a chance to try that piece out, but I will be exploring it for sure! It might be something that is in the works, as I couldn't find the options to synchronize currently in the tool. Sounds a lot more elegant than my homebrewed scripts that a nest of code needing some cleaning. Well done Aireforge team!

Easy Visual Summary of differences

Quite a few ways to filter and sort down to the information you care about.

Easy Visual Summary of differences

Listing of the servers you want to compare

Add more or import a list of them to easily do a comparison on configuration in the environments.

Listing of the servers you want to compare

Configuring A Server

Simple and quick to add a server, as well as tag them so you can easily compare based on whatever grouping you see fit. For example, you could compare all common versions, all UAT type environments, etc.

Configuring A Server

Configuration Differences

Makes it very easy to immediately just view configuration scoped differences between each

Configuration Differences

  • 6 min read

Continual Deployment of Visual Studio SqlProj

Unveil the inner workings of the esoteric build system...

As a data professional, I've never worked extensively with msbuild or other pipelines. I'd been mostly focused on just running schema comparisons and publishing. However, I've had the needed to try and deploy a database project from visual studio automatically, and this is my process through it.

There are benefits for those who don't necessarily want to run this against production, but instead for those who want to continually deploy check-ins and run tests, documentation, or other tasks against the deployed schema. This was my personal goal.

There are other solutions that plug into this that can make the process easier, such as Red Gate's DLM Automation with SQL Continuous Integration. For this case, since migrating to a new project format wasn't possible until later, I worked with the native sql project from the SQL Server Data Tools in Visual Studio 2015.

Terminology

  • Build Controller: This is like Service broker. It handles the queuing and assignment of the defined builds to the appropriate agents. It doesn't do any actual building, but instead handles the delegation of the work.
  • Build Agent: This is the "wrapper" for msbuild. It does the actual work of building whatever you pass to it.

Initial Setup & Install

  1. Configure Build Service Wizard Choose the configure option of just the build service Configure Build Service Wizard

  2. Configure collection Configure collection

  3. Choose configuration options for the build services Choose configuration options for the build services

  4. Setup build service account Setup build service account

  5. Finished with configuration. After running checks and resolving any issues (I thankfully had none for this simple install of just build controller/agent; you can proceed Finished with configuration

  6. Create Build Agents Create Build Agents

Future Configuration of Build Agents

Once you've finished the install of the build service and agents, you need to configure them. After completing the install the Team Foundation Server Administration Console should open, if not open manually (start menu)

  1. Review build service configuration Review build service configuration
  2. Build Configuration Pane Build Configuration Pane
  3. Reviewing Build Agent Properties Reviewing Build Agent Properties

Setup of Build

  1. Create new build Create new build

  2. set trigger to continous integration set trigger to continous integration

  3. map your working folder to the database project map your working folder to the database project

  4. setup the process details Make sure to the map the project to the .sqlproj file to only build the sqlproj. You can adjust other items as you desire, but this should cover the core settings. setup the process details

  5. Copy Local Make sure to have the new profile copied locally as part of the build or it won't have any publish profile copied when the build is triggered, and therefore might result in hours of you wondering why it is ignoring your profile target settings (true story). You can configure to not do this of course, if you want to have a fixed file on your drive instead of copying from source control each time. Recommendation - Prepare a Publish Profile file Copy Local

  6. Setup Parameters for Build The arguments I choose to use were: (note the publishprofile parameter which wasn't in older versions of SSDT)

    /t:Build /t:Publish /p:SqlPublishProfilePath=foobar.publish.xml" /p:PublishScriptFileName=foobar.publish.sql /p:TargetDatabaseName="foobar";TargetConnectionString="Data Source=localhost;Integrated Security=True;Pooling=False" /p:PreBuildEvent= /p:PostBuildEvent= /p:VisualStudioVersion=14.0

The infuriating thing about working through this is that when it doesn't find the publish profile, it defaults to the "Deploy" settings, so in my case it kept trying to deploy the changes to a local database named the same thing as my project. Please don't waste the same amount of time grinding your teeth at MSBuild as I did, and watch out for the paths to be correct! Make sure you have it set to copy the publish profile over or it will not exist and default to the deploy settings.

When I omitted the VisualStudioVersion option, it had an error "unable to connect to target server"...... 2 days of work later I realized the version parameter was critical for it to use the right msbuild version and deploy. I didn't go into more research on this, so feel free to comment if you more details on this.

Setup Parameters for Build

Running MSBuild Manually

A few tips from my work with launching the process to build the database locally, if you experience an issues. This is work I was doing with MSBUILD 14 (visual studio 2015). I would trigger msbuild and it would deploy the database, but never exit the process to report success to powershell allowing my script to continue.

/p:UseSharedCompilation=false               --> (Roslyn compiler bypassed http://bit.ly/1WmMVzx)
/m:4                                        --> (limit to only 4 cores http://bit.ly/1VSl9uF)
/nr:false                                   --> same link above
/verbosity:quiet
/p:Configuration=Release                    --> don't need debugging for this output, so just output release
/p:DebugSymbols=false                       --> no need for extra debugging, potential improvement in timing to get rid of this
/p:DebugType=None
/t:Build;Deploy                     --> optional. Could rebuild if you want to
/p:PreBuildEvent=                   --> bypass any prebuild/post build events if you have something doing copying of files around (optional)
update after got everything working:
/p:VisualStudioVersion=14.0 --> ensure you match the version of msbuild you need

Optional: If you have further issues with 2015 and want to disable globally the node reuse settings then you can do this with a registry entry. Following the directions from TechDocs I did this.

Running MSBuild Manually

Resources

MSDN Documentation Tasks

Creating tasks is documented in

The MSBuild XML project file format cannot fully execute build operations on its own, so task logic must be implemented outside of the project file. The execution logic of a task is implemented as a .NET class that implements the ITask interface, which is defined in the Microsoft.Build.Framework namespace.

The task class also defines the input and output parameters available to the task in the project file. MSBuild Tasks