I had the strangest thing happen to me recently. I recently order and configured a new server for a client with SQL 2008. This was a beefy box. 8 gb of ram, dual quad core cpus, the works.

First impressions of SQL 2008 were good. Performance was good, and the SQL Server Studio had some nice usability enhancements that I appreciated. Anyway, this server supported 2 ArcSDE instances for a very high volume site. I set up SQL server to pretty much take as much memory as it wanted. I really wanted to cache as much as I could.

So, one day, I pop in and open up Process Explorer and see how we are looking. To my shocking disbelief, the server was reporting that it was almost maxed out on physical memory - right around 7.4 gbs. Wow I thought, thats a lot of RAM. Then I looked a bit closer, and the SQL Server process itself was only report 200 mb of RAM usage! Wha wha what? Where in the heck is all my memory going if SQL Server isnt using it? Again, using Process Explorer, I exported all my active processes and dumped into excel, where I added up all my process' RAM usage - and it only came to about 1.6 gbs. Where in the heck did remaining memory go?

Well, turns out, SQL Server 2008, unlike previous versions, does not report its total memory usage to task manager or to Process Explorer.

The amount of memory that will show up in Task Manager (or Process Explorer) for the SQL Server process is just what is used by the process itself. It does not represent the memory used by the SQL Server buffer pool. The SQL execution engine (SQLOS in SQL 2005+) manages its own threads and therefore takes care of its own memory allocations. The SQL command

DBCC MEMORYSTATUS

will provide a breakdown of the SQL memory clerks. In particular, take a look at the MEMORYCLERK_SQLBUFFERPOOL output, as this will tell you how much memory is being used by the buffer pool, which is likely where most of the system's memory is allocated.

For more information, see these links to the Microsoft Developer Network (MSDN) site about SQL Server Memory:

Memory Management Architecture - http://msdn.microsoft.com/en-us/library/cc280359.aspx Monitoring Memory Usage (This talks specifically about monitoring memory usage in SQL 2008. Provides some SQL-specific perfmon counters that show the actual memory usage) - http://msdn.microsoft.com/en-us/library/ms176018.aspx

In the end, I was able to use Perfmon to find all my missing memory. I used SQLServer:Memory Manager - Total Server Memory in PerfMon. Not sure I think that this is the smartest thing to do MS. I have a feeling this will freak out a good amount of folks...At least I know now though...Hope this helps someone and I can save them the 3 days of frantic Google Searches...

Hey folks...I have run into this issue twice in the past couple of weeks. 2 public access sites that we developed, all of the sudden came to a screeching halt. For some unknown reason, certain layers would start to draw extremely slow, and bogging down the entire application. Looking in the ArcIMS logs it was pretty easy to see the slowness. Layers that previous had a sub second draw time were now taking 30 secs+ to draw. After lots (and lots) of trial and error we determined the cause to be due to different ArcSDE service pack levels.

Yes, I know that ESRI drills it into our heads that we should always keep everything at the same service pack level, and now I really believe them. What was happening was this...An internal-only instance of SDE was creating SDE export files and pushing them to the external instance of SDE. The internal was at SP4 and the external was at SP2.

After upgrading the external to SP4, this bizarre behavior disappeared.

Here is a guest post from a developer friend of mine, Darina Tchountcheva who works at the city of Clearwater, Florida. I first met Darina when ROK was contracted by the city to assist them in some ArcSDE and ArcIMS migrations. It quickly became clear that I was not needed, and that Darina was going to teach me a thing or two. Darina is an amazing talent, who has also had her ArcIMS site recently showcased by ESRI...check it out here.

Anyway, without further yapping from me, here is Darina's excellent instructions on migrating ArcSDE from SQL Server 2000 to 2005.

Upgrading SDE from SQL Server 2000 to 2005.

I was looking for a step-by-step upgrade guide, and I decided to write down my upgrades steps once I go through the process. And, of course, my scenario might not be the same as yours, but if it is, I hope, it will help you get prepared and move on to SQL Server 2005. (I am kind of late, right? SQL Server 2008 is coming! Microsoft, please, slow down!)

I am not an expert SQL Server administrator, so forgive me if I haven't done everything perfect.

My configuration and scenario:

