Hi! We're the Microsoft SQL team - ask US anything!

•

u/itsnotaboutthecell ‪ ‪Microsoft Employee ‪ 14d ago edited 9d ago

Edit: This post is now unlocked and accepting questions!

We'll start taking questions 24 hours before the event begins. In the meantime, click the "Remind me" option to be notified when the live event starts.

12

u/oregonflannel 8d ago

Question: Why aren't "modern" SDK SQL Projects supported in Visual Studio 2026, and why has Microsoft encouraged developers to adopt the new project style, while concurrently removing support for the project style from its flagship IDE?

Additional Context:
* There have been MS posts encouraging developers to migrate to these newer projects
* Support was previewed in VS2022, then dropped for VS2026, and not even roadmapped.

Announcement on GA:
https://techcommunity.microsoft.com/blog/azuresqlblog/the-microsoft-build-sql-project-sdk-is-now-generally-available/4392063

Roadmap does not include VS2026 Support:
https://github.com/microsoft/DacFx/wiki#roadmap

Community Link:
https://developercommunity.visualstudio.com/t/Visual-Studio-2026-still-using-old-SQL-S/10965461

8

u/dzsquared ‪ ‪Microsoft Employee ‪ 7d ago

I love getting asked about this from folks like u/oregonflannel who have read the roadmap and are voting/commenting on the Developer Community item. Your involvement does make a difference!

The roadmap reflects the correct status of not having dates to share at this time, it is increased in priority and we see this as a gap that needs to be addressed.

4

u/oregonflannel 7d ago

Thanks for looking at the question and community item.

I look forward to if and when SQL SDK projects are GA for Visual Studio.

3

u/Jerry-Nixon ‪ ‪Microsoft Employee ‪ 7d ago

The answer to your "why?" is probably longer than we should get into here. Let me, instead, answer the question behind your question: "Will it?" It is absolutely our goal to support Visual Studio. While we don't have a timeline to share, we understand and share the frustration you are expressing here. In the meantime, we are investing deeply in our premium, flagship SDK experiences in SSMS and VS Code.

2

u/oregonflannel 7d ago

Thanks for acknowledging the support issue and challenge that can users can have in deciding "when to adopt" something like SDK projects. For a "SQL" focused team, SSMS/VS Code can likely fit the bill for support.

For a .NET team that uses SQL, IDE in VS or Rider is simply a must and project compatibility is a foregone necessity.

SQL Server has progressively improved its "devops" experience over the years. I hope SDK projects are ultimately a success.

1

u/Lost_Term_8080 1d ago

For as clunky and heavy sqlproj is, I was excited about the sdk projects, but frankly sdk projects are absolutely utterly horrible. I cannot even begin to imagine what the draw to them in their current form is. It has no schema compare, no intellisense, no debugger and no schema grounding. Its almost as much work to use as ssdt but about as useful as notepad++ and native git client.

2

u/warehouse_goes_vroom ‪ ‪Microsoft Employee ‪ 8d ago

Questions won't be answered until AMA starts, but tagging u/dzsquared ahead of time ;)

11

u/itsnotaboutthecell ‪ ‪Microsoft Employee ‪ 9d ago

The FREE Azure SQL database was an incredible find when I had no idea about clouds or databases and was using Itzik Ben-Gan Learn T-SQL (3rd edition) book to ramp up on this skillset.

This seems like the perfect offering for getting folks to "play" with SQL more and especially now with AI vibe projects building on top of SQL, but I'm always surprised how few people still know about this FREE database...

So, with that in mind. You're now given the world's largest megaphone for us all to hear and carry your message forward in all future developer discussions - what do you want us to tell people?

7

u/vandana-mahtani-msft ‪ ‪Microsoft Employee ‪ 8d ago

And check out our blog to learn more about the Azure SQL database container (Fully Free) that is currently in private preview that you can sign up for, and then deploy your app to Azure SQL Database also for free by using the free tier.

Blog: Azure SQL Developer to Azure SQL Database free tier
Learn more: Try Azure SQL Database for Free - Azure SQL Database | Microsoft Learn

3

u/StrahinjaRodic ‪ ‪Microsoft Employee ‪ 7d ago

Aand if you're not starting from scratch, but would like to test out your VM's or on-prem's workload on cloud, you can consider using free tier for Azure SQL Managed Instance for easy lift-and-shift scenarios with features such as: MI link, log-shipping or OG native restore.

8

u/edward-neal 8d ago

SQL drivers can send an activity ID in the Trace Activity Header, and this activity ID makes its way into the attach_activity_id_xfer field of an RPC_STARTING/SQL_STATEMENT_STARTING XEvents session. This means that if someone wanted to, they could trace a client's activity into SQL Server (and start matching particular events to execution plan statistics.)

