Category: SQL Server

2009

MicrosoftSQL Server

(re)Gain sysadmin access to SQL2005 or SQL2008

In SQL Server 2005 and 2008 the local Administrators account is not sysadmin by default. This makes it even more important that the one setting up the Database also remembers to add a SQL Server admins group to the sysadmin role. If this step is forgotten, the user installing the database server is the only one that will ever be sysadmin. In some extreme cases I’ve seen situations where no one except some dude on vacation is sysadmin and I need to install or upgrade a bunch of applications and services. In these cases I have also been assigned Local Administrator rights on the server, but since the local Administrators group isn’t sysadmin either I still cannot login to the SQL server. What to do!? Thanks to Raul Carcia’s blog post it’s not that a big deal. The instructions are written for SQL Server 2005 but works equally fine on SQL Server 2008 and the only pre-requirement is that you are a local server administrator. Here’s what to do: Open the SQL Server Configuration Manager. In SQL Server Services, open the properties for the SQL Server instance you need access to. In the Advanced tab, find Startup Parameters. Add “;-m” to the end of that line. Press OK and restart the SQL Server into “Maintenance Mode” or “Single User Mode” if you like. (check that a restart is OK first 😉) Open a command prompt (right-click, “Run as Administrator” in Windows 2008) and go to C:\Program Files\Microsoft SQL Server\100\Tools\Binn (C:\Program Files\Microsoft SQL Server_90_\Tools\Binn for SQL2005) Execute sqlcmd /A /E /S <INSTANCE> (use . for local default instance and .INSTANCE for local named instance) In the CLI, execute: EXEC sp_addsrvrolemember 'DOMAIN\yourusername', 'sysadmin';GO Return to the SQL Server Configuration Manager and restore the Startup Parameters to it’s previous settings. Restart the SQL Server instance to allow users to get access to it again. Now, you should be able to login to the SQL server with sysadmin rights using your current user. This would also be a good time to actually set up a SQL Server Admins group (local or domain) to add to the sysadmin role to avoid having others to the above steps when you, yourself, are on vacation. 😉 As Raul Carcia point out in his original post, this is really a disaster recovery procedure and there’s definitely nothing sneaky about it since it leaves a lot of trails in the event logs. All in all, a Great article by Raul and all credit should go his way.

MicrosoftSQL Server

Installing SQL Reporting Services 2005 on Windows 2008 x64

Let’s say you have followed this guide: http://support.microsoft.com/kb/938245/ Still not working? The one thing I forgot, or rather did not find in any of the guides, was to change the website application pool to “Classic .NET AppPool”. It is actually noted in KB938245 but only after the installation, during the configuration. For some reason I have not been able to install Reporting Services 2005 on Windows 2008 without changing this prior to the installation. Maybe I am doing it wrong but this seems to be working all right for me.

MicrosoftSQL Server

Why not use SQL Express? It’s for free!

Background I get this question every now and then and every time I find myself completely flabbergasted and having to look things up once again. To avoid wasting my time on the same question once again and perhaps help others doing the same, here’s a little guidance. Don’t get me wrong now. SQL Express has it’s applications and for a free database server, it’s not half-bad. Small development sites, minor, not that extremely important systems with lower performance and feature demands, minor website databases et cetera could do well with SQL Express. My Checklist Here’s my list of questions you have to ask to find if SQL Express is the correct choice. 1. Do your applications support SQL Express? If your application developers cannot say “Yes” to this, you’re out of luck. You could probably get their applications to run on SQL Express anyway, but application support if something goes bad will most likely be zilch. 2. Do your applications fit the hardware limitations? SQL Express is limited to 1GB RAM, 1 CPU and 4GB of databases. 1GB of RAM seems a bit tight to me for any production data. Also, on SQL Express 2005, according to Microsoft, you cannot run parallel queries. ”SQL Server Express can install and run on multiprocessor machines, but only a single CPU is used at any time. Internally, the engine limits the number of user scheduler threads to 1 so that only 1 CPU is used at a time. Features such as parallel query execution are not supported because of the single CPU limit.”