- ArcSDE 9.2 SP3, multi-database model - upgrade must happen on the same server - Intel, 32 bit, Windows 2003 SP2 - server must be rebuild and RAID configured again, because of a system drive problem - clean install of SQL Server 2005 SP2 - the upgrade must work or I am busted. :-)

Steps:

1. Get Prepared!!! - here are some articles that I received from ESRI support, which helped me get started:

http://www.sqlservercentral.com/articles/Administration/2987/

http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx#EAH

2. Decide what your upgrade path will be: in-place or side-by-side

3. Take care of transfering your users. This step must be done while the old (SQL Server 2000 SDE) is still running!

Why do you need this step? If you perform a side-by-side upgrade, a new master database 2005 will be created for your SQL Server 2005 version. There will be no logins in this database that match the users that you currently have in your spatial database(s). The script provided in the article below will create script, which you will run on your SQL Server 2005 master database and will recreate all of the logins (with their passwords) you currently have in your SQL Server 2000. Run the script and save the output as .sql or .txt file on a save location.

http://support.microsoft.com/?id=246133

And read this article to get a better understanding of how users and schemas in SQL Server 2005 affect SDE 9.2:

http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?topicname=users_and_schemas_on_sql_server_2005

4. Compress SDE database. Backup all GIS databases (if you use multi-database model you have more than one). Backup master and msdb databases.

Important: From here the steps are specific for side-by-side upgrade.

5. Decide how you will move the databases: attach, or restore. I used attaching.

If you are going to use attaching: stop the SDE service, stop SQL Server service, and copy the data+log files of all databases (including master and msdb) to a safe location. If you are restoring, you will be using your backups. Here is a link that will help you with this:

http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?TopicName=About_exporting_and_importing_ArcSDE_geodatabases&anchor=movess

6. Make sure you have some documentation and a plan how to move your DTS packages.

Luckily, most of my complicated stuff is written in PL/SQL and resides on the Oracle databases end. The few I have in SQL Server I rewrote in SSIS before the upgrade.

7. Run the SQL server Uprade Advisor, and see if there are any major problems that might occur. I personally had only DTS packages warnings and the weird Metadata packages error, which I ignored.

http://msdn2.microsoft.com/en-us/library/ms144256.aspx

8. Start upgrading (this is side-by-side upgrade):

- install SQL Server 2005

- install SQl Server 2005 SP2

- Open SQL Server Management Studio and make sure you can connect.

- Open New Query window and load the script you saved in step 3. Check to see of any of the users have a default database different than master. Change it to master, because the other databases are still not existing. Later on you can use the same Alter statements to assign the correct default database to your users. Run the script. Check to see if all the logins are created successfully. Try connecting using some of the logins as a test.

- attach the databases (do not attach master and msdb). Or if you decided to do a restore, restore your databases. Do not change the names of the databases. Open them and see if you can see tables, views, stored procedures, users. I used attach, and when I checked the users, I found out that SQL Server has created a schema for each one of the users in every database, and made the user the owner of the schema. I don't think I need a schema for every user (only for the ones creating datasets, features classes, relationships...), but they are already there, so I kept them.

Here is a link that might give you some insights on the topic: http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?TopicName=The_ArcSDE_administrative_account - change the compatibility level of your databases - I personally didn't need to change to Single_User mode:

http://blog.sqlauthority.com/2007/05/29/sql-server-2005-change-database-compatible-level-backward-compatibility/

- install ArcSDE 9.2

- run Post Installation - use the provided Installation guide and follow your scenario: dbo or sde schema.

- apply the ArcSDE 9.2 SPX (X - the current SP of all ArcSDE clients).

- open ArcCatalog and connect as administrator, and as a regular user to test the SDE. Hope, you are happy right now and there are no problems that you need to resolve.

- at this point I configured sQL Server Mail and some of my maintenance plans to run during the night - backup, and update statistics.

- everything else is up to you.

I posted a while back about moving much of our server infrastructure over to a virtual environment. I have been pleased, for the most part with the outcome. However....While I already sorta new the answer to this question before I started, I would now not recommend a ArcSDE / SQL Server instance in a virtual machine. Or at least one where there is serious competition for disk io. So, we have now migrated our production box out of a VM onto a dedicated box. However, we still have a testing environment running ArcSDE and SQL in a VM. Its just for beta applications and has pretty low load.

