Considering Other Views

I love my Pebble 2 watch. It’s simple, the charge lasts for days, and it does what I want out of a device. However, it’s a device that isn’t made anymore, and after a fall, I have a crack in the screen. Since I like having some sort of watch in my wrist, and I like gathering some health data, I started looking for other options. I was reading a review of the Fitbit Versa the other day, and the first part of the review was a literal problem with much software design I’ve seen recently.

The first item in the review talks about notifications. Those are valuable on a device like this, and one of problems is text size. There’s a quote in there: “The text size is tiny, even when you select the item”. That’s a problem I’ve had with many devices, including the Pebble. In fact, I see this in software overall as a problem with design. It’s one that comes about because I think that far too often we don’t consider a wider range of viewpoints.

I’m getting older. A consequence of that is my eyes do not focus as well as they used to and I need to increase the fonts on screens. I can easily do this in browsers, and make SSMS fonts larger in most cases, but not all. I can increase the size of some things in Windows, but that can cause issues in others. My phone allows some font changes, but that can be limited. There are times when notifications or other text is hard to read, and this limits the usefulness of these features.

Technology has been growing and expanding as I’ve aged. However, it seems that more and more often companies are using people in their 20s and 30s to design systems that will increasingly be used by an aging population. From dashboards in vehicles to labels in applications, it seems that far too many designers don’t consider the impact of their font, icon, and graphic choices on older eyes. Even the design of our operating systems don’t seem to have deeply embedded extensive flexibility of changing text and icon sizes. Sure we can alter resolution, but that’s a very intrusive operation and may break other things. Often we just need labels enlarged.

Software and UX design are hard, and often we get caught up in our own viewpoint of how to build an application. We won’t ever completely solve that as users will always have different ways they want to work with our systems. The ability to change options, and especially deal with accessibility choices will grow as our systems move out to a wider and wider audience. What might have seemed simple and intuitive may be more complex for new users that haven’t evolved with out system.

Our database software likely isn’t going to be often seen by end users, and certainly we don’t have control over the tools they use, but we still ought to consider  how our objects will be used by others. More descriptive names, extended properties that tools can read, and even views to remap complex structures are good ways to provide an easier interface to the data for report designers and third party tools. Those items come with a cost and have to be maintained as we change schemas, but that might be a small price to pay if we can prevent lots of support tickets requesting details about what OrdrLnPrc means.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.6MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged | 1 Comment

Adding the KeyMap Extension in SQL Operations Studio

I’m playing with SQL Operations Studio a bit to get a feel for what works and what doesn’t. So far, I don’t love it over SSMS, but one of the main reasons is that many of my common keyboard shortcuts don’t work.

Then I saw this:

2018-04-11 15_42_23-Kevin Cunnane on Twitter_ _Any interest in contributing to an extension providin

You can download the extension from here, but you can’t click it to install. You need to follow this process.

First, click the File menu and select “Install Extension from VSIX Package”.

2018-04-11 15_36_30-● SQLQuery1 - SQL Operations Studio

Then find your download.

2018-04-11 15_36_42-Install from VSIX

In a few seconds, you should see the message at the top of SOS.

2018-04-11 15_36_50-● SQLQuery1 - SQL Operations Studio

Restart, and it should load.

As of when I installed it, only a few of the mappings were added. If you click the Extensions icon on the left (lowest box looking one) and then the extension, details pop in the pane. Click the Contributions link to see the commands.

2018-04-11 15_44_46-Extension_ SSMS Keymap - SQL Operations Studio

So far things seem to work, but I need to add a couple more for myself.

Of course, until SQL Prompt gets ported, not sure I want to spend a lot of time in this tool.

Posted in Blog | Tagged , | Leave a comment

Trekking Through Formatting Forest on the #SQLPrompt Treasure Map

I enjoy themes, and when I ran across the SQL Prompt Treasure Island, I had to take a few minutes and go through it. I wrote about Code Snippet Cove recently, and this post continues to move across the map.

Formatting Forest

Walking through a forest can be daunting. Living in Colorado, I’ve had the chance to hike and explore the mountains of the state. If you’re near the top and can view landmarks, it seems easy. Walking through some forests, when you can’t see a peak, you get a little worried about which direction to go without a trail. It’s serious, as people die every year in my state because they get lost in the woods.

Writing T-SQL isn’t a life or death endeavor, but it can be frustrating when we see code that’s formatted in a way that we don’t expect. I’ve seen truly ugly T-SQL code (from my perspective) in the SQLServerCentral forums, and I often need to copy and paste it into SSMS to get a sense of what’s happening. I used to reformat by hand, but CTRL+K,Y is an ingrained SQL Prompt habit that lets me get code into a style that I can work with.

The part of the treasure map that talks about formatting is long and detailed, and there are lots of resources that can help you format the code better.  Likely, however, you’ll want to open the formatting dialog (shown below) and play with settings to get what you want.

2018-04-04 10_40_25-SQL Prompt - Formatting styles

One of the best features, at least for me, is that I can quickly switch styles from my own to any other, such as a corporate standard. With a right click, I can choose another style, CTRL+K,Y to get to some other format, and then reverse that later.

2018-04-04 10_41_22-

I do this before committing to Version Control, as you should. If you haven’t tried SQL Prompt formatting, get an eval and give it a try. It’s amazing. This alone saves me a ton of time when writing code.

In the next post, I’ll continue on to the Caves of Code Analysis.

Posted in Blog | Tagged , , , | Leave a comment

IoT Success

After quite a bit of press the last few years, the IoT crazy has slowed a bit. I suspect this is because many of the consumer type IoT devices, such as refrigerators and washing machines, haven’t proven to be as useful or exciting as their developers originally thought. With all of the security issues that we’ve seen as well, having more interconnected “things” doesn’t seem to be so desirable for many individuals.

In the industrial world, IoT is still thriving and growing, as businesses see just how inefficient they have been in the past. The way that many people have gotten business done is with lots of hard work and good luck along the way. Certainly farmers and ranchers have counted on instincts and good weather more often than science. Those items still matter, but smaller sensors and connected IoT devices can help reduce some of the risk and ensure better yields from your efforts.

There’s an example of this in a short article about how various IoT devices are being used to help shrimp farmers in Columbia. Some of the uses, such as cameras to detect theft, are obvious, but the additional use of computing power and logic to reduce the need for a human to stare at a screen, vastly increases the chances of knowing when a theft is about to occur. Others sensors are used to check weather and actually monitor the shrimp in their environment, tasks which weren’t feasible for individual companies in the past to perform cost effectively.

The key here really is being able to gather more data and process it efficiently. Lower hardware costs, better algorithms  and a variety of programmable sensors enable the capture and examination of this data in a way that can be customized for individual companies. Data can be very powerful for most organizations that perform actions in the analog world. IoT is helping them learn to better understand data they they wouldn’t previously have been able to capture.

There are certainly still potential security problems with off the shelf sensors and grid connections. Anyone that wants to start to use these types of devices needs to understand the networking and potentially limit access to your main databases and systems, perhaps with tightly controlled ETL processes to funnel the data to staging areas. If you can do that, however, there are going to be opportunities to increase the efficiency of your operations, with plenty of data analysis and visualization jobs for the data professionals.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.9MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged | Leave a comment

T-SQL Tuesday #101–Tools

tsqltuesdayThis month’s T-SQL Tuesday is brought to you by Jens Vestergaard. The invitation for #101 is for essential SQL Server tools. Since I work for a tool vendor, you can guess that I’m going to be shaded that way.

This is the monthly blog party on the second Tuesday of the month. You can participate by writing your blog, linking to the invite and leaving a comment there (or a pingback). This is the creation of Adam Machanic, and if you want to host, drop him a note.

My Essential SQL Server Tools

There are really three main tools I’d say that are needed for SQL Server work. At least, these are the ones I need.

  1. SSMS – It’s free, and it’s the main tool for connecting to SQL Server. You might like VS Code or some other editor, especially on other platforms, but really SSMS is required for most people.
  2. SQL Prompt – It’s expensive for some, but after using this for years, I think the the native intellisense hurts more than helps. Just formatting alone make this critical for me. I’m moving through quicksand, mixed with molasses, when I don’t have Prompt installed.
  3. Git – Another free one, but an important one. As we write code, SQL Server will only keep the latest version. Much of what we do means that we need to understand where we came from with code. We do have integrations at Redgate for SQL code, but no matter what, just having a record of work is important, especially with multiple people. I love git, but if you want SVN or something else, use that. Just use something.

These are the main ones. Making these the main ones doesn’t mean that others aren’t important, but it just means these are the top of the list. After these, you can add in others. Here are the ones that I think are important for getting things done in a modern, multi-environment world.

SQL Search – From Redgate, but useful for finding things in a large environment. Way better than browsing.

Plan Explorer – I think SQL Monitor has a nice plan tool, but for those of you writing code and wanting to test it, this is way better than the native plan viewer.

DLM Dashboard – Free, and let’s you see and track those changes in production. Find out when code is being released and you aren’t aware of it.

