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.

 

 

 

 

 

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/

SQL: Suggestion for SSMS -> Save as table

I often look at the results of a query in SSMS and want to save them off somewhere, and what I really want is a table. To do that, at present, I need to:

  • Right-click and use Save Results As to go to a CSV
  • Use the flat file import wizard (or something) to import the CSV

Now obviously, in some cases, if it was a SELECT query, I could add an INTO clause and just run the query again, but there are many many cases where I want to save the outcome of another type of query. It could also be that I just can't run the query again for whatever reason.

CSV Pain

Going via a CSV has a whole host of other problems. We all know that outputting into a CSV and loading it again can involve a world of pain.

A real benefit of going direct is the data-typing. SSMS already knows the data type of the results, so it would avoid the entire mess of having to change column data types, etc. when importing CSVs. It's painful.

Multi-Server Queries

Where I've really come across this lately is with multi-server queries. In those cases (apart from configuring a whole load of linked servers that I don't want), there really isn't another good option, apart from the CSV method.

What's needed?

I'd love to see an option in the Results grid in SSMS, in the Save To area, that let you save to a table.

Agree? If so, you know the drill, vote once, vote often:

https://feedback.azure.com/d365community/idea/18481740-8aec-ee11-a73d-000d3adc65a4

 

SQL Down Under show 87 with guest Ronen Ariely discussing the importance of SQL Server internals is now published!

Today, another SQL Down Under show is published!

This time I had the great pleasure yesterday to record a podcast with an old friend from the SQL Server and data communities Ronen Ariely.

Ronen is a senior consultant and an applications and data architect with more than 20 years of experience in a variety of programming languages and technologies.

He was awarded as a Microsoft MVP seven times and is active in communities, mostly related to Microsoft Azure, Data Platforms, and Dot.Net programming.

Ronen is a moderator in the Microsoft forums, a member of the board at the Microsoft Learn Community, and administers several large data platform groups on social media.

A prolific writer of technical blogs, tutorials, and articles, he leads the Data GlobalHebrew user group and the Cloud Data Driven user group in New Jersey.

Ronen is also a co-admin of the Data Driven Community and the Principal Organizer of the Future Data Driven summit which is where I've mostly come across him.

In the show, we discuss important aspects of SQL Server internals that Ronen wishes people knew. You can find Ronen's blog here: https://ariely.info/Blog/tabid/83/language/en-US/Default.aspx
and you can find the call for speakers at the Future Data Driven Summit here: https://sessionize.com/future-data-driven-summit-2024/

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

SQL Down Under show 86 with guest Armando Lacerda discussing SQL Server 2022 snapshot backups and data virtualization is now published

And yet another new SQL Down Under show is published!

Once again, I had the great pleasure yesterday to record a podcast with one of my long-term data platform MVP friends Armando Lacerda.

Armando is a cloud architect and engineer focused on data platform.
He is a long-term data platform MVP, MCT, speaker, trainer and coder.

In the show, we discuss changes involving two aspects of SQL Server 2022:

  • Backups using snapshots
  • Data virtualization

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

SQL Down Under show 85 with guest Bob Duffy discussing building data warehouses is now published

And another new SQL Down Under show is published!

Once again, I had the great pleasure yesterday to record a podcast with one of my UK based friends Bob Duffy.

Bob is a Principal Data Architect at Prodata's SQL Centre of Excellence in Dublin, Ireland. He helps enterprise customers build large scale enterprise data warehouses in Azure, typically integrating ERP and financial systems.

Bob is a long-term Data Platform MVP, a Microsoft Certified Architect, Master, and Analysis Services Maestro. He holds a number of Azure role-based certifications. Bob also specialises in performance tuning, architecture, load testing, DevOps, Software engineering, MQ architecture, semantic modelling and meta data driven design on the MS platform.

Bob used to work for Microsoft Consulting Services a Senior Consultant and subject matter expert for BI/data in Western Europe in 2005.

In the show, we discuss the approaches he takes to building data warehouses on the Microsoft BI platforms.

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

Fabric Down Under show 7 with guest Philip Seamark now available!

Once again, I had the great pleasure to record a Fabric Down Under podcast. This time it was with a fellow "Down Under" guest Philip Seamark, from across the "ditch" (as we both call it) in New Zealand.

Phil is a member of the Fabric Customer Advisory Team and works as a DAX and Data modelling specialist.

He gets involved when enterprise customers need deeper technical support.

In this show, I discuss Phil's thoughts on Direct Lake which is one of the very new options that came with Microsoft Fabric. It adds another mode to Power BI, in addition to Import and Direct Query modes that have been there previously.

You'll find this show, along with the previous shows at: https://podcast.fabricdownunder.com

Fix: Getting R and Python to actually work on SQL Server 2022

