Tag Archive: dac

The Dedicated Admin Connection: Why You Want It, When You Need It, and How To Tell Who’s Using It

Written on August 30, 2011 by in SQL Server

There’s a DAC in SQL Server that you should know and love. Sadly, you probably either haven’t met or you’ve forgotten to turn it on.

DAC? What’s That?

First, a little disambiguation. The acronym ‘DAC’ is too popular.

For the context of this article, our DAC is the ‘Dedicated Admin Connection’.

SQL Server implements a totally unrelated DAC as well: a data tier application or DAC package. We’re not talking about that here.

What Can DAC Do For You?

Have a SQL Server that’s in crisis? The DAC can help you stage an intervention.

The Dedicated Admin Connection was built to help you connect and run basic troubleshooting queries in cases of serious performance problems. This is your opening to grab a ‘Get Out of Jail’ card, but since you don’t use this on a regular basis it’s easy to forget how to use it. It’s also easy to forget to enable access to the DAC remotely.

How the Dedicated Admin Connection Works

The DAC uses a special reserved scheduler which has one thread for processing requests. This essentially means that SQL Server is keeping a backdoor open to processor resources available just for you.

Don’t be too tempted to abuse this privilege. That one thread is just one thread— there’s no parallelism for queries running on the DAC. Two percent of you will be tempted to use this for your maintenance jobs on busy systems. Seriously, just don’t go there. The DAC was not designed for high performance.

How to Enable the DAC for Remote Connections and Clusters

By default the DAC is only enabled for accounts logged onto the local machine. For production servers, that means it only works for remote desktop sessions to non-clustered SQL Server instances. If your instance is clustered or if you’re connecting over TCP/IP, you’re out of luck unless you change one setting. That setting is ‘Remote Admin Connections’.

Should you enable remote connections? I agree with Books Online, which points out:

If SQL Server is unresponsive and the DAC listener is not enabled, you might have to restart SQL Server to connect with the DAC.

For clusters, sign me up! I’m in favor of enabling it for other instances as well. In times of trouble, you want to minimize the amount of time you spend using remote desktop on a server having problems. You want to use that only to gather information you can’t get another way.

Enabling the DAC for remote connections is easy as pie. It is controlled by the ‘Remote Admin Connections’ setting. To enable it, you simply run this bit of code:

EXEC sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO

There’s always a catch. You many need to get firewall ports opened as well, depending on your environment and where you intend to connect from. This will probably be port 1434, but that will vary depending on your configuration. (Books Online has got your back: read more in the “DAC Port” section here.)

Only One Sysadmin Can Ride This Horse At a Time

This isn’t a party line, only one sysadmin at a time can use the DAC. Also, you should only run simple, quick queries using the DAC.

In other words, only connect to the DAC when you really need to. When you’re done wipe the seat make sure to disconnect.

How to Connect to the DAC

You can connect to the DAC using the command line. Use the “-A” option with SQLCMD.exe.

I find it more convenient to connect in Management Studio itself. You do this by prefixing the instance name you are connecting to with “Admin:”.

One FYI: Object Explorer can’t connect to the DAC. If you open SSMS and have Object Explorer connecting by default, the first connection prompt you see with be for that. If you try to tell that to connect to the DAC, it’ll fail. That’s a good thing, we wouldn’t want the power to go to Object Explorer’s head.

Who’s Been Sleeping in My DAC? How to Tell Who’s using the Dedicated Admin Connection.

If you try to connect to the DAC when someone is already connected, you’ll see a connection error. It probably won’t tell you straight out that someone’s connected to the DAC already, but if you check the SQL Server Error log you should see the message:

Could not connect because the maximum number of ‘1’ dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process.

So if you can’t get the DAC, how can you tell who’s using it?

Here’s a hint— the DAC uses a Dedicated TCP Endpoint. It’s even endpoint #1. The DAC is ranked #1 by endpoints everywhere!

This makes finding someone using the DAC nice and simple, because endpoint_id is listed in sys.dm_exec_sessions. So you’ll just need a little something like this:

select
	case
		when ses.session_id= @@SPID
		then 'It''s me! '
		else '' end
		+ coalesce(ses.login_name,'???') as WhosGotTheDAC,
	ses.session_id,
	ses.login_time,
	ses.status,
	ses.original_login_name
from sys.endpoints as en
join sys.dm_exec_sessions ses on
	en.endpoint_id=ses.endpoint_id
