ArcSDE/SQL Server/weekly maintenance workflow question

[jennifergarner]

Hello everyone-

I am a GIS Analyst and manager that was tech savvy enough to be the one assigned with the task to manage and maintain our new enterprise GIS. I do not have a CS background and do not have any programming knowledge as of yet (although Python is on my "to do" list). I  love working with computers: software and physically building out my own systems, and have always had an interest in databases among other things.

I am in the process of learning SQL Server 2012, but it is slow going as I am also managing Arcserver/Citrix and working on a team of 3 to train about 30 non-traditional GIS users on ArcMap. We are migrating over from MapInfo. I am also developing exercises and workflows for training while still creating maps along with these other tasks. I am currently the only data editor for our versioned database. I hope to train others if things ever slow down a little. I worked directly with consultants to help develop our schema and am making tweaks to it as we roll new testers on to the system. I have set it up so our users can edit points and basic data in our main feature classes, but none know how to do any data checks and make sure all the foreign keys to the related information, match up. I'm also doing any QC checks for things like duplicates, incorrect domain edits in the attributes, etc...basically anything that would mess up a query or is wrong.

With all that being said- I'm not able to do much QC during the week. If I was able to go through that process during the week, I am still faced with having to pull in all the edits that our people make on Friday so I can go through the whole process of checking the data/reconcile/posting/deleting versions/rebuilding indexes/analyze datasets/compress/rebuild indexes & analyze datasets again/recreate the versions then restart all our online services again. Without knowing our schema- I know it may be difficult to tell how long this should usually take. I've automated what processes I can, but a good bit is hands on editing and cleanup. Most of our users are not GIS professionals, so I feel there's a little more cleanup than normal. I'm seeing anywhere between 1000 - 100,000 edits a week in version changes. Of course this whole process cannot be done while users are connected and we can't take them offline during the week. We also want to stop all incoming connections so I don't get any new edits while working the final changes each week.

My company has charged me with seeing what this process is like for other companies on an enterprise system that have such custom requests and edits.

So here is the main question I'm getting out now that you have some background on what we are trying to do: Is it fairly normal for the GIS dba to have to work the weekends? 

I'm not currently seeing a way around it. I am the only DBA support btw. We do not have a regular SQL Server DBA, so I am learning both sides along with my other tasks. I've tried to do this every other week, and the database becomes too slow for the production speed we must maintain.

Thoughts? Suggestions?

Thanks 🙂

[WilliamCraft]

Weekends are pretty much mandatory as a DBA, whether a GIS DBA or not.  That being said, I don't see a need for this to occur every weekend unless there is some very special, specific need to do so.  We have what's called "Database Weekend" at my organization where we have block time (planned downtime) for our production databases in order to perform necessary maintenance that couldn't otherwise be done when users are connected.  Friday nights at close of business tend to be the least disruptive time to begin this type of work.  For us, this includes rebuilding geometric networks, compressing to state 0, making schema changes that require exclusive locks, server patching and rebooting, and a variety of other tasks.  The number of people needed for this type of work is dependent on the complexity and size of your environment as well as the amount of maintenance that you need to do.  Database weekend is once per month for us, and we publish the schedule in advance so users know that at 5 PM on Friday night we will be taking the databases down.  We send out an email to the user community a few days beforehand in order to remind them of this, and we send another email to them when the databases are available again for the users (this could be late Friday night, Saturday afternoon, or Sunday morning depending on the volume of work).  So, I think the answer to your question is "yes, you should plan to work weekends but probably not every weekend".  If you have some control over when maintenance is done and how it is structured, then I would suggest setting up a workflow with block time (e.g., planned downtime) like I've described above.  That way everyone knows what to expect.  Changes that require down time should wait until Database Weekend, unless there is an emergency of some sort. 

Also, analyzing objects and rebuilding indexes can be done with users connected during regular business hours, although many will script this to run nightly or weekly.  I would also recommend a nightly automated reconcile and post (depending on your workflows and versioning structure) followed by a compress if your edit volume is what you say it is.  That should reduce the wait time for your block time compress when and if you need to go to state 0.  One additional thing for the moment... if you want to prevent users from accessing the database(s) while you are performing your QA/QC prior to reconciling and posting, there are many approaches that work for this, one of which includes temporarily locking non-essential database accounts.  I've had good luck with this approach because I can lock out regular users but not folks on the database team (i.e., administrators).  Of course, you'll want to script this if you have a lot of users.