Frequent use of attach and detach

We are considering an architecture in which "detach" and "attach" are used
on a daily, automated basis for about 50 databases, each containing about 10
gigs of data.
We use network attached storage (Network Applicance).
We want to have a farm of load servers for a data warehouse. When a load
server in the farm is available, it "attach"es a local database to a raw
database file "F" and loads data. After loading data, the database is
detached. Another server later detaches its local database file and
attaches to file "F."
This scheme frees personnel from manually configuring servers around
unpredictable load times. It is virtually impossible for a human to
optimally allocate and schedule machines for this task.
We contacted MS tech support to get their feedback and they don't advise
such a strategy. The consequences of frequent use of "attach" and "detach"
are unknown, according to tech support. This seems a little strange to me,
since "attach" and "detach" are real features.
Has anyone tried to do something like this?
Thank you.Hi Terris.
I'm inclined to agree with you on this as you're right - attach / detach is
a feature that should work regardless of usage frequency.
However, Replication stands out to me as an obvious alternative to your
solution. Have you considered it? If so, why was it discarded? I'd suggest
that replication might be your best bet as it's purpose built for scenarios
like this and if used, would help define an architecture for the solution
that is easier to maintain. I say this due to familiarity with DBAs
generally with replication and also availability of tools for managing
replicated processes - you'd also need to build admin tools capable of
monitoring your solution..
HTH
Regards,
Greg Linwood
SQL Server MVP
"Terris Linenbach" <nospam@.nospam.com> wrote in message
news:%23SdL896CEHA.3132@.TK2MSFTNGP11.phx.gbl...
> We are considering an architecture in which "detach" and "attach" are used
> on a daily, automated basis for about 50 databases, each containing about
10
> gigs of data.
> We use network attached storage (Network Applicance).
> We want to have a farm of load servers for a data warehouse. When a load
> server in the farm is available, it "attach"es a local database to a raw
> database file "F" and loads data. After loading data, the database is
> detached. Another server later detaches its local database file and
> attaches to file "F."
> This scheme frees personnel from manually configuring servers around
> unpredictable load times. It is virtually impossible for a human to
> optimally allocate and schedule machines for this task.
> We contacted MS tech support to get their feedback and they don't advise
> such a strategy. The consequences of frequent use of "attach" and
"detach"
> are unknown, according to tech support. This seems a little strange to
me,
> since "attach" and "detach" are real features.
> Has anyone tried to do something like this?
> Thank you.
>|||Hi
Are you using 'Multiserver Administration' in the SQL Agent'
If not look at this.
Think twice on using replication, there's a lot of management.
Regards
Jesper
-- Greg Linwood wrote: --
Hi Terris.
I'm inclined to agree with you on this as you're right - attach / detach is
a feature that should work regardless of usage frequency.
However, Replication stands out to me as an obvious alternative to your
solution. Have you considered it? If so, why was it discarded? I'd suggest
that replication might be your best bet as it's purpose built for scenarios
like this and if used, would help define an architecture for the solution
that is easier to maintain. I say this due to familiarity with DBAs
generally with replication and also availability of tools for managing
replicated processes - you'd also need to build admin tools capable of
monitoring your solution..
HTH
Regards,
Greg Linwood
SQL Server MVP
"Terris Linenbach" <nospam@.nospam.com> wrote in message
news:%23SdL896CEHA.3132@.TK2MSFTNGP11.phx.gbl...
> We are considering an architecture in which "detach" and "attach" are used
> on a daily, automated basis for about 50 databases, each containing about
10
> gigs of data.
> server in the farm is available, it "attach"es a local database to a raw
> database file "F" and loads data. After loading data, the database is
> detached. Another server later detaches its local database file and
> attaches to file "F."
> unpredictable load times. It is virtually impossible for a human to
> optimally allocate and schedule machines for this task.
> such a strategy. The consequences of frequent use of "attach" and
"detach"
> are unknown, according to tech support. This seems a little strange to
me,
> since "attach" and "detach" are real features.|||Thanks for your reply.
Downsides to replication:
1. Additional network I/O
2. We would like the reporting database to be read-only to avoid any sort of
locking
3. Complex to set up and monitor
4. We are concerned about CPU and I/O overhead of indexed views
5. We are concerned about atomicity and deadlocks
6. It doesn't solve our load farm goals. We want to dedicate a CPU to a
database-intensive process (mostly stored procs).
Our objectives don't stray very far from those of grid computing. We are
trying to avoid the bottleneck represented by the database server. Manually
assigning hardware to a database server is cumbersome (impossible) for ASPs,
and I have no doubt the practice will be eliminated by database vendors
eventually in a seamless fashion. Today the best we have to work with is
attach/detach, unless somebody else out there has a better idea.
Thanks,
Terris
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:upAPAH9CEHA.3256@.TK2MSFTNGP09.phx.gbl...
> Hi Terris.
> I'm inclined to agree with you on this as you're right - attach / detach
is
> a feature that should work regardless of usage frequency.
> However, Replication stands out to me as an obvious alternative to your
> solution. Have you considered it? If so, why was it discarded? I'd suggest
> that replication might be your best bet as it's purpose built for
scenarios
> like this and if used, would help define an architecture for the solution
> that is easier to maintain. I say this due to familiarity with DBAs
> generally with replication and also availability of tools for managing
> replicated processes - you'd also need to build admin tools capable of
> monitoring your solution..
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Terris Linenbach" <nospam@.nospam.com> wrote in message
> news:%23SdL896CEHA.3132@.TK2MSFTNGP11.phx.gbl...
used
about
> 10
load
> "detach"
> me,
>