It was just a little too much to ask. Things ran fine, for a while, but as we brought more and more services online, it became obvious that this VM wasn't going to be able to handle the load we were throwing at it. I did even try some intermediate steps, like moving the OS and Host drive to a raid 5 array, but that still wasn't enough.

Now, with all that being said...I am still extremely happy with the Virtual Environment for applications like ArcIMS, ArcGIS Server, DNS, etc. These have worked out very well for us.

Today we have a guest entry from Steve Eakins, who is the GIS Manager at ETM, an engineering firm from Jacksonville, Florida. ROK and ETM have collaborated often on some large scale projects. Without further delay, here is Steve discussing a recent collaboration on Geodatabse replication.

Working with ROK we have been able to setup SDE geodatabase replication using the ESRI 9.2 Desktop product since ESRI added that function in their release last year.

Database replication gives us the ability, through a secure VPN connection, to maintain our data on the ROK SDE geodatabase. We can, on an automated regular schedule, synchronize only the changes thereby, minimizing network bandwidth utilization during peak hours while keeping our clients ROK hosted website up to date. Replication for us is enabled one way so that the ROK database is a child of our parent master.

Using replication reduces delays in mailing or oversights in forgetting to send all the changed data. This is a huge time saver and is truly a win-win scenario.

For more information see ESRI's website on database replication using ArcGIS 9.2 at http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?TopicName=Understanding_distributed_data

Then talk to the ROK!

Steven

Quick little tidbit that has caught me from time to time. This is only with SQL 2005 and ArcSDE 9.2 (or 9.1 with sp2).

I move a lot of data around from server to server and to client sites, etc etc. Back in the old days, you had to set up new databases and services by hand via command line. While I occasionally have to bust that out, I usually just use the post install wizard that comes with ArcSDE.

One thing to look out for when creating a new database with the post install tool...choose your SDE user password carefully. If you are like me, my SDE user gets 'sde' as its password too. Yeah, I know its bad. Point of the story though, if SQL 2005 is setup with a Password Policy and you choose a simple password like 'sde' your service will not start, because that password isnt valid with a password policy. Something more complex is required - like S$E12user. Or you can uncheck 'Enforce password policy' in that properties of the database that the wizard created for you. Unfortunatley, it wont give you an error until you try to start the service - and unless you know to look at the .../etc/sde_instancename.log for a login failure message, you'll be perplexed as to why it doesn't work.

I would expect that ESRI will update the post install routine to check for this in future releases, but until then, you know what to do.

Have you (or one of you users) ever deleted an ArcSDE layer through something other than ArcCatalog or the command line? Well then, you probably figured pretty quickly out that you shouldn't be doing that.

The problem with using other deletion methods is that the ArcSDE does not 'know' remove references to the missing tables.. There are several references to the deleted tables stored in various places inside the SDE database. So, if you made the mistake of deleting those tables on your own, you'll need to maually remove these references.

Now, I do not recommend playing around in the SDE database. I have hosed up more than one installation playing around in there. However, these command are pretty safe, and shouldnt give you any trouble....But, I cannot stree enough the importance of BACKING UP YOUR SDE DATABASE before you do this. Ok, so that means you cant send me nasty emails if it breaks.

While this script is for SQL server, you can adapt it for Oracle as well. Run this in Query Analyser (or sql+) against your SDE database (where your SDE metadata is stored if you are using the multigdb model). Just change dbname to the name of the database where your layer was, and layername to the name of the layer.

I thought that I would share with you the single best pieces of advice that you can get when loading rasters images into ArcSDE.

