Database mirroring

4 May 2006 8:28 AM

Craig HB

I have a database that I want to split up so that I separate the
transactional side from the reporting side. It seems that database mirroring
(or something similiar) is what I should do.

So I'll a have Transaction database that mirrors with a Reporting database.
They both have the same data but all the inserts, update etc happen on the
Transaction database and all the reporting happen on the Reporting database.

And now my question...

The Transaction database only needs the last weeks data to do its job. The
Reporting database needs all the data for reporting (about 3 years of data).
It seems a waste that we keep 3 years of data in the Transaction database.
Even though all changes in the Transaction database should be reflected in
the Reporting database, I was wondering if I flagged a row as Do_Not_Mirror
and then deleted that row, it would NOT replicate that delete in the
reporting database. That way the Transaction database would be small and
efficient, and the Reporting database would have all the data needed for
reports. I don't know if such a thing exists.

Is this a good idea ? Or am I wasting my time trying to reduce the size of
the Transaction database ? I'm hoping there's a standard way of doind this.
Or that someone has done something similiar.

Any advice would be welcome
Craig

4 May 2006 9:31 AM

ML

The purpose of database mirroring is to increase the availability of the
server - i.e. the database mirror is used when the principal server is dow
for some reason.

While mirroring is engaged the mirror cannot be accessed.

What you're describing is replication - look at the options in Books Online.

ML

---
http://milambda.blogspot.com/

4 May 2006 9:37 AM

Tibor Karaszi

The "database mirroring" feature in SQL Server 2005 will not be good for this. However, consider
using transactional replication. You could for instance have triggers on the subscription database
which reads this "do not mirror" flag and doesn't do the delete for these cases. Perhaps even
instead of triggers would be the best choice here. I'd first verify whether you really need to
delete old data, first...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"Craig HB" <Crai***@discussions.microsoft.com> wrote in message
news:56677404-98F7-42CE-B393-BC917F760396@microsoft.com...
>I have a database that I want to split up so that I separate the
> transactional side from the reporting side. It seems that database mirroring
> (or something similiar) is what I should do.
>
> So I'll a have Transaction database that mirrors with a Reporting database.
> They both have the same data but all the inserts, update etc happen on the
> Transaction database and all the reporting happen on the Reporting database.
>
> And now my question...
>
> The Transaction database only needs the last weeks data to do its job. The
> Reporting database needs all the data for reporting (about 3 years of data).
> It seems a waste that we keep 3 years of data in the Transaction database.
> Even though all changes in the Transaction database should be reflected in
> the Reporting database, I was wondering if I flagged a row as Do_Not_Mirror
> and then deleted that row, it would NOT replicate that delete in the
> reporting database. That way the Transaction database would be small and
> efficient, and the Reporting database would have all the data needed for
> reports. I don't know if such a thing exists.
>
> Is this a good idea ? Or am I wasting my time trying to reduce the size of
> the Transaction database ? I'm hoping there's a standard way of doind this.
> Or that someone has done something similiar.
>
> Any advice would be welcome
> Craig