One of my more surprisingly popular blog posts in the past talked about Machine Learning: Testing your installation of R and Python in SQL Server 2017. The problem is that SQL Server 2022 changed things.

Now the SQL Server team has published an article on how to make it work.  What I've been finding though, is that people are struggling to follow that article. And more concerning, people using named instances of SQL Server (like SQLEXPRESS) couldn't get it to work at all, no matter how much they tried.

It took me a while to work out what was needed, so I figured it was time to share it with others. I've also submitted a few pull requests to get that page updated. So, by the time you read this, it might already be updated.

What you need to do for R and Python on SQL Server 2022

You need to start by installing SQL Server and making sure you choose at least BOTH the Database Engine Services and the Maching Learning Services and Language.

Unlike earlier versions, the R and Python libraries are not installed or configured for you. If you try to execute R code at this point, even after configuring SQL Server, you will see this type of error:

The error for Python is similar:

I've included both in case someone is searching for this. At least it tries to link to a solution, even though the link is still a docs link not a Learn link. (I'll put in a pull request for that too)

Getting R to work

Let's start by getting R to work.

You need to download and install the latest R 4.2. I got this from https://cran.r-project.org/bin/windows/base/old/4.2.3/ and I installed it with all default options.

R download

You then need to install some standard R dependencies. You do this by running RTerm. I found it here:

Location of RTerm

I right-clicked and ran as administrator, just in case. Then you execute this code step one row at a time, making sure that each installs ok. Note that on the first one you install, you'll probably be prompted for a CRAN mirror site. Pick one near you.

Then there are some Microsoft packages that need to be installed.  Execute these statements one at a time, making sure that each works. You can ignore warnings at this point. (And note this is two lines even though they show line-wrapped here)

Now the first challenge

The next step is where things went wrong. You need to find a program called RegisterRext.exe. For me, it was in this folder: C:\Program Files\R\R-4.2.3\library\RevoScaleR\rxLibs\x64

RegisterRext location

You open a command prompt (cmd.exe) as an administrator. Then you need to configure the instance of SQL Server with the version of R. The article says you do this by executing:

That works fine if you have a default instance of SQL Server and if it's called MSSQLSERVER (the default name). Not so much if you have a named instance. I was trying this on a machine with a SQLEXPRESS named instance as I'd heard this was an issue.

The article didn't give you any idea or example for a named instance. So I tried all the things I normally would for a named instance, including not knowing if I needed to escape the backslash:

  • ".\SQLEXPRESS"
  • "MACHINENAME\SQLEXPRESS"
  • ".\\SQLEXPRESS"
  • "MACHINENAME\\SQLEXPRESS"

and so on. I was about to give up when I finally just tried "SQLEXPRESS". And that worked ! I understand why people are confused. Nowhere else in SQL Server land do you refer to a named instance that way.

So the command needed in that case was just:

Configure and test R in SQL Server

Then it's time to test the SQL Server end.  You need to start by executing:

And you then need to restart the SQL Server service.

Next you can see if it's working. Execute the following in SQL Server:

That should show you 1 as a response.

But no cigar yet

All good so far. So, then I tried an actual R query, as suggested in the article:

Well, I didn't get a great response with that. It should have worked at that point but instead, after quite a while, it returned this:

OK, that looked a bit scary. I started spending ages trying to work out what on earth was wrong with sqlsatellite.dll.

And the weird thing? After a while, and a few more attempts, it just started working. No clue as to why it didn't just work the first time. But I've now seen that on two machines, so it's no coincidence. Be patient. Eventually it returned:

More complex R query

I then was able to execute something more complex:

And R is working

And then R is working !

Python

Then it was time to see how I went with Python. Once again, not as smooth sailing as I'd hoped.

Again, you need to download Python installer. But the link for Python 3.1 that's included in the article goes to a page that now tells you that it's too old to have binaries available. (For Python apparently that means less than a year old). I started to see if it would work with Python 3.11 (the earliest with binaries) but eventually scared myself off, as I knew that wasn't going to be good.

I searched for "Python 3.10 for Windows" and ended up with a link at the same site, and it did have binaries. That link was:

https://www.python.org/downloads/release/python-3100/

I downloaded it, but importantly, I chose a custom install. (Very easy to miss on the first page). The main thing you need to change is the install folder. It will want to install it in one of your private folders by default, but instead, choose C:\Program Files\Python310 as the install folder. Near the end of the install I also chose hte option to Disable the path length limit. I doubt that's needed here but I've run into it before so I decided to choose it.

Next in an admin command window (same one as before if it's still open), you need to step through these three commands:

That took a little while but was all good.

Another challenge

You then need to set some permissions on folders. The instructions say to execute this command:

Again though, that assumes that the service that's running your SQL Server Launchpad service is "NT Service\MSSQLLAUNCHPAD".

Mine wasn't.

You can find what it is (if you don't remember from when you installed it), by opening (just Windows start/run) services.msc and looking for the service.

services.msc output

Being a standard SQLEXPRESS installation, mine was actually "NT Service\MSSQLLaunchpad$SQLEXPRESS", so instead of the above command, I needed to execute:

That then worked ok. We also need to set one more permission (and it's unrelated to your instances):

We're not done yet

At this point, we're back to needing to use a different version of RegisterRext.exe to configure the SQL Server instance for the version of Python.

The instructions say to execute:

Once again, that's not going to work for my SQLEXPRESS named instance, so I needed to execute this instead:

Now it's time to test Python in SQL Server

Because you've already enabled external scripts in SQL Server, if you've previously installed R, you don't need to enable them again.

Instead, I tested Python:

And that worked:

Simple Python test

As did a more complex query:

More complex Python query

And we're finally done !

I hope that helps someone else get started. (I've also pushed pull requests to the Learn site to get some of this updated. Hopefully it will be by the time you read this).

 

 

 

SQL: Understanding Change Data Capture for Azure SQL Database – Part 5 – Accessing CDC from another database

This is the final part of a series on using Change Data Capture with Azure SQL Database, and looks at accessing the data from change data capture from another database.

Scenario

I often use Azure SQL Database as a data warehouse. It's a perfectly fine database for that. But the challenge with Azure SQL Database is always about how to access data in other databases.

Many people don't seem to realize that you can use External Tables (and External Data Sources) to read data in one Azure SQL Database, in another Azure SQL Database.

In an earlier post, I talked about how you can access the list of logins in the master database from within an Azure SQL Database. I'd seen far too many people who were told that wasn't possible.

In that post, I showed how to set up a database scoped credential, an external data source, and an external table, so I won't repeat that detail here. Please refer to that post for specifics.

With Change Data Capture (CDC), what I'm often needing to do is this:

Two databases with arrow linking them

I have a source database (probably an OLTP database) and have enabled CDC on some tables. But I want to drag the data changes across to a staging area in the data warehouse database.

I wish the product team had made this easy but, at present, it isn't.

The Challenge

I can create an external table in the data warehouse database, that points to a table or a view in the source database. What I want to access though, is a table-valued function like the one I described in part 3:

cdc.fn_cdc_get_net_changes_dbo_NewEmployees

But it requires parameters, most importantly a @FromLSN and a @ToLSN. But I can't pass parameters to an external table. (I wish I could).

So how do I create a way to read from these functions, when I can't pass parameters, and I don't want all the data since the beginning of time?

There are two ways to tackle this.

Approach 1

In the past, here's what I've been doing instead:

Step 1: Local table

I create a local table, let's call it DataLoad.SourceConfigurations, and it has at least two columns for FromLSN and ToLSN. (Note these will be binary columns).

Step 2: External Table in the Source Database

I then create an external table in the source database, that points to that table that I just created in the data warehouse database. Let's call it DW.SourceConfigurations.

Step 3: View in the Source Database

I next create a view in the source database that reads from the DW.SourceConfigurations external table, to get the FromLSN and ToLSN values and uses them to call the CDC table-valued function. Let's call the view cdc.get_required_changes. I'll leave it in the cdc schema along with the standard functions. (Obviously I could have put it elsewhere).

The beauty of this, is that I can now control what's returned by this cdc.get_required_changes view, by updating the values in the DataLoad.SourceConfigurations table in the data warehouse database.

Step 4: External Table in the Data Warehouse Database

The final setup step is then to create an external table, let's call it DataLoad.RequiredChanges, in the data warehouse database, that points to the cdc.get_required_changes view in the source database.

End Result

Once we've done this, we have a workable solution. I can process changes from the source database in the data warehouse database almost as though they were local CDC changes.

I just put the LSN values that I want into DataLoad.SourceConfigurations, and then read rows from DataLoad.RequiredChanges.

Approach 2

A more recent, and simpler approach is to wrap the call to the CDC functions in a stored procedure.

We can now call a remote stored procedure by using

sp_execute_remote

This option appeared well after the other options were available. We'll still need a local table to store where we are up to, but we can just pass the values directly to the stored procedure, which can make the SELECT statement against the CDC function.

I hope this helps someone get started with this trickier aspect of using CDC in Azure SQL Database.

  1. Why use Change Data Capture for Azure SQL Database?
  2. How Change Data Capture works in Azure SQL Database
  3. Enabling and using Change Data Capture in Azure SQL Database
  4. Change Data Capture and Azure SQL Database Service Level Objectives
  5. Accessing Change Data Capture Data from Another Azure SQL Database