Restoring a SQL Server 2000 database backup onto SQL Server Express 2005 using SSMSE CTP

by acha11 5. May 2006 02:01

i was on a client-of-a-client-of-a-client-site yesterday, parachuted in to address some issues with a .NET thick client application i hadn't seen before. the thing was a VB WinForms client running on 2000 and XP, making (predominantly) stored procedure calls to a SQL Server 2000 back end. Two versions of the client had been deployed:

  • a older version, running on five client machines in .NET 1.1 using the Crystal "9" (i believe) bundled with VS2003
  • a newer version, running on a single client machine in .NET 2.0 using Crystal 10 (bundled with VS 2005) and including some new functionality.
My problem (one of my problems, for there were many) was how to establish a dev environment on my XPSP2 notebook given that I had only SQL Server Express 2005. After a quick survey of the web, it seemed as though restoring SQL Server 2000 backups to SQL Server Express 2005 should be possible, but that people out there had run into a dauntingly wide range of disturbingly un-informative error messages while attempting to do so.

So, the first big find that I'm really happy with is Microsoft's SQL Server Management Studio Express (currently available in CTP release). It provides (in my opinion) a nicer UI for dev/admin-y work than Enterprise Manager or Query Analyser, and is free, as far as I can tell. For a CTP, it was quite robust. I didn't run into any crashes or odd behaviours other than in the area of restoring backups, which brings me back to our topic.

Having used the client's SQL Server's licensed Enterprise Manager, on their network, to perform a full backup of the database in question, and transferred it to my laptop using a USB key, i tried using SSMSE to restore it directly:

 image

but i got this error:

 image

Or, for those about to google: System.Data.SqlClient: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL\Data\DBName_Data.MDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Express.Smo)

anyway, the fix i found was to use mark russinovich of sysinternals' junction tool to set up an NTFS reparse point (kind of like a hard link in Unix) which causes requests to the MSSQL\Data folder the SqlClient was looking in on behalf of SMO above to be directed to the same location as requests to the folder the actual data turned out to be stored in (which i determined using filemon) by running this command-line:

 image

C:\>junction "C:\program files\Microsoft SQL Server\MSSQL"
   "C:\program files\Microsoft SQL Server\MSSQL.1\MSSQL"
Junction v1.03 - Win2K junction creator and reparse point viewer
Copyright (C) 2000-2002 Mark Russinovich
Systems Internals - http://www.sysinternals.com
Created: C:\program files\Microsoft SQL Server\MSSQL
Targetted at: C:\program files\Microsoft SQL Server\MSSQL.1\MSSQL

after which, i was able to restore the database successfully and get some actual work done. hooray for kludgey hacks to deal with CTPs, semi-supported scenarios and high-pressure client environments!

Serviced component code not re-entrant

by acha11 7. October 2005 02:14

after around 30 person-days of effort working on improving the performance and locking characteristics of a particularly horrendous distributed transaction in our .NET, ServicedComponent-based legacy architecture, we encounter this microsoft hotfix which resolves the issue instantly.

to be precise, this wasn't actually a performance issue, it was a process-wide hang which snared any thread that made a ServicedComponent call after something, somewhere happened. we're still not sure what the something is, because the microsoft hotfix bug description basically says "the servicedcomponent code we shipped isn't re-entrant. here's an unsupported, available-by-phone-only, hotfix". a shitty level of support, imho, given what it cost us to diagnose the problem.

the top portion of the stack trace of the hung threads looks like this:

system.enterpriseservices.thunk.dll!System.EnterpriseServices.Thunk.Proxy.RevokeObject(int cookie) + 0x80 bytes	
system.enterpriseservices.dll!System.EnterpriseServices.ServicedComponentProxy.CleanupQueues(bool bGit) + 0x71 bytes	
system.enterpriseservices.dll!System.EnterpriseServices.ServicedComponentProxyAttribute.CreateInstance(System.Type serverType) + 0x3b bytes	
mscorlib.dll!System.Runtime.Remoting.Activation.ActivationServices.IsCurrentContextOK(System.Type serverType, System.Object[] props, bool bNewObj) + 0x4b bytes	
mscorlib.dll!System.Activator.CreateInstance(System.Type type, bool nonPublic) + 0x43 bytes	
mscorlib.dll!System.Activator.CreateInstance(System.Type type) + 0x8 bytes	  

