I needed to investigate the various setup options for using ArcSDE 9.2 with SQL Server 2005. These are condensed notes that I made while checking things out and setting up a few instances.

SDE Setup Options

There are really three sets of questions which you need to think about when setting up ArcSDE 9.2:

• where will the SDE schema tables reside?
• how will the users connect?
• how will users be authenticated?

SDE Schema Location

For the last few releases (possibly since 9.0?), you could setup SDE in two ways - with the SDE schema, or "stand-alone" databases. At 9.2, this is up front and a choice during the post-install, and they are formally named  "dbo-schema" or "sde-schema".  This is really referring to the location of the sde_* and gdb_* tables which hold the "sde metadata" (i.e. domains, metadata, relationship definitions etc).

DBO Schema

In this option, all the SDE repository information is stored in the target database. You need to run the post-install for each spatial database you want to create. If you use "dbo-schema" with SDE services, you will need a separate service for each database - which is somewhat more complex to setup, but should be workable once you get layerfiles/templates setup. I see this setup as a better option if you are using direct connect. In that scenario, there are no extra services to setup, and since everything required for your spatial database in one place, you only have to backup\restore that one database - which really helps if you store a lot of static data in ArcSDE (i.e. rasters)

SDE Schema

This is the traditional model where the SDE schema information is in a separate SDE database. The only downside to this model is managing a mix of static and dynamic databases. In order to keep all your backups in synch, you need to back up the SDE database, as well as all of the databases containing spatial data. That said, this is certainly workable, as this is how most users have things setup.

Connection Type

As with 9.1, there are two connection types: SDE Service or Direct Connection. With the SDE service connection, the client connects to a running process on the ArcSDE server (giomgr.exe), which communicates with the database. Using Direct Connect, there is no running "SDE" process on the database server, and the client handles the details of interacting with the tables. Although direct connect was slower in previous releases, ESRI has done a lot of work to reduce the network "chatter", so the performance is close to being on-par with the SDE Service.

The really big upside of direct connect is the off-loading of the "SDE" work to the client. On a fully loaded SDE Server, approximately 1/2 of the load is the ArcSDE process (according to the ESRI System Architecture document & class). By moving this load to the client, the database server can handle almost twice the number of connections. When I heard this, my initial thought was - "that just makes the client slower". While this is true to some extent, as a percentage of the entire transaction, the SDE communcations is just a tiny percent as comparte to the rest of the client (ArcMap for example) Another way to put it is that the load gets big on the SDE box because it's the focal point for a lot of little processes from all the clients. Re-distributing that load out to the clients has little noticable impact, and your database scales way better. Which is good because DBMS licensing is not cheap.

It's worth noting that while Direct Connect has many upsides,  it does add complexity to upgrades. The version of the client dlls (installed with Desktop/IMS/Server etc) must exactly match ArcSDE. Thus, to do any upgrade you must upgrade all the software at once. As I understand it (and I'm still waiting for confirmation of this) this includes service packs. Conversely, if you use an ArcSDE service, this acts as a version proxy between the client and the underlying database, and thus you have more flexibility with mixing software versions when deploying an updated. Just something to keep in mind when designing your system.

Database Authentication vs Windows Authentication

Regardless of how you have setup the schema or the connection, you can now use either Windows or Database authentication. Windows Authentication is convenient in that you do not need to create additional logins in the database, and you can assign privileges to groups of Windows Logins from within ArcCatalog - thus simplifying the database managment.

Quick note on Performance...

In my experience, direct connect seemed slower during the inital connecting to the spatial database. Once connected, there was no noticable difference. Not sure if this is a function of my setup (I set up ArcSDE to use services, then I stopped the service and specified direct connect in ArcCatalog), but connections via ArcSDE were almost instant. Both were using Windows Authentication. My suggestion is to try it out and see what works best for you.