SQL Server, convert a named instance to default instance? - Stack

Default port SQL Server 2008

Port / May 6, 2021

A while back, I worked on a project that required persisting data to multiple databases. The requirement was to save some data to a Microsoft SQL Server (which hosted the billing application data) and save a different set of data to an Oracle database (which housed the database for the flagship Online Transaction Processing [OLTP] system). Both of the database servers were protected by the almighty firewall, valiantly protecting the valuable database servers from a variety of virulent violations.

Ailing attempts at alliteration aside, here’s a high-level diagram describing the application I’m talking about:

While we were testing the application in the Integration Testing environment, we quickly noticed that the persistence operations were failing. Read operations were working fine, but that’s because we were reading our data from yet another database (the account/CRM database, omitted from the diagram above for simplicity). The Windows System Administration group reported seeing errors related to the Application Event Log on the Application Server. The errors in the Application Event Log pointed to the (MS DTC or just plain ol’ DTC).

But everything worked so well in the Development environment! An environment where there’s no firewa — oh. Crap. Crap on a crap cracker.

Looking over the Windows System Admin’s shoulder, we saw that the Distributed Transaction Coordinator was being enlisted during a save operation. Watching the DTC statistics, we noticed that the transactions were aborting. Okay, that’s a good start, which led to this conversation:

SA: Why are the DTC transactions aborting – are they being blocked by the firewall?

DEV: That seems pretty likely, especially considering we didn’t see this happen in Development.

SA: What port should we open?

DEV: Well, the DTC uses RPC to communicate. According to Microsoft, RPC uses port 135. Can you telnet to the database server from the application server over port 135?

SA: Wait a sec… nope doesn’t look like it. I’ll get that port opened and we can try again.

[Windows SAs work with the network admins to get port 135 open on the almighty firewall. Testing resumes. Abysmally.]

SA: Port 135 has been opened, but the DTC is reporting that transactions are still being aborted. What now?

DEV: What now? Umm… to the batcave! I need to do some homework to figure out what’s going on.

SA: Wait, one more question – why is the MS DTC involved anyway?

DEV: Well, our application has to do a 2-phase commit. We have to save data to SQL Server and Oracle. If a save to one database fails, we want to roll everything back. We’re using the System.Transactions namespace for our transaction management. Since we’re opening multiple database connections, System.Transactions is escalating our operation to the DTC, which handles the whole thing. It’s actually pretty —

SA: Okay, okay. I get it. Stop talking and go fix it… nerd.

What’s Going On

Whenever the DTC steps in and starts managing transactions, it does a whole lot of work for you. Here are a couple of the highlights (the highlights we care about, anyway):

Uses RPC, which communicates over port 135. As part of the normal MSRPC protocol, MSDTC chooses a dynamic port between 1024 and 65535. Aha – lightbulb moment! This is why our transactions are still being aborted. We’ll see how to address this in the section.

Troubleshooting Steps

If you find yourself in a similar situation to what’s described above, you can take the following steps (distilled from articles here and here).

WARNING

Before attempting any of this, have a conversation with your SA team and network admin team. Make sure they’re okay with everything being suggested below. The solution to this issue isn’t code related – it’s all server configuration/management. In a way, you – the developer – are acting as the DTC on your application’s behalf, making sure that the SA and network admins are coordinating and committing their changes together.

Troubleshooting Summary

Here’s the short version of what to look for:

    Make sure DTC is configured properly (i.e. can accept connections). Make sure your application and database servers can communicate through the firewall over port 135. Limit the port range DTC can use when communicating via RPC. Open those ports (identified and configured in Step 3) bi-directionally on the firewall. Use DTCPing to verify/troubleshoot.

Is MS DTC Configured Properly?

WARNING: I know I said it before, but it really bears repeating: Work with your SA team before making any of these changes.

Here are a couple of things you can look for on your application and database servers:

Check the MS DTC settings (you can get to this from the control panel) on the application server AND the database server(s). The following MS DTC settings should be turned on/checked: Allow network access Allow remote administration (not required, but advisable for testing/debugging) This setting should be shut off in Production. Safety first, kids. Allow inbound connections Allow outbound connections Make sure you can telnet from your application server to the database server (and vice versa). use the following command: telnet [the name of your server] 135 If you see a blank screen with a blinking cursor, then telnet worked. Port 135 is open, and your servers can communicate over it

Source: fairwaytech.com