the key warning sign is the RevokeObject method at the top. from the
"CleanupQueues" method (second in the call stack), i'm inferring that
the code does opportunistic clean-up each time ServicedComponentProxyAttribute.CreateInstance() is called. something
has hosed the queue structures, or something referenced by them,
causing the RevokeObject to block infinitely, loop, or otherwise die.
looking at the disassembly for RevokeObject, there's some GIT (global
interface table) calling going on which is opaque to me, given my level
of understanding. i haven't drilled down any further than that.

other ... people ... have ... encountered the same problem. the cost to developers and clients is significant. this is not the right way to manage an issue of this sort, MS.

update [later the same day]: a little more research turns up a DisableAsyncFinalization registry key:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\COM3\System.EnterpriseServices]
"DisableAsyncFinalization"=dword:00000001
which is suggested as a possible solution here.

an ms kb article exists titled FIX: COM+ application that uses the Global Interface Table (GIT) may deadlock. remember that within RevokeObject, there's a GIT-style method call. two excerpts from the article:

  • "If you experience this issue, multiple threads in the process show call stacks that involve access to the Global Interface Table (GIT)."
  • "When you use COM+ components that are written by using managed code, such as Visual C# or Visual Basic .NET and you do not explicitly call the Dispose method on these objects."
so it's possible that our app is just not being rigorous enough about doing its Dispose()s/using()s. more later.

Tags:

Miscellaneous Oracle howtos

by acha11 25. November 2004 03:01

transactions and pl/sql developer

i'm just learning my way around pl/sql developer, which generally seems to be a more productive environment for day-to-day development work than, oh, say, oracle enterprise manager. haven't used toad enough to do a fair comparison.

something which confused me because i'm new to oracle dev, and confused simon because he's used to toad rather than pl/sql developer, is that dev windows in pl/sql developer tend to reuse existing transactions, so that if you run DML in window a without committing, then read from that table in window b, you'll see uncommitted changes. if you read from that table in a completely separate sqlplus window, you won't see the uncommitted changes.

run a script against a db from the commandline

 

sqlplus /@ @

e.g.:

 

C:\andrew.chalmers>sqlplus system/passwordhere@aboded12_local @x.sql
SQL*Plus: Release 9.2.0.5.0 - Production on Thu Nov 25 09:12:03 2004
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Table created.
Package created.
Synonym created.
Package body created.
SQL> quit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

submitting multiple chunks of data to a(n Oracle) stored procedure in a single hop

there are a few different approaches.

  • comma delimited varchar2 params - supported by ODP.NET and MS provider, ugly as hello kitty, need to write an encoder at the middle tier and a decoder in the database. Supports jagged input vector sizes (see below).
  • ArrayBinding over a standard stored procedure - ODP.NET only. Stored procedure is standard, middle tier effort is slightly lower than the encoder necessary for comma delimiting. Does not support jagged input vectors (see below).
  • Associative Arrays - Stored procedure accepts one associative array for each input parameter. Middle tier effort is about the same as that for ArrayBinding. Supports jagged input vectors.
I haven't done any performance testing as yet. I don't know enough off the top of my head to identify a massive perf advantage or disadvantage in any of the approaches.

"Jagged input vectors" can best be explained with an example. Consider a simple "InsertParentRow" stored proc which accepts three parameters, one for each field in the target table. If fifty rows are to be inserted, each of the three parameters will need to take fifty different values in turn. Consider now a "InsertChildRow" stored proc which accepts two parameters, one for each field in its target table; one hundred rows are going into this table, so each of the parameters to this proc will need to take on a hundred values in succession. Imagine now that we aggregate the two sprocs into a "InsertParentAndChildRows" sproc. 50 rows are going into the parent table, and 100 into the child. The first three params (destined for the parent table) must take 50 different values, and the last two must take 100 - this is an example of jagged input vectors. Because ArrayBinding requires you to provide a fixed number of values for each parameter, you'd be in a position where you have no data to provide for the parent table fields in the last 50 parameter value sets. Because comma-delimiting and Associative Arrays support jagged input vectors, this isn't a problem you need to deal with in those approaches.

We're using Associative Arrays to aggregate tens of row inserts into a Grandparent, parent and child table complex into a single stored procedure call.

Tags:

Powered by BlogEngine.NET 1.4.5.0
Theme by Mads Kristensen

RecentComments

Comment RSS