ESRI/SQL 2000 Question
-
I have ESRI/SDE on one of my SQL Server 2000 systems and I'm not even remotely an expert on ArcInfo.
Last Friday we had a layer blow up, and it was one of our core/heavily-used layers. It took us three hours to get it restored, and I consider that unacceptable. The layer has 43,000 or so points.
So here's my thought. As far as I can see, at least from the SQL Server level, there are no dependencies to this particular layer/table (but that doesn't mean much as we've never had the same layer blow up twice). And a table is usually just a table, right? (An ArcInfo layer = one SQL table)
So I'm thinking about creating two copies of this particular database on the same server and having two DTS jobs that would export the data to one DB on Monday/Wednesday/Friday and to the other on Tuesday/Thursday, so we'd always have the two previous days online. My thought is that if any given layer blows, we should be able to drop the table and re-load it from the most recent backup. If we have to go back further than two days, then it's off to the Tivoli or local MDB copies to restore the DB to another name and dig deeper.
My question is whether this would screw anything up. Is this a viable strategy? It's not something that I can afford to screw with if I don't have a reasonable level of confidence that it'll work.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
are you saying the restore from a backup took 3 hours to run? that's seems very long. how many gb is thd database?
---------------------------------------
elsasoft.org -
It took three hours of work, not three hours to run. It's only 43,000 rows in the specfic table. There were a couple of false starts, then trying to figure out what went wrong because we were trying to reload the layer in question from a desktop MDB.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Regular SQL Server backups to disk are your best bet. You can set the GeoDatabase(s) to full recovery and execute full/diff/tran backups scheduled as makes sense for you. When something blows up, as it not uncommonly does in SDE, you have the ability to restore to a point in time.
DTS will not provide you with the consistency between tables necessary unless there is very minimal activity in the GDB.
BTW, each object (layer, etc.) is actually composed of several tables depending upon the object type and 'options' (e.g. versioning) that are enabled.
HTH
-
One of the things that I find incredibly frustrating is the lack of SQL Server "best practices" material from ESRI. The guy who runs the system has all of the databases set as Simple recovery, so I don't have point-in-time capability. I'm aware of the versioning A & D tables, but you're right, DTS wouldn't give me point-in-time.
We had a post-mortem meeting yesterday regarding the crash. It looks like the woman who was applying the updates wasn't turning off versioning on the table before doing mass-updates, so that might have been a primary cause of the problem. We're also not yet doing statistics updates, I've only been on the job for four weeks and I've just gotten two of my four servers up to the configuration (DB config, DBCC, backups) that I want.
I definitely prefer backing up to disk, unfortunately my biggest raster DB is 175gig right now and I don't think that I have enough disk space to do a full backup, so I'm kind of stuck with Tivoli's TDB(?) agent.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Wayne,
SDE is not just one table per feature class. Since SDE provides the capabilities to have multiple versions of the map, the data is stored in the base table plus the adds and deletes table. Also there are some ancillary tables for the feature class (f-table, s-table and i-table). Also, the database that the features are stored in are incorporated in some of the tables and stored proceedures and therefore, copying from one database with anything but ESRI tools does not work. I found this out when I tried to set up a test database by using DTS to copy from the production database. SDE is a complex database setup that is not ameniable to using standard database tools.
One thing I have done is set up standard SQL-Server replication to another department but the database name must be the same for that to work.
Steve
-
Thanks, Steve. I'm slowly increasing my knowledge of ArcSDE (and honestly I'd've been happy to remain ignorant
), I just found out about the PDFs sitting in the SDE installation directory and today had them printed and wire-bound for reference: I refuse to read almost 400 pages on my monitor!We're hoping that the procedures that we worked out at our meeting on Monday may take care of future problems.
Let me ask you this, since you seem like you've been using it for a while. What recovery model do you use? Currently most of our databases for SDE are in Simple mode, and I don't like that. I'm much more comfortable with Full recovery.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Wayne,
I would definitely use Full recovery unless there is a problem with space. You will also need to make sure the transaction logs get backed up regularly or that could cause a space problem.
Steve
-
Space isn't a big issue, our largest raster DB is about 175gig and I have 163gig free on my largest partition. We're also probably going to be doing a server rebuild on our SDE box in the not-too-distant future and get some more disk in it. We don't have a huge volume of changes going on, but that would definitely change on our next annexation (I work for a city gov't).
Right now we're using Tivoli and their TDS(?) agent to directly back up the databases. I'm much more comfortable with the backup(/dump) methodology that I used for so long on previous versions of SQL Server, but that 175gig DB is going to be tricky to use that method. I'm going to be looking into something like SQL Litespeed as I'm not yet 100% confident in/comfortable with Tivoli.
My off-the-cuff plan is transaction log backups every 15 minutes throughout the day with a database incremental at noonish.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Transaction log backups will not work properly if you do not have a full backup (SQL-Server) to begin with. I would be nervous about relying on Tivoli (if it is just backing up to tape) to cover you as well. I know that there are many backup solutions that would take up less room than the standard SQL-Server backup but I have no experience with them.
Steve
-
I'm sure the owning company of this website would be happy to recommend a backup solution for you...
---------------------------------------
elsasoft.org -
TDP works just fine. If you've got it, use it. It is not so easy to figure out scheduling of backups, but the GUI is great for restoring.
-
Although It is not to your scale. We have a large raster (40gb) but it doesn't change very often. At ArcSDE 9.0, ESRI Licenses changed so that you can run mutiple instances of the SDE services on the same box. So we placed the large unchanging raster in one SDE geodatabase and all the other feature layers in another. We backup the raster geodatbase weekly with daily transaction logs, And backup the other geodatabase daily with hourly transaction logs. It was only a little painful to go through all the existing map documents and point the raster to the new instance of SDE. It certainly saved a lot of space with the SQL Server backups.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply