Voice of the DBA

Author: way0utwest

  • 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.

  • 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.

  • 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.

  • 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.