SQL Down Under show 91 with guest Mohamed Kabiruddin discussing SQL Server on Google Cloud is now published!

I hadn't seen Mohamed Kabiruddin for a while. He used to be Australian based, and worked for Microsoft for some time. Now he's a product manager at Google. He's always fascinating to chat to and so I was so pleased to have him on a SQL Down Under podcast today.

Mohamed leads Cloud SQL for SQL Server working with the engineering team to deliver features and capabilities for enterprises to run their SQL Server workloads on Google Cloud SQL. His expertise lies in architecting globally distributed data and information retrieval systems, databases, cloud database migrations, and vector embeddings.

Mohamed spent several years working on applications with SQL Server, is a frequent speaker at industry conferences, meetups and user groups. He is an active community evangelist for SQL Server and a mentor for aspiring product managers.

I hope you enjoy it. You'll find this show (and previous shows) here: https://podcast.sqldownunder.com/

Book Review: Extending Power BI with Python and R

I've seen a few books lately from the PackT people. The latest was the second edition of Extending Power BI with Python and R by Luca Zavarella: Perform advanced analysis using the power of analytical languages.

Author

Luca Zavarella

The author is Luca Zavarella. I've been working with Power BI since before it was released, and ever since I've seen discussions around using R (initially) and Python (later), Luca has been one of those people that everyone listens to.

Luca is a fellow Microsoft MVP and has a great background in data science. In recent times, he seems to have been focussing on advanced analytics, data science, and AI. He's been prolific in these areas.

The Book

This is another very comprehensive book and took quite a while to read. It will be a vital reference for anyone trying to apply Python and/or R in Power BI. I've heard many argue that this is a pretty niche topic but I can't say I agree. I deal with a real mixture of clients and while not everyone does this, quite a few do. That of course particularly applies to anyone from a data science background.

In the book, Luca's experience shines through.

The real power of the book is the way that Luca shows you how to do things with Python and/or R that most people working in Power BI without them, would think were impossible.

In the DBA world, people often talk about the "accidental DBA" i.e., that's someone who wasn't planning to be a DBA but ended up being the one doing the tasks. I can well imagine that if you are the "accidental data scientist" working with analytics, there's a great amount to learn from this book.

I could also imagine this book being useful to people who use other analytic products, not just Power BI. because Luca explains the theory, not just the practical application.

And even though I've worked with Power BI from the start, I found a few interesting things in the book about Power BI, not just about using Python and R with it. It's important to always just keep learning, and every time I read something about a product that I think I already know well, I invariably learn something anyway. That's often because everyone uses the products differently, or applies them to problems that you hadn't considered.

Summary

Another great book. And another one where I can't imagine how long it must have taken to write. And the second edition adds great value.

It's not one for all my usual data audience, but anyone with an interest in applying advanced analytics in Power BI should check it out. You will learn a lot.

9 out of 10

 

PG Down Under show 2 with guest John Miner is now published!

Welcome to show 2 for PG Down Under!

I really enjoyed recording today's show with John Miner. John is a data architect at Insight in the USA. Over many years, John was a data platform MVP, is a strong community contributor, and blogs at craftydba.com.

After I created the Wide World Importers sample databases for Microsoft back in 2016, I did also do a migration of the code to PostgreSQL. That didn't get released for quite a while but I've now seen it out there in the wild. I wanted to do a show on the migration but was concerned that I had done it so long ago, that I might have forgotten what surprised me at the time.

John has had a project in the last year that required migrating from SQL Server databases to PostgreSQL. I thought that made him the perfect guest for this topic. He's knowledgeable about SQL Server and able to share his experiences and what he learned while migrating code.

He's provided a link to his presentation details (including a PowerPoint presentation) here: https://github.com/JohnMiner3/community-work/tree/master/moving-to-postgresql

You'll find this show along with all PG Down Under shows here:

https://pgdownunder.com/podcast

 

SQL Server Management Studio issues with Central Management Servers in v20.1

I've recently been doing work with a site that makes extensive use of Central Management Servers. And that's an issue if you upgrade past v19.3 of SSMS.

Central Management Servers

If you haven't used these, it's the Registered Servers window that you can open in SSMS. (View -> Registered Servers) What it lets you do is set up groups of servers, and execute multi-server queries against all the servers in the group. You can also have a hierarchy of groups with nested folders and then work at different group levels.

The details for these are all these groups and servers are stored in tables in the msdb database. In fact, it's easy to programmatically add/remove servers from these lists. We do that all the time.

And if you haven't used them, they are quite magical. It's awesome to be able to easily run a query against a large number of servers, and to combine the results into a single result grid in SSMS, along with the server name that each row of data came from.

What it has never really dealt with though, is any form of authentication except Windows authentication. (i.e., no SQL logins)

So what's changed?

In v20 and later of SSMS, you'll notice a new entry on the first page of the connection dialog.

Trust server certificate dialog

The options for Connection Security have moved to the first page, but more importantly, the defaults have changed. The default option now (Mandatory) requires you to either have a certificate on the server that you already trust, or to check the box to trust the self-signed certificate on the server.

