Book Review: 100 SQL Server Mistakes

I was approached by Manning Publications and asked to review 100 SQL Server Mistakes and How to Avoid Them. They gave me a free copy of the book (and offered a second one as well), but didn’t put any conditions on my work.

This is a preliminary review of the EAP version of the book, which is still in progress as of now. If you buy the book, you can get digital chapters as they are written and edited, as well as the final book.

This is part of a series of book reviews I’ve done on my blog. You can see them all under the book reviews tag.

100 SQL Server Mistakes

The book is designed to give you 100 things that people commonly do wrong with a SQL Server instance and/or database, and starts with mistake 0 being that people think

From there, the book goes into an explanation of the 4Cs diagrams, which are ways of representing systems. This was mildly interesting to me, though less useful when I was reading on my mobile as seeing the details of the diagrams is hard.

The first few mistakes are on standards. Naming, prefixes, using sp_, and more. I liked this as I think that having some good basics communicate information between team members, or even users of your database for reporting. There are reasons given for why each of these is a mistake, as well as example code to showcase potential issues.

Data Types are the next set of mistakes, showing common things people do when designing their data model or objects. There is also a few mistakes on database design with common mistakes that people make.

There are sections for T-SQL mistakes, including error handling as well SSIS mistakes and installation problems. Each of these is grouped together with a variety of common issues that people may run into.

The version I have of the EAP is 8 chapters, with a few more to come. Overall, this is less a what you should do, and more of a what you shouldn’t do. I like this approach. Aaron Bertrand did something similar with his Worst Practices series. Often we are stuck with certain designs, and we may not be able to implement best practices. However, we should try to avoid worst practices.

I’d even say that if you have some worst practices, don’t continue them for the sake of continuity or consistency. Start refactoring or at least improving new development.

This book is a good reference for beginner to intermediate SQL Server developers and administrators, and might even be a good gift for welcoming employees early in their SQL Server journey. Many of these would be guidelines I’d want to implement for  a team.

If you’re looking for some knowledge to help you avoid producing bad code, check out this book. It doesn’t have all the answers, but it has some good thoughts on code smells and ways to correct them. It might give you inspiration to fix some code in your shop.

If you want another view, Kevin Feasel has his own thoughts.

You can pick up the book here: https://www.manning.com/books/100-sql-server-mistakes-and-how-to-avoid-them

Posted in Blog | Tagged , | Comments Off on Book Review: 100 SQL Server Mistakes

Another View of DevOps

Chocolatey Solutions Engineer Stephen Valdinger said, “DevOps isn’t something you do, but rather, it’s a way of doing things. What works for us here, may not work for you there, so you adjust.” He then went on to say that DevOps is a way of working that reduces time to introduce changes, while at the same time making changes traceable, accountable, and revertable.

I’ve seen many companies try to copy what another company has done, especially with regards to DevOps and software development. I see companies copy the organization of teams from Amazon, Spotify, or others. Often quite a bit of time and effort is spent changing the way your development team works, and often without a lot of success.

DevOps is a lot like my studies of martial arts, where you learn some techniques, but it is up to you to implement and use those techniques in your own way. While we may practice in patterns, the actual use of the skill is up to the user. That’s what DevOps really is, a goal and set of ideals you aim for, but the actual implementation varies from company to company.

Most of us want to build better software, and most managers want better quality applications, but often we can’t get out of our own way because either too many people are resistant to change, or there isn’t any incentive to work in a better way. This might be from individual contributors or from management, but without both groups making an effort to improve the software development process and quality of code, we won’t achieve much.

To get better at software development, whether C# or SQL, you need to read and learn about how to write better code. You need to learn how to automate the testing, compilation, and deployment of your code to downstream systems. And then you need to discuss and debate what works well, what doesn’t, and adjust how the team writes code. Not just you, but the team. We might also need to adjust how we store, package, test, and run code on other systems. We have to experiment in small ways, testing out new ideas, algorithms, designs, and more.

In short, we need to be a team. I like the quote above, but I also hope most of you realize the “you” in that quote is not singular, but plural.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Posted in Editorial | Tagged | Comments Off on Another View of DevOps

Auto Renew Subscriptions

This week I saw an announcement from Azure that you can set up auto-renew subscriptions for your reservations for your resources. This allows you to set up auto-renew for reservations to prevent them from not renewing and being charged at the pay-as-you-go rates. This reminds me of the old Columbia House deals, which I had for both cassettes and CDs. For some time these were good deals for me, but over time, I’d lose track of the subscription, forget to return things, and end up purchasing items I didn’t want.

An auto-renewal is likely both a good idea and a bad one, as I suspect that many organizations lose track of their renewal dates when they have an estate of any size. Administrators or finance people will come and go, and they might not know the history of why we reserved something, like a VM, and could forget to renew these. Often we are still using resources, so renewals make sense.

However, we also stop using resources or don’t use them at the same capacity levels, so perhaps we want to not renew. The docs for this feature note that the renewal creates a new reservation, and this setting can be changed at any time. However, some of the other docs aren’t clear to me. The descriptions read as though someone has an idea about how this works, and they don’t quite clearly detail the workings of the system for readers that are not familiar with this feature.

As an example, the docs say if you enable renewal more than 30 days before expiration, then  you get an email explaining renewal costs. However, with automatic renewal, do you get the email? When are costs disclosed or detailed? Maybe more importantly, the doc notes the price may change between when you lock the renewal price and when the renew time occurs. Does this mean you pay the higher price? Are you notified each year?

Maybe more importantly, if we create resources at different times, can we somehow get renewals dates to align? Whether this comes to finance people or IT people, at some point with a big cloud presence, you might have renewals coming every month or week. Who would be able to track this stuff appropriately if it’s a constant item to check? I can see plenty of people starting to ignore these notices if they come too often. There are other issues as well, such as a SKU being different (or deprecated) over time. Will someone figure out how to upgrade to a newer SKU? If so, will they remember to set auto-renewal? Does this transition to the new SKU?

The documentation is woefully incomplete, though I’d be happy to submit PRs for edits. I just don’t know what the answers are, and I suspect that whoever built this feature might not have thought through all the scenarios. Certainly the individual writing the documentation didn’t.

The cloud is complex, and billing is complex. Tracking and managing this is going to be partially a technical role, and I suspect many of us won’t know what the implications are for the choices we make. I also suspect we’ll get notifications for resources that we have no knowledge of and will need to research more about the item. We’ll become more of a financial DBA over time, which may or may not be the job we want.

Steve Jones

Posted in Editorial | Tagged | Comments Off on Auto Renew Subscriptions

The PASS Data Community Summit 2024 Call for Speakers

The PASS Data Community Summit call for speakers and volunteers is open. You have until April 10 to submit something, and you can do that here: Data Community Summit CFS.

I have tended to submit every other year, though I did put in one session last year, which was rejected. See, it happens to everyone and it’s no big deal. The volunteers didn’t like my session and didn’t choose it.

I submitted a couple of sessions this week, and you should as well. If you’ve every presented at a user group, SQL Saturday, or even to your team internally, take a chance. Write an abstract and send it in. If you need help, the Speakers Library here has resources for you: https://www.newstarsofdata.com/speaker-improvement/

This is a great chance to start growing your brand and improving your career.

Take a chance and submit today

Posted in Blog | Tagged , , | Comments Off on The PASS Data Community Summit 2024 Call for Speakers