Need advice on Designing a VLDB OLTP Database

Hi,
I am trying to design a sql2005 Database with 4 tables of 35 million
rows. we need to update fields in one of the table by joining with
other three. Also we need to delete the roughly 2 million rows daily
from these tables as new data is added. Please suggest if breaking all
these tables into different databases is better or having them all in
one single database is better?. Also the joining keys are varchar
fields. So any suggestions on indexing?
thanks
KrisPartitioning is your friend.
If your daily data sets are in different partitions, the drop can be
metadata-only. That is very fast. Essentially, you are truncating the
partition. To make this work, you have to have indexes aligned with the
partitioning function. Read all about partitioning in BOL.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
<krishnasingaraju@.gmail.com> wrote in message
news:1192490857.778064.244440@.i13g2000prf.googlegroups.com...
> Hi,
> I am trying to design a sql2005 Database with 4 tables of 35 million
> rows. we need to update fields in one of the table by joining with
> other three. Also we need to delete the roughly 2 million rows daily
> from these tables as new data is added. Please suggest if breaking all
> these tables into different databases is better or having them all in
> one single database is better?. Also the joining keys are varchar
> fields. So any suggestions on indexing?
> thanks
> Kris
>|||> Partitioning is your friend.
Yes, very well said (I like it). Also reference DPV's (Distributed Partition
Views).
In a DPV you create n databases and link them together with a view. While
I've heard of multiple partitions on one server and that t performed well, a
DPV can be split up onto multiple nodes of an active/active cluster. There
is one important point, which will be in BOL, make sure that the data is
arranged so that at least 80% of the data you need comes from one
partition/server, or performance could actually be worse.
Jay
> If your daily data sets are in different partitions, the drop can be
> metadata-only. That is very fast. Essentially, you are truncating the
> partition. To make this work, you have to have indexes aligned with the
> partitioning function. Read all about partitioning in BOL.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> <krishnasingaraju@.gmail.com> wrote in message
> news:1192490857.778064.244440@.i13g2000prf.googlegroups.com...
>> Hi,
>> I am trying to design a sql2005 Database with 4 tables of 35 million
>> rows. we need to update fields in one of the table by joining with
>> other three. Also we need to delete the roughly 2 million rows daily
>> from these tables as new data is added. Please suggest if breaking all
>> these tables into different databases is better or having them all in
>> one single database is better?. Also the joining keys are varchar
>> fields. So any suggestions on indexing?
>> thanks
>> Kris
>|||Given the size info, I'm not sure you really want to even consider DPV.
Linchi
"Jay" wrote:
> > Partitioning is your friend.
> Yes, very well said (I like it). Also reference DPV's (Distributed Partition
> Views).
> In a DPV you create n databases and link them together with a view. While
> I've heard of multiple partitions on one server and that t performed well, a
> DPV can be split up onto multiple nodes of an active/active cluster. There
> is one important point, which will be in BOL, make sure that the data is
> arranged so that at least 80% of the data you need comes from one
> partition/server, or performance could actually be worse.
> Jay
> > If your daily data sets are in different partitions, the drop can be
> > metadata-only. That is very fast. Essentially, you are truncating the
> > partition. To make this work, you have to have indexes aligned with the
> > partitioning function. Read all about partitioning in BOL.
> >
> > --
> > Geoff N. Hiten
> > Senior SQL Infrastructure Consultant
> > Microsoft SQL Server MVP
> >
> >
> >
> >
> > <krishnasingaraju@.gmail.com> wrote in message
> > news:1192490857.778064.244440@.i13g2000prf.googlegroups.com...
> >> Hi,
> >>
> >> I am trying to design a sql2005 Database with 4 tables of 35 million
> >> rows. we need to update fields in one of the table by joining with
> >> other three. Also we need to delete the roughly 2 million rows daily
> >> from these tables as new data is added. Please suggest if breaking all
> >> these tables into different databases is better or having them all in
> >> one single database is better?. Also the joining keys are varchar
> >> fields. So any suggestions on indexing?
> >>
> >> thanks
> >> Kris
> >>
> >
>
>

Need a leg up on migrating from SQL 7 to SQL 2005 (HOW TO?)

I have a SQL7 server that runs one database for our company. The rest of the datastore is on a SQL2005 server. How best can I transfer the SQL7 database to SQL2005??

There are a variety of ways, but the most straightforward is to simply back up the SQL 7.0 database, and restore it onto a SQL 2005 instance...

--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html?bID=457
--

|||I have tried to do that, but the SQL 2005 server balks at the backup. Is there anything special one has to do in order to get the backup to restore properly?|||?

Is your SQL 7 server patched to the last SP (4, if I recall properly)? I believe that is a prerequisite. If it is, what is the exact error you're getting?

--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html?bID=457
--

|||

I applied SP3 and SP4. Afterwards, I was able to produce a useable backup that I was able to restore to SQL 2005

Thanks for your help

Need a good book...

I've been a SQL Server dba for 5 or 6 years now. With the upcoming release (eventually, I'm sure) of Yukon/SQL2005, I've read that it's important for DBA's to pick up one of the .NET languages - I've figured, I'll try to learn VB.NET - I've had a little exposure to it, and can usually figure out what's going on in VB code I've read - however, I seriously doubt I could write anything in it from scratch - I want to learn it in a bad way - can you all recommend any self-paced books that will walk me thru it? I've never had any formal training with it, don't know a class from a DLL...Thanks in advance for your help!!What did you do as a DBA in 5 or 6 years?|||lots of TSQL, performance tuning, trouble shooting - not much at all with VB...little bit of Crystal, a little ASP...|||lots of TSQL, performance tuning, trouble shooting - not much at all with VB...little bit of Crystal, a little ASP...
http://www.w3schools.com/