where en.name='Dedicated Admin Connection'

Your Mission: Get Back with your DAC Today

Take a few minutes today to connect with the DAC in a test environment. Check whether you have remote admin connections enabled in your environments, and talk about what the impact of that might be.

The DAC can save your bacon— make sure you have the ability, and use the power wisely.

Kendra Little

Kendra specializes in high availability and performance tuning. She is a Microsoft Certified Master in SQL Server-- the highest technical SQL Server Certification available. Kendra loves databases and software development more than long walks on the beach. Those cartoons in her blog posts? She draws 'em all. Read more and contact Kendra.

Website - Twitter - Facebook - More Posts

8 Comments - Leave a comment!

SQL Server 2008 R2 Review

Written on May 13, 2010 by in SQL Server, SQLServerPedia Syndication, Virtualization

In August 2008, Lady Gaga released her premier album The Fame. This 13-track work produced several hit singles including Just Dance, Poker Face, and Paparazzi – but it also produced plenty of controversy. Her exaggerated use of fashion, musical cliches, and sex gave her plenty of media attention – both good and bad. If you’ve never heard one of her songs or seen a video, the first 30 seconds of Poker Face will give you a pretty good idea of her style (musical and otherwise). Actually, you don’t even really have to hit play – the first frame showing her poolside between to two Great Danes probably gives you a pretty good idea where things will go next.

Anybody can get famous doing an Internet donkey Great Dane show. The big challenge for suddenly-famous artists isn’t the first release, though – it’s the sophomore release. The second album shows if the star has the potential to keep cranking out new material, or if they drop off the face of pop culture as a flash-in-the-pan.

A little over a year later, Gaga released a followup album called The Fame Monster, but there’s some debate over whether it should even be considered her sophomore work. Due to the short time between releases, its shorter length (only 8 tracks), and its sales method (often packaged with The Fame in a single box), it was seen as an extension of her debut album. This effort didn’t win over any new fans; if you didn’t like The Fame, then The Fame Monster wasn’t going to turn you into a Little Monster, as Gaga fans are known.

It’s a lot like SQL Server 2008 and 2008 R2.

Microsoft SQL Server 2008: The Fame

It’s impossible to review this month’s newest release from Microsoft, SQL Server 2008 R2, without reflecting back on the recent release of SQL Server 2008. R2 comes quickly on the heels of 2008 and tells more of the story that was first started in 2008.

SQL Server 2008′s hit singles for production DBAs included:

  • Backup compression – this instant-winner of a feature has been available in third party software like Quest LiteSpeed for years, and 2008 Enterprise Edition users got it built into the engine.
  • Transparent Data Encryption – if you need it, you desperately need it. TDE makes it ridiculously easy to protect your company’s data from headlines about stolen backup tapes.
  • IntelliSense – wildly popular at first, but quickly fell off the charts as users discovered it didn’t work with previous versions of SQL Server.

The less popular B-side songs were:

  • Data compression – like backup compression, this feature can give an instant performance benefit under certain circumstances. Even better, the data is only compressed once, and then all backups are smaller and faster without repeated CPU work. I think this is the most underrated song in the album, although filtered indexes & statistics are a close second.
  • Policy-Based Management – a cult favorite that hasn’t managed to crack the charts for widespread adoption. Everybody’s heard about this song, but nobody knows the words.
  • Resource Governor – originally touted as the ability to stop runaway queries or ease consolidation pains, this didn’t actually work as intended due to its inability to throttle IO. Storage is usually SQL Server’s biggest bottleneck.
  • Spatial data – programmers have worked around the challenges of storing latitude/longitude data in RDBMS’s for years. With support built into the engine, now developers could use spatial indexes to execute tough queries much faster.
  • Filestream storage – answering a question DBAs everywhere kept asking, “How can we make our databases larger and more difficult to back up?”

The release was chock full of upbeat songs, but the beats were just a little too tricky for mainstream dance clubs. SQL Server 2005 was good enough, and companies didn’t have the money to roll through quick upgrade projects, so I didn’t see a widespread adoption of 2008. In the recent PASS Summit poll, 52% of responders said less than half of their servers were SQL Server 2008. I haven’t heard anyone say they dislike SQL Server 2008 – they just didn’t care quite enough to deploy it to every server in the shop.

SQL Server 2008 R2: The Fame Monster

