Regular Session at the 2011 PASS Summit: Troubleshooting SQL Server with SysInternals Tools

 

It looks like my talk at SQLSaturday 92 in Portland, OR was a big hit – and I get to deliver it at the 2011 PASS Summit!

If you’re at the Summit this week, go check out my talk – I promise I will show you techniques and tips that will be very useful to you.

Details:

Session DBA-415

Friday, 10:15 – 11:30am, Room 4C1-2

Followed in the afternoon by my Lightning Talk “Quick Tips to Enhance tempdb Performance” at 2:30pm, Room 201.

I’m honored to speak at the Summit this year – and I hope to meet you there!

Speaking at PASS Summit 2011 – Lightning Talk Accepted!

Quick post – I got news that I will be speaking at the PASS Summit 2011. What a huge honor. As I understand it, I am the first Venezuelan ever to speak at PASS. I’m super excited!

The Lightning Talk is called “Quick Tips to Enhance tempdb Performance”, and I have to practice this talk a lot. I’ll have exactly 5 minutes to deliver it, and discussions about tempdb performance can go on for hours. So I need to keep it concise and to the point. 

A full lineup of all Lightning Talks can be found here: http://www.sqlpass.org/summit/2011/SummitContent/LightningTalks2011.aspx

Also, I changed my Twitter handle to @DBArgenis, to better reflect who I am, my passion and commitment towards the SQL Server community.

Can’t wait for the Summit now.

Think Your Windows Administrators Don’t Have Access to SQL Server 2008 by Default? Think Again.

We had an email thread at work where someone was asking for assistance with an unknown sa password on a SQL instance on a new laptop. “No big deal” – I said to myself – “You can reset the sa password by starting SQL in single-user mode” (If you haven’t heard about this, have a look here).

And then I started thinking – but what if this happen to be a production server with tons of users and suddenly we found ourselves locked out of SQL Server? Starting with SQL Server 2008, the BUILTIN\Administrators group is not made a member of the sysadmin server role unless you add it explicitly. You’d have to bring the server down to reset the sa password. Downtime is not good eats.

Yeah, well – don’t get a false sense of security. Members of the local Administrators group can still login to SQL Server. With full sysadmin privileges.

interesting_lock

(Taken from http://www.flickr.com/photos/librarianinblack/5898803951/in/photostream)

How? By impersonating the NT AUTHORITY\SYSTEM login that is created by Setup and granted sysadmin privileges by default. You can easily do this with PsExec (using the –s switch) or creating a Windows Scheduled Task running under the SYSTEM account.

Let me show you what I mean:

By default, things look like this on SSMS on a default installation (this particular instance just happens to be named, other than that it’s all default values for setup – MAGMA is the name of my test VM and SQLPS is the name of the domain)

image

As you see, the Administrators group is not listed as a login. Which means that if you try to login using the Administrator account, you’ll get a nice error window that says:

image

I happen to have a copy of PsExec on my C:\bin directory, so I will use it to illustrate this example. Let’s kickoff a cmd window (running as Administrator) that will run PsExec and launch another cmd window running as cmd – the parameters I used for PsExec are: Interactive (-i) and Run as System (-s):

image

I ran “whoami” on the child cmd window to show who’s running it. That was easy, eh?

Now, I just launch sqlcmd and run a couple of statements:

image

And voilà – I’ve created a new login for the Administrator user and made it a member of the sysadmin server role, all while running under NT AUTHORITY\SYSTEM.

So what is Microsoft’s guidance in regards to this NT AUTHORITY\SYSTEM login? To leave it as is – here is the KB article that states it. It was written for SQL Server 2005 and it hasn’t been updated since. I asked some of my contacts at CSS and they confirmed that the article is still considered valid for 2008 and 2008 R2. And that would be my humble recommendation as well. On a heavily secured installation though, you will definitely want to remove that login and perform careful testing of the side-effects of removing it.

Cheers!

 Edit: As Jorge Segarra pointed out below, NT AUTHORITY\SYSTEM is no longer provisioned as member of the sysadmin server role by setup in SQL 11. I confirmed the behavior on a Denali CTP3 instance I setup from scratch.

Transactional Replication and WAN links

We recently worked on a transactional replication setup that involved a very active VLDB and a subscriber being located on a different datacenter. What made it even more interesting is that the WAN link was not particularly fast. In this post, I would like to mention a few of the challenges we faced while and how we got past them, in the hopes that our experience can help you in your future endeavours.

The Problem with Slow Distribution Servers

 If your distribution server is slow, your replication performance will tank. You will get behind on transactions and might not ever catch up. In our scenario we had a distribution server that was outdated. The server was running Windows Server 2003 and SQL Server 2005. In our case, this was the biggest issue. After we moved to a new distribution server that was running Windows Server 2008 R2 and SQL Server 2008 R2, our performance increased greatly. One of the biggest benefits in moving to Windows Server 2008 R2 is the set of enhacements made to the TCP/IP stack – particularly send/receive TCP windows. For more information, see this article on Technet.

Careful with WAN Accelerators

While WAN accelerators can be fantastic in a myriad of scenarios, in our testing we noticed that the device wasn’t really optimizing replication traffic – and it was actually causing latency. With the help of our Networking team, we made sure that traffic from the distributor to the subscriber was skipped by the WAN accelerator. Obviously, your mileage will vary – so test accordingly.

Use Pull Subscriptions

Our initial setup was done using a Push subscription. This was a big mistake. While Push subscriptions are easier to setup and maintain, their performance across WAN links is plain dismal. The MSDN team at Microsoft put out a great White Paper on Geo-Replication Performance which is, in my opinion, required reading for replication to other datacenters. We saw huge performance gains when we switched to Pull. Orders of magnitude faster. Put simply – never use Push subscriptions across WAN links.

One of the design decisions made in our scenario that I would like to point out: we intentionally kept the distribution database near the publisher (i.e., on the same datacenter) – the reason behind this is simple: if your level of confidence in your WAN link isn’t that high, the concern becomes the Log Reader agent and getting the Transaction Log to clear reliably and constantly.

Initialize the Subscriber from a Backup

With a WAN link and high latency involved, initialization of the subscriber from a backup is your best bet. We saved ourselves a lot of headaches by doing it. Creating and transferring a snapshot of a VLDB is out of the picture when you’re concerned with WAN latency. In our scenario, the publisher was running SQL Server 2005 and backups were being taken using LiteSpeed. We transferred the most recent full backup to the remote datacenter using robocopy (could have used FTP also) plus the latest differential taken after changing the properties of the publication to allow initialization from backup. Restored at the subscriber using LiteSpeed tools, and then used the Extractor utility to create native-format backup files to initialize from backup. This is because you cannot initialize from a LiteSpeed backup, as SQL needs to read LSN information from the backup file and it uses a system stored procedure for that purpose. Here are some tips: you only need the first backup file created by Extractor to initialize. Also, you don’t have to initialize with a differential backup – you can use a T-Log backup just as well.

 Here is a good post on Initialization from Backup at ReplTalk that might be helpful if you run into issues.

Other Optimizations

There are other replication features that help reduce the amount of commands sent across to the subscriber. Namely:

Happy Publishin’!