Author Archives: sqldch

SQL Server upgrade to 2008R2 and “Wait on the Database Engine Recovery handle failed”

  By | Random errors and weird experiences
Leave a comment

Recently, I was tasked with doing an in place upgrade of SQL Server from 2005 to 2008R2.  My environment was virtualized so I had the luxury of snapshot’ing the server before I performed the upgrade.  This was fortunate, as it took me three tries to be successful.

My first attempt failed with the following, non-descript message:

“Wait on the Database Engine Recovery handle failed.  Check the SQL Server error log for potential causes.”

Put that phrase into Google and you’ll get lots of hits.  One of ’em mentioned that there could be problems when renaming the ‘sa’ account, for security purposes:

http://support.microsoft.com/kb/968829

I was on to something, as I had disabled the ‘sa’ login from connecting remotely.  I reverted to the snapshot, enabled the ‘sa’ account and ran the upgrade again.  FAIL!  With the same error message.

My next thought was possibly a problem with the account that was running the SQL Server service, reference this forum post:

http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-setup/9997/SQL-Server-2008-Installation-problem

Red herring, as I always use the SQL Server Configuration Manager to set the owner of my services.  But, I set the accounts back to LOCAL SYSTEM or LOCAL SERVICE and ran the upgrade again.  FAIL!  With the same error message.

All this time I have been looking in the error logs for the smoking gun, but only seeing these:

1&2. Event ID 17182: TDSSNIClient initialization failed with error 0x7e, status code 0x3a.

3. Event ID 17826: Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

4. Event ID 17120: SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems

These related to the deprecated protocol VIA, which I knew should not be enabled.  I passed over them each time thinking that the messages were in response to the disabling of the service.

After reverting to the snapshot, I went into SQL Server Configuration Manager.  The VIA protocol was enabled…

It appears that the previous two upgrades were successful, as on my third attempt when I received the “Wait on the Database Engine Recovery…” error and the failed upgrade message, I went into SQL Server Configuration Manager and tried to start the service manually.  It failed.  I then disabled the VIA protocol and the SQL Server service started up without issue.  I queried the database engine to see it’s version:

select @@version

and it returned

Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 

Not wanting to take any chances, I reverted to the snapshot,  disabled the VIA protocol, enabled the ‘sa’ account, left the service owners as my domain accounts and re-ran the upgrade.  It completed successfully.

Getting information from Oracle into SQL Server using SSIS

  By | Working with Oracle
Leave a comment

I needed to populate a SQL Server database with information from an Oracle database, but found when I created a linked server and ran the INSERT statement manually, I was getting errors regarding truncation and datatypes.  I had already run this successfully using the IMPORT data wizard (and had my application owner confirm the data) so was a bit confused.  SSMS wasn’t reporting which row or column was problematic.  Overall, my goal was to run a nightly job to run the INSERT statement, but this wasn’t working.

Ultimately, I found that during the IMPORT data wizard, I could choose to save the process as an SSIS package, in the MSDB database.  I chose this option, created a job to call the package and was off and running.

This was great, but what if there were problems during the data move?  Doing some research, I had come across this outlining logging in SSIS packages.  I exported my existing package to the file system, opened it up in BIDS and gave it some logging options.  I renamed it, imported it back into MSDB and altered the job to call the new package.

How to export a package from the MSDB database:

Open SSMS, choose “Integration Services” for “Server Type:”.  Enter your SSIS credentials.

In the “Object Explorer” pane under “Integration Services”, expand “Stored Packages” -> MSDB.

Right click the package to export, choose a location on the file system and name for the package.

To edit this package in BIDS:

Open BIDS, choose “File” -> “Open” -> “File.  Locate and choose the freshly exported package.

To add logging to a file for this package:

In BIDS, choose “SSIS” -> “Logging”.

Choose the “Provider Type” -> “SSIS log provider for Text Files”.  This will populate the grid below.

Click on the “Configuration” field, a drop down will appear.  Choose “New Connection”.