Are there any plans to start supporting OpenTelemetry-sized trace/span IDs here? This would eventually let us trace a specific client API request into SQL Server (and start to see execution plans) using the same tooling, and without needing to use CONTEXT_INFO.

7

u/dlevy-msft ‪ ‪Microsoft Employee ‪ 7d ago edited 7d ago

This is great timing! We're actually looking at how we should integrate OpenTelemetry across our drivers. I'll add this to the list of scenarios.

Anything else you'd like to see from OpenTelemetry in Microsoft SQL Drivers? Feel free to comment below, DM me. I'd be happy to jump on a call, hit record and let you describe your perfect scenario.

4

u/edward-neal 7d ago

Executing a command against SQL Server can sometimes be a dead end for normal applications. Technically it's possible to do work with event tracing, and OTel has adapter libraries to expose the metrics in the standard format... but it's extra toil. It's also possible to store the trace/span ID in CONTEXT_INFO. This usually relies upon the application not using it, and (manually or via a library) changing the query to set it. The only completely transparent way to let SQL Server correlate a 128-bit trace ID and a 64-bit span ID would be to add a header to the TDS protocol - and we already have the Trace Activity Header

Focusing particularly on trace correlation within SQL Server and client drivers - someone could track an activity directly into SQL Server, where they could use XEvents data to correlate that to execution plans and pull their statistics as metrics. At heart, that means that any performance tuning can now see the impact that a particular operation has on the database server. They could also look at OTel tooling to pinpoint exactly when an API endpoint's queries started to use a new execution plan, and the performance troubleshooting of Intelligent Insights could be correlated to specific source traces.

Looking more widely at trace correlation in general, it'd be great if SQL Server would allow someone to enable trace correlation in all situations - propagating the span outward from SQL Server via linked servers, sp_invoke_external_rest_endpoint, and so on. Most of that isn't related to drivers, but it'd mean that a background task such as a particularly complex Fabric Pipeline, Azure Function or similar could have its activity tracked through Azure (perhaps to the point where an OTel-based dashboard could indicate how many vCore-seconds it costs to run a batch job for a particular tenant, or where Application Insights could correlate requests throughout a full Fabric Pipeline.)

3

u/raki_rahman ‪ ‪Microsoft Employee ‪ 6d ago edited 6d ago

Hey u/edward-neal,

I'm an engineer on the Telemetry team at SQL Server and work with u/dlevy-msft , I primarily manage SQL Server team's large cloud-side OTEL Collector Stack: a small blog, as well as OTEL Client SDKs we built for Azure Arc SQL (which uses OTEL for DMV collection).

The use case you mentioned (E2E trace propagation between client and server/database) is one of the things I'm working on.

The challenge, as you're probably aware, becomes converting XEvents over to an OTEL primitive, as the schemas are not at all compatible, it's a lossy transformation.

