Voice of the DBA

Author: way0utwest

  • Using the DAC with SSMS

    I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

    While troubleshooting another issue, I needed to connect to SQL Server with the DAC. I couldn’t remember the syntax, so I looked it up quickly and ran into this link: Diagnostic Connection for Database Administrators. I added the ADMIN: to my connection in SSMS for a query window and clicked Connect.

    dac_g

    This took entirely too long and then I got this:

    dac_a

    That error resulted in a rabbit trail for me to debug this, but it worked out and I’ve learned a few things. The main one is to be sure that I’ve read the documentation and errors correctly.

    As I tried a few things, including SQLCMD, I realized I had issues. I searched and found that the DAC gets set to a specific port. I checked the error log to get the number.

    dac_b

    With that, I added it to my connection dialog, sure that this would work, but of course, it didn’t.

    dac_c

    Eventually I stumbled on the post about errors and realized I should have checked the SQL Browser earlier. I thought about it, but discarded that thought because I could connect in other ways. Mistake. Check networking first, and networking is where the browser comes in. Sure enough, it was stopped.

    dac_d

    The properties were set to DISABLED, so I had to change that before I could start it. I assume you can do that, if not, poke around the properties until you find that setting. With that changed, I started the service.

    dac_e

    Then I could connect. Here’s the SQLCMD version.

    dac_f

    With this working, the first command dialog at the top of this piece worked from an SSMS query window.

    SQLNewBlogger

    This was longer. What I thought was a quick lookup turned into a troubleshooting exercise that lasted about 20 minutes as I searched, read, experimented, etc. At least I made a bunch of screenshots as I was experimenting, so I had lots of data and didn’t duplicate anything.

    The actual writing was only about 10 minutes, most of that looking through screenshots and trying to organize my thoughts.

    References

  • Practical Hekaton

    I got into a debate recently with someone about the In-Memory OLTP tables (formerly Hekaton) feature that was released with SQL Server 2014. This person asked if anyone was actually using the technology, given all the limitations that were placed on these structures in this first release.

    I’ve had quite a few people actually get upset with Microsoft releasing a “feature” that doesn’t just work as a replacement for the regular tables in SQL Server. They feel that Microsoft should have spent more time working to make the transition to these tables seemless, perhaps not releasing in-Memory tables until SQL Server 2016.

    I feel differently. I think that Microsoft should get the technology working and stable, and then release it. Even if it has limitations, I’d like to see an implementation available early and give companies the chance to see how it performs in real world workloads. If you can’t workaround the limitations, then you can’t use it, but then does it matter if the feature is available in 2014 or 2016? I’m not sure it does.

    We would really like to publish more guidance here, and help people understand the feature better. If anyone is out there actually using Hekaton in production, or has a development project underway that should get to production soon, we’d like to contact you and work out a way to publish information. You’re welcome to write an article, or we can write one for you with a little interview time, either way is fine; we’d just like to get some practical, real world information out for everyone to learn from.

    Please contact us if you’re using In-Memory OLTP tables and natively compiled stored procedures.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.4MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Unexpected Windows 10

    This was strange. I noticed a new icon in the taskbar today. When I hovered over it, I saw this:

    2015-06-10 10_59_31-

    I downloaded the Windows 10 beta in a VM, but not here. However apparently some update added this. If I click on it, I see this:

    2015-06-10 10_59_44-Get Windows 10

    I hadn’t been reading about Windows 10 too much. Mostly I want it for the copy/paste in the command line, but apparently I need to download it to get the upgrade. I hadn’t heard this before, and I was concerned.

    If I click the reserve upgrade, I see this:

    2015-06-10 11_02_12-Get Windows 10

    I don’t need a download now. Apparently I’ll get an automatic one when the OS goes to RTM, which is fine. I assume I can install it elsewhere, but perhaps I’m limited to this device?

    It’s hard to tell. This will be an interesting process. I went through the FAQ and it appears I could upgrade later. The upgrade I reserved is for this device, so I’ll need to do this separately on my laptop.

    A new process for Windows, and I’m interested to see how it works. I’ve seen a few MS people have issues with Windows 10 in demos, so I’m a little concerned about upgrading now, but I am looking forward to seeing the new OS in July or August.

  • Archive Every Day

    I regularly see questions posted online from people struggling to manage a large archive process. Often the inserts into an archive table and the deletes from the source require large amounts of resources that strain systems and block other activity. These types of processes can also require lots of transaction log space, something many people forget about.

    The best solution, in my mind, is to archive regularly. Decide how much data you need and then trim your working set back to that level on a regular, preferably daily, level. That way you limit the amount of resources you need at one time.

    However that’s not always easy to implement in a system. I’ve encountered many applications that were set up to read from a single table and archiving data means no one can access that data unless it’s inserted back into the main table.

    I wish that we would have better patterns and frameworks for building archiving into systems from the start. I almost wonder if we should be using a view built across a a regular table and archive table from the start for accessing data. This isn’t a complete solutions, and there are certainly problems here. Maybe we should have partitioning implemented early on, though the edition limits on that technology are problematic.

    Microsoft is bringing us “stretch” tables in SQL Server 2016. Not a bad idea for archiving cold data, but as Brent Ozar points out, are they going to do better partition elimination? Will Microsoft give us a good way to handle backups? There are certainly issues here, and likely room for improvement in later versions.

    Ultimately I think the complexity of archival data doesn’t matter for most systems. These days 10GB of data is a pittance, and since many applications (and their databases) are smaller than this, why bother. If you have a larger system, however, I’d encourage you to think about archival before you need it. When you actually have time to implement it well.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn.