historical data

A general data design question:

We have data which changes every week. We had considered seperating
historical records and current records into two different tables with
the same columns, but thought it might be simpler to have them all
together in one table and just add a WeekID int column to indicate
which week it represents (and perhaps an isCurrent bit column to make
querying easier). We have a number of tables like this, holding weekly
data, and we'll have to query for historical data often, but only back
through the last year -- we have historical data going back to 1998 or
so which we'll rarely if ever look at.

Is the all-in-one-table approach better or the seperation of current
and historical data? Will there be a performance hit to organizing data
this way? I don't think the extra columns will make querying too much
more awkward, but is there anything I'm overlooking in this?

Thanks.rottytooth (rottytooth@.gmail.com) writes:
> A general data design question:
> We have data which changes every week. We had considered seperating
> historical records and current records into two different tables with
> the same columns, but thought it might be simpler to have them all
> together in one table and just add a WeekID int column to indicate
> which week it represents (and perhaps an isCurrent bit column to make
> querying easier). We have a number of tables like this, holding weekly
> data, and we'll have to query for historical data often, but only back
> through the last year -- we have historical data going back to 1998 or
> so which we'll rarely if ever look at.
> Is the all-in-one-table approach better or the seperation of current
> and historical data? Will there be a performance hit to organizing data
> this way? I don't think the extra columns will make querying too much
> more awkward, but is there anything I'm overlooking in this?

One thing you could consider is partitioned views, with one table
per year. (So each year you add a new table and change the view.)

A partioned view is constructed as:

SELECT col1, col2, ...
FROM tbl1
UNION ALL
SELECT col1, col2,
FROM tbl2
UNION ALL
...

In the tables there needs to be a check constraint on a column which is
part of the PK, and the constraint should define the range for that
table. Ranges must not overlap.

The point is that when you query the view, SQL Server will only access
the tables that are covered by the condition on the partioning column.

Please see Books Online for further details.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Hilary and Paul : SQL 2005 Book and Materials

I considered the idea, but have decided to produce a few
articles on the main changes that I'm interested in
instead - merge over the internet and peer-to-peer
transactional. Hilary's the man for books, and he'll be
able to update you about his plans.
BTW to be honest as MVPs we don't have much of an
advantage over anyone else in this regard - we still have
to do the research, trial and error, digging through
system stored procedures etc, although some changes we
hear about a little earlier. I know Kalen was able to
interview PMs for the various groups but that was
exceptional and we don't really have such access. If this
is something you're interested in - ie close contact with
the developers - then now is the time we can all do it.
Have a good look at the Beta2 and try to figure the
replication features out, then post your questions in the
Beta2 newsgroups
(http://communities.microsoft.com/new...s/default.asp?
icp=sqlserver2005&slcid=us). I have used these and the
Yukon ones, and the questions are answered by the actual
TSQL code developers - the people we don't usually see on
the public newsgroups, so this is pretty valuable stuff.
Regards,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Paul is right. Please post your comments about SQL server 2005 betas. We
will be delighted to have your feedback.
thanks - Deepak
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1f5e01c4f7c1$8cafb250$a401280a@.phx.gbl...
> I considered the idea, but have decided to produce a few
> articles on the main changes that I'm interested in
> instead - merge over the internet and peer-to-peer
> transactional. Hilary's the man for books, and he'll be
> able to update you about his plans.
> BTW to be honest as MVPs we don't have much of an
> advantage over anyone else in this regard - we still have
> to do the research, trial and error, digging through
> system stored procedures etc, although some changes we
> hear about a little earlier. I know Kalen was able to
> interview PMs for the various groups but that was
> exceptional and we don't really have such access. If this
> is something you're interested in - ie close contact with
> the developers - then now is the time we can all do it.
> Have a good look at the Beta2 and try to figure the
> replication features out, then post your questions in the
> Beta2 newsgroups
> (http://communities.microsoft.com/new...s/default.asp?
> icp=sqlserver2005&slcid=us). I have used these and the
> Yukon ones, and the questions are answered by the actual
> TSQL code developers - the people we don't usually see on
> the public newsgroups, so this is pretty valuable stuff.
> Regards,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

High Availability Choices from Mgt. Perspective

I need to make a brief presentation to upper management about High Availability options in SQL Server 2005.

Current choices being considered are:

- Failover clustering

- Log shipping

- Mirroring

Q1: Are there other choices?

Q2: How do these choices compare in terms of cost, complexity of setup, ease of deployment, recovery procedures in case of a disaster?

Ben Aminnia

What is the amount of time you can afford to be offline?
The failover time is quite different between the 3 options.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

The obvious answer would be "The quicker the recovery, the better!"

Can you prioritize the 3 methods in terms of recovery speed? e.g. fastest, medium, slowest or 5-minutes, 30 minutes, 2-hours (or something like that)?

We can then look at them from the setup-complexity angle.

Thanks,

Ben

|||

In theory database mirroring provides failover in a matter of seconds when it is combined with .NET 2.0 which supports transparent failover.

Clustering is a matter of minutes and depends on the number of resources and how fast they start on your server.

Log shipping depends a bit on your procedures but it takes a bit more work because there is not automatic way to 'failover' to the log shipping database. You would have to get the database up and running on the destination server and redirect the application servers to the new destination server. All this is not as transparent as database mirroring but many people have more faith in log shipping because it is 'old' technology which has proven itself (just like clustering).

Hope this helps

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Inaddition to what Wesley said, you can refer this link for the comparision features between the 3.........

http://sql-articles.com/articles/diffdbmrr.htm Database mirroring seems to be the best as there's less downtime with automatic failover if you use high availability mode.........

|||

I followed the link and the descriptions seemed a little mixed up. For example, the description under mirroring was really talking about clustering and vice versa. Nonetheless, there are good points in it.

Thanks,

Ben

|||

Well, I think each option has its place, and that's why there are options: because one size doesn't fit all.

So let me ask another question: Let's say "money is no object" then which method is most suitable in what kind of scenario? For example, if we have three types of databases:

a. OLTP databases with heavy online transactions all day long

b. Data warehouses with bulk inserts / DTS / SSIS loads overnight

c. Middle tier databases, with complex business rules and interrelationships, but infrequent updates

What high-availability protection method is most suitable for each of the above 3 categories?

Ben