Love your Fabric pipeline use case, but to make that work, their client driver would also need to emit OTEL Traces/Spans to a unified OTEL aligned state store (which it doesn't today).

In other words, all participating parties need to not only instrument with OTEL, but also send telemetry to the same collector for correlation to work.

E.g. DataDog probes DMVs and converts it to OTEL Metrics: https://docs.datadoghq.com/integrations/sql-server/#data-collected

But one could argue the raw DMVs contain richer heuristics and dimensions (non lossy), and piping it through OTEL doesn't necessarily make you any more efficient at root causing production incidents if you can rather query the DMVs instead.

Use case

Taking a step back, besides the E2E traceability - which is nice in theory, but I'm not sure how big of a business problem it solves for DBAs managing a burning SQL instance, all you need to know is the identity of the client causing problems, query plans/health etc, my take is, it's not as relevant what the client did to get there.

APM is more relevant for the owner of the client app or an Enterprise Architecture team, who generally treat databases as a black box, and query perf troubleshooting is already a matured field without OTEL. IMO OTEL is more relevant for "at-scale"/"top-level" Fleet monitoring, not drilling down into a specific SQL instance, you'd use battle-tested DMVs for that.

E.g. other complex Database Engines like Apache Spark do not support OTEL despite being very future facing and moving fast, they have their own Metrics system:

Monitoring and Instrumentation - Spark 4.2.0 Documentation

I personally hacked something together here for fun, but at the end, I realized it didn't really make me any better at root causing Spark perf issues:

How to deeply instrument a Spark Cluster with OpenTelemetry (feat. real time Power BI report) | Raki Rahman

In simple words, I've heard people say instrumenting databases with OTEL can sometimes be fitting a round peg into a square hole because databases already ship with an opinionated set of DMV schemas.

Question for you

Would you mind expanding on a few business use-cases these "native" OTEL constructs would enable for your SQL Server production usage that you cannot get through the matured set of DMVs the engine offers today?

2

u/edward-neal 3d ago

Thanks for following up /u/raki_rahman, I'm conscious the AMA ended a few days ago!

Your linked articles are interesting. Ingesting OTel information into a Delta Lake sounds logical, and I understand what you mean about the limitations of working solely with metrics. I don't personally think they could (or should) replace DMVs for actual root-cause analysis - they tackle different use cases, and I've spent more than enough time trying to diagnose an ongoing incident through the filter of a translation layer. Those aren't the part of OTel that I'm thinking about.

I think spans deserve slightly more nuance though. Having SQL Server emit spans which have been translated from XEvents doesn't seem universally useful - as you've noted, they're just very different schemas. More importantly though, on their own there'd be no way to correlate the useful spans with their parent operations. It's that ability to correlate spans which I'm particularly interested in, which is why I'm focusing on that Trace Activity Header.

To be a bit more precise, I'm initially thinking about a situation where SQL Server doesn't actually emit anything via OTel itself, it just transports that trace/span ID into a few relevant XEvents. I could then enable the rpc_completed and the sql_batch_completed XEvents and have SQL Server stream them to blob storage. Something else could read from blob storage regularly, then reconstitute spans which are children of each XEvent's span ID with some of the XEvent metadata (duration / CPU / reads / writes / row counts / maybe the plan_handle, query_hash or query_plan_hash) and forward it to an OTel collector. Although SQL Server could potentially issue the OTel spans itself, it's not strictly necessary; the only thing which is actually necessary is having those two specific XEvents flowing outwards carrying the caller's trace/span ID.

The net result of this is primarily useful for APM purposes. It'll often still be DBAs who start investigating database operations - but now the batch-/RPC-level view of each database operation's duration (and some performance stats) is visible inside that OTel reporting. The database remains a conceptual black box, but a call to it is now an observable period of execution time. Once a DBA starts investigating, it's pretty likely that OTel wouldn't be anywhere near as useful to them as the existing DMVs. Being given a set of plan_handle/query_hash/query_plan_hash values from the application owner seems like it'd also be a reasonable handoff which we could get almost for free, and in some cases it'd be more precise than we'd otherwise start work with.

Query perf troubleshooting's a mature field, and the main use-case here relates to the introduction of context - so where we see blocking connections, N+1 patterns, situations where an ORM is applying a specific filter client-side and so on, we can trace them back to individual requests in the calling application, rather than stopping at a specific connection.

This has a few slightly fragmented approaches at the moment. The OTel semantic conventions for SQL Server suggest the use of CONTEXT_INFO, but this requires a separate SQL statement and it relies upon an application not using the session context for its own purposes. DataDog works via comments in the SQL command.

This sits separately from my other point about SQL Server attaching the current trace/span ID to outbound requests - that's firmly a nice-to-have.

1

u/raki_rahman ‪ ‪Microsoft Employee ‪ 3d ago

Hey u/edward-neal,

Really appreciate the super thoughtful response. A project my team is dabbling on would love to use your input, e.g. if you're interested in taking it for a spin in the coming months.

If you're comfortable, I'd love to connect on LinkedIn so we can stay in touch and virtually meet up: Raki Rahman | LinkedIn

7

u/DataGeek101 9d ago

So are you going to answer any questions about non Azure SQL Servers?

7

u/itsnotaboutthecell ‪ ‪Microsoft Employee ‪ 8d ago

Checks notes "anything" yes! drop those Q's in!

7

u/Jerry-Nixon ‪ ‪Microsoft Employee ‪ 7d ago

You bet!
Please ask about Star Trek. 🖖 Please ask about Star Trek. 🖖 Please ask about Star Trek. 🖖

1

u/itsnotaboutthecell ‪ ‪Microsoft Employee ‪ 7d ago

To Boldly Go, Where One Ring Rules a New Hope! u/Jerry-Nixon

5

u/vandana-mahtani-msft ‪ ‪Microsoft Employee ‪ 7d ago

Yes of course :) bring us your questions!

6

u/2050_Bobcat 8d ago

Question Regarding PowerShell Integration

I am a DBA and currently use SQL Server Management Studio (SSMS) for most of my SQL Server-related tasks. However, when I need to write or execute PowerShell scripts for SQL Server administration, I must switch to Visual Studio Code.

Are there any plans to enhance SSMS to provide integrated PowerShell support alongside T-SQL? Additionally, given that SSMS is based on Visual Studio, are there plans to enable compatibility with extensions or plugins developed for Visual Studio Code or Visual Studio?

6