By having a certificate that you trust, I mean that SQL Server has a certificate installed on it manually, and that certificate is issued by a publisher that your client computer has in its list of trusted publishers. You need to install a certificate on each SQL Server, and get a trusted publisher pushed out to every client, if the certificate is from a publisher that the clients don't already trust.

So what's the challenge for Central Management Servers (CMS)?

I totally understand the desire to tighten up the security, but it's an issue for people using Central Management Servers and not already having trusted certificates in place.

As an example, the people I'm helping at the moment have hundreds of servers, all related to different business groups, and changing any one of them requires change processes with change requests, approvals, external system management teams to implement it, etc. Bottom line is that even though it would be desirable, it's not going to happen any time soon.

There is no option with CMS to automatically select an option to trust the server certificates. You need to do that server by server, and it's stored on a per-user basis, not as a value in msdb. That means that even if I went through and edited every server's configuration, every other person using the CMS would need to do the same.

So how do I work around it?

Bottom line is that there's no easy fix for this. Clearly, installing trusted certificates on every server, and pushing out the certificate authority as a trusted publisher to every client is the desirable outcome.

If you can't do that, you need to keep using v19.3 of SSMS, at least for now.

 

 

 

 

SQL Down Under show 90 with guest Joe Sack discussing Azure SQL Database and Copilot is now published!

Joe Sack is an old friend. (I've known him a long time. He's not actually old). He's always fascinating to chat to and so I was so pleased to have him on another SQL Down Under podcast today. Last time was in 2017.

Joe is a Senior Product Manager at Microsoft and is working with the integration of data and AI with SQL Copilots. The aim is to use AI and natural language processing to make database management simpler, whether you've been doing it for years or just getting started. The experiences he discusses in the show include Azure Copilot integration and Natural language to SQL.

I hope you enjoy it. You'll find this show (and previous shows) here: https://podcast.sqldownunder.com/

Book Review: Web API Development with ASP.NET Core 8

Another book that I was recently sent for review by the PackT people was Web API Development with ASP.NET Core 8: Learn techniques, patterns, and tools for building high-performance, robust, and scalable web APIs.

Author

The author is Xiaodi Yan, who is a fellow longer-term Microsoft MVP and an experienced software developer, focussing on .NET, AI, DevOps and all things cloud-related. You'll also find him on LinkedIn Learning as an instructor.

During the Covid lockdowns, I started to get involved in attending the New Zealand Chinese IT user group meetings that were being held online. I thought it would be a great opportunity to keep trying to improve my Mandarin, particularly with technical content. Xiaodi Yan was one of the people who made me feel most welcome.

The Book

This is a very comprehensive book. I remember the author posting about his relief at having finished it, and I now know why. It's a huge book and it took me a while to go through it. Even though I did so, I think this will be a great ongoing reference in the future when I work on projects that are implementing APIs this way. The structure of the book lends itself to being a great ongoing reference.

I liked to see so many code examples. They really helped with understanding the points that he was making.

One area that I've always felt a bit weak on is dependency injection. I'm a developer by background but spend most of my days working in data, and dependency injection was one of those patterns that came along when I was less focussed on it. He explained it so well.

Lately, with any projects I've been involved with, .NET Core is usually the target. I particularly loved the fact that that's what he targeted in this book.

Like many books, there are a few further edits that might have helped. But overall, it's a masterful work.

Summary

What a great book. I can't imagine how long it must have taken to write. And how long it must have taken to get all the required thoughts in the appropriate structure and order to make it such a good reference.

It's not one for my usual data audience, but anyone involved with Web API development should check it out.

9 out of 10

 

SQL: Connecting to LocalDB from SQL Server Management Studio (SSMS)

I saw a question on the Q&A forums about how to connect to the LocalDB version of SQL Server from SQL Server Management Studio (SSMS). I thought it was worth posting a walkthrough of how to do it.

LocalDB

The first thing to understand about LocalDB is that it doesn't run as a service. You fire it up as an attached process to another program. The basic idea was that you'd use it from Visual Studio or Azure Data Studio, and those programs would instantiate it as required.

SqlLocalDB

But what if you wanted it to be launched in the background, to have other tools like SSMS connect to it?

Now SSMS doesn't fire it up for you, but Microsoft provide a utility called SqlLocalDB.exe that does that all for you. The linked page shows the available commands.

On my machine, I've opened a command prompt and changed to my C:\Temp folder. (Doesn't matter where) I've then executed the following command to check the current status for LocalDB:

And it returned:

info

This tells me the LocalDB instances that have been created. In this case, I'm going to create a new one:

