Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am dealing with large imports of data into SQL Server and need to know the easiest way to perform the import and rebuilding of indexes while minimizing the effect on web services that primarily read from a named database.

At the moment the write permissions for the web server login are removed, a backup made, web config is manually updated to point to the copy, the import performed and finally the web configs pointed back to the original.

I need to automate this, preferably just on the database server.

One idea is to remove the write permissions, back up the database, restore it to another name, perform the import and reindex on the copy and then rename the new one to the old name and restore write permissions. This means some admin tasks can't be perform during an import but has the benefit of creating a back up on a regular basis.

The question is how to rename things with connections still active. Is it possible without having to change the connection string on the web server? Something clever with ports or redirects?

Any better ideas?

share|improve this question
    
Edited: I should have mentioned that I want to automate this want to try and minimize the import time so need to disable the index during the import. Hence the working copy. –  Chris Woodward Oct 25 '12 at 13:34

1 Answer 1

You assume static data - possible.

Altenratively just - use enterprise edition and reorgnaize the indices. This can be done without taking down the old index there. HA is generally tricky and an enterprise level feature.

share|improve this answer
    
Yes, data is static in the sense that if the webservice gets stale data that's fine. –  Chris Woodward Oct 25 '12 at 13:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.