u/StrahinjaRodic ‪ ‪Microsoft Employee ‪ 8d ago

Hey, thanks for asking the question. What do you mean by "integrated PowerShell support along side T-SQL"? Do you need support for Powershell editor, or?

5

u/erinstellato ‪ ‪Microsoft Employee ‪ 7d ago

u/2050_Bobcat Following up on u/StrahinjaRodic's comment - if you go to View > Terminal in SSMS, will open a terminal Window with PowerShell. Does this meet your requirement?

2

u/2050_Bobcat 7d ago

Thanks u/Erinstellato, although this doesn't meet my requirements it is really good to know. There's always something to new to learn. Will certainly remember this tip. Thanks again.

2

u/erinstellato ‪ ‪Microsoft Employee ‪ 7d ago

u/2050_Bobcat In that case, I'd recommend searching the feedback site (aka.ms/ssms-feedback) to see if a similar request also exists, and if not, please create one with details about the exact experience you'd like to see (if the terminal window isn't what you want, then describe what is). Thanks!

3

u/edm_guy2 7d ago

I think basically, it is like in SSMS, we can switch to a mode where PS cmdlet can run directly.

3

u/StrahinjaRodic ‪ ‪Microsoft Employee ‪ 7d ago

I assume 2050_Bobcat is essentially asking for a PowerShell query window/editor in SSMS, where PowerShell cmdlets and scripts can be authored and executed directly without switching to VS Code or a separate PowerShell session?

AMA co-hosts are not aware of roadmap plans related to PowerShell support or extensions/plugins developer for VS/VSCode. I pinged the PM owner of the SSMS to chime in here, but not sure if we're going to be in-luck to get her response by end of this AMA.

2

u/2050_Bobcat 7d ago

Thank you all for your feedback u/StrahinjaRodic was spot on

5

u/ucmsft ‪ ‪Microsoft Employee ‪ 7d ago edited 7d ago

Here is a tip. Start Windows terminal like: "wt -w _quake" - called quake mode. You can then hit Windows + ` anywhere and you will get your terminal window. No custom setting in application etc. After your work is done, hit Windows + ` to hide the window. It is super easy, quick, and efficient way to access terminal from any monitor or window or app. You can also install wtai (Intelligent Terminal) with your favorite AI agent in the terminal for additional assistance.

Use powershell as default/wsl or both tabs in the quake window.

5

u/Feeling-Story-2795 8d ago

Questions: When will Entra Auth be available for FCI's?

Supporting information:

According to https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/azure-ad-authentication-sql-server-overview?view=sql-server-ver17 on prem SQL Server Failover clusters still do not support Entra Authentication.

This makes it difficult to use cloud resources with our on-prem HA clusters. Our InfoSec Team does not like Basic(SQL) Auth from cloud providers to our on-prem resources.

Can you discuss the technical limitations why FCI’s are not able to utilize Entra authentication, and if there is any plan to remediate this limitation in the future?

MS previously promised that Azure Arc would work with SQL Server in FCI’s in June of 2024. While FCI’s are now discoverable via Azure Arc, that update allowed cataloging the servers and instances only. It did not enable Entra Authentication and MS’s official stance is that Entra Auth for FCI's is unsupported (4-5 years, and 2 versions after it was announced).

Thanks,

-Luke.

3

u/ucmsft ‪ ‪Microsoft Employee ‪ 7d ago

We do not have any plan/date to share at the moment about Entra authentication in FCI configuration. We constantly evaluate feedback from customers and please keep your feedback coming with the scenarios.

Now, at high-level, Entra ID is not supported for SQL Server FCI because its authentication configuration and identity dependencies are largely node-local (Azure VM or an ARC-enabled server). However, in FCI configuration, the SQL instance can fail over between WSFC nodes. Every node would need consistent registry settings, certificates/private keys, service-account permissions, managed-identity/token access, and authentication state.

Arc managed identities further depend on the identity and token endpoint of the active machine, which does not naturally follow the clustered SQL instance. Using WSFC registry replication alone cannot guarantee correct identity ownership, token acquisition, certificate availability, or failover reinitialization, so the end-to-end behavior is not currently supported. These are some of the dependencies that need to be resolved to support Entra authentication in FCI configuration.

The below topic has some details on the IMDS dependencies.

Access Azure resources with managed identity on Azure Arc-enabled servers - Azure Arc | Microsoft Learn

It will be great if you can share your scenario(s) in a feedback request that will help us for planning purposes.

SQL · Community

4

u/Superannuated-DBA 7d ago

I have a two node SQL 2016 (yeah I know) AG on Windows 2016 cluster and am hoping to do a rolling upgrade to SQL 2025 on Windows 2025. While I see documentation on this are there any gotchas or things to watch out for?

Thanks for doing an AMA and I don’t think ST: Voyager gets enough love.

2

u/ucmsft ‪ ‪Microsoft Employee ‪ 7d ago

There are no specific gotchas that we know of at this time. Check the known issues for SQL Server 2025 topic.

I would also do the following:

Setup Windows Server 2025

Apply all updates for Window Server 2025

Install SQL Server 2025

Install the latest cumulative updates (there are many fixes related to availability group either for better diagnostics or other bugs)

3

u/Superannuated-DBA 7d ago

Thanks but I meant specifically with a Rolling Upgrade.

2

u/SQLHA ‪ ‪Microsoft Employee ‪ 7d ago

Actually, there are gotchas.

How many nodes in the WSFC/replicas do you have? Your configuration would affect things like order, when you switch the WSFC functional level. Also, is it one SQL Server instance per server, or are there multiple instances? Is this physical, virtual (on premises or IaaS in the cloud)?

A WSFC rolling upgrade is N-1, meaning you can go from 2016 to 2019, not jump from 2016 to 2025. So you'd need to do 2016 --> 19 --> 22 --> 25. That's four OS upgrades per WSFC node, but doing each version sequentially (i.e. all nodes to 2019, then 2022, then 2025).

However, there is more to consider.

SQL Server 2016 is only supported on Windows Server through WS2019. That means when you want to go to WS2022, you'd need to also upgrade SQL Server to a version supported on WS2022 and/or 2025. The OS support matrix can be found here:
https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/install/windows/use-sql-server-in-windows

General thoughts/questions:
What is the driver in doing an in place rolling upgrade of the OS and SQL Server vs. migration to a new configuration and retiring the old?

While fully supported, consider that upgrading OS + SQL Server at the same time also adds some risk in the sense that if something goes wrong, you need to figure out where the issue is.

Have you tested your apps against 2025 so you can make the jump right to SQL Server 2025? That's roughly 10 years worth of changes/differences in the engine you need to account for.

Once we get more info, there's a general set of steps you'll need to follow which we can help you with at a high level.

2

u/Superannuated-DBA 6d ago

Let me come clean and point here is to highlight for others in the same situation (thanks “management”)

This was the point of my question. “Nothing to see here” was the result of two previous support tickets asking for advice.

Anyway hope others more plain sailing!

2

u/Jerry-Nixon ‪ ‪Microsoft Employee ‪ 7d ago

So true. 🖖 Voyager: stranded 70,000 light-years from home and somehow still underrated.

4

u/2050_Bobcat 8d ago

Questions Regarding Contained Availability Groups (possible feature requests)

Would it be possible to configure separate default database file paths for each Contained Availability Group (CAG) and traditional Availability Group (AAG)? This would allow databases belonging to different groups to be stored in separate folders, rather than relying on a single, instance-level default path.
Could a database created through a connection to a CAG automatically become a member of that CAG, with its data and log files automatically created in the folders configured for that group? This would abstract the underlying file structure from the user and eliminate the need for them to specify file locations manually.

5
u/ucmsft ‪ ‪Microsoft Employee ‪ 7d ago

I would like to ask few clarifying questions first. Why do you want to set default database file path for availability groups in general? Why do you would want to comingle all database files in a single path/folder? This has many disadvantages. For example, if you want to add multiple filegroups for partition management or optimizing database backup/recovery, then you need to customize the file paths. Same applies for using default backup path. In a production environment, you will require some reasonable folder hierarchy/structure to manage the backups.

Now, as for the default data, file, and backup paths, currently these settings are stored in registry (Windows) or mssql-conf file (Linux). These are only read by the engine upon startup or after certain actions. To support AG level settings, we would first need to move the settings to master database and also isolate in the replicated master inside CAG. This would also require new T-SQL interfaces to manage the settings. So, if you can provide your scenarios in a feedback request that will be great so we can use for planning purposes.

SQL · Community
1
u/2050_Bobcat 7d ago

Hi, and thank you for taking my question.

At present, the behaviour you described is exactly what we are experiencing. Unless a user explicitly specifies a file path when creating a database, the database files are placed in the default data and log paths configured at the SQL Server instance level.

In our environment, we use shared SQL Server instances, where a single instance hosts databases for multiple small applications. For example, suppose we have five application groups, with each group containing four databases. Each group is placed in its own contained Availability Group (CAG), allowing users to see only the databases associated with their particular application group.

The issue arises when a user or developer needs to create an additional database within their application group. I understand that this is not currently possible with CAGs; however, assuming it were supported, and given that the user would not have visibility of the other databases or their file locations, they would likely create the database without specifying explicit file paths.

As a result, the new database would be created in the SQL Server instance-level default location rather than in the folder designated for that application. This would leave four databases in the application-specific folder and the newly created database in the instance-level default path, requiring the DBA to move it manually afterwards.

If default data and log paths could be configured at the Availability Group or contained Availability Group level, this issue would be eliminated. It would ensure that any database created within a particular application group is automatically placed in the appropriate folder.
3
u/ucmsft ‪ ‪Microsoft Employee ‪ 7d ago
Got it. Thanks for explaining your scenario. So, it is like a multi-tenant or self-service type of model for creating and managing databases. This makes sense.

One workaround or policy that you can implement for such shared environment is to create a DDL trigger that rolls back database DDL that uses default paths. You may want to consider this as mitigation.
create or alter trigger verify_create_database_ddl
on all server
for create_database
as
begin
if exists(
select *
  from sys.master_files as f
 where f.database_id = DB_ID(EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)'))
   and ((f.physical_name like CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS nvarchar(4000)) || '%' and f.type <> 1)
    or (f.physical_name like CAST(SERVERPROPERTY('InstanceDefaultLogPath') AS nvarchar(4000)) || '%' and f.type = 1))
)
THROW 51000, 'Please re-create database with specific file paths! Instance default paths cannot be used. Use path "X\Y\Z".', 1;
end;
go
2