This command creates a new instance and starts it (that's the -s part). It returns:

create and start

Note that it shows both creating it and starting it. I've then shared it as a specific name:

share

You can tell if the instance is started or stopped by executing the info command:

info

In that image, I've shown you one that's not started, then starting it with a start command, and checking again with the info command. Note that the shared name is also provided.

Connecting to LocalDB from SSMS

At this point, you have two choices for connecting to LocalDB. You can use named pipes. You can see the named pipe address at the bottom of the info command above.

In my case, I'm going to connect from SSMS. I open SSMS and enter the address as follows:

(localdb)\.\GREGLOCALDB

Note that I'm using (localdb) which says to use the in-memory provider for localdb, and then it's referring to the shared name.

ssms connection window

Also note that I'm using Windows authentication. I can create a SQL login, user and password and use those to connect. In this case, Windows is fine, and I just click Connect.

ssms connected

And we're in business !

NOTE 1: Sometimes when I try this, it will not connect at first, and gives a timeout. Yet if I wait a few minutes, it works fine.

NOTE 2: the documentation says that you can't remotely manage LocalDB using SSMS. It doesn't mean you can't use SSMS, it just has to be on the same machine so that the shared memory provider or the local named pipes provider will work. I do have vague recollections of actually enabling TCP/IP for LocalDB at one point and connecting remotely to it, but that might have been in the early days and no longer possible. If you're doing that though, you're better off with SQL Server Express edition.

I hope that helps someone get started.

 

 

 

 

 

Book Review: Azure Data Factory Cookbook – 2nd Edition

The people at PackT recently sent me a book to review, and I was happy to do so as it was on a topic that's dear to my heart: Azure Data Factory. The book was Azure Data Factory Cookbook and it's the second edition of the book. The authors are Dmitry Foshin, Tonya Chernyshova, Dmitry Anoshin, and Xenia Ireton.

PackT

In the past, I wasn't keen on PackT books. When they first appeared, they tended to be low cost books from unknown authors, many of whom struggled with writing in English, and pretty poor editing of the content.

I'm really pleased to see how this has changed in recent times. The authors of most of their books are now people who are knowledgeable about the topics, write well in English, and the editing has improved out of sight.

Not sure how that was achieved, but am really pleased to see that it has.

In terms of production, there are only two comments I'd make:

  • I find the font style, size, etc. still harder to read than an equivalent book from, say, Apress. I find the books harder to read for long periods.
  • I know it's hard to ask for colour, but I have to agree with one of the reviewers on Amazon who commented that the lack of colour make some of the pictures hard to read.

Other than that, the book was large, solid, and well-presented.

Content Style

I like books that are cookbook style. I used to think the same about books on topics like MDX and DAX. There is a place for books that teach the theory but often what people need once they get past the basics, are books that just say "if you're trying to achieve this, do this", and have a big list of recipes.

This book does that. Most of the topics are covered with walkthroughs that step you through how to do a task. I liked that approach.

Topic Coverage

This book covers a lot of topics. Given the title of the book was about ADF, I was really suprised to see the breadth of topics that were covered. The subtitle is A data engineer's guide to building and managing ETL and ELT pipelines with data integration. And that gives a clue to the fact that the coverage is much, much broader than ADF.

I was surprised to see so much coverage of pipelines in other places like Synapse Analytics, Fabric, etc. but more surprised to see coverage of HDInsight and big data concepts. I can't remember the last time I saw anyone using HDInsight. I always thought it was seriously over-hyped while it was being promoted, and still think the same way.

It made more sense to see a bunch of coverage of Databricks, delta tables and integrating ADF with Azure Machine Learning, Azure Logic Apps, Azure Functions and more.  They are relatively common areas of integration for ADF, along with migrating on-premises SSIS packages to ADF.

Note: in general, I don't like migrating SSIS packages to ADF in any way except rewriting them. Most of my customers never complain about the cost of using ADF. The only ones I hear complaining are people who use either the SSIS runtime for ADF or those using dataflows in ADF. (I don't like using those either)

Summary

The book is substantial, well written, and comprehensive.

What I really would have liked is more ADF content. I don't want the book to be larger, but for a book with this title, I'd prefer more depth on how to do things in ADF and less on other related but ancilliary topics.

7 out of 10

SQL Down Under show 89 with guest Erin Stellato discussing SQL Server and data-related tools is now published!

Another bunch of fun today recording a SQL Down Under show and it's now published!

This time I had the pleasure of discussing SQL Server and other data-related tools with Erin Stellato.

Erin is a Senior Program Manager at Microsoft and works directly with the tools that I wanted to discuss.I've known Erin quite a while and she's always interesting to hear from.

I hope you enjoy it. You'll find this show (and previous shows) here: https://podcast.sqldownunder.com/

SQL Down Under show 88 with guest Angela Henry discussing data types in SQL Server is now published!

I really enjoyed recording today's SQL Down Under show and it's now published!

This time I had a great conversation with fellow Microsoft Data Platform MVP Angela Henry.

Angela is a principal consultant at Fortified, and on LinkedIn, describes herself as a data mover and shaper.

Angela has a particular fondness for the Microsoft BI Stack and Databricks.

You'll find Angela online as @sqlswimmer.

I hope you enjoy it. You'll find this show (and previous shows) here: https://podcast.sqldownunder.com/