Real music critics seem to have a somewhat positive view of The Fame Monster, giving it 75 points out of 100 on Metacritic. Gaga clearly resonates with the purchasing public, having earned over 17 platinum records as of this writing. People between those two extremes have a much more negative reaction, however – my music-critic friends (people who think they know a lot about music, yet don’t have jobs in the business) uniformly hate her. They say she’s low-brow, populist, and oversexed.

SQL Server 2008 R2 will have a similar reputation in the database world. Analysts, the IT industry’s equivalent of music critics, love SQL Server. On the other extreme (paying customers), Microsoft sells the heck out of SQL Server, so it seems to be popular with the folks with money. People in the middle are more divided, though – I hear plenty of people on other platforms referring to SQL Server as not good enough for all kinds of reasons. It’s too expensive, it’s not full-featured enough, it’s too-full-featured (yes, that’s the NoSQL claim), it’s not open source, the list goes on and on.

People who liked SQL Server 2008 will like 2008 R2, but for different reasons. It brings some really new material to the party, albeit with exactly the same theme as 2008.

PowerPivot Caters to the Paparazzi

Lady Gaga Fan Art

She's no Donald Farmer, but she'll do

Microsoft used sex to sell SQL Server 2008 – and that sex was Business Intelligence. BI is by far the sexiest topic for businesses today. It’s in every magazine, every book, and companies are dying to figure out how to get more actionable information out of their raw data. SQL 2008′s material read like a pinup calendar – “We’ve got all your forms of data right here, big boy! C’mon over and analyze me.”

R2 takes the sex appeal even further – BI isn’t in the hands of pros anymore, but rather amateurs. Any sleazy middle manager with a fetish for data can whip out an Excel spreadsheet and get his PowerPivot on, doing multi-million-row analysis on his laptop.

This high-quantity, low-quality analysis comes with its own challenges. Who rates the data? Are you sure the numbers in that loose schema are right? Is that juicy profit figure really profit, or a big, fat, hairy loss masquerading as something else? Who let that underage data into the corporate report? And in the middle of a steamy analysis session, does anyone even care?

PowerPivot doesn’t appeal to buttoned-down database administrators accustomed to pure, cleansed data stored behind locked doors, but that’s not the point. It sells to managers and executives who want fast, easy access to decisionmaking numbers, and they’re going to love it – especially when combined with SharePoint.

StreamInsight: It’s Money Honey

Packaging StreamInsight in the SQL Server box will be controversial, but the feature is great for developers. StreamInsight helps developers analyze massive amounts of incoming data – think stock trades, Twitter, or clickstream data for web sites. Developers can then build rules (no, not with T-SQL) to handle and parse that data.

Notice how I said “developers” three times in three sentences?

StreamInsight doesn’t really have anything to do with SQL Server. The coding is done in Visual Studio and the data doesn’t go through the SQL Server engine. SQL Server just happens to be a really expensive box – especially the Datacenter Edition, where StreamInsight is best used – and Microsoft needs to monetize this product. Presto: StreamInsight becomes part of SQL Server.

Database administrators can safely ignore this feature because they won’t be deploying it, managing it, backing it up, etc. CIOs, however, need to pay close attention to what features are bundled into what editions, because buying SQL Server is getting a little more complicated.

CIOs are Seeing Microsoft’s Poker Face

My poker face, let me show you it

My poker face, let me show you it

SQL Server 2008 R2 brings a new top-end SKU, Datacenter Edition. Microsoft breaks down features by edition, but here’s my simplified explanation:

Express Edition (Free) has:

  • Databases up to 10GB
  • Completely free

Standard Edition (roughly $6k/socket) adds:

  • No limits on database size
  • Backup compression

Enterprise Edition (roughly $30k/socket) adds:

  • Up to 8 CPU sockets
  • Management features for up to 25 instances
  • Virtualization rights for four guests – on a two-socket server, you can buy two sockets of SQL Server 2008 R2 Enterprise Edition, then run four virtual servers on there with SQL Server.
  • Data compression, online index rebuilds, and other engine features that help with databases over around 100GB

Datacenter Edition (roughly $60k/socket) adds:

  • Unlimited CPU sockets
  • Management features for over 25 instances
  • Unlimited virtualization rights – on a two-socket server, you can buy two sockets of SQL Server 2008 R2 Datacenter Edition, then run an unlimited number of virtual servers on there with SQL Server.

