ExcelSerialToDateTime – there's so much corporate data stored in Excel, and if you need to import it to SQL Server, you quickly find that dates and times are a challenge. I have a friend who says that the only data types in Excel are numbers and strings, and that everything else is an illusion. I think that's pretty accurate. This function takes one of the funky Excel decimal numbers (called a Serial) that represents a date and time, and converts it to a SQL Server datetime value.
DateTimeToExcelSerial – no prizes for guessing what this one does. It's the opposite of the above.
We've done a lot of updates to internal data within the tools, like all up to date SQL Server build numbers, etc.
DateDimensionColumns now has StartOfWeek and EndOfWeek
DateDimensionPeriodColumns now has IsSameWeek, DayOffset, MonthOffset, and WeekOffset
A while back, I reviewed a book called Atomic Habits by James Clear. I mentioned at the time that I nearly didn't get past the first chapter as I thought it was going to be another pretty cheesy self-help book. But I ended up loving the book. James really made me think about habits in a way that I hadn't done so before.
And so I was interested when Luke and Phil from Mandarin Blueprint released a new online course called the Habit-Building Bootcamp.
I wasn't at all surprised that they acknowledge how many ideas they had from reading Atomic Habits. But they've taken it further in two ways:
They have extended the ideas in James' book
They have directly applied it to learning Mandarin.
This course is designed as a companion to their Mandarin Blueprint course, but what I like about it, is that it's a good general purpose course on habit building, even for those with no interest in learning Mandarin.
Phil also added two extra lessons on breaking bad habits. Doing that is often much harder than forming new habits in the first place.
If you are interested in changing your habits, you might well find this low-cost course useful.
I recorded the first of a series of podcasts for the SQL Server 2022 timeframe recently. Once again, it was great to chat with another old friend Davide Mauri. Davide is a Principal Product Manager for Azure SQL Database.
Note that the topic I chose for today was the new sp_invoke_external_rest_endpoint system stored procedure, and it's currently only available for Azure SQL Database but I expect it will get ported to other flavours of SQL Server over time. Davide has been deeply involved with the development of this procedure.
Developers have wanted a better way to interact with external services from within Azure SQL Database and this procedure really helps with that. I feel it lets the database now integrate much more closely into the Azure ecosystem.
For this summit, I'll be presenting a quick (around 40 minutes) session highlighting what's changed in T-SQL for SQL Server 2022. I'm always so glad to see T-SQL enhancements in SQL Server and SQL Server 2022 has more than what we've seen in other recent versions. There are a number of very important enhancements that will take a little while to get our heads around, on the best way to use them.
I've also seen the list of people presenting and the range of topics for the conference, and it really looks quite fascinating. There is content in Polish but the majority is in English so it's completely accessible for us English speakers.
I'd really love to see as many of you as possible attending, to support the Polish data community.
I've just finished publishing another show for the Cosmos Down Under Podcast. This time the guest is Charles Feddersen.
Charles (and ex-Melbourne local) is working for Microsoft in the Azure Cosmos DB team. His current focus is the PostgreSQL API that Azure Cosmos DB supports.
In this show, Charles discusses the state of the PostgreSQL market, how it's implemented in the Azure Cosmos DB for PostgreSQL API, how that compares to Microsoft's Flexible Server offering for PostgreSQL, distributed tables, scaling databases, and so much more.
I have a varied client base. Many of my customers are traditional larger financial organizations. But I also work with start-ups and software houses. (Microsoft calls these ISVs – Independent Software Vendors).
Most software houses don't want to sell software any more. Instead, they want to repackage their solutions as services i.e. they want to offer Software as a Service. (SaaS)
Converting an application to a service is not easy. Even Microsoft had false starts with moving their on-premises applications to cloud-based offerings.
Lately, I've spent a lot of time considering Microsoft's Well Architected Framework (WAF). It provides pillars of guidance targeted at architectural excellence. There is a specific article that covers Multitenant SaaS on Azure. Part of that discussion is around the use of Azure SQL Database and Elastic Pools. This is an area that's often missed in these discussions, and it's the database aspects of that topic that I want to drill into further in this post.
Supporting SaaS with Databases
Databases are dear to my heart. It's critical to get the database aspects of an SaaS project right, and do that early on.
On-premises applications are mostly single-tenant. They support a single organization. We do occasionally see multi-tenant databases. They hold the same types of information for many organizations.
But what about SaaS based applications? By default you'll want to store data for many client organizations. Should you create a large single database that holds data for everyone? Should you create a separate database for each client? Or should you create something in-between.
As with most things in computing, there is no one simple answer to this.
Here are the main decision points that I look at:
Isolation
For me, this is the #1 item. You have to decide how important it is to isolate one client's data from other's. Developers always tell me "the app does that".
In multi-tenant databases, you are always only a single WHERE clause away from showing one client's data, to another client. Writing clean code with great separation is much harder in multi-tenant databases.
You need to start by considering what would happen if this went wrong. For some ISVs, this would be deeply embarrassing but still manageable. For other ISVs, this would be terminal for the organization.
Imagine the discussion between your CEO and a client after you showed their data to someone else. How would that discussion go? Would you still have a job? Would you still have an organization?
Image by Dmitry Ratushny
If you have even the slightest doubt about this, use a separate database per client. You still have the chance to mess that up, but you are starting in a better place.
Price
This one is pretty easy. Single larger databases still cost less than large numbers of smaller databases. If cost is your main issue, you will lean towards single-database designs.
Note: Azure SQL Database offers elastic database pools. Consider them before making your decision.
Cloudiness
A key aspect of "cloudiness" is resource granularity. Having separate databases for each client is much more "cloudy". Multi-database designs have many advantages. Each of your clients might need different levels of performance. Or they might need different features. This is easy in a multi-database design.
You might also be able to add (and on-sell) features to specific clients. For example, one client might want a read-only copy of their data in another location. Other clients don't need that.
Having separate databases lets you decide these things on a client-by-client basis. You can scale the databases up and down to different levels, for each client.
And it can even vary within a single client organization. They might require different environments (i.e.: Production, UAT, Test, etc.). Each environment can have different capabilities and features.
Noisy Neighbors
Everyone who's used a single database to support a large number of clients has run into the "noisy neighbor" situation at some time.
Image by Nik Shuliahin
One client can't get their work done because of the overhead from another client. They don't realize they are using the same database as someone else.
These problems are far easier to solve with separate databases.
If your clients are all in a single database, moving one client to another database can be hard. You need to plan for that from day #1.
Query Performance and Complexity
With multi-tenant databases, almost every table will contain the tenant ID. This column will be part of almost every join operation.
It's easy to see that these databases perform worse.
The code you need to write is also more complex.
DevOps and Deployment Friendliness
A multi-database model is much more DevOps friendly. Single smaller databases can often be "spun-up" and then destroyed again, much faster than larger databases.
It is much easier to give each developer their own environment with their own databases during development and testing.
A single large database is obviously easier to manage, but you should be looking to automate the deployment and core management of your databases anyway.
Recovery/Rollback/Import/Export/Retention
Have you considered what happens when an "oops" moment occurs? One client wants to roll back their data to an earlier point in time. This can be very complex to achieve with single-database designs. It's trivial to achieve with multi-database designs.
What if different clients have different data retention policies?
What if clients want to be able to import/export their own data?
You can do all these things with both designs, but they are much simpler with multiple databases.
Data Ownership
You need to consider who will own the data that's used by your application. When you use a single-database model with multiple tenants, it's clearly going to be a database that you own and are responsible for.
Is that an appropriate risk for your organization?
One model that I'm seeing more commonplace now is that while the ISV owns and operates the application, the end customers own their own databases (and pay for them). This might simplify your legal situation in regards to data ownership. It might also help if there are data sovereignty issues.
Take legal advice on this.
Summary
As I mentioned, this isn't a simple decision. Nowadays for cloud-based PaaS (Platform as a Service) databases to support SaaS applications though, unless there is a compelling reason not to, I'd suggest starting with a separate database for each client every time.
I've just finished publishing another show for the Cosmos Down Under Podcast. This time the guest is Will Velida.
Will is an Engineer for Microsoft working in the Fast Track for Azure team. His focus is on application development and modernization. He is passionate about developers from all walks of life build great things using Azure. Prior to joining Microsoft, he has worked as a software engineer in the financial and agricultural sectors. He was also a Microsoft MVP in the Data Platform category, focusing on Azure Cosmos DB.
In this show, Will discusses how data is stored in Azure Cosmos DB. In particular, we discussed containers and partitioning.
This month, T-SQL Tuesday is hosted by an old friend of mine Kevin Kline. He's an old friend as I've known him a long time, not because of age. (There are lots of miles left in Kevin). The invite asked about a conference or event that changed your life, that created an opportunity, or just changed your life. It could be career related or another area.
Background
I was invited to join the Microsoft Regional Director program back in the early 2000's. The RD program had grown out of the original DevDays events, and the RDs were the people who spoke at those events. By the time I joined, they were commonly used as speakers at events like TechEd around the world.
In fact, unlike the MVP program, the RD program had an agreement. An MVP award is for what you've done in the previous year. There are no expectations of what you'll do during the year you are awarded, apart from basically not being a horrible person. The RD program had an agreement on what you will do in the upcoming period. Speaking at tier-1 events was expected, and you were also expected to be in the top 10% of speaker ratings at those events.
Tough crowd to join!
TechEd USA events
I was used to technical training, and I spoke at many TechEd and other events around the world. However, at one of the first TechEd events in the USA, I remember signing up for the provided speaker training. I can't tell you the effect that had on me.
Microsoft had set aside a full conference room, with lighting, etc. just for the training, and they had an amazing speaker coach (Richard Klees) who worked with you one-on-one. Interestingly, his entire focus was on "big room" speaking, not on presentations in general. Speaking in big rooms is different. I remember him pointing out that the Rolling Stones don't play coffee shops, and if they did, they would act entirely differently.
Confronting Impact
I spoke to several other RDs who had attended the training with him before, and did not like him. Ironically, I loved working with him. Others said they found his training far too confronting. They didn't seem to understand that he wasn't there to be your friend. He was there to change just enough things to help you make a better impact, without trying to totally change how you speak. He didn't want to turn you into a basket-case a day or so before your session or sessions.
There were so many things I learned in those sessions. A big issue for me is that I was too softly spoken for a big room. By the time he was happy, I felt like I was pretty much shouting, and he still rated the energy level at about 5 out of 10.
By the time it was finished, I was fairly shattered and completely exhausted. Did it improve my sessions? Absolutely no doubt about it. There are so many things he said that are stuck in my mind still.
TechEd Australia Keynote
I loved speaking at TechEd when it ran. I remember presenting in various locations in the USA, Barcelona, Hong Kong, Australia, New Zealand, and more. Working with the developer evangelists from around the world was a highlight. And of course our local ones like Chuck Sterling, Andrew Coates, Dave Glover, etc. were highlights.
The 2004 event in Australia was notable for me, because as well as my sessions, I presented part of the keynote with Eric Rudder, a Microsoft senior vice-president of server and tools. I ended up spending quite a bit of time at various events with Eric during that week.
It was fascinating to see the contrast between the social Eric with user events, and the business Eric in the rehearsals for the keynote, where he was razor-sharp focussed on the delivery were all going to make. People attending those keynotes really had no idea how much work and rehearsing went into delivering them. The section that Terry Clancy and I delivered was pretty smooth. You'd hope so, given the number of times we rehearsed it over many nights. Let's just say that I'm glad I wasn't part of the SharePoint section.
When the event arrived, I remember watching Eric psyching himself up to present, and the level of energy he put into each of us backstage. I was pleased with our section and Eric seemed super happy backstage afterwards.
This book seems to be somewhat new and somewhat an update, but this time with the main focus on query tuning and optimization. That's pleasing as the main way to get better performance out of SQL Server is to fix the queries. much more so than anything to do with the hardware or server configurations that so many people focus on.
I've known Benjamin for a long time. He's a very skilled SQL Server professional. And again the technical reviewer for the book is another very skilled old friend in Mark Broadbent. Brandon Leach, Ajmer Dhariwal, and Artur Wysozanski were also technical reviewers. Once again, I had high expectations.
And once again, I wasn't disappointed.
The book covers a rich set of topics, including most things you need to know when performing query optimization for SQL Server. It provides a detailed background to each area, and then recommendations on how to proceed.
Once again, it was so pleasing to read a technical book that was well written, used English well, and wasn't full of technical errors. Nowadays, there aren't many technical books that fall into this category. Several times lately, I've been sent technical books for review, and I've ended up telling the publisher that they don't want me to write a review as those books were so poorly written. It's important to support people who are still writing quality technical books.
Now, were there again areas that I disagreed with Benjamin on?
Yes.
I just don't share the enthusiasm for the in-memory OLTP options in SQL Server, even for temporary objects. I wish it wasn't so, but I've spent so much time trying them over the years, and let's just say, I don't use them. The SQL Server team spent so much money on their in-memory OLTP and in its current form, even in SQL Server 2022, it just doesn't deliver. I think it's one of Bob Ward (product team)'s dreams to ever have me happy with it, but that's not happened yet. It shines in a few very niche areas, but for most customers, I tell them to avoid it.
Another area that I'd also differ on now is the Data Collector. I really don't think this has much future. Benjamin covers it still in this version of the book, but I'd strongly suggest that customers give it a miss.
Were there again areas where I'd love to see the book improved?
Yes.
Here are some suggestions:
I'd like to see statement terminators in all the code (personal bugbear).
The order of the topics in theory makes sense, but again in practice, I'm not so sure. I'm concerned that most developers who pick up this book won't get through the theory, to then find the gold. Perhaps there's a need for another book that just lists all the common issues for query tuning, and then for each one, explains what's going on. Alternately, it might have made for an interesting appendix, to have a list of common issues, and for each to tell you which parts of the book are most relevant. So many developers would be just looking for quick answers, and aren't going to read the whole book to find that.
I'd love to see more info on working with captured traces. There is so much value in this analysis, of both the core queries, and the normalized versions of the queries. Nowadays, it's the number one thing I start looking at when doing performance tuning work on systems that I haven't worked on before.
I'd like to see the book call out even more on the importance of appropriate application design. In my work, I find that at least 70% of the SQL Server performance-related issues that I run into, are application design issues.
The Verdict ?
Overall though? Again, a wonderful book. 9 out of 10.
It should be released very soon: https://packt.link/AbvGr
Want to learn more?
If you'd like to learn much more about tracing queries and improving your T-SQL, check out our online courses at https://training.sqldownunder.com
One of our popular free resources is the SDU Tools library. If you haven't checked it out, I'd encourage you to do so. It's a large library of functions, procedures, and views all written in native T-SQL code.
You can easily use it as a complete library, or use it as examples of how to write T-SQL code. v22 is now available for download.
If you aren't on our notification list, you can add yourself here:
I'm sorry it's taken a while longer to get this version out than I would have liked, but we've finally caught up after a really busy period.
In v22, we've added the following:
Calculate Age in Months – think it's easy to work out ages? Just use DATEDIFF? If you think so, you'd be mistaken. DATEDIFF could even tell you that someone who is one day old is a month old. That's not true where I live. This function fixes that.
Last SQL Server Restart (work out when SQL Server was last restarted)
Languages (this is a great list of all the world's languages, categorized into their language families, includes the name of the language in English, and in the native language, and has the two and three character ISO codes for them.
List Constraints with System Names (you know you should name constraints explicitly and not let the system do it. This tool helps you find the ones that have slipped through)
File Path To File Extension, File Path To File Name, File Path To Folder Path – these functions take a full file path and extract out the extension, the file name, and the folder. They work with local file paths, UNC paths, and with Windows and Unix path delimiters.
List Untrusted Check Constraints, List Untrusted Foreign Keys – e previously provided tools to attempt to retrust untrusted check and foreign key constraints, but we realised we did not have a tool to find them in the first place. One new tool is used to list any check constraints that are currently untrusted. The other new tool does the same for untrusted foreign keys.
We've also done the usual updates to lists of SQL Server versions and builds, and a few patches to existing tools.