1. Use either ArcCatalog or Command Line, but not mix and match 2. If you are mosaicing, load the first image without pyramids and do not build statistics. Load all but one of the remaining ones without pyramids or statistics. Load the last one with pyramids and statistics checked. 3. Never, ever ever ever try to do the smart thing and load them in a big batch. That would be too simple. Instead load them in a maximum of 20 increments. Check back every 4 hours or so and repeat. 4. If you have the raw images (like tif's) load those instead of sids. They will load much quicker.

I seem to struggle with some issue or another every single tiime I try to load new images. These are my new rules to live by. They certainly are a pain, but it is nice when they are done.

This one slipped under my radar. Not only is ArcSDE 9.1 service pack out...it also extends support for SQL Server 2005. While I have not done any testing with 2005, I hear its really quite impressive.

Here it is....ArcSDE 9.1 SP1

Us Coldfusion ArcIMS folks have grown to love and hate the Out_Querytable variable. For those of you that don't know what Out_Querytable is, its a query object that gets returned from Coldfusion after you send a query (spatial or attribute) to ArcIMS. This is very handy to have - becasue Coldfusion works so well with these types of objects. For you windows based folks, its essentially the same as a recordset.

The good: You can loop over them, display them, make a nice value list, query them (with coldfusion's ability to query an existing query object). Basically anything that you can do to a coldfusion query object, you can do to the Out_Querytable variable.

The bad: Things tend to get a bit hairy when you use ArcSDE based data. Who uses shapefiles these days anyway...The biggest issue for me are column names that get returned. For example, lets say I query roads. The columns that get returned will be fully qualified - like DBName.Owner.Tablename (in the case of SQl Server). Now try making a nice valuelist with VALUELIST(Out_QueryTable.DBName.Owner.Tablename) and see what evil things happen. Luckily, a few years back, James Welch (where are ya these days James?) created a sweet custom tag called cf_ArcSDEquery, which essentially converts the query to wddx, fixes it, at converts it back to a query object. The tag used to be on ArcScripts, but I haven't seen it there in quite some time. So, I'll attach it for everyone if they don't already use it.

I also do lots of multiple spatial queries right after each other - like in a drill down identify. The problem is that lets say I find a result in the first, but not the second, and then the 3rd returns a result. If, after each query, I test to see IsDefined("Out_QueryTable") then run some processing...Its going to bomb after the 2nd spatial query - becasue it does exist - but not from that query. Of course, you can rename your queries and do all sorts of crazy work arounds to compensate for this. However, I recently discovered a fantastic undocumented function - removeRows:

Then I can do the next drill down spatial query....Hope that makes sense and or helps someone out. Don't forget, here is the code for the cf_ArcSDEquery which will makle your life sooo much easier when working with SDE data:

As we all probably know, ArcIMS and other ESRI products eat lots and lots of ArcSDE connections. Sometimes ArcSDE can even (gasp) leave orphaned connections out there. To overcome these issues, I always bump up my number of available connections.

I find myself refering to these instructions, emailing them, talking people through this over the phone, etc, quite often. This is a huge deal, but I don't feel that ESRI has stressed this enough. It used to be totally undocumented but now it is mentioned in the Config and Tuning guide for Sql Server and there is a knowledge base article found here. But, I thought I would post the info here just as a friendly reminder.

First, bump up the allocated memory: The size of the interactive and non-interactive desktop heaps is determined by a registry setting under \HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\SubSystems. Within the 'Windows' string value, there is a 'SharedSection' parameter that by default should read: SharedSection=1024,3076,512 Change the 512 to 1024

To bump up the number of available SDE connections, you need to now edit the SDE_server_config table-->prop_name-->connections change that value to the number of connections you need. Don't go overboard - just add what you need to be safe. See my previous posting for more info on how to do this.

This is just a reminder to all you old school SDE users...This one has caught me more than once. I just bumped up my connection limit, and was hittin my head against the wall as to why they were still being capped at 48. Remeber back in the day when you could just modify your dbtune.sde file or your giomgr.defs file, restart (arc)SDE and that was it? Well, just a reminder that you cant do that anymore. You need to make those changes and then use the appropriate command to import that file into your spatial database.

I know, most folks either started at version 9 or actually read instructions and already know to do this. But us old schoolers who started at (arc)SDE at version 3 have to be reminded sometimes.

One of these days I'm going to post the SDE Configuration and Tuning Guide for version 3 - all 11 pages of it, which I have saved for sentimental reasons, just to show you how tough we all had it back then. Whats that guide up too now? 200 + pages if I recall...

As many of you have read in the posting previous to this one, I have been doing a lot more direct connections to ArcSDE from ArcIMS. We'll I've been having a bit of trouble with a few things, but no big deal....However I did discover a few new things about ArcIMS 9.1.

I read about a new parameter for connection to ArcSDE on the ArcIMS whats new page:

"For Image and Feature services that use ArcSDE data, a new feature parameter defines the time interval between attempts to reconnect to ArcSDE when the SDE server becomes unavailable."

wOOt! Sounds great to me, so I go looking for it. Looks like its located in the %arcims install dir%\server\etc\aimssdeio.cfg. Open up that file and look at the new parameter connectioncheckinterval="300". First thing that came to mind for me was this is could be why it was taking my mapservices so freaking long to refresh (see this post for more info). I don't know that for sure, but I'm guessing that it is, so I immediately lowered that number to 20. I'll let you know what happens.

The next thing I noticed was a new log file getting created in that same etc directory called sdedc_SQL Server.log. Of course I check it out, because I am all about log files. Its a great start to just have a log file specifically for direct connections, but this didn't really contain a whole lot of useful information.

IDBInitialize::Initialize Failed CAN'T OPEN INSTANCE: sde:sqlserver. Spatial Engine Connection Failed (-9).

Yeah, I already know that. Thanks for nuttin. But, I applaud ESRI for recognizing the need for that log file, and I hope that with future releases, we'll get some more info. By the way, I left encrypted="true" in my sdeworkspace - and thats what threw the error above.

Ran into an interesting issue recently.

I am using Direct Connect for a pretty high volume ArcIMS mapservice that would otherwise eat a ton of connections because of all the spatial servers its using. They start adding up pretty quick.

So I notice that when I added the service, that the expected number of new connections got registered with SDE, even though I'm using direct connect. I checked this using the sdemon -o info -I users command.

I check the map service and it works fine. I stop sde, and the mapservice still works ok. However, upon starting SDE back up, these connections from the mapservice register themselves again - or so it seems when I do sdemon...I also tested the same principle with Arccatalog. Same result.

Use 2 tier when SDE is stopped and 3 if its running? What gives? Maybe this has been the default behavior all along and I just never noticed it?

Well, it seems that Sdemon -o info will report direct connections too - it is just querying the sde.sde_process_information table in the SDE database (or whatever your sde service is using for its database). Look at that table and there is a column called direct_connect that will report whether or not it is a direct connection.

However, looks like these connections still 'count against you' so to speak. You still need to bump up your number of connections, but you don't need the hardware to support it.

Howdy Folks....I had a lot of trouble with an app of mine last week. It took me a while to pin this one down. I hadn't seen any info posted anywhere on this before, so here we go...

Here is the info...

SDE 9 with sp3 SQL Server 2k IMS 9 with sp3 ServletExec 5 Jre 1.4.2 TCP keepalive is set to True on the Arcsde box

I have this application that will work fine for a few hours, and then bam, it will start throwing errors. Looking through the ArcIMS logs, I get:

[Jun 14, 2005 10:20:00 AM][3468 4000 ERROR] [ERR2407] (SDE error code -10 ) SE_stream_query : Network I/O error

I get the above most of the time, but depending on what I'm doing the application at the time (query, map request, etc), I may get the one of the following:

[Jun 14, 2005 7:38:47 AM][3500 3684 ERROR] [ERR2402] Field #SHAPE# not found.

[Jun 13, 2005 8:11:52 PM][3236 4044 ERROR] [ERR2407] (SDE error code -38 ) SE_stream_execute : Attribute column not found

[Jun 13, 2005 7:58:24 PM][3236 4068 ERROR] [ERR2407] (SDE error code -10 ) SE_layer_get_info : Network I/O error

The weird part is this...one REQUEST (image or query) will work one second, and the next it will fail with one of the above showing up in the errlog. It almost seems to alternate for a while between working and failing, then it will all seem to work fine for another hour, and then the problem shows up again for a while

My best guess is that this is a communication break between ArcIMS and SDE. After a lot of trial and error, we decided to try direct connect a try...Guess what? That did the trick! We have been error free for a week+ now.

Here is a quick primer on Direct Connect for those who are unfamiliar. Direct connect is another way of connecting to ArcSDE. All of the ArcGIS clients now (since 8.2 I think?) ship with all the necessary libraries to connect to ArcSDE without going though ArcSDE. In other words, the work that ArcSDE traditionally performs can be 'sidesteped'. There are some finer points to it, but essentially, you just need to add an SDEHOME environment variable to point to the %arcims install directory%\ArcGIS\ArcIMS\Server and then change your ArcSDE AXL connection string to instance="sde:sqlserver:datasourcename" instead of instance="port:5151" (or whatever your port is).

So, moral of the story - if you are having ArcSDE - ArcIMS based connectivity issues, it never hurts to try direct connect!