With R2, prices went up, and I’m totally okay with that. I’m not a Microsoft shill here – I think the market has changed, and Microsoft’s prices needed to reflect it. Virtualization is on fire. Microsoft has to price products not just for today, but for now through the next release. Intel’s packing more and more cores per chip, Cisco’s got blades that handle an insane amount of memory, and I routinely see virtualization hosts with more than 4 guests on them – usually several times that many. It’s not inconceivable that we’ll see hosts with more than 4 virtual SQL Servers on them soon as well, and Microsoft licensing revenues would plummet.

The price increase also sort of reflects the new tools like PowerPivot and StreamInsight – but only kinda. Some of the new features don’t have polish, and there’s a risk of them being one-and-done.

I Like It Rough: DAC Packs

Maybe not that rough.

Maybe not that rough.

The iTunes release of Fame Monster includes a bonus track, “I Like It Rough,” that depicts a relationship where both sides do a lot of giving and taking. It’s insanely catchy, but with the subject matter, I can see why it didn’t get widespread release.

DAC Packs are R2′s bonus track. I’ve written about DAC Packs before, and I believe they have an awesome long-term potential. The version shipped in R2 is very rough for all kinds of reasons – it does side-by-side deployments (copying your entire database just to change a single stored proc), it doesn’t support a lot of database features, there’s no tooling whatsoever for DBAs, and it does a worse job of just about everything than Data Dude did. (And nobody bought Data Dude, either.)

Microsoft says that DACs are a down payment on a bigger vision – and I do believe in that vision – but Microsoft has a pretty bad credit score. They make an awful lot of down payments, and then when the first monthly invoice comes in, they seem to have left their wallet in their other pants. Want to see a credit check?

  • SQL Server Notification Services
  • English Query
  • DTS (how do you like rewriting all those packages into SSIS? Good times, eh?)
  • HTTP SOAP access (I’d almost forgotten about that one, but it was even one of Microsoft’s Top 10 Features for DBAs in 2005. Quote: “…eliminating the need for a middle-tier listener, such as IIS, to access a Web services interface that is exposed by SQL Server 2005.”)
  • Vardecimal (yes, we actually had a one-and-done datatype)
  • Service Broker (yes, it still works great, but there haven’t been any investments here whatsoever)

Ouch. That’s a lot of things they bought on layaway, and then never paid for.

In my Top 10 Developer Mistakes That Won’t Scale presentation, my #1 thing to avoid is relying on v1.0 features. Sometimes Microsoft follows through and improves the feature over time, like SQL Server Integration Services. Sometimes, though, they forget their wallet and look the other way. When I’m making architectural decisions on where to invest programmer resources, I follow Microsoft’s own lead – if they didn’t care enough to invest the time it takes to do something right, then I won’t invest my own time in it either. Let’s see if they make the next payment first.

The old adage used to be, “I won’t deploy it until Service Pack 1, because there might be bugs.”

Now I tell my developers, “Don’t code against it until V2, because there might not be one.”

Like Fox Mulder, I want to believe, but Microsoft needs to pour a whole lot more work into DAC Packs – fast. Otherwise, server virtualization is going to bypass database virtualization as a better way to solve management problems.

SQL Server 2008 R2: Not a Bad Romance

If you’re into dance music, you couldn’t help but hear The Fame when it came out. If you liked it, you’re going to like The Fame Monster, but if you didn’t like it, The Fame Monster won’t change your mind about Lady Gaga. The Fame Monster is really just more of the same songs in the same style.

Most SQL Server professionals who liked SQL 2005 are going to pick up this latest release, throw it into their playlists, and keep right on working as if nothing changed – because for the most part, it hasn’t. SQL Server’s engine hasn’t really changed since 2005 came out. Unlike The Fame Monster, though, I think R2 has a chance of winning over people who weren’t fans of SQL Server before. Microsoft is extending its range with StreamInsight, PowerPivot, and Parallel Data Warehouse Edition, and these might bring in a new audience.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

30 Comments - Leave a comment!

My Weekly Bookmarks for October 30th

Written on October 30, 2009 by in SQL Server

Here’s my bookmarked links for October 26th through October 30th:

SQL Server Links

#SQLPASS Links

Tech Links

The Junk Drawer

These bookmarks are automatically imported from my bookmarks at Delicious.com. If you’d like to get up-to-the-minute updates on what I’m bookmarking, you can subscribe to my bookmark RSS feed.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

1 Comment - Leave a comment!