Do You Want a Microsoft Solution?

Posted on November 15, 2024 by way0utwest

Microsoft constantly releases new features and products in the data platform space. Many of us have seen the SQL Server product grow in new ways, some of which are very useful to us. As an example the changes from log shipping to clustering to Availability Groups has improved our HA/DR options as well as the capabilities available to us in different situations.

With that in mind, I saw someone recently that wanted to deploy SQL Server on Kubernetes, which is something that could be a very interesting way of managing your different systems. However, this individual wanted to know when Microsoft would release their own supported solution with a Microsoft operator to manage the instance. There is guidance from Microsoft, but no official operator.

I saw a recommendation to use DH2i, which has a solution with an operator that can help here. In fact, in the MS docs, there are articles on using DH2i with SQL Server. However, the docs note that DH2i is responsible for supporting their product. The person asking about support didn’t want another vendor and wanted a Microsoft solution.

Is that something you want? Do you want a Microsoft solution for most (or all) things? Or do you think third parties or bespoke solutions are acceptable? This could be your opinion or a policy/guideline from your employer, but let us know.

To me, I think third parties are necessary. Microsoft can’t do everything, and they might not provide the support or flexibility that someone else can. Many of us sp_whoisactive, which isn’t an official Microsoft solution. There is a First Responder kit, diagnostic queries, a pressure detector, and plenty of other resources that people have created and shared. There are plenty of tools for SQL Server (and most other products) that various vendors have produced and sell which meet the needs of their customers.

My view isn’t to choose a Microsoft solution for everything because their solutions aren’t always the best choice for my problem. Even when they work well, they are often incomplete and I need to do some work to get them to fit into my environment. To be fair, most anything often needs a little work (or time) to fit into many environments.

Let us know today. Do you want official Microsoft solutions for your data platform? Or Oracle ones for Oracle? Who makes you comfortable with PostgreSQL, Aurora, Redshift, Databricks, or other platforms? Or do you only use tools and capabilities inside the platforms?

Steve Jones

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

Note, podcasts are only available for a limited time online.

Posted in Editorial | Tagged | 2 Comments

A New Word: Ochisia

Posted on November 15, 2024 by way0utwest

ochisia – n. the fear that the role you once occupied in someone’s life could be refilled without a second thought, which makes you wish that every breakup would include a severance package, a non-complete clause, and some sort of romantic placement program.

I struggled with this one. I have had quite a few friends in the last few years that got divorced and my heart goes out to them. Some ended up in financial hardships, so oshisia seems like a poorly-timed joke.

However, I get the idea. I’ve been attracted to someone, enjoying the dating, and then had it end. I’ve also ended it. I get that there is not only the loss, but the anger, the sadness, the wandering, the fear that someone else can easily replace you.

I think many teenagers who’ve been in love go through ochisia at some point.

From the Dictionary of Obscure Sorrows

Posted in Blog | Tagged , | Comments Off on A New Word: Ochisia

Rename a Database: #SQLNewBlogger

Posted on November 13, 2024 by way0utwest

I had someone ask me how to rename a SQL Server database recently. They were doing some development work and wanted to rename databases to test an application. I thought I remembered, but in this post, I show I learned something.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Using sp_rename

I thought sp_rename would work, and sure enough, it did.

2024-10_0232

However, I need to object type. If I remove that parameter, it fails:

2024-10_0233

The command is looking for an object in the current database by default.

Technically, I ought to do this to be explicit, naming the parameters.

2024-10_0234

I have a better way, however. Note: it’s not sp_renamedb, which is marked for deprecation.

ALTER DATABASE

I don’t know when this changed, or if, but you can use ALTER DATABASE to change the database name. There is a MODIFY NAME option for this command that works well. You can see this below.

2024-10_0235

This is very clear and seems like better DDL For this process, which can easily be captured as code without worrying about parameters or ordering or anything else. I’d recommend using this.

SQL New Blogger

This post took me about 10 minutes to write. Easy. I had done a few experiments and I had code ready (which went to the customer), so I didn’t spend time there. Just rewrote what I did and learned in a few minutes.

You could do this, add to your blog, and maybe get an interviewer to ask you about this after they saw your post.

Posted in Blog | Tagged , , | 1 Comment

Separate Reads

Posted on November 13, 2024 by way0utwest

Recently I was watching a presentation on how to scale performance in your SQL Server environment and one of the suggestions was setting up Availability Groups (AGs) and having read-intent connections that would query the secondary and not the primary. It’s not a bad idea, and the SQL Native Client (and other drivers) support this and make it easy to implement.

The pattern of using multiple connections in an application, one for reads and one for writes, has been suggested often. However, in practice, I’ve rarely seen this work. Apparently having a connection variable, named dbConn, for writes and a second one, named dbConnReadOnly, for reads is too complex for most developers or teams.

Or maybe the idea of having to pick the right access point is a human problem? I’ve seen no shortage of problems in restaurants when we have specific “in” and “out” doors. Lots of people go through the wrong one and we end up with plates and food on the floor. Even broken noses or fingers at times. Perhaps I shouldn’t pick on software developers too much.

How many of you use two connections from apps? Meaning, do you think about reads and writes in separate connections. Even if you read and write from the same database, this can be a nice practice that future proofs code. It’s a small change, but it gives you room to grow if you get a read replica for analytics or reporting.

Of course, you could take it too far with different connections for different “services”, aiming for a microservice-style architecture. We could have dbUser for user stuff and dbOrders for the business side, and other connections for other services. I wouldn’t do that, as I think many of us will get confused, and we’ll often be doing two different type of service things in the same code. If I need something from a customer to write an order, do I have two connections in my method? I could, but I bet lots of developers would try to re-use a single one.

Plus, if developers get into trouble with two connections, then what will they do with 5 or more? There are lots of ORMs that might even support this, or if they do, not make this easy to code.

I’ve always liked the idea of separating reads and writes, but maybe the better solution is using one connection whenever we have simple CRUD work and another one for any sort of complex querying or reporting. That would make more sense as I suspect many of us will eventually offload reporting or analytics in some way to another system. A Delta Lakehouse of some sort seems likely if the current trend takes hold in more organizations.

Let me know today if you used (or have tried) different connections for reads and writes.

Steve Jones

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

Note, podcasts are only available for a limited time online.

Posted in Editorial | Tagged , | 2 Comments