Cannot Generate SSPI Context

Oh this cursed error! If you google it you will find tons of possible solutions that fixes this dreaded issue. In this short post I will describe what fixed the problem for me.

First a little background on previous events. One of our SQL Servers was rebuilt by creating a separate VM for it with the same name as the original after the original was taken offline.

Next day I am unable to use Windows Authentication to log into this new server. I get the Cannot Generate SSPI Contect message. I can log into the server itself and access the databases without any problem locally. I can even do that on someone else's computer, just not mine.

I play around with the setspn command line utility for a while but nothing helps.

Finally I log into the server using a SQL account. After that is successful I try the Windows login and finally, I'm in!

Such a simple solution. I hope this will help someone else too.

Go!

Can you guess it's a slow time for me at work? This is my third post within one week! During downtime I like to read articles to further my knowledge of SQL Server. That's when I catch up on newsletters that collected in my inbox or prepare for my upcoming certification test.

Today, I ran across Kalen Delaney's article about a curious undocumented feature of SQL Server. If you put an integer after the batch separator "go" (e.g. go 10) the sql statement(s) before the "go" will be executed that many times (in my example 10 times.)

She uses this interesting little feature to populate test tables with test data but it can be used as a quick and dirty stress/concurrency testing technique as well.

Thank you Kalen for documenting the undocumented!

SQL Server Data Collector Woes

This post is to list the idiosyncrasies I came across while setting up Data Collector.

Cache directory
Issue: As the name implies this is the location where the cache collects the data. When clicking on the ellipsis it opens up the drive and folder structure of the server where the management data warehouse resides. Guess what? If you specify a folder on that server you will get the following error: The system cannot find the file specified.
Solution: The folder needs to exist on the server against which the data collection is executed.
Tip: This folder seems to be used even if you set the data collection to be non-cached.

Permissions
Issue: Login failed for user error.
Solution: Make sure the user set up to run the SQL Agent Service has access to the database server where the management data warehouse resides.

QueryActivityUpload
Issue: The error "Arithmetic overflow error converting expression to data type int" during Query Statistics processing.
Solution: As per this Connect page explains Cumulative Update #5 for SQL Server 2008 SP1 will fix this problem. However, if you cannot wait to get and apply that update there is a manual workaround explained on that same page. The workaround involves modifying the QueryActivityUpload SSIS package. I went by that route and the modifying the package does work. I just want to add that There are 2 places where the fix needs to be applied: OLE DB Source in the DFT - Create Interesting Queries Last Snapshot and the ODS - Get current snapshot of dm_exec_query_stats in the DFT - Create Interesting Queries Upload Bath dataflow tasks.

SQL Server Deprecated Features

I ran across this very useful performance counter today: SQLServer:Deprecated Features. This shows you how often SQL Server encountered listed deprecated features since the latest start.

In my SQL 2008 environment I see such item as "String literals as column aliases" or "sp_change_users_login." Some of these features are self-explanatory. For example, I can deduct from "Table hint without WITH" what I need to use the keyword "WITH" for table hints to correct this for a future version of SQL Server when skipping WITH will not be allowed.

For some others it's not so obvious or I need to find out another option. In those cases I can look at Microsoft's page that describes all the deprecated features in detail giving alternatives: http://technet.microsoft.com/en-us/library/bb510662.aspx. On this article, for instance, I find out that instead of DBCC DBREINDEX I need to use the REBUILD option of ALTER INDEX.

If you have a lot of legacy code in your application (and who doesn't?) be sure to take a look at this counter by running the following SQL statement: SELECT * FROM sys.dm_os_performance_counters where object_name = 'SQLServer:Deprecated Features'

Migrating SQL Jobs

About once a year I am faced with the fact to migrate one of our SQL Servers to a better, bigger, newer server. Usually it's not a big deal because for the most part it can be done by backing up and restoring the database. How about migrating the SQL Jobs though? Sure, that can be done as well by backing up and restoring the MSDN database but if you are also upgrading SQL Server that may not be a wise idea.

There are a few ways this can be done. SSIS has a Transfer Jobs Task but I ran into a problem with that as most of the jobs had the server specified like this: @server=N'SERVERNAME' so the task was not able to transfer those jobs. Individually scripting out SQL Jobs is possible of course if you only have a handful. In my case I had 87 SQL Jobs and frankly, I am too lazy for that much work. So I found a shortcut.

Click Jobs in Management Studio and then click F7. That will pull up Object Browser with the list of all the SQL Jobs. Highlight all and then right click and select Script Job As>Create To>New Query Window. That will script out all your jobs. Once you have them scripted out you can just a search and replace for server names or other variables you need to change. Then you change the connection to your new SQL Server, hit Execute and you are done!

In my case I was almost done. I also had some Maintenance Jobs to transfer as well. Those are basically SSIS packages kept on SQL Server as opposed to in the File System. The solution is to export them to the file system, change the connection and then import them into the new SQL Server. Here are the steps to accomplish that.

1. Connect to your server's Integration Services in SSMS. You can do that by hit Connect>Integration Services. Once you are connected you will find the maintenance plan packages under Stored Packages>MSDB>Maintenance Plans.
2. Export all packages. You can do that by right clicking each package then selecting Export Package. On the dialog box that box up you need to select File System in the Package location section. Then navigate to the location where you want to save your package. I left the default in the Protection level section.
   
   
3. Open the packages in notepad and change the connection information. Just search for the name of the server and replace it with the name of the new server. This step is necessary because you are unable to edit the Local Server Connection in the package editor. You may also change other things as well such as backup locations and such. Save the file.
4. Import the package. Connect to your new servers Integration Services. Navigate down to the location where the Maintenance Plans are located which should be the same as in the source server (see step 1.) Right click Maintenance Plans and select Import package. Select File System in the Package location then in the Package path using the ellipsis button navigate to the location where you exported your packages to. The Package name will be automatically filled in although you could change it.
5.  If by accident you skipped step 3, don't fret, you can still do it. You will have to repeat step 4 again and this time you will be overwriting your existing package.
6. You probably want to double-check on each package to make sure there are no other adjustments need to be done. Connect to the new SQL Server's database engine this time and right-click Modify on the maintenance plans. (Management>Maintenance Plans)

That's all there is to it. I hope I saved someone time with this post.

Adventures in SQL Land: Edition and Version Upgrades

My latest task was to upgrade SQL 2005 Express version to SQL 2008 Standard version. The SQL 2005 instance was installed by Office Communicator Server 2007 R2. I thought it would be a straightforward task since I have done both SQL edition and version upgrades before. Oh how wrong I was!

Problem #1

There are two ways to get from SQL 2005 Express to SQL 2008 Standard. Both require two upgrades. One way is to first upgrade SQL 2005 Express to Standard and that to SQL 2008 Standard. The other way is to upgrade SQL 2008 Express and do an edition upgrade to Standard.

First I tried to go the Express-->Standard-->SQL 2008 route and that's where I hit the first problem. The SQL 2005 Express version had SP2 applied already. That meant that running the SQL 2005 setup.exe with the SKUUPGRADE=1 switch gave me the following error: "Your upgrade is blocked because the existing component is a later version than the version you are trying to upgrade to. "

Ok. No big deal. I'll just get the SQL 2005 files with SP2 or SP3 already included.

Problem #2

Unfortunately the only install files we had did not have a separate Setup.exe which is required to do an edition upgrade for SQL 2005. So I abandoned this route.

Problem #3

Let me try to upgrade to SQL 2008 Express first then. I didn't have the install files so I had to download it from Microsoft. I had a little trouble with that because I kept coming across with the web installation option as opposed to finding the old-fashioned download page. Finally our server admin suggested I'd search for the redistributable SQL 2008 Express so I found it. I feel a dummy for not thinking about it but I rarely have to download from Microsoft as we have everything on a network share. Here is the link if you need it: http://www.microsoft.com/downloads/details.aspx?FamilyID=58ce885d-508b-45c8-9fd3-118edd8e6fff&displaylang=en
Problem #4

Finally I have the exe to run but when I run it I don't get the option to select an instance to install. What on Earth is happening? After searching for this and searching for that I came across someone mentioning that you can't upgrade 32-bit version to 64-bit. Hmmm... Sure enough the SQL 2005 Express that the Office Communicator Server 2007 R2 installed is the 32-bit version even thought Office Communicator Server 2007 R2 can only be installed on a 64-bit server. Nice job, Microsoft. So I downloaded the 32-bit version of the installation file and now I was finally be able to upgrade from SQL 2005 Express to SQl 2008 Express.

Problem #5

I'm halfway there now let me do an edition upgrade. I run the Setup.exe file for SQL 2008 and under the Maintenance I select edition upgrade. On the system check there is a error: "Upgrade architecture mismatch" The upgrade is running in 64-bit so it can't upgrade the 32-bit version of SQL. You are trying to tell me that the upgrade can't detect what version I want to upgrade? OK. Onto some more searching. Aha! I found it. On the main screen before selecting the edition upgrade there is an Options section on the left side of the window. Clicking on that I can tell the upgrade process what architecture to use. I select x86 and the edition upgrade us finally successful!

Problem #6

The original task has been completed but as a good DBA I need to set up some maintenance plans for this instance. However, SQL Agent is missing. I go to SQL Server Configuration Manager and I see that the Agent is there but disabled. Oh it will be easy! I just have to enable the Agent and start it. I should have learned this by now but nothing is easy anymore. I can't start the Agent because of the following error: "SQLServerAgent could not be started (reason: Error creating a new session)." Urgh! Back to searching for a solution. I found it on a KB article page: http://support.microsoft.com/kb/955763 Since the server is running Windows Server 2008 I had to specify a user for the Agent and gave it sysadmin rights in SQL Server. I am not happy about the last one but there is not much I can do about it.


So what should have taken me just a couple of hours at most took me more than a day. Learn from my example: run "Select @ @Version" before you do any upgrades and note both the service packs applied and architecture it is running on. If I had paid more attention to that information I could have saved some time for myself.

DBCC to the Rescue!

It's rare when I find myself in a situation like this. One of the hard drives (E) died on one of the servers that housed the log files for some databases. So log files are gone but the database (mdf) files are still intact. No big deal, we'll just recover everything from backup. Right.

Well, there was no backup done for this server. At all.

I know! I know! What am I doing as a DBA as that's the first thing a DBA makes sure of is to have backups of everything. It happened because our server admin neglected to tell me that they had SQL Express installed on this box and not only that but what he has originally intended only as a dev box for the Office Communicator backend has been promoted to live a while back!

I have tried a few tricks to get the database back online but nothing worked. In fact I made matters worse because somehow I managed to make the database dissapear (a.k.a. detach) which was not my intention.

Eventually I got the database to attach by doing the following:

• I created a blank database with the same name.
• I shut down SQL Server.
• I replaced the blank database file with the original database file.
• I deleted the blank log file.
• I restarted SQl Server.
  

So the database appeared in the list but it was in a recovery mode so still useless and I was back on square one.

I thought that's it, we lost everything... until I found this page from SQLSkills. I would like to give full credit to Paul S. Randall for this solution. His page explains everything about how to use DBCC to have SQL Server rebuild the log file in emergency mode. I will just post the actual script to use. If you need to use this script, replace DB_in_distress with the name of your own database.

--put the database in emergency mode
ALTER DATABASE DB_in_distress SET EMERGENCY;
go
--set it to single user mode
ALTER DATABASE DB_in_distress SET SINGLE_USER;
go
--rebuild log
DBCC CHECKDB (DB_in_distress, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS
go
--check to make sure DB is back online
SELECT state_desc FROM sys.databases WHERE name='DB_in_distress'
--put it back into multi-user mode
ALTER DATABASE DB_in_distress SET MULTI_USER

I'd like to mention one important point. SQL Server insisted on using the original location to recreate the log. Therefore this solution did not work for us until we recreated the E drive that caused all this problem.

Obviously, as Mr. Randall mentions, this should be used only as the very, very last resort and you can expect some data loss. However, some data loss is still better then all data lost.