Re: Duplicating a database

From: Alejo Leguizamo \(MVP SQL\) (SQL_at_sql.sql)
Date: 11/07/04 

Date: Sun, 7 Nov 2004 15:11:48 -0500

Correct me if i am wrong, but what i think Randy is looking if for an
alternative to get a Query environment. So, if can make use of indexed
views, to point all the reporting needs to there, he will have an overload
(i do not know if high o low until seeing the envieronment ) on his OLTP,
while having high responses to his queries, given that Indexed views make
them real data.

Jus t my 2 cents :) 

-- 
Alejandro Leguizamo
MVP SQL Server
Colombia
"Danny" <istdrs@flash.net> wrote in message 
news:reXid.20306$5b1.9749@newssvr17.news.prodigy.com...
> Randy,
>
> What kind of fault tolerance are you looking to solve?  Hardware or 
> Software.  I assume Clustering is too expensive as well.  Availablity 
> solutions run a sliding scale of cost vs. maintenance and overhead. 
> You've banned most of the good solutions.  Although note the SQL 2005 DB 
> mirroring will most likely still be expensive (Enterprise edition and 
> separate servers with a third server to manage the resources)  I doubt 
> anyone is going to come up with an awesome low cost, low overhead, easily 
> maintained solution. :(
>
> Alejandro - Indexed Views???  to help with availability???
>
> Danny
>
> "Alejo Leguizamo (MVP SQL)" <SQL@sql.sql> wrote in message 
> news:%23ejNFy4wEHA.3336@TK2MSFTNGP11.phx.gbl...
>> have you tried indexed views over your system?
>>
>>
>> -- 
>> Alejandro Leguizamo
>> MVP SQL Server
>> Colombia
>>
>> "Randy Rabin" <randyr@channeladvisor.com> wrote in message 
>> news:u8OlZjqwEHA.3840@tk2msftngp13.phx.gbl...
>>> Hi,
>>>
>>> Hoping someone out there has some advice on what to me seems like a 
>>> common
>>> database problem.
>>>
>>> We have a high-throughput 24/7 transactional system that we're looking 
>>> to
>>> mirror to a different physical server, the intent being to redirect our
>>> query system which right now hits the OLTP databases, to instead hit a 
>>> copy
>>> of those databases. The query system is live; it's part of our website 
>>> UI so
>>> it needs to be fast and always available.
>>>
>>> My question is, we have yet to find a good solution for pumping the data
>>> from one server to the other. The requirements are that the target be 
>>> always
>>> online, near real-time, requiring few or no changes to the application, 
>>> and
>>> that it runs with little to no performance hit on the OLTP systems.
>>>
>>> We've already considered these options, and are not happy with any of 
>>> them:
>>>
>>> 1) Logshipping: target is not always online, plus it's not near 
>>> real-time
>>> enough.
>>> 2) Transactional replication: makes schema changes unwieldy, adds load 
>>> to
>>> the OLTP servers.
>>> 3) Triggers to duplicate changed rows: also adds extra load on the OLTP
>>> servers.
>>> 4) Database mirroring: not until SQL2005, plus the mirror targets are 
>>> not
>>> online.
>>> 5) Filesystem replication: target databases are not online
>>> 6) SAN solution: not much experience here, but guessing again that the
>>> target databases cannot be online while mirroring is running??
>>>
>>> The schema doesn't need to change, that is the query databases can be 
>>> the
>>> exact same schema as the OLTP (e.g. we do not have a requirement for 
>>> data
>>> transforms mid-stream).
>>>
>>> We're open to any options, including 3rd-party software or hardware
>>> solutions. I'm posting this to the datawarehouse group as it seems like 
>>> this
>>> is the same basic problem as populating and maintaining a warehouse from
>>> OLTP data without overburdening the live systems.
>>>
>>> Thanks for the help!
>>> Randy Rabin
>>> ChannelAdvisor Corp.
>>>
>>>
>>>
>>
>>
>
>

Relevant Pages

  • Re: Duplicating a database
    ... MVP SQL Server ... > We have a high-throughput 24/7 transactional system that we're looking to> mirror to a different physical server, the intent being to redirect our> query system which right now hits the OLTP databases, to instead hit a> copy ... > that it runs with little to no performance hit on the OLTP systems. ... target is not always online, plus it's not near real-time> enough. ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Duplicating a database
    ... Well Randy, you got me! ... MVP SQL Server ... > latency) set of read-write databases on it. ... >> on his OLTP, ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Duplicating a database
    ... Availablity ... with a third server to manage the resources) I doubt anyone is going to ... >> that it runs with little to no performance hit on the OLTP systems. ... >> target databases cannot be online while mirroring is running?? ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: multi table multi database join?
    ... you could also do this using a "linked server" allowing to perform joins ... the query should likely retrieve results based ... sybase runns on one machine mssql on another, ... the those tables even if the tables are in databases on ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: error opening table in Enterprise MGR
    ... >table in any of the databases on a SQL 7 server from the server where the ... >Designer encountered a query error: ... >query analyzer and I am able to view the tables through Enterprise Manager ... >when I link to these databases from another computer. ...
    (microsoft.public.sqlserver.server)
Loading