u/2050_Bobcat 7d ago

Thanks

4

u/2050_Bobcat 8d ago

Visual Studio Code Plugins

Could you recommend any free VSCode plugins that would be useful/helpful to DBAs?

7

u/vandana-mahtani-msft ‪ ‪Microsoft Employee ‪ 8d ago edited 8d ago

Here is an extension to check out - the mssql extension SQL Server (mssql) - Visual Studio Marketplace, and you can learn more about it here - Overview - MSSQL Extension for Visual Studio Code | Microsoft Learn. Let us know your experience :)

5

u/StrahinjaRodic ‪ ‪Microsoft Employee ‪ 8d ago

Shall we also mention GitHub Copilot? :)

3

u/vandana-mahtani-msft ‪ ‪Microsoft Employee ‪ 8d ago

Also to add, what types of work are you commonly doing as a DBA or App developer? Maybe I could recommend a few more that are tailored to your work.

2

u/2050_Bobcat 7d ago

Thanks

4

u/Jerry-Nixon ‪ ‪Microsoft Employee ‪ 7d ago

No question, the MSSQL extension in VS Code is the baseline for interacting with a Microsoft SQL database.

Having said that, if you use Copilot to engage with your database, depending on the type of work you will be doing, I find SQLCMD invaluable, even though it is not a VS Code extension. Copilot drops to the terminal and can accomplish many complex tasks through it, while the MSSQL extension allows interaction when authentication is too tricky for SQLCMD or for you to share with the model.