A new window will open, you can choose to log to a new file, existing file, new folder or existing folder.  I chose a new file each time and put it in my SQL Server log directory (…\MSSQL.1\MSSQL\Log)

Create a linked server to an Oracle database (and not lock up the instance)

  By | Random errors and weird experiences, SQL Server w/o the GUI, Working with Oracle
6 Comments

I’ve created numerous linked servers to Oracle from SQL Server and what should be a straightforward process using SSMS has caused me some headaches.  For whatever reason, when using the GUI, I’ve actually locked up the entire SQL Server instance and had to restart the service when trying to create a linked server to Oracle.  This is not good when working in production. The following will outline the do’s and don’ts for this process.

To begin: download, install and configure the Oracle client to communicate with your Oracle database.  This link can assist you with that process.

Once the Oracle client is installed, here’s the steps that can cause potential problems (skip to code section for the correct solution):

Using SQL Server 2005 Management Studio, I expanded ‘Server Objects‘, right clicked ‘Linked Servers‘ and chose ‘New Linked Server‘.  I entered a name in the ‘Linked Server‘ text box then went to ‘Provider‘ (after choosing the ‘Other Data Source‘ radio button).  The drop down showed me a list of the drivers installed; to connect to Oracle we have two choices: the Microsoft driver, called ‘Microsoft OLE DB Provider for Oracle‘ and the Oracle driver, ‘Oracle Provider for OLE DB‘.  Here’s where I ran into problems: if I chose the Oracle driver, about half the time the drop down would freeze and eventually the instance would become unresponsive.  I’d have to restart the SQL Server service.

Here’s the code section, showing how to create a linked server using T-SQL:

-- Create the linked server
EXEC master.dbo.sp_addlinkedserver
@server = N'LinkToOracle', -- name of the Linked Server
@srvproduct=N'Oracle',
@provider=N'MSDAORA', -- Microsoft driver
@datasrc=N'myOracleDatabase' -- TNS name of the Oracle database
go
 
-- Add the user to connect to the Oracle database
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'LinkToOracle', -- name of the Linked Server
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'myOracleUser', -- Oracle database user
@rmtpassword='########' -- Enter the correct password here
go

The above code will create a linked server called ‘LinkToOracle’ using the Microsoft OLE DB driver, then using an Oracle database user to authenticate.  To use the Oracle OLE DB driver, replace the ‘@provider’ line in the top call to the sp_addlinkedserver stored procedure with:

@provider=N'OraOLEDB.Oracle'

To test connectivity, open a query window and do a select from a table that the Oracle user has access to.  The following assumes an ‘Employee’ table in the ‘HumanResources’ schema exists in the Oracle database:

SELECT count(*) FROM LinkToOracle..HumanResources.Employee

Notice the double dots between ‘LinkToOracle‘ and ‘HumanResources‘.  This denotes use of the default catalog of the linked server.

To send the query to the Oracle database for processing, which would allow you to write PL/SQL, use the OPENQUERY command:

SELECT * 
FROM OPENQUERY(LinkToOracle, 'select count(*) from HumanResources.Employees')

SQL Server service won’t start after Reporting Services and Analysis Services install

  By | Random errors and weird experiences
Leave a comment

I received an error after trying to start the SQL Server service for my 2005 default instance:

 

“WMI Provider Error [call to WMI Provider returned error code: 0x800742a2]”

 

In the event viewer, I got:

 

“initerrlog: Could not open error log file ‘D:\sqlserverdata\MSSQL.1\MSSQL\LOG\ERRORLOG’. Operating system error = 5(Access is denied.).”

 

A search of the web brought me a few different hits for solutions, one being a Microsoft KB:

http://support.microsoft.com/kb/955496

Unfortunately, this wasn’t exactly my problem, but it was similar.

 

My scenario:

SQL Server 2005 Enterprise edition install (database engine only), as the default instance.  I used Windows domain accounts as the various owners of the services.

Few months later, SQL Server 2008 R2 install (database engine only) as a named instance.  I used the same Windows domain accounts as owners of the services for the 2005 install (bad idea apparently).

Another few months later, I needed Reporting and Analysis services.  Installed those two options into the 2008 R2 named instance.

Analysis and solution:

When I attempted to start my 2005 service, I received the above errors.  Looking at the ACL for the default instance folders, it showed that the domain account I had used to own the SQL Server and SQL Server agent services were no longer in the list.  There were two “orphaned” (for lack of a better term) SIDs in the list.  So it appears that the second error was correct; I simply added my domain account to the top level folder of the SQL Server data folder (for me it was d:\MSSQL.1), those permissions were propogated down the tree and I was able to start the service.

See http://msdn.microsoft.com/en-us/library/ms143547(v=SQL.90).aspx for information concerning the default directories.

Get information about your databases

  By | SQL Server w/o the GUI
Leave a comment

Let’s begin with the catalog view ‘sys.databases’. It has lots of useful information:

-- find the name, owner, create date and recovery mode of all user databases
SELECT 
   name,
   SUSER_SNAME(owner_sid),  -- SUSER_SNAME() returns the login when passed a 'sid'
   create_date, 
   recovery_model_desc
FROM 
   sys.databases
WHERE 
   database_id > 4;

You can also find the compatibility level, collation and state of the database, among other things. Database IDs 1 – 4 are the system databases:

1 = master
2 = tempdb
3 = model
4 = msdb

There’s a system stored procedure that gives similar information. Pass a database name as a parameter to find information about that database. If no argument is supplied, information is returned about all databases:

EXEC sp_helpdb N'AdventureWorks';
EXEC sp_helpdb;

Find the name, location and size of your database files using ‘sys.database_files’:

-- type_desc will let you know if it's the data or log file
SELECT 
   name, 
   type_desc, 
   physical_name, 
   size
FROM 
   sys.database_files

Working with SQL Server logins

  By | SQL Server w/o the GUI
Leave a comment

SQL Server ‘logins’ are what’s known as a server level ‘principal’. From BOL, “Principals are entities that can request SQL Server resources.” There are two types of logins, one where the credentials are stored in SQL Server and one where the credentials come from Windows, either locally or through Active Directory. So let’s begin with a list of our logins. Not surprising, the catalog view ‘sys.server_principals’ works nicely:

-- List logins on the instance
SELECT 
   name, 
   sid, 
   type_desc, 
   create_date, 
   default_database_name
FROM 
   sys.server_principals
WHERE 
   type = 'S' 
OR 
   type = 'U'
ORDER BY type_desc
For the above example, the various ‘types’ are:
S = SQL Server login
U = Windows authenticated login

 

The catalog view ‘sys.sql_logins’ is also useful, but only shows information for logins that store credentials in SQL Server:

SELECT 
   name, 
   sid, 
   create_date, 
   default_database_name,
   is_policy_checked, 
   is_expiration_checked
FROM 
   sys.sys_logins

 

We can create a new login with the following code:

-- Create a SQL Server login called 'testuser', adhering 
-- to password expiration and policy standards
CREATE LOGIN [testuser] WITH PASSWORD = 'StrongPasswordHere!',
CHECK_EXPIRATION=ON, CHECK_POLICY=ON
-- Create a Windows authenticated login
-- called 'testuser' in the domain 'united'
CREATE LOGIN [united\testuser] FROM WINDOWS,
CHECK_EXPIRATION=ON, CHECK_POLICY=ON

 

Conversely, we can delete a login:

-- Delete the 'testuser' SQL Server login
DROP LOGIN [testuser]
 
-- Delete the 'united\testuser' Windows authenticated login
DROP LOGIN [united\testuser]

 

Here’s more useful commands when working with logins:

-- Change 'testuser' password from 'StrongPasswordHere!' 
-- to 'AnotherStrongPassword!'
ALTER LOGIN [testuser] WITH PASSWORD = 'AnotherStrongPassword!'
 
-- Disable and enable the 'testuser' login
ALTER LOGIN [testuser] DISABLE
ALTER LOGIN [testuser] ENABLE