Sublime Text – I tend to do some work in other languages, and in text, and having a quick, easy to use editor is nice. I think Sublime is great and worth the small fee.

Password Safe – Having long, secure passwords is important. I don’t try to remember them anymore, and I do want separate ones for every site. I like this open source tool, and have ports on my mobile devices.

Dropbox – Critical for me, since I use files on multiple machines. I keep copies of my presentations, password safe files, and more in sync across devices with Dropbox. Plus I can scan in docs from my phone if I need to send and share them.

dbatools – This is an amazing project. I hated working with the sqlps module and really avoided PoSh for many things before dbatools. Give this module a try and you may learn to love PowerShell.

SQLServerCentral – I’m biased, for sure. However, everyone could use a few things that we provide. A community, where you can get inspired and learn things (daily articles and editorial). A way to ask questions and get answers (our forums), and a place to keep scripts that are helpful for you (Scripts and Briefcase). These are important to rounding out your career, beyond just your current job.

There are plenty more choices, and certainly lots of tools within SQL Server. You should know about Extended Events, or Query Store (or Open Query Store) and more. Learn to use the resources you have, practice and test with them, and if you need a paid tool, don’t be afraid of asking. If the tool provides an ROI, it’s worth it. If it doesn’t, tell the vendor why.

Posted in Blog | Tagged , , | 2 Comments

The Best Ever

Every few years, or every year now, we see a new version of SQL Server proclaimed as the best ever. A few weeks ago, I saw a number of people leaving hte MVP Summit, rating it as the best ever summit. At SQL Bits or the PASS Summit every year, there’s no shortage of people posting about that event being the best conference.

Is this a human view, that the latest one is often the best one ever if we enjoyed it? Perhaps we learn something and we’re excited about the latest new SQL thing and so we’re excited to proclaim it the best?

What’s the best SQL Server version? Is there one for you? Some of you might be new to SQL Server or have worked with few versions, but perhaps one stands out. I’ve had the opportunity to work with a lot of versions, so the choices are hard. I’ve written code for SQL Server: 4.2, 6.0, 6.5, 7.0, 2000, 2005, 2008, 2008R2, 2012, 2014, 2016, 2017.

I have two votes here. You might like one, and likely will disagree with one. First, I’ll say that I have a fondness for SQL Server 2000. This was a stable, long awaited version that dramatically improved my life from SQL 6.5. It was more reliable and faster, and since it came after a relatively short time after v7, I ended up standardizing on this at a few jobs. Most of my upgrades were to SQL Server 2000 and it lasted a long time as the standard version. It was 5 years before SQL Server 2005 was released, an eternity in today’s software lifecycle. There were some bumps in the road (SQL Slammer), but a better security coding model came about at Microsoft during this version as well as SSRS (added later).

My other choice, maybe the top one, is SQL Server 2016. A number of security changes (AE, RLS, etc) as well as the inclusion of many features in Standard Edition (with SP1) make this my favorite. We got stability from the second version of OLTP tables and the third version of Availability Groups. I think SQL Server 2016 was perhaps the best version of SQL Server I’ve seen.

You might have other votes, and let me know today. Is there a version that’s near and dear to your heart.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.0MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged | Leave a comment

Code Snippet Cove on the #SQLPrompt Treasure Map

I’ve been away a bit in the last week with personal and family commitments. As a result, I feel a bit out of touch with work, and I have some catching up to do. However, when I ran across the SQL Prompt Treasure Island, I had to take a few minutes and go through it.

Code Snippet Cove

I love snippets. I first loved the idea in Visual Studio and Query Analyzer a long time ago, but SQL Prompt has taken this to a new level. I’ve used snippets quite often to make life easier and speed up development.

This part of the island talks about all the tokens that are available. There are all sorts, but some of my favorite are using $CURSOR$ to ensure the cursor gets dropped where I want it. This lets me immediately start typing.

I love $DATE$ and $USER$ for pre-populating comment sections of code, and there are plenty more metadata/environment type tokens that you can use, including the $PASTE$ for the clipboard.

While the tokens are great, I think that just the Snippets themselves, with custom placeholders, such as $userrole$, which I use for adding security to object creates, are incredibly handy. I’ve even used partial snippets for joins. For one job, we constantly needed to put together  these tables: Product, ProductCategory, OrderHeader, OrderLine, and Address. We had a snippet that just contained these tables with the appropriate ON clauses for joins.

Explore snippets, and you might never want to work without SQL Prompt again.

In the next post, I’ll take at look at the rest of the map.

Posted in Blog | Tagged , , , | Leave a comment

What’s Downtime?

There was a time when I worked for a company that sold products on-line  Since our wares could be purchased at any time of the day or night, we wanted to ensure that our systems were running all the time. This led us to build some sort of monitoring, which we tried. That led us to buy some monitoring software, which we did. This led us to build more tools, and it felt like we were in an endless loop for a period of time.

Eventually we stepped back and tried to answer the question that many Operations people have asked themselves and others: what is downtime?

It’s a tough question, and I want to give you a few examples of how I’ve viewed things, and debates I’ve had. For example, we had a database server and a web server. We used a simple script to ensure that the services (IIS and SQL) were running on both machines. If they weren’t, we received a page. Is that sufficient to detect if our system is working?

We also had a process that would ping our web server from outside the data center, using a public machine. If that works, is the system working?

In this job, we deployed new code every week, in a DevOps style process that existed before anyone had ever uttered the term. These updates sometimes included schema changes, but almost always included application changes. If a page on our website broke after a deployment, was our system up or down?

We integrated with some third party software to perform various tasks. There were times that we couldn’t communicate with the third party, or received broken communications. In those cases, were we up or down?

We built our application to work with multiple browsers, but at times there would be a new piece of functionality that didn’t render or work correctly on either a new (Firefox)  or old browser (IE6). Did that mean the application was down?

Determining uptime isn’t a single thing. Even when you provide mechanisms that ensure all parts of your application are working, are they working for everyone? Many of us might see this in various online calls, where a system like GoToMeeting or Skype might work for some of the audience and not others. I see this at times with Microsoft sites where some of us can use one of their online systems, but others can’t, sometimes because of the browser of the end user.

I was thinking about this while researching zero-downtime deployments, which can be hard for database changes. There are people that have success, but many others don’t. At Redgate Software, we are trying to build tools to make this easier for everyone, but there seem to be plenty of edge cases that cause issues. There are also many different processes and flows that groups use to perform database development, which often affects the final deployments. It is hard to build a general solution that needs to apply to specific environments.

I tend to learn towards measuring uptime of the systems I’m responsible for and letting others worry about intermediate infrastructure. I’ll caveat that with the note that I sometimes only worry about sections of the system and if those are broken. It’s good to be clear when talking about this topic with others. For example, we might be able to take orders, but can’t report on them, or can’t add new customers. That’s downtime for some sections of our application, but less stressful than if we couldn’t take orders.

Let us know today. How do you measure downtime or uptime, and where is your responsibility?

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 5.5MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged , | Leave a comment

In Memory of Robert

I was returning from a trip yesterday when I got an email from a friend that Robert Davis (@sqlsoldier) had passed away. Unfortunately, this happens more and more to me as I age. Family and friends are getting older and some will leave us sooner than we’d like. A Memorial and Grief fund has been set up, if you’d like to contribute.

There are various tributes from Kendra LittleThomas LaRockJen McCown, and likely a few others. Robert was a friend, and I’m again saddened by his passing. If you knew of Robert, either from reading his blog, following him on Twitter, or attending one of his talks in person, you know he was a very intelligent, humorous, and as Tom says, quite generous soul. I was honored he syndicated his blog at SQLServerCentral, but it wasn’t because of me or the site. Robert was overly giving of his knowledge, always looking to help others with their problems. He probed and questioned the questioners, trying to ensure that he could provide the best advice and solutions to others.

It wasn’t easy, physically, for Robert to always get around, but he made the effort to attend many events in person, and help others even though it was a challenge. I was always pleased to see him in person, shake his hand, and marvel at the lengths he was willing to go in order to help others and share his wisdom and experience. Others have noted his sharp mind and amazing recall. I’ll point to the 92,800 tweets he left behind, many of which were in service to others.

Kendra noted his New Year’s Resolutions for 2018 included more time for Robert and less work. Those are important mantras for us all.

I encourage people to work on their brand, or their career, or improve their skills. I really want all of you to do those things. Become better craftsman and craftswomen and improve the state of our industry. I want you to work at being better. When I present this live, or even write, I do want you to always keep this in mind.

We work to live; we don’t live to work.

Find a balance in life, find time for family, faith, hobbies, and friends. In the last decade, I’ve lost a number of family and friends, far too many in their 40s or 50s. I’m at the age where I likely have less life ahead than behind me. Each of us never knows how many days we have left.

Find a balance and ensure you enjoy your life along the way.

Robert, you are missed, and I hope Chrissy finds solace and strength in your memories. Please consider contributing to the Memorial and Grief fund.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 5.1MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged | Leave a comment

RIP, Robert Davis

A sad day for me.

A Memorial and Grief fund has been set up, if you’d like to contribute.

Posted in Blog | Tagged , | Leave a comment