ArcSDE/SQL Server/weekly maintenance workflow question

Discussion created by jennifergtscg on Mar 1, 2014
Latest reply on Mar 1, 2014 by crafty762
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 :)