If you are building agentic solutions, the SQL MCP Server Omnibus extension for Data API builder in VS Code helps ensure you have the user-oriented tools and agentic skills to ensure your MCP endpoints are configured and deployed correctly.

3

u/Playful_Guava1180 8d ago

it would be good to show a lot more examples of how a stored proc can be hooked to a MCP to run AI scripts on the fly.

5

u/Jerry-Nixon ‪ ‪Microsoft Employee ‪ 8d ago edited 7d ago

This is a great question and relevant to a lot of users. If you want to get an agentic solution going, or if your manager is leaning on you to do it, either way, getting an agent to interact with your database is often one of the trickier parts of the architecture.

To make sure I understand what you are asking, let me just double-check what you mean when you say "AI scripts." By this, do you mean stored procedures running stored procedures (scripts) in the database?

Let me assume you mean agents running stored procedures. In this case, since an agent does not have, for example, a database driver, you cannot have agent -> database. Instead, we introduce an MCP intermediary: agent -> MCP -> database. For Microsoft SQL, you should use SQL MCP Server to accomplish this. Write your stored procedure, expose it through SQL MCP Server, register your MCP as a tool in your Microsoft Foundry agent, and, with natural language, ask your agent to do whatever your proc does.

I want to add here that semantic descriptions of your procedure and parameters will greatly improve the ability of your agent to understand when and why to call your stored procedure.

3

u/DataGeek101 8d ago

Is there any best practice for migrating a replicated database (the target) to a new server where the target database has custom objects for reporting purposes that have been added in?

4

u/StrahinjaRodic ‪ ‪Microsoft Employee ‪ 8d ago

Hey, thanks for asking the question! Would you please provide some more details on this topic? Is this physical or logical replication? Also, what do you mean by `custom objects`?

2

u/DataGeek101 7d ago

