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?