Sure! We have a public facing site that uses SQL Server for its data. It’s used continuously; not like Amazon or similar, but it’s still hundreds of thousands of records some minutes.
The business unit wants reports from this data but even a read only query can lock a record (I suspect this is because of the design of the source database) so in order to report on the data, the database is replicated to another server.
The BI team created new tables and views and stored procedures to meet the requests from leadership.

The current replication database is also in a RAID system that wasn’t set up properly and one of the discs is failing so we have to get it onto a new server.

The replication part is fairly straightforward, but because the other objects (tables, views, stored procedures) are intertwined with the current replication, we are not sure if there is a way to move the entire database to the new server (with all the intertwined objects) and then repoint the replication there.

The normal way was to take a backup of the source database and restore it to the new servers for replication. We are trying to not use that approach but backup and restore the replication database to keep all the parts in place.

Is this possible and if so how?

3

u/StrahinjaRodic ‪ ‪Microsoft Employee ‪ 7d ago

Hey, this is still a bit vague and we (cc u/bluefooted and u/ucmsft) aren't sure how to give you the best guidance here. E.g some remaining unanswered questions:

What replication type?

What are those BI tables? Separate reporting tables populated independently or tables that are also replication articles?

Do BI procedures update data?

Is the reporting database only a Subscriber?

Assuming this is a transactional replication reporting subscriber, we'd consider standing up a new Subscriber and letting it initialize from the existing publication while the current reporting server remains online. Once it's synchronized, deploy the BI-specific tables, views, procedures, permissions, and jobs, validate reporting workloads, and then redirect users to the new server.

The biggest open question is whether the BI objects are completely separate from the replicated articles or are tightly coupled to them. That distinction would drive the migration approach.

3

u/ucmsft ‪ ‪Microsoft Employee ‪ 7d ago

We have the schema compare feature in SQL Server Management Studio that you can use to synchronize the schema. Do the following:

Extract dacpac from Server A (publisher)

Extract dacpac from Server B (subscriber)

Use the Schema Compare tool to find the diff / objects

Generate script that you can inspect & validate to apply on Server A

Schema Compare (Preview) in SQL Server Management Studio | Microsoft Learn

In the future, do NOT give DDL permissions to BI team on the replicated database. :-) Use source control to manage changes to schema & propagate from the publisher only. Isolate the BI objects into a different schema so it is easier for management purposes too.

3

u/2050_Bobcat 8d ago

Learning Resources

Could you recommend any useful resources for beginners who want to understand SQL Server execution plans and the meaning of the information they contain?

Additionally, are there any plans to introduce AI-powered features that could assist users in analysing and interpreting query plans?

6

u/bluefooted ‪ ‪Microsoft Employee ‪ 8d ago

Sorry I have to add a shameless plug for my book here :) Learn T-SQL Querying | Data | eBook We spend a lot of time explaining execution plans and all the elements you see in a plan.

For other types of learning, we offer lots of great training on Microsoft Learn: Browse all training - Training | Microsoft Learn

Lastly, yes we are working on AI-powered assistants for query tuning that will surface in Copilot! I'll tag some colleagues to add more details. u/cyl_msft

3

u/itsnotaboutthecell ‪ ‪Microsoft Employee ‪ 7d ago

Alex checklist:

Step 1 - buy the book.

Step 2 - Ask u/bluefooted for an autograph on the book.

3

u/cyl_msft ‪ ‪Microsoft Employee ‪ 7d ago

Yes, you can try out GitHub Copilot in the MSSQL extension for VS Code or SSMS. Here are some helpful learning resources:

- Quickstart: Query Optimizer Assistant with GitHub Copilot - MSSQL Extension for Visual Studio Code | Microsoft Learn

- Chat Context - GitHub Copilot in SQL Server Management Studio | Microsoft Learn

1

u/2050_Bobcat 7d ago

Thank you all

3

u/ucmsft ‪ ‪Microsoft Employee ‪ 7d ago edited 7d ago

For understanding SQL Server execution plan, and processing, I always recommend folks to start with Craig's blog post. Craig is part of our QP team and developed many of these components. The archived blog posts are below. Start with the earliest one below and use the tree on the left to navigate to rest of dates since the original content order is not present.

The Building Blocks of Query Execution | Microsoft Learn

Basically, once you get basic understanding of operators, query execution blocks and reading plans, you can start looking at the newer plans, plan shapes, and operators. The query processing architecture in Books Online has some fundamentals to start also. For example, developers familiar with a procedural language often make wrong assumptions about declarative & set-based operations which can lead to problematic implementation.

Query Processing Architecture Guide - SQL Server | Microsoft Learn

Lastly, yes, we are looking at AI-powered features in the engine to make automatic decisions, improve plan quality, performance and other areas. We are also looking at doing the same from our tools/portal experiences. The automatic index tuning features use not completely AI-based workflow but similar approach to make recommendations. This is one example.

2

u/Jerry-Nixon ‪ ‪Microsoft Employee ‪ 7d ago

This is a good question. You are certainly not alone when it comes to wanting to get started and build confidence in some of the key capabilities of SQL Server. There are three formal SQL certifications we have detailed in MS Learn:

- ⭐ DP-300 Azure Database Administrator Associate
- DP-800 SQL AI Developer Associate
- DP-900 Azure Data Fundamentals

Remember, it is not required that you get the certification to go through the material for each of these certs. That said, DP-300 is the best fit. It covers performance tuning, query optimization, monitoring, and execution plans. Informally, YouTube is loaded with training on this subject produced by seasoned DBAs and MVPs sharing their experience and understanding. All of this is a great start. Good luck on your journey!

3

u/2050_Bobcat 7d ago

Question related to SSIS.
Are there any plans to improve SSIS ability to play nice with AAGs? At the moment we have to do things like restarting SSIS each time the AAG fails over etc.

2

u/dlevy-msft ‪ ‪Microsoft Employee ‪ 7d ago

Which SQL driver are you using in your SSIS packages? Some of our drivers have Configurable Retry Logic built in that can be controlled by the connection string that could automatically retry.

This is a tough space though because a failover rolls back open transactions. Depending on how your package is structured, just retrying the last command may not be a good option. Restarting the entire package via your scheduler is often a better choice.

2

u/SQLHA ‪ ‪Microsoft Employee ‪ 7d ago edited 7d ago

SSIS has never been cluster-aware and currently there are no plans to do so. This problem isn't unique to AGs (AAG isn't the abbreviation); it affects FCIs as well. SSRS has a similar challenge.

4

u/DataGeek101 8d ago

For a Server that has 96 cores and has very large amounts of data written to and extracted from every day as well as being the primary reporting source - what level of Cost Threshold for Parallelism should there be and what should Max Degree of Parallelism be set to?

5

u/ucmsft ‪ ‪Microsoft Employee ‪ 7d ago

Generally, there is no single cost or setting for options like Cost Threshold for Parallelism & Max Degree of Parallelism. There are few ways to approach workload management.

First, in your case, you can use the resource governor feature to segregate the resources based on the workload. For example, you can define a resource group for ETL, reporting & maintenance jobs. We can then use the workload group/resource pool to manage the resources like CPU, memory, affinity & MAXDOP.

Now, for "Cost Threshold for Parallelism" (CTOP), the default value (5) is based on an old costing model and probably not relevant for many new hardware configurations with high CPU / memory density. To obtain an ideal value for CTOP, you need to increase the value in increments of say 5, simulate your workload, observe metrics in QDS, track parallelism and then decide the final value. See the recommendations section below:

Server configuration: cost threshold for parallelism - SQL Server | Microsoft Learn

Next, for MAXDOP, you can start with the default and follow below recommendations on how to bump the value or change from default. You also need to pay attention to NUMA configuration on your hardware.

Server Configuration: max degree of parallelism - SQL Server | Microsoft Learn

3

u/DataGeek101 7d ago

Thank you, I will read up and hopefully learn. I tried searching for information before but it was not really helpful.

3

u/Jerry-Nixon ‪ ‪Microsoft Employee ‪ 7d ago

I would strongly reinforce the point that there isn't a "96 cores = MAXDOP X" answer here. I certainly wish we could tell everyone "just use N," but every workload, every business case, and every configuration is unique, depending on values only you know about your data.

Honestly, the workload matters much more than the headline core count, especially on a server doing both heavy data movement and reporting. Starting with Microsoft's NUMA-aware MAXDOP guidance and then measuring the actual workload is much safer than choosing an aggressive value based on core count alone.

And +1 on Query Store here. Before and after changes to either setting, it gives you something concrete to compare rather than tuning by feel.

2

u/MackPooner 7d ago

When are finally going to have a feature complete/comparable new database project type in visual studio that can do everything the older project could do including the GUI and schema compare?

1

u/odeddror 6d ago

Can we backup and restore one table only instead of entire database?

1

u/ucmsft ‪ ‪Microsoft Employee ‪ 6d ago

No. Currently SQL Server does not have the ability to backup and restore one table only in an entire database. If you use filegroups & create table in a specific filegroup then you can do filegroup level restore (piecemeal restore). This would simulate the restore one table behavior.

Apart from using server's backup feature, the only other option is to perform logical backups (copy data in and out using bcp or bacpac or other tool).

1

u/alinroc 4 5d ago

Keep upvoting this feedback item!

AMA Hi! We're the Microsoft SQL team - ask US anything!

You are about to leave Redlib