Advice on SQL statement please.

I have a query that generates the dataset below, based on the year being
filtered I get the sum of an amount Group By the type. What I would like to
do is use the exact qry using a differnet date, to generate a third column
called Prior12Mnths. How would I use my qry to accomplish this task.
I appreciate the help.
Here's my qry:
Select GroupType, Sum(SumRevAmt) as Last12Mnths
from MyQRY
Where Period = '200006'
Group by GroupType
Type Last12Mnths_200006 Prior12Mnths_199906
Airlines 1234.50 '
Concessions 73854.00 '
etc......Russell Verdun wrote:
> I have a query that generates the dataset below, based on the year being
> filtered I get the sum of an amount Group By the type. What I would like t
o
> do is use the exact qry using a differnet date, to generate a third column
> called Prior12Mnths. How would I use my qry to accomplish this task.
> I appreciate the help.
> Here's my qry:
> Select GroupType, Sum(SumRevAmt) as Last12Mnths
> from MyQRY
> Where Period = '200006'
> Group by GroupType
>
>
> Type Last12Mnths_200006 Prior12Mnths_199906
> Airlines 1234.50 '?
?
> Concessions 73854.00 '
> etc......
>
>
It's not clear from your example what this "other date" would be, so
I'll use a different example. Say I have a table containing
transactions, and each transaction consists of an account, a transaction
date, and an amount:
SELECT
account,
SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) <= 12 THEN amount
ELSE 0) AS Last12Months,
SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) BETWEEN 13 AND 24
THEN amount ELSE 0) AS Prev12Months
FROM table
GROUP BY account
Is that enough to get you started?|||Tracy McKibben wrote:
> Russell Verdun wrote:
> It's not clear from your example what this "other date" would be, so
> I'll use a different example. Say I have a table containing
> transactions, and each transaction consists of an account, a transaction
> date, and an amount:
> SELECT
> account,
> SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) <= 12 THEN amount
> ELSE 0) AS Last12Months,
> SUM(CASE WHEN DATEDIFF(m, transdate, GETDATE()) BETWEEN 13 AND 24
> THEN amount ELSE 0) AS Prev12Months
> FROM table
> GROUP BY account
> Is that enough to get you started?
>
Sorry, those CASE statements are missing ENDs...|||Hi Russell,
I believe you could do something like this:
Select GroupType, Sum(CASE Period=200006 THEN SumRevAmt ELSE 0 END) as
Last12Mnths, Sum(CASE Period=199906 THEN SumRevAmt ELSE 0 END) as
Prior12Mnths
from MyQRY
Where Period = '200006' or Period = '199906'
Group by GroupType
I Dont know if thats the best way, but that is what first comes to
mind.
Paul T.|||>> I have a query that generates the dataset below, based on the year being
filtered I get the sum of an amount Group By the type. What I would like to
do is use the exact qry using a differnet date, to generate a third column
called Prior12Mnths. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is also helpful if the data elements have good
names.
CREATE TABLE Revenues -- guess at meaningful name
(grp_type INTEGER NOT NULL
REFERENCES GroupTypes(grp_type),
rev_amt DECIMAL(12,2) NOT NULL,
rev_date DATETIME NOT NULL PRIMARY KEY);
In the vague pseudo-code you posted, only some kind of vague date can
be a key
The best trick for this kind of summary is to build a reporting range
table
CREATE TABLE ReportRanges
(range_name CHAR() NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
PRIMARY KEY (range_name, start_date));
INSERT INTO ReportRanges
VALUES ('2006-06: Prior12' , '2005-06-01', '2006-06-31' );
INSERT INTO ReportRanges
VALUES ('2006-06: ytd' , '2006-01-01', '2006-06-31' );
SELECT grp_type,
SUM (CASE WHEN R.range_name = '2006-06: ytd'
THEN rev_amt ELSE 0.00 END) AS ytd,
SUM (CASE WHEN R.range_name = '2006-06: Prior12'
THEN rev_amt ELSE 0.00 END) AS Prior12,
etc.
FROM Revenues
GROUP BY grp_type;
Adjust the table as needed.

Advice on SQL Server 2005 vs. Express Edition w/ Advanced Services

I've been using SQL Server 2005 Std. for my development work on a laptop w/ 2 GHz Celeron and 768 Mb RAM. Now that Express Edition has Reporting Services and Full-Text Search, would I be wise to just use it instead? I actually have both on my machine since Express Edition installed with VS 2005 Standard. Also, if I uninstall SQL Server 2005 Std. what will I lose? Thanks in advance.

-Mike

It depends what you want to do. SQL Express Advanced Services is not the same as Std, Std has more features (OLAP, Integration Services) richer features (better SSMS, better reporting including report builder) it also has less restrictions on memory, procs etc.

If you are only using this for development you should also be able to use Developer Edition which is < 50USD but is Enterprise Edition, with a no production use clause.

|||

I've decided to uninstall SQL Server 2005 Std. and try to work with Express w/ Advanced Services. I believe it will do all I need. I'm more concerned with performance on my laptop than features like OLAP, etc. If I gain any incite after using it for a while, I'll post back here. Thanks.

-Mike

sql

Advice on SQL 2005 Install

Hello,
I am looking for some advice on installing SQL 2005 x64 Standard on a
new Windows 2003 x64 Standard Dell 2950 Box. We have a Quad 2.6 GHz
with 16 GBs Memory, 5x 73 GB 15k RPM Hard drives.
A couple things we are looking into is the best way to partition the
Raid 5, what we have come up with so far is to create an OS partition,
Backup Partition, Temp DB partition, and Everything else Partition.
We would like to stay with raid 5.
Next is there any to make Windows 2003 STD work better for SQL on a
fresh install?
SQL tips, We have read up on a few such as dedicating processor to the
Temp DB. We use our DB for an in-house sales package (software
designer included), Great Plains, and a CRM program.
Thanks for any help.
Don Graham
Why do you want to partition the drives? That does absolutely nothing for
performance since they are all on the same physical array. By partitioning
the array you now run into a situation where one or more of the partitions
may be too small down the road. And why do you want to stay with Raid 5?
Is 5 disks the max you can have in the box? I don't know who large your dbs
will be or how you will use them but if performance is a goal placing
everything on a single Raid 5 may not be the best option in the long run.
Andrew J. Kelly SQL MVP
"Donnie3iii" <donnie3iii@.gmail.com> wrote in message
news:1184020787.195967.302410@.e16g2000pri.googlegr oups.com...
> Hello,
> I am looking for some advice on installing SQL 2005 x64 Standard on a
> new Windows 2003 x64 Standard Dell 2950 Box. We have a Quad 2.6 GHz
> with 16 GBs Memory, 5x 73 GB 15k RPM Hard drives.
> A couple things we are looking into is the best way to partition the
> Raid 5, what we have come up with so far is to create an OS partition,
> Backup Partition, Temp DB partition, and Everything else Partition.
> We would like to stay with raid 5.
> Next is there any to make Windows 2003 STD work better for SQL on a
> fresh install?
> SQL tips, We have read up on a few such as dedicating processor to the
> Temp DB. We use our DB for an in-house sales package (software
> designer included), Great Plains, and a CRM program.
> Thanks for any help.
> Don Graham
>
|||On Jul 9, 3:59 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:[vbcol=seagreen]
> Why do you want to partition the drives? That does absolutely nothing for
> performance since they are all on the same physical array. By partitioning
> the array you now run into a situation where one or more of the partitions
> may be too small down the road. And why do you want to stay with Raid 5?
> Is 5 disks the max you can have in the box? I don't know who large your dbs
> will be or how you will use them but if performance is a goal placing
> everything on a single Raid 5 may not be the best option in the long run.
> --
> Andrew J. Kelly SQL MVP
> "Donnie3iii" <donnie3...@.gmail.com> wrote in message
> news:1184020787.195967.302410@.e16g2000pri.googlegr oups.com...
>
>
>
>
The database size will total around 20-25GB (for now, they grow at a
rate of 7 GBs a year), as for Raid 5 we were going to go with Raid 10
but we went with the 15k drives instead. Our programmer/dbo swears by
Raid 5. We can have up to 8 drives in the box but cost was a
consideration here.
|||Well without knowing more about how you will actually use this server I
can't say whether you will have issues with the Raid 5 or not. But if your
programmer swears by them who am I to say .
Andrew J. Kelly SQL MVP
"Donnie3iii" <donnie3iii@.gmail.com> wrote in message
news:1184022630.326849.120520@.j4g2000prf.googlegro ups.com...
> On Jul 9, 3:59 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
> The database size will total around 20-25GB (for now, they grow at a
> rate of 7 GBs a year), as for Raid 5 we were going to go with Raid 10
> but we went with the 15k drives instead. Our programmer/dbo swears by
> Raid 5. We can have up to 8 drives in the box but cost was a
> consideration here.
>
|||On Jul 9, 8:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:[vbcol=seagreen]
> Well without knowing more about how you will actually use this server I
> can't say whether you will have issues with the Raid 5 or not. But if your
> programmer swears by them who am I to say .
> --
> Andrew J. Kelly SQL MVP
> "Donnie3iii" <donnie3...@.gmail.com> wrote in message
> news:1184022630.326849.120520@.j4g2000prf.googlegro ups.com...
>
>
>
>
>
>
"But if your programmer swears by them who am I to say"
That was my thought as well but the more I look into it the more I
start to disagree. I may go for a raid 1 (OS), Raid 1 (Trans log or
TempDB), and I am not sure for the DB drive. Raid 10 is a little to
expensive.
Overall we have about 25-30 users, it looks like a lot of database
operations for the program are done with stored procedures.
|||You have plenty of cache so hopefully you won't need to do as much I/O on
the data drives. So if you separate the Tran logs fromt he data files you
will have a good start. By the way if price is an issue I would look at
placing the Log files on the same Raid 1 as the OS. As long as you don't
have other apps using the OS drive there isn't much activity and most small
to medium size apps do fine with sharing the logs and OS. That will give you
more drives for the data array.
Andrew J. Kelly SQL MVP
"Donnie3iii" <donnie3iii@.gmail.com> wrote in message
news:1184081530.969779.121120@.i13g2000prf.googlegr oups.com...
> On Jul 9, 8:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
> "But if your programmer swears by them who am I to say"
> That was my thought as well but the more I look into it the more I
> start to disagree. I may go for a raid 1 (OS), Raid 1 (Trans log or
> TempDB), and I am not sure for the DB drive. Raid 10 is a little to
> expensive.
> Overall we have about 25-30 users, it looks like a lot of database
> operations for the program are done with stored procedures.
>
|||On Jul 11, 5:38 am, "Rasmus Glibstrup" <ras...@.it-
craft.THIS.REMOVE.dk> wrote:[vbcol=seagreen]
> I would go fo a tripple RAID 1, one for OS, one for Log and One for DB
> files.
> To begin with I would place the TempDB as any other database, unless your
> applications require much use of TempDB.
> But generally my experience is never to use RAID 5 unless you have a very
> large percentage of reads as opposed to writes on your database. And even
> then I would go for RAID 10.
> Maybe you want to take a look at point 2,3 and 5 in this from MS:http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage...
> --
> Rasmus Glibstrup
> MCDBA,MCITP (dba,dev,bi)
> "Donnie3iii" <donnie3...@.gmail.com> wrote in message
> news:1184081530.969779.121120@.i13g2000prf.googlegr oups.com...
>
>
>
>
>
>
>
>
>
>
Thank you all for the suggestions, I am planning on reconfiguring the
raid and going with either 2 or 3 depending on how much money I can
squeeze out. Thanks!
Don

Advice on SQL 2005 Install

Hello,
I am looking for some advice on installing SQL 2005 x64 Standard on a
new Windows 2003 x64 Standard Dell 2950 Box. We have a Quad 2.6 GHz
with 16 GBs Memory, 5x 73 GB 15k RPM Hard drives.
A couple things we are looking into is the best way to partition the
Raid 5, what we have come up with so far is to create an OS partition,
Backup Partition, Temp DB partition, and Everything else Partition.
We would like to stay with raid 5.
Next is there any to make Windows 2003 STD work better for SQL on a
fresh install?
SQL tips, We have read up on a few such as dedicating processor to the
Temp DB. We use our DB for an in-house sales package (software
designer included), Great Plains, and a CRM program.
Thanks for any help.
Don GrahamWhy do you want to partition the drives? That does absolutely nothing for
performance since they are all on the same physical array. By partitioning
the array you now run into a situation where one or more of the partitions
may be too small down the road. And why do you want to stay with Raid 5?
Is 5 disks the max you can have in the box? I don't know who large your dbs
will be or how you will use them but if performance is a goal placing
everything on a single Raid 5 may not be the best option in the long run.
Andrew J. Kelly SQL MVP
"Donnie3iii" <donnie3iii@.gmail.com> wrote in message
news:1184020787.195967.302410@.e16g2000pri.googlegroups.com...
> Hello,
> I am looking for some advice on installing SQL 2005 x64 Standard on a
> new Windows 2003 x64 Standard Dell 2950 Box. We have a Quad 2.6 GHz
> with 16 GBs Memory, 5x 73 GB 15k RPM Hard drives.
> A couple things we are looking into is the best way to partition the
> Raid 5, what we have come up with so far is to create an OS partition,
> Backup Partition, Temp DB partition, and Everything else Partition.
> We would like to stay with raid 5.
> Next is there any to make Windows 2003 STD work better for SQL on a
> fresh install?
> SQL tips, We have read up on a few such as dedicating processor to the
> Temp DB. We use our DB for an in-house sales package (software
> designer included), Great Plains, and a CRM program.
> Thanks for any help.
> Don Graham
>|||On Jul 9, 3:59 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:[vbcol=seagreen]
> Why do you want to partition the drives? That does absolutely nothing for
> performance since they are all on the same physical array. By partitioning
> the array you now run into a situation where one or more of the partitions
> may be too small down the road. And why do you want to stay with Raid 5?
> Is 5 disks the max you can have in the box? I don't know who large your db
s
> will be or how you will use them but if performance is a goal placing
> everything on a single Raid 5 may not be the best option in the long run.
> --
> Andrew J. Kelly SQL MVP
> "Donnie3iii" <donnie3...@.gmail.com> wrote in message
> news:1184020787.195967.302410@.e16g2000pri.googlegroups.com...
>
>
>
>
>
>
>
>
The database size will total around 20-25GB (for now, they grow at a
rate of 7 GBs a year), as for Raid 5 we were going to go with Raid 10
but we went with the 15k drives instead. Our programmer/dbo swears by
Raid 5. We can have up to 8 drives in the box but cost was a
consideration here.|||Well without knowing more about how you will actually use this server I
can't say whether you will have issues with the Raid 5 or not. But if your
programmer swears by them who am I to say .
Andrew J. Kelly SQL MVP
"Donnie3iii" <donnie3iii@.gmail.com> wrote in message
news:1184022630.326849.120520@.j4g2000prf.googlegroups.com...
> On Jul 9, 3:59 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
> The database size will total around 20-25GB (for now, they grow at a
> rate of 7 GBs a year), as for Raid 5 we were going to go with Raid 10
> but we went with the 15k drives instead. Our programmer/dbo swears by
> Raid 5. We can have up to 8 drives in the box but cost was a
> consideration here.
>|||On Jul 9, 8:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:[vbcol=seagreen]
> Well without knowing more about how you will actually use this server I
> can't say whether you will have issues with the Raid 5 or not. But if you
r
> programmer swears by them who am I to say .
> --
> Andrew J. Kelly SQL MVP
> "Donnie3iii" <donnie3...@.gmail.com> wrote in message
> news:1184022630.326849.120520@.j4g2000prf.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
"But if your programmer swears by them who am I to say"
That was my thought as well but the more I look into it the more I
start to disagree. I may go for a raid 1 (OS), Raid 1 (Trans log or
TempDB), and I am not sure for the DB drive. Raid 10 is a little to
expensive.
Overall we have about 25-30 users, it looks like a lot of database
operations for the program are done with stored procedures.|||You have plenty of cache so hopefully you won't need to do as much I/O on
the data drives. So if you separate the Tran logs fromt he data files you
will have a good start. By the way if price is an issue I would look at
placing the Log files on the same Raid 1 as the OS. As long as you don't
have other apps using the OS drive there isn't much activity and most small
to medium size apps do fine with sharing the logs and OS. That will give you
more drives for the data array.
Andrew J. Kelly SQL MVP
"Donnie3iii" <donnie3iii@.gmail.com> wrote in message
news:1184081530.969779.121120@.i13g2000prf.googlegroups.com...
> On Jul 9, 8:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
> "But if your programmer swears by them who am I to say"
> That was my thought as well but the more I look into it the more I
> start to disagree. I may go for a raid 1 (OS), Raid 1 (Trans log or
> TempDB), and I am not sure for the DB drive. Raid 10 is a little to
> expensive.
> Overall we have about 25-30 users, it looks like a lot of database
> operations for the program are done with stored procedures.
>|||I would go fo a tripple RAID 1, one for OS, one for Log and One for DB
files.
To begin with I would place the TempDB as any other database, unless your
applications require much use of TempDB.
But generally my experience is never to use RAID 5 unless you have a very
large percentage of reads as opposed to writes on your database. And even
then I would go for RAID 10.
Maybe you want to take a look at point 2,3 and 5 in this from MS:
[url]http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx[
/url]
Rasmus Glibstrup
MCDBA,MCITP (dba,dev,bi)
"Donnie3iii" <donnie3iii@.gmail.com> wrote in message
news:1184081530.969779.121120@.i13g2000prf.googlegroups.com...
> On Jul 9, 8:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
> "But if your programmer swears by them who am I to say"
> That was my thought as well but the more I look into it the more I
> start to disagree. I may go for a raid 1 (OS), Raid 1 (Trans log or
> TempDB), and I am not sure for the DB drive. Raid 10 is a little to
> expensive.
> Overall we have about 25-30 users, it looks like a lot of database
> operations for the program are done with stored procedures.
>|||On Jul 11, 5:38 am, "Rasmus Glibstrup" <ras...@.it-
craft.THIS.REMOVE.dk> wrote:[vbcol=seagreen]
> I would go fo a tripple RAID 1, one for OS, one for Log and One for DB
> files.
> To begin with I would place the TempDB as any other database, unless your
> applications require much use of TempDB.
> But generally my experience is never to use RAID 5 unless you have a very
> large percentage of reads as opposed to writes on your database. And even
> then I would go for RAID 10.
> Maybe you want to take a look at point 2,3 and 5 in this from MS:http://www.micr
osoft.com/te...tice/storage...
> --
> Rasmus Glibstrup
> MCDBA,MCITP (dba,dev,bi)
> "Donnie3iii" <donnie3...@.gmail.com> wrote in message
> news:1184081530.969779.121120@.i13g2000prf.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
Thank you all for the suggestions, I am planning on reconfiguring the
raid and going with either 2 or 3 depending on how much money I can
squeeze out. Thanks!
Don

Advice on SQL 2005 Install

Hello,
I am looking for some advice on installing SQL 2005 x64 Standard on a
new Windows 2003 x64 Standard Dell 2950 Box. We have a Quad 2.6 GHz
with 16 GBs Memory, 5x 73 GB 15k RPM Hard drives.
A couple things we are looking into is the best way to partition the
Raid 5, what we have come up with so far is to create an OS partition,
Backup Partition, Temp DB partition, and Everything else Partition.
We would like to stay with raid 5.
Next is there any to make Windows 2003 STD work better for SQL on a
fresh install?
SQL tips, We have read up on a few such as dedicating processor to the
Temp DB. We use our DB for an in-house sales package (software
designer included), Great Plains, and a CRM program.
Thanks for any help.
Don GrahamWhy do you want to partition the drives? That does absolutely nothing for
performance since they are all on the same physical array. By partitioning
the array you now run into a situation where one or more of the partitions
may be too small down the road. And why do you want to stay with Raid 5?
Is 5 disks the max you can have in the box? I don't know who large your dbs
will be or how you will use them but if performance is a goal placing
everything on a single Raid 5 may not be the best option in the long run.
--
Andrew J. Kelly SQL MVP
"Donnie3iii" <donnie3iii@.gmail.com> wrote in message
news:1184020787.195967.302410@.e16g2000pri.googlegroups.com...
> Hello,
> I am looking for some advice on installing SQL 2005 x64 Standard on a
> new Windows 2003 x64 Standard Dell 2950 Box. We have a Quad 2.6 GHz
> with 16 GBs Memory, 5x 73 GB 15k RPM Hard drives.
> A couple things we are looking into is the best way to partition the
> Raid 5, what we have come up with so far is to create an OS partition,
> Backup Partition, Temp DB partition, and Everything else Partition.
> We would like to stay with raid 5.
> Next is there any to make Windows 2003 STD work better for SQL on a
> fresh install?
> SQL tips, We have read up on a few such as dedicating processor to the
> Temp DB. We use our DB for an in-house sales package (software
> designer included), Great Plains, and a CRM program.
> Thanks for any help.
> Don Graham
>|||On Jul 9, 3:59 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> Why do you want to partition the drives? That does absolutely nothing for
> performance since they are all on the same physical array. By partitioning
> the array you now run into a situation where one or more of the partitions
> may be too small down the road. And why do you want to stay with Raid 5?
> Is 5 disks the max you can have in the box? I don't know who large your dbs
> will be or how you will use them but if performance is a goal placing
> everything on a single Raid 5 may not be the best option in the long run.
> --
> Andrew J. Kelly SQL MVP
> "Donnie3iii" <donnie3...@.gmail.com> wrote in message
> news:1184020787.195967.302410@.e16g2000pri.googlegroups.com...
> > Hello,
> > I am looking for some advice on installing SQL 2005 x64 Standard on a
> > new Windows 2003 x64 Standard Dell 2950 Box. We have a Quad 2.6 GHz
> > with 16 GBs Memory, 5x 73 GB 15k RPM Hard drives.
> > A couple things we are looking into is the best way to partition the
> > Raid 5, what we have come up with so far is to create an OS partition,
> > Backup Partition, Temp DB partition, and Everything else Partition.
> > We would like to stay with raid 5.
> > Next is there any to make Windows 2003 STD work better for SQL on a
> > fresh install?
> > SQL tips, We have read up on a few such as dedicating processor to the
> > Temp DB. We use our DB for an in-house sales package (software
> > designer included), Great Plains, and a CRM program.
> > Thanks for any help.
> > Don Graham
The database size will total around 20-25GB (for now, they grow at a
rate of 7 GBs a year), as for Raid 5 we were going to go with Raid 10
but we went with the 15k drives instead. Our programmer/dbo swears by
Raid 5. We can have up to 8 drives in the box but cost was a
consideration here.|||Well without knowing more about how you will actually use this server I
can't say whether you will have issues with the Raid 5 or not. But if your
programmer swears by them who am I to say :).
--
Andrew J. Kelly SQL MVP
"Donnie3iii" <donnie3iii@.gmail.com> wrote in message
news:1184022630.326849.120520@.j4g2000prf.googlegroups.com...
> On Jul 9, 3:59 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
>> Why do you want to partition the drives? That does absolutely nothing
>> for
>> performance since they are all on the same physical array. By
>> partitioning
>> the array you now run into a situation where one or more of the
>> partitions
>> may be too small down the road. And why do you want to stay with Raid 5?
>> Is 5 disks the max you can have in the box? I don't know who large your
>> dbs
>> will be or how you will use them but if performance is a goal placing
>> everything on a single Raid 5 may not be the best option in the long run.
>> --
>> Andrew J. Kelly SQL MVP
>> "Donnie3iii" <donnie3...@.gmail.com> wrote in message
>> news:1184020787.195967.302410@.e16g2000pri.googlegroups.com...
>> > Hello,
>> > I am looking for some advice on installing SQL 2005 x64 Standard on a
>> > new Windows 2003 x64 Standard Dell 2950 Box. We have a Quad 2.6 GHz
>> > with 16 GBs Memory, 5x 73 GB 15k RPM Hard drives.
>> > A couple things we are looking into is the best way to partition the
>> > Raid 5, what we have come up with so far is to create an OS partition,
>> > Backup Partition, Temp DB partition, and Everything else Partition.
>> > We would like to stay with raid 5.
>> > Next is there any to make Windows 2003 STD work better for SQL on a
>> > fresh install?
>> > SQL tips, We have read up on a few such as dedicating processor to the
>> > Temp DB. We use our DB for an in-house sales package (software
>> > designer included), Great Plains, and a CRM program.
>> > Thanks for any help.
>> > Don Graham
> The database size will total around 20-25GB (for now, they grow at a
> rate of 7 GBs a year), as for Raid 5 we were going to go with Raid 10
> but we went with the 15k drives instead. Our programmer/dbo swears by
> Raid 5. We can have up to 8 drives in the box but cost was a
> consideration here.
>|||On Jul 9, 8:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> Well without knowing more about how you will actually use this server I
> can't say whether you will have issues with the Raid 5 or not. But if your
> programmer swears by them who am I to say :).
> --
> Andrew J. Kelly SQL MVP
> "Donnie3iii" <donnie3...@.gmail.com> wrote in message
> news:1184022630.326849.120520@.j4g2000prf.googlegroups.com...
> > On Jul 9, 3:59 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> > wrote:
> >> Why do you want to partition the drives? That does absolutely nothing
> >> for
> >> performance since they are all on the same physical array. By
> >> partitioning
> >> the array you now run into a situation where one or more of the
> >> partitions
> >> may be too small down the road. And why do you want to stay with Raid 5?
> >> Is 5 disks the max you can have in the box? I don't know who large your
> >> dbs
> >> will be or how you will use them but if performance is a goal placing
> >> everything on a single Raid 5 may not be the best option in the long run.
> >> --
> >> Andrew J. Kelly SQL MVP
> >> "Donnie3iii" <donnie3...@.gmail.com> wrote in message
> >>news:1184020787.195967.302410@.e16g2000pri.googlegroups.com...
> >> > Hello,
> >> > I am looking for some advice on installing SQL 2005 x64 Standard on a
> >> > new Windows 2003 x64 Standard Dell 2950 Box. We have a Quad 2.6 GHz
> >> > with 16 GBs Memory, 5x 73 GB 15k RPM Hard drives.
> >> > A couple things we are looking into is the best way to partition the
> >> > Raid 5, what we have come up with so far is to create an OS partition,
> >> > Backup Partition, Temp DB partition, and Everything else Partition.
> >> > We would like to stay with raid 5.
> >> > Next is there any to make Windows 2003 STD work better for SQL on a
> >> > fresh install?
> >> > SQL tips, We have read up on a few such as dedicating processor to the
> >> > Temp DB. We use our DB for an in-house sales package (software
> >> > designer included), Great Plains, and a CRM program.
> >> > Thanks for any help.
> >> > Don Graham
> > The database size will total around 20-25GB (for now, they grow at a
> > rate of 7 GBs a year), as for Raid 5 we were going to go with Raid 10
> > but we went with the 15k drives instead. Our programmer/dbo swears by
> > Raid 5. We can have up to 8 drives in the box but cost was a
> > consideration here.
"But if your programmer swears by them who am I to say"
That was my thought as well but the more I look into it the more I
start to disagree. I may go for a raid 1 (OS), Raid 1 (Trans log or
TempDB), and I am not sure for the DB drive. Raid 10 is a little to
expensive.
Overall we have about 25-30 users, it looks like a lot of database
operations for the program are done with stored procedures.|||You have plenty of cache so hopefully you won't need to do as much I/O on
the data drives. So if you separate the Tran logs fromt he data files you
will have a good start. By the way if price is an issue I would look at
placing the Log files on the same Raid 1 as the OS. As long as you don't
have other apps using the OS drive there isn't much activity and most small
to medium size apps do fine with sharing the logs and OS. That will give you
more drives for the data array.
--
Andrew J. Kelly SQL MVP
"Donnie3iii" <donnie3iii@.gmail.com> wrote in message
news:1184081530.969779.121120@.i13g2000prf.googlegroups.com...
> On Jul 9, 8:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
>> Well without knowing more about how you will actually use this server I
>> can't say whether you will have issues with the Raid 5 or not. But if
>> your
>> programmer swears by them who am I to say :).
>> --
>> Andrew J. Kelly SQL MVP
>> "Donnie3iii" <donnie3...@.gmail.com> wrote in message
>> news:1184022630.326849.120520@.j4g2000prf.googlegroups.com...
>> > On Jul 9, 3:59 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
>> > wrote:
>> >> Why do you want to partition the drives? That does absolutely nothing
>> >> for
>> >> performance since they are all on the same physical array. By
>> >> partitioning
>> >> the array you now run into a situation where one or more of the
>> >> partitions
>> >> may be too small down the road. And why do you want to stay with Raid
>> >> 5?
>> >> Is 5 disks the max you can have in the box? I don't know who large
>> >> your
>> >> dbs
>> >> will be or how you will use them but if performance is a goal placing
>> >> everything on a single Raid 5 may not be the best option in the long
>> >> run.
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >> "Donnie3iii" <donnie3...@.gmail.com> wrote in message
>> >>news:1184020787.195967.302410@.e16g2000pri.googlegroups.com...
>> >> > Hello,
>> >> > I am looking for some advice on installing SQL 2005 x64 Standard on
>> >> > a
>> >> > new Windows 2003 x64 Standard Dell 2950 Box. We have a Quad 2.6 GHz
>> >> > with 16 GBs Memory, 5x 73 GB 15k RPM Hard drives.
>> >> > A couple things we are looking into is the best way to partition the
>> >> > Raid 5, what we have come up with so far is to create an OS
>> >> > partition,
>> >> > Backup Partition, Temp DB partition, and Everything else Partition.
>> >> > We would like to stay with raid 5.
>> >> > Next is there any to make Windows 2003 STD work better for SQL on a
>> >> > fresh install?
>> >> > SQL tips, We have read up on a few such as dedicating processor to
>> >> > the
>> >> > Temp DB. We use our DB for an in-house sales package (software
>> >> > designer included), Great Plains, and a CRM program.
>> >> > Thanks for any help.
>> >> > Don Graham
>> > The database size will total around 20-25GB (for now, they grow at a
>> > rate of 7 GBs a year), as for Raid 5 we were going to go with Raid 10
>> > but we went with the 15k drives instead. Our programmer/dbo swears by
>> > Raid 5. We can have up to 8 drives in the box but cost was a
>> > consideration here.
> "But if your programmer swears by them who am I to say"
> That was my thought as well but the more I look into it the more I
> start to disagree. I may go for a raid 1 (OS), Raid 1 (Trans log or
> TempDB), and I am not sure for the DB drive. Raid 10 is a little to
> expensive.
> Overall we have about 25-30 users, it looks like a lot of database
> operations for the program are done with stored procedures.
>|||I would go fo a tripple RAID 1, one for OS, one for Log and One for DB
files.
To begin with I would place the TempDB as any other database, unless your
applications require much use of TempDB.
But generally my experience is never to use RAID 5 unless you have a very
large percentage of reads as opposed to writes on your database. And even
then I would go for RAID 10.
Maybe you want to take a look at point 2,3 and 5 in this from MS:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx
--
Rasmus Glibstrup
MCDBA,MCITP (dba,dev,bi)
"Donnie3iii" <donnie3iii@.gmail.com> wrote in message
news:1184081530.969779.121120@.i13g2000prf.googlegroups.com...
> On Jul 9, 8:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
>> Well without knowing more about how you will actually use this server I
>> can't say whether you will have issues with the Raid 5 or not. But if
>> your
>> programmer swears by them who am I to say :).
>> --
>> Andrew J. Kelly SQL MVP
>> "Donnie3iii" <donnie3...@.gmail.com> wrote in message
>> news:1184022630.326849.120520@.j4g2000prf.googlegroups.com...
>> > On Jul 9, 3:59 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
>> > wrote:
>> >> Why do you want to partition the drives? That does absolutely nothing
>> >> for
>> >> performance since they are all on the same physical array. By
>> >> partitioning
>> >> the array you now run into a situation where one or more of the
>> >> partitions
>> >> may be too small down the road. And why do you want to stay with Raid
>> >> 5?
>> >> Is 5 disks the max you can have in the box? I don't know who large
>> >> your
>> >> dbs
>> >> will be or how you will use them but if performance is a goal placing
>> >> everything on a single Raid 5 may not be the best option in the long
>> >> run.
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >> "Donnie3iii" <donnie3...@.gmail.com> wrote in message
>> >>news:1184020787.195967.302410@.e16g2000pri.googlegroups.com...
>> >> > Hello,
>> >> > I am looking for some advice on installing SQL 2005 x64 Standard on
>> >> > a
>> >> > new Windows 2003 x64 Standard Dell 2950 Box. We have a Quad 2.6 GHz
>> >> > with 16 GBs Memory, 5x 73 GB 15k RPM Hard drives.
>> >> > A couple things we are looking into is the best way to partition the
>> >> > Raid 5, what we have come up with so far is to create an OS
>> >> > partition,
>> >> > Backup Partition, Temp DB partition, and Everything else Partition.
>> >> > We would like to stay with raid 5.
>> >> > Next is there any to make Windows 2003 STD work better for SQL on a
>> >> > fresh install?
>> >> > SQL tips, We have read up on a few such as dedicating processor to
>> >> > the
>> >> > Temp DB. We use our DB for an in-house sales package (software
>> >> > designer included), Great Plains, and a CRM program.
>> >> > Thanks for any help.
>> >> > Don Graham
>> > The database size will total around 20-25GB (for now, they grow at a
>> > rate of 7 GBs a year), as for Raid 5 we were going to go with Raid 10
>> > but we went with the 15k drives instead. Our programmer/dbo swears by
>> > Raid 5. We can have up to 8 drives in the box but cost was a
>> > consideration here.
> "But if your programmer swears by them who am I to say"
> That was my thought as well but the more I look into it the more I
> start to disagree. I may go for a raid 1 (OS), Raid 1 (Trans log or
> TempDB), and I am not sure for the DB drive. Raid 10 is a little to
> expensive.
> Overall we have about 25-30 users, it looks like a lot of database
> operations for the program are done with stored procedures.
>|||On Jul 11, 5:38 am, "Rasmus Glibstrup" <ras...@.it-
craft.THIS.REMOVE.dk> wrote:
> I would go fo a tripple RAID 1, one for OS, one for Log and One for DB
> files.
> To begin with I would place the TempDB as any other database, unless your
> applications require much use of TempDB.
> But generally my experience is never to use RAID 5 unless you have a very
> large percentage of reads as opposed to writes on your database. And even
> then I would go for RAID 10.
> Maybe you want to take a look at point 2,3 and 5 in this from MS:http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage...
> --
> Rasmus Glibstrup
> MCDBA,MCITP (dba,dev,bi)
> "Donnie3iii" <donnie3...@.gmail.com> wrote in message
> news:1184081530.969779.121120@.i13g2000prf.googlegroups.com...
> > On Jul 9, 8:04 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> > wrote:
> >> Well without knowing more about how you will actually use this server I
> >> can't say whether you will have issues with the Raid 5 or not. But if
> >> your
> >> programmer swears by them who am I to say :).
> >> --
> >> Andrew J. Kelly SQL MVP
> >> "Donnie3iii" <donnie3...@.gmail.com> wrote in message
> >>news:1184022630.326849.120520@.j4g2000prf.googlegroups.com...
> >> > On Jul 9, 3:59 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> >> > wrote:
> >> >> Why do you want to partition the drives? That does absolutely nothing
> >> >> for
> >> >> performance since they are all on the same physical array. By
> >> >> partitioning
> >> >> the array you now run into a situation where one or more of the
> >> >> partitions
> >> >> may be too small down the road. And why do you want to stay with Raid
> >> >> 5?
> >> >> Is 5 disks the max you can have in the box? I don't know who large
> >> >> your
> >> >> dbs
> >> >> will be or how you will use them but if performance is a goal placing
> >> >> everything on a single Raid 5 may not be the best option in the long
> >> >> run.
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >> "Donnie3iii" <donnie3...@.gmail.com> wrote in message
> >> >>news:1184020787.195967.302410@.e16g2000pri.googlegroups.com...
> >> >> > Hello,
> >> >> > I am looking for some advice on installing SQL 2005 x64 Standard on
> >> >> > a
> >> >> > new Windows 2003 x64 Standard Dell 2950 Box. We have a Quad 2.6 GHz
> >> >> > with 16 GBs Memory, 5x 73 GB 15k RPM Hard drives.
> >> >> > A couple things we are looking into is the best way to partition the
> >> >> > Raid 5, what we have come up with so far is to create an OS
> >> >> > partition,
> >> >> > Backup Partition, Temp DB partition, and Everything else Partition.
> >> >> > We would like to stay with raid 5.
> >> >> > Next is there any to make Windows 2003 STD work better for SQL on a
> >> >> > fresh install?
> >> >> > SQL tips, We have read up on a few such as dedicating processor to
> >> >> > the
> >> >> > Temp DB. We use our DB for an in-house sales package (software
> >> >> > designer included), Great Plains, and a CRM program.
> >> >> > Thanks for any help.
> >> >> > Don Graham
> >> > The database size will total around 20-25GB (for now, they grow at a
> >> > rate of 7 GBs a year), as for Raid 5 we were going to go with Raid 10
> >> > but we went with the 15k drives instead. Our programmer/dbo swears by
> >> > Raid 5. We can have up to 8 drives in the box but cost was a
> >> > consideration here.
> > "But if your programmer swears by them who am I to say"
> > That was my thought as well but the more I look into it the more I
> > start to disagree. I may go for a raid 1 (OS), Raid 1 (Trans log or
> > TempDB), and I am not sure for the DB drive. Raid 10 is a little to
> > expensive.
> > Overall we have about 25-30 users, it looks like a lot of database
> > operations for the program are done with stored procedures.
Thank you all for the suggestions, I am planning on reconfiguring the
raid and going with either 2 or 3 depending on how much money I can
squeeze out. Thanks!
Don

Advice on Simple Structured Sql Query (Search)

Hi All,
I have created a well structured cross relationship db.. To extract all
the details out is not a problem but i am trying to now include a
simple search to filter results.. Where is the best place to build the
query, within my .Net web app or within a sp itself..
My basic setup is a table with a list of jobs (Jobs_Jobs) and fields
(JobID, Title, Keywords) and then another table that lists the
locations of the jobs(Jobs_Locations) and fields(ID,JobID,LocationID)..
What i am then trying to do is include a keyword search and a location
search together, i need to make it boolean as well and split keywords
up and then search within these to make sure they are in the
locations.. In my head it seemed fairly approchable but cant seem to
grasp it and cant find any decent tutorials.. Could you please point me
in the right direction: Do i need to be using dynamic sql', Should i
be doing most of the work within .net
A typical search would be for:
Keywords: IT,Graduate
Within Location: 12,35,48
I can do one or the other but not combined.. I also dont know how to
split up the keywords to make them two seperate keywords..
If anyone can point me in the right direction as this is my last
hurdle.. On this project at least :)Have a look at
http://www.sommarskog.se/dyn-search.html and
http://www.sommarskog.se/arrays-in-sql.html
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"ponyoo" <anthonykallay@.hotmail.com> wrote in message
news:1133857887.366250.221060@.g14g2000cwa.googlegroups.com...
> Hi All,
> I have created a well structured cross relationship db.. To extract all
> the details out is not a problem but i am trying to now include a
> simple search to filter results.. Where is the best place to build the
> query, within my .Net web app or within a sp itself..
> My basic setup is a table with a list of jobs (Jobs_Jobs) and fields
> (JobID, Title, Keywords) and then another table that lists the
> locations of the jobs(Jobs_Locations) and fields(ID,JobID,LocationID)..
> What i am then trying to do is include a keyword search and a location
> search together, i need to make it boolean as well and split keywords
> up and then search within these to make sure they are in the
> locations.. In my head it seemed fairly approchable but cant seem to
> grasp it and cant find any decent tutorials.. Could you please point me
> in the right direction: Do i need to be using dynamic sql', Should i
> be doing most of the work within .net
> A typical search would be for:
> Keywords: IT,Graduate
> Within Location: 12,35,48
> I can do one or the other but not combined.. I also dont know how to
> split up the keywords to make them two seperate keywords..
> If anyone can point me in the right direction as this is my last
> hurdle.. On this project at least :)
>

Advice on security model for XML Web Services for many users in Active Directory role

I am working on the security model for an application that will be used by 100s of users with a dedicated SQL 2005 database for this application and access via SQL XML Web Services.

The client has asked to make it "open" during alpha testing such that anyone can access the web services without having to set them up first. Is there a way to do this? The best I can figure is to use mixed mode security and hard code a login and password. Any method using Windows authentication would require that I add every user at a minimum to the database.

In production, all users will have an active directory role specified that determines if they should have access to the web services or not. However, it is my understanding that to use Windows authentication, I would still need to add each individual user at a minimum as a Login to the SQL Server, and under best practices also as database users with permissions granted to the endpoint.

Am I correct in the above, or is there a more efficient way to achieve these results?

Thanks

-L

Your post seems to have been moved to this forum, as I have not seen it before. Could you please let me know where you posted this originally?

If the individual users are all belonging to a Windows group, you could just grant connection permissions to that group. But I am not familiar with XML Web Services, so I cannot say whether this is the best course of action.

Have you checked the following link?

http://msdn2.microsoft.com/en-us/library/ms191274.aspx

I'd suggest to also post in the XML forum: SQL Server XML

Thanks
Laurentiu

sql

Advice on sa password change

I am taking over a server that doesn't seem to have been managed very well a
nd am planning on changing the sa password (for security).
However I know that various bits and bobs (on this server) seem to rely on i
t.
So I'm writing a document to outline the steps I have to take. What I need
to know is as full a list as possible of all the places I should check to se
e if the sa password has been used.
So apart from Security - Login - sa - properties, what else should I be chec
king?
Thanks in advance
DerekHi,
For 'SA' login , It is recommended to have mixed case passwords with a few
numbers along with special characters so that a hacker can not guess
the password.
Have a look into the below site from Vyas to get the best practices to be
followed in secuity.
http://vyaskn.tripod.com/sql_server...t_practices.htm
Thanks
Hari
MCDBA
"Derek" <anonymous@.discussions.microsoft.com> wrote in message
news:CAF7226E-6A3B-4210-810F-732A23E70A97@.microsoft.com...
> I am taking over a server that doesn't seem to have been managed very well
and am planning on changing the sa password (for security).
> However I know that various bits and bobs (on this server) seem to rely on
it.
> So I'm writing a document to outline the steps I have to take. What I
need to know is as full a list as possible of all the places I should check
to see if the sa password has been used.
> So apart from Security - Login - sa - properties, what else should I be
checking?
> Thanks in advance
> Derek|||If you are wondering about things that might break when you change the
password:
Look at all DTS packages - Connection properties to this SQL Server. Also
any scheduled DTS package that was scheduled in Enterprise Manager while
logged in as the sa account will have to be resecheduled.
Look at all replication agents to make sure they are not using the sa
account to connect to this server.
Look at all other jobs as well.
Any client application that uses the sa account to connect to this server
will need to be modified to account for the new password.
There are probably some things that I have missed but these have been the
ones that have caused the most problems when the sa password is changed.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Linked servers may break, but you will have to update the remote server
itself to fix the problem. Besides, you shouldn't be linking servers with
the SA login anyway.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:U%23$JPkfLEHA.3364@.cpmsftngxa10.phx.gbl...
> If you are wondering about things that might break when you change the
> password:
> Look at all DTS packages - Connection properties to this SQL Server. Also
> any scheduled DTS package that was scheduled in Enterprise Manager while
> logged in as the sa account will have to be resecheduled.
> Look at all replication agents to make sure they are not using the sa
> account to connect to this server.
> Look at all other jobs as well.
> Any client application that uses the sa account to connect to this server
> will need to be modified to account for the new password.
> There are probably some things that I have missed but these have been the
> ones that have caused the most problems when the sa password is changed.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

Advice on RAID

We sell a software package that runs on dedicated servers at sites that
typically have no db administrator. SQL Server 2000 is the database, and the
application is very write intensive – data for 50 to 200 parameters is
written to the database every minute, 24 hours a day, 7 days a week. Typical
database size is about 4 or 5 GB. Usually there are a few client
workstations. The application is also read intensive. We have configured
the systems with 3 drives such that tempdb is on C, database files are on D
and the log is on E. Because of frequent disk failures and the relatively
high expense of correcting those failures, we want to switch to RAID. Having
little experience, we are looking for advice. RAID 10 is probably not an
option because of the expense. I would highly appreciate any suggestions.
Rethink your aversion to RAID10. It performs better than RAID5 on
write-intensive installations and there is more high-availability associated
with it. Calculate how much revenue your business will lose in one day.
It's likely more than a handful of disks.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:87394BC3-4BD5-4D70-A0B4-C4D4C2DCC7AA@.microsoft.com...
We sell a software package that runs on dedicated servers at sites that
typically have no db administrator. SQL Server 2000 is the database, and
the
application is very write intensive – data for 50 to 200 parameters is
written to the database every minute, 24 hours a day, 7 days a week.
Typical
database size is about 4 or 5 GB. Usually there are a few client
workstations. The application is also read intensive. We have configured
the systems with 3 drives such that tempdb is on C, database files are on D
and the log is on E. Because of frequent disk failures and the relatively
high expense of correcting those failures, we want to switch to RAID.
Having
little experience, we are looking for advice. RAID 10 is probably not an
option because of the expense. I would highly appreciate any suggestions.
|||With such small amounts of data you might as well go with RAID 1
(mirroring), with three pairs of drives. RAID 10 has advantages but
with just 4 to 5 GB of data it would be difficult justifying enough
drives to make a good RAID 10 set. For a write intensive application
you should avoid RAID 5 altogether.
One question I have to ask. You say you have "frequent disk
failures". What kind of drives are you using? (For server
applications I expect SCSI.) What brand are they?
Good luck!
Roy
On Wed, 22 Feb 2006 12:11:27 -0800, KMP
<KMP@.discussions.microsoft.com> wrote:

>We sell a software package that runs on dedicated servers at sites that
>typically have no db administrator. SQL Server 2000 is the database, and the
>application is very write intensive data for 50 to 200 parameters is
>written to the database every minute, 24 hours a day, 7 days a week. Typical
>database size is about 4 or 5 GB. Usually there are a few client
>workstations. The application is also read intensive. We have configured
>the systems with 3 drives such that tempdb is on C, database files are on D
>and the log is on E. Because of frequent disk failures and the relatively
>high expense of correcting those failures, we want to switch to RAID. Having
>little experience, we are looking for advice. RAID 10 is probably not an
>option because of the expense. I would highly appreciate any suggestions.

Advice on RAID

We sell a software package that runs on dedicated servers at sites that
typically have no db administrator. SQL Server 2000 is the database, and the
application is very write intensive â' data for 50 to 200 parameters is
written to the database every minute, 24 hours a day, 7 days a week. Typical
database size is about 4 or 5 GB. Usually there are a few client
workstations. The application is also read intensive. We have configured
the systems with 3 drives such that tempdb is on C, database files are on D
and the log is on E. Because of frequent disk failures and the relatively
high expense of correcting those failures, we want to switch to RAID. Having
little experience, we are looking for advice. RAID 10 is probably not an
option because of the expense. I would highly appreciate any suggestions.Rethink your aversion to RAID10. It performs better than RAID5 on
write-intensive installations and there is more high-availability associated
with it. Calculate how much revenue your business will lose in one day.
It's likely more than a handful of disks.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:87394BC3-4BD5-4D70-A0B4-C4D4C2DCC7AA@.microsoft.com...
We sell a software package that runs on dedicated servers at sites that
typically have no db administrator. SQL Server 2000 is the database, and
the
application is very write intensive â' data for 50 to 200 parameters is
written to the database every minute, 24 hours a day, 7 days a week.
Typical
database size is about 4 or 5 GB. Usually there are a few client
workstations. The application is also read intensive. We have configured
the systems with 3 drives such that tempdb is on C, database files are on D
and the log is on E. Because of frequent disk failures and the relatively
high expense of correcting those failures, we want to switch to RAID.
Having
little experience, we are looking for advice. RAID 10 is probably not an
option because of the expense. I would highly appreciate any suggestions.|||With such small amounts of data you might as well go with RAID 1
(mirroring), with three pairs of drives. RAID 10 has advantages but
with just 4 to 5 GB of data it would be difficult justifying enough
drives to make a good RAID 10 set. For a write intensive application
you should avoid RAID 5 altogether.
One question I have to ask. You say you have "frequent disk
failures". What kind of drives are you using? (For server
applications I expect SCSI.) What brand are they?
Good luck!
Roy
On Wed, 22 Feb 2006 12:11:27 -0800, KMP
<KMP@.discussions.microsoft.com> wrote:
>We sell a software package that runs on dedicated servers at sites that
>typically have no db administrator. SQL Server 2000 is the database, and the
>application is very write intensive ? data for 50 to 200 parameters is
>written to the database every minute, 24 hours a day, 7 days a week. Typical
>database size is about 4 or 5 GB. Usually there are a few client
>workstations. The application is also read intensive. We have configured
>the systems with 3 drives such that tempdb is on C, database files are on D
>and the log is on E. Because of frequent disk failures and the relatively
>high expense of correcting those failures, we want to switch to RAID. Having
>little experience, we are looking for advice. RAID 10 is probably not an
>option because of the expense. I would highly appreciate any suggestions.

Advice on RAID

We sell a software package that runs on dedicated servers at sites that
typically have no db administrator. SQL Server 2000 is the database, and th
e
application is very write intensive – data for 50 to 200 parameters is
written to the database every minute, 24 hours a day, 7 days a week. Typica
l
database size is about 4 or 5 GB. Usually there are a few client
workstations. The application is also read intensive. We have configured
the systems with 3 drives such that tempdb is on C, database files are on D
and the log is on E. Because of frequent disk failures and the relatively
high expense of correcting those failures, we want to switch to RAID. Havin
g
little experience, we are looking for advice. RAID 10 is probably not an
option because of the expense. I would highly appreciate any suggestions.Rethink your aversion to RAID10. It performs better than RAID5 on
write-intensive installations and there is more high-availability associated
with it. Calculate how much revenue your business will lose in one day.
It's likely more than a handful of disks.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:87394BC3-4BD5-4D70-A0B4-C4D4C2DCC7AA@.microsoft.com...
We sell a software package that runs on dedicated servers at sites that
typically have no db administrator. SQL Server 2000 is the database, and
the
application is very write intensive – data for 50 to 200 parameters is
written to the database every minute, 24 hours a day, 7 days a week.
Typical
database size is about 4 or 5 GB. Usually there are a few client
workstations. The application is also read intensive. We have configured
the systems with 3 drives such that tempdb is on C, database files are on D
and the log is on E. Because of frequent disk failures and the relatively
high expense of correcting those failures, we want to switch to RAID.
Having
little experience, we are looking for advice. RAID 10 is probably not an
option because of the expense. I would highly appreciate any suggestions.|||With such small amounts of data you might as well go with RAID 1
(mirroring), with three pairs of drives. RAID 10 has advantages but
with just 4 to 5 GB of data it would be difficult justifying enough
drives to make a good RAID 10 set. For a write intensive application
you should avoid RAID 5 altogether.
One question I have to ask. You say you have "frequent disk
failures". What kind of drives are you using? (For server
applications I expect SCSI.) What brand are they?
Good luck!
Roy
On Wed, 22 Feb 2006 12:11:27 -0800, KMP
<KMP@.discussions.microsoft.com> wrote:

>We sell a software package that runs on dedicated servers at sites that
>typically have no db administrator. SQL Server 2000 is the database, and t
he
>application is very write intensive data for 50 to 200 parameters is
>written to the database every minute, 24 hours a day, 7 days a week. Typic
al
>database size is about 4 or 5 GB. Usually there are a few client
>workstations. The application is also read intensive. We have configured
>the systems with 3 drives such that tempdb is on C, database files are on D
>and the log is on E. Because of frequent disk failures and the relatively
>high expense of correcting those failures, we want to switch to RAID. Havi
ng
>little experience, we are looking for advice. RAID 10 is probably not an
>option because of the expense. I would highly appreciate any suggestions.

advice on querying large tables

I have to deal with a new database with a number of tables with 100,000+
records. Can anybody give me any advice on the different methods of
querying large tables as opposed to small tables?
Cheers,
Mike
*** Sent via Developersdex http://www.examnotes.net ***"Mike P" <mike.parr@.gmail.com> wrote in message
news:eEhRZu8tFHA.360@.TK2MSFTNGP12.phx.gbl...
>I have to deal with a new database with a number of tables with 100,000+
> records. Can anybody give me any advice on the different methods of
> querying large tables as opposed to small tables?
Learn to use the WHERE clause.
Learn how indexes work (in depth) and then build them appropriately for your
queries. Take a look at the index tuning wizard for additional help there.
Only ask for the data you actually need.
This should get you started..
Rick Sawtell
MCT, MCSD, MCDBA|||Learn how to use the Show Execution Plan feature of Query Analyzer.
"Mike P" <mike.parr@.gmail.com> wrote in message
news:eEhRZu8tFHA.360@.TK2MSFTNGP12.phx.gbl...
>I have to deal with a new database with a number of tables with 100,000+
> records. Can anybody give me any advice on the different methods of
> querying large tables as opposed to small tables?
>
> Cheers,
> Mike
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Regarding how learning about indexes and execution plans, I have found the
following book to be very helpful:
Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook, by
Ken England
"Mike P" wrote:

> I have to deal with a new database with a number of tables with 100,000+
> records. Can anybody give me any advice on the different methods of
> querying large tables as opposed to small tables?
>
> Cheers,
> Mike
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Yes, I have that one too and found it informative.
"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:FBBB8707-300C-4510-BE0E-CDE1D4408B75@.microsoft.com...
> Regarding how learning about indexes and execution plans, I have found the
> following book to be very helpful:
> Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook, by
> Ken England
> "Mike P" wrote:
>|||I found this book better....
"SQL Server Query: Performance Tuning Distilled" by Sajal Dam (Curlingstone
Publisher)
"JT" wrote:

> Yes, I have that one too and found it informative.
> "Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
> news:FBBB8707-300C-4510-BE0E-CDE1D4408B75@.microsoft.com...
>
>sql

advice on query

Hi,
my question is - how do I get just the best price from this query:
item is a shopping list
prod is the product list
price is the list of prices, some product have multiple prices for different
quantities
eg Prod price minquant
6 3.53 1
6 3.22 30
6 2.95 100
SELECT item.OrderItemID, item.ProductID, item.iQuant, prod.sItem,
prod.sShortDesc, price.cPrice
FROM mms_tblOrderItemList item INNER JOIN
mms_tblProductList prod ON item.ProductID =
prod.ProductID INNER JOIN
mms_tblProductPriceList price ON prod.ProductID =
price.ProductID
WHERE (item.OrderHeadID = 9)
gives
20 1 1 Kistenspanners K-10 4.5
18 5 1 De-Sta-Co #311 22.42
21 6 35 Spring 3.53
21 6 35 Spring 3.22
21 6 35 Spring 2.95
I just want the price relevant to the quantity - eg in eaxample above, 35
Springs qualifies for >30 price of 3.22.
Appreciate any help!
thansk,
NEILwell, i didn't have time to recreate the tables and data you listed
without the DDL, but i think you need a subquery. try adding something
like this to your WHERE clause, and you should get only the most
appropriate quantity:
AND (item.iQuant > (SELECT MAX(minquant) FROM price AS price2
WHERE price2.prod = item.ProductID AND minquant < item.iQuant))
for a whole query of:
SELECT item.OrderItemID, item.ProductID, item.iQuant, prod.sItem,
prod.sShortDesc, price.cPrice
FROM mms_tblOrderItemList item INNER JOIN
mms_tblProductList prod ON item.ProductID =
prod.ProductID INNER JOIN
mms_tblProductPriceList price ON prod.ProductID =
price.ProductID
WHERE (item.OrderHeadID = 9)
AND (item.iQuant > (SELECT MAX(minquant) FROM price AS price2
WHERE price2.prod = item.ProductID AND minquant < item.iQuant))
hope this helps
jason|||actually, it should probably look more like this:
AND (price.minquant = (SELECT MAX(minquant) FROM price AS price2
WHERE price2.prod = item.ProductID AND minquant < item.iQuant))|||You can try this:
SELECT item.OrderItemID, item.ProductID, item.iQuant, prod.sItem,
prod.sShortDesc, price.cPrice
FROM mms_tblOrderItemList item INNER JOIN
mms_tblProductList prod ON item.ProductID =
prod.ProductID INNER JOIN
mms_tblProductPriceList price ON prod.ProductID =
price.ProductID
WHERE (item.OrderHeadID = 9)
and minquant = (select max(minquant)
from mms_tblProductPriceList price2
where price2.minquant < item.iQuant
prod.ProductID = price2.ProductID)
Perayu
"Neil Jarman" wrote:

> Hi,
> my question is - how do I get just the best price from this query:
> item is a shopping list
> prod is the product list
> price is the list of prices, some product have multiple prices for differe
nt
> quantities
> eg Prod price minquant
> 6 3.53 1
> 6 3.22 30
> 6 2.95 100
> SELECT item.OrderItemID, item.ProductID, item.iQuant, prod.sItem,
> prod.sShortDesc, price.cPrice
> FROM mms_tblOrderItemList item INNER JOIN
> mms_tblProductList prod ON item.ProductID =
> prod.ProductID INNER JOIN
> mms_tblProductPriceList price ON prod.ProductID =
> price.ProductID
> WHERE (item.OrderHeadID = 9)
> gives
> 20 1 1 Kistenspanners K-10 4.5
> 18 5 1 De-Sta-Co #311 22.42
> 21 6 35 Spring 3.53
> 21 6 35 Spring 3.22
> 21 6 35 Spring 2.95
> I just want the price relevant to the quantity - eg in eaxample above, 35
> Springs qualifies for >30 price of 3.22.
> Appreciate any help!
> thansk,
> NEIL
>
>|||Thank you both sooooo much - I was really stuck with that one!!!
NEIL
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:dekp74$kvk$1$8300dec7@.news.demon.co.uk...
> Hi,
> my question is - how do I get just the best price from this query:
> item is a shopping list
> prod is the product list
> price is the list of prices, some product have multiple prices for
> different quantities
> eg Prod price minquant
> 6 3.53 1
> 6 3.22 30
> 6 2.95 100
> SELECT item.OrderItemID, item.ProductID, item.iQuant, prod.sItem,
> prod.sShortDesc, price.cPrice
> FROM mms_tblOrderItemList item INNER JOIN
> mms_tblProductList prod ON item.ProductID =
> prod.ProductID INNER JOIN
> mms_tblProductPriceList price ON prod.ProductID =
> price.ProductID
> WHERE (item.OrderHeadID = 9)
> gives
> 20 1 1 Kistenspanners K-10 4.5
> 18 5 1 De-Sta-Co #311 22.42
> 21 6 35 Spring 3.53
> 21 6 35 Spring 3.22
> 21 6 35 Spring 2.95
> I just want the price relevant to the quantity - eg in eaxample above, 35
> Springs qualifies for >30 price of 3.22.
> Appreciate any help!
> thansk,
> NEIL
>|||my pleasure, glad it helped

Advice on query

Hi,

Quite possibly this is easy but my brain is bazango'd right now and for the life of me I can't think of a good way to get the following...

I have a table with 4 columns

ID (int)
User (varchar(200))
Timestamp (datetime)
Note (text)

The ID, User, Timestamp make up the primary key and nulls are not permitted in any column

I'm trying to select the top 3 most recent rows [using timestamp] for each ID.

anyone?

thanks in advance
Mac

Hi,

SELECT *

FROM

(

SELECT

[id], [user],[timestamp], --you should not use reserved words for column names

RANK() OVER (PARTITION BY id,user,timestamp ORDER BY Timestamp DESC) AS Ranked

) Subquery

WHERE Ranked <=3

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Mac:

Maybe something like this?

set nocount on

declare @.mockUp table
( ID integer not null,
[USER] varchar (200) not null,
Timestamp datetime not null,
Note text not null
)

insert into @.mockUp values (1, 'Mugambo', '10/1/6', 'Note #1')
insert into @.mockUp values (2, 'Mugambo', '10/2/6', 'Note #2')
insert into @.mockUp values (3, 'Mugambo', '10/5/6', 'Note #3')
insert into @.mockUp values (4, 'Mugambo', '10/4/6', 'Note #4')
insert into @.mockUp values (5, 'Mugambo', '10/8/6', 'Note #5')

insert into @.mockUp values (11, 'Herman Munster', '10/1/6', 'Note #1')
insert into @.mockUp values (12, 'Herman Munster', '10/2/6', 'Note #2')
insert into @.mockUp values (13, 'Herman Munster', '10/3/6', 'Note #3')
insert into @.mockUp values (14, 'Herman Munster', '10/3/6', 'Note #4')
insert into @.mockUp values (15, 'Herman Munster', '10/5/6', 'Note #5')

select [user],
id,
Timestamp,
Note
from (
select id,
[user],
Timestamp,
rank ()
over (partition by [user] order by timestamp, id)
as Seq,
Note
from @.mockUp
) x
where seq <= 3

-- --
-- SQL Server 2005 SAMPLE OUTPUT:
-- --

-- Herman Munster 11 2006-10-01 00:00:00.000 Note #1
-- Herman Munster 12 2006-10-02 00:00:00.000 Note #2
-- Herman Munster 13 2006-10-03 00:00:00.000 Note #3
-- Mugambo 1 2006-10-01 00:00:00.000 Note #1
-- Mugambo 2 2006-10-02 00:00:00.000 Note #2
-- Mugambo 4 2006-10-04 00:00:00.000 Note #4

select x.ID,
x.[User],
x.Timestamp,
y.note
from (
select a.id,
a.[user],
a.Timestamp,
count(*) as Seq
from @.mockUp a
inner join @.mockUp b
on a.[user] = b.[user]
and ( a.timestamp > b.timestamp or
a.timestamp = b.timestamp and
a.id >= b.id
)
group by a.id, a.[user], a.Timestamp
) x
inner join @.mockUp y
on x.seq <= 3
and x.id = y.id
order by x.[user], x.seq

-- --
-- SQL Server 2000 SAMPLE OUTPUT:
-- --

-- Herman Munster 11 2006-10-01 00:00:00.000 Note #1
-- Herman Munster 12 2006-10-02 00:00:00.000 Note #2
-- Herman Munster 13 2006-10-03 00:00:00.000 Note #3
-- Mugambo 1 2006-10-01 00:00:00.000 Note #1
-- Mugambo 2 2006-10-02 00:00:00.000 Note #2
-- Mugambo 4 2006-10-04 00:00:00.000 Note #4

|||I definitely agree with Jens' solution!|||Jens, why didn't you used a TOP / ORDER BY solution ? does the "ranking over" performs better ?|||

thank you both, exactly what I was needing.

|||

Hi,

That should be worth a try for the original poster:

SELECT *

FROM SomeTable ST
WHERE Exists

(

SELECT
TOP 3 [id], [user],[timestamp], --you should not use reserved words for column names
From SomeTable Subquery
Where
Subquery.[id] = ST.[id] AND
Subquery.[user] = ST.[user] AND
Subquery.[timestamp]
= ST.[timestamp]

)

I don′t know if you meant TOP 3 in the upper query, that would not take the effect to get back the first three OF EVERY ID.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Advice on Partitioning and Processing

I'd appreciate some advice on the following:

I'm building a cube which is based on data in a SQL 2000 DB. There are around 1.2 billion rows of fact data in 26 tables. Each table uses approx 6 Gig. There are 4 Dimensions, 3 dimensions have less than a 1000 members with the 4th having 65000. For convenience I am creating 26 Cube partitions aligned to the DB tables, each partition will have around 50 million rows. Does this sound like a reasonable proposal? The reason I ask is that I am seeing very poor performance whilst calling ProcessData. I have processed various numbers of partitions in parallel and cannot achieve the same times that I can get on the existing AS2000 rig using the Parallel Processing Utility.

I have tested the data read by writing a .net application to execute the same DB queries in parallel that the partitions use (achieving 200 million rows in 20 minutes) and there are no issues related to network, disk or the DB server.

Processing seems to start well but around a minute in the Network utilisation drops down to 10 percent and CPU utilisation to 30%. It then goes back up to 100% for a short period and then drops again. This is repeated continually until processing is finished. At current speeds it will take 26 hours to complete as opposed to 10 hours on AS2000. The box is a 64-bit 4way hyperthreaded with 32 GB of RAM. AS2005 is Enterprise with SP1.

Any suggestions would be appreciated as I'm running out of ideas!

Rob.

You should check yourself against best practices you would find the project REAL implemetation http://www.microsoft.com/sql/solutions/bi/projectreal.mspx

I would say the partition size is a bit big. You should try and partition some more for and split a single partition in two. But that doesnt look like a major problem.

Question for you: in AS2005 you processing partitions in parallel. How many? Is the pattern the same for any number of partitions?

What OLEDB provider you are using to read data from SQL Server?

The reading rates looks to be slow. See where the bottleneck is. In your test application, the fair comparison not only when you execute SQL query, but you also retrevie all the data returned by the query.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Advice on indexes

I have a table (detail) with 4 columns and over 30 million rows .
Say the columns are called ColA, ColB, ColC, ColD

ColA and ColB form a foreign key as this table is a 'detail' table for another master table in the database (master). The master table uses these two columns as its primary key.
To speed up joins between the two tables I have created an index over ColA and ColB

I frequently need to join the two tables together and so most of my queries are of the form:

SELECT master.*, detail.ColC, detail.ColD FROM master JOIN detail
ON master.ColA = detail.ColA and master.ColB = detail.ColB
WHERE master.ColA = @.valA
ORDER BY master.ColB, detail.ColC

This could return up to 60000 rows for a specific value of @.valA.
This is usually a sub-query that feeds directly into another query or a temporary table.

However, I also quite often need to filter the details further by ColC so I have queries like

SELECT master.*, detail.ColC, detail.ColD FROM master JOIN detail

ON master.ColA = detail.ColA and master.ColB = detail.ColB

WHERE master.ColA = @.valA AND detail.ColC = @.valC

ORDER BY master.ColB

The results set from this could be just a few hundred rows depending on @.valC

I clearly have a requirement to have an index across ColA, and ColB, presumably as the clustered index.

My question is how to speed up queries that involve ColC.
Do I create another index across ColA, ColB and ColC or just ColC by itself?
Will an index just on ColC make use of the the clustered index on the other columns?
Should I make the clustered index cover ColA, ColB and ColC instead ? etc ..

I am using SQL Express and am approaching the 4GB limit and this table and another one similar to it account for 96% of the database size.
The four columns in the detail table are all integer types and so don't take up much space per row. I am worried that a wide index will significantly add to the storage space per row and therefore significantly reduce the amount of data I can store in the database.

Any advice would be appreciated.

You defintely do not ever want to create one index that is a subset of another as you asked, ie. do not create two indexes on

ColA, ColB

ColA, ColB, ColC

The one on ColA, ColB, ColC does everything that the first one does, so it is all you need.

In your case it sounds like a clustered index on ColA, ColB, ColC should be all you need. Leaving out one column won't make much difference to the size. The size will be affected by the fillfactor and the fragmentation level of the index, so you should rebuild the index from time to time (how often depends on the nature of your updates) with a fillfactor of 100%, although a high fillfactor will increase the possibility that some updates will be slower.

|||Thanks for that.

I have done as you have said and created an index across all three columns and I am happy with both the speed of the queries and the size of the database.sql

Advice on how to handle backup files

Hi all,
Our client wants some guidance on how to handle backup files created by
SQL Server 2005 for our product.
I was thinking that we would create
1) a nightly backup to say e:\backup
2) create a transaction log backup every hour to e:\backup
Now is it best to create a new file each time - or is it best to
overwrite the original file?
Reason I ask is the client will need to them back these files up to an
external tape drive.
It will probably be easier for them if they have the same 20+ files
each time (ie. one full backup, and hourly transaction files)
If the filenames incremented, then they would need to delete older
files that have been backed up (which can invite user error)
What are peoples opinions on this? Is there a "good way" to do this'
Cheers
DavidHi
Well, in our shop we do a full backup every night to c:\ and log backup
every 15 minutes to d:\
A log file may contain many files so you'll need to specify i like
drop database test
go
create database test
GO
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'd:\db.bak' WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak'WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak' WITH NOINIT
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery
...............
.............
<davconts@.gmail.com> wrote in message
news:1151992078.629506.142340@.p79g2000cwp.googlegroups.com...
> Hi all,
> Our client wants some guidance on how to handle backup files created by
> SQL Server 2005 for our product.
> I was thinking that we would create
> 1) a nightly backup to say e:\backup
> 2) create a transaction log backup every hour to e:\backup
> Now is it best to create a new file each time - or is it best to
> overwrite the original file?
> Reason I ask is the client will need to them back these files up to an
> external tape drive.
> It will probably be easier for them if they have the same 20+ files
> each time (ie. one full backup, and hourly transaction files)
> If the filenames incremented, then they would need to delete older
> files that have been backed up (which can invite user error)
> What are peoples opinions on this? Is there a "good way" to do this'
> Cheers
> David
>|||This is a multi-part message in MIME format.
--070701000301070106090101
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
davconts@.gmail.com wrote:
> Hi all,
> Our client wants some guidance on how to handle backup files created by
> SQL Server 2005 for our product.
> I was thinking that we would create
> 1) a nightly backup to say e:\backup
> 2) create a transaction log backup every hour to e:\backup
> Now is it best to create a new file each time - or is it best to
> overwrite the original file?
> Reason I ask is the client will need to them back these files up to an
> external tape drive.
> It will probably be easier for them if they have the same 20+ files
> each time (ie. one full backup, and hourly transaction files)
> If the filenames incremented, then they would need to delete older
> files that have been backed up (which can invite user error)
> What are peoples opinions on this? Is there a "good way" to do this'
> Cheers
> David
>
Hi David
I don't think there's a single "good way" to do it. It pretty much
depends on personal preferences and I don't think one mtethod is better
than others.
Bottomline is that no matter how you decide to do it, it will require
some degree of knowledge on how it works in order to maintain it and
restore a backup in case of a failure.
I prefer to only have one backup in each file and then name the files
with a name that are composed by date and a forth running number( for
the log files). This is because I like to know that each file only
contains one backup so when I need to restore I have to find the full
backup file and then the corresponding logfile 1, 2, 3... up to the
point in time I need. If I had more backups in the same file, I'd first
have to find out which file number I had to restore for the fulle backup
and then I'd also have to find out from which filenumber the logfile
backup would start. Another thing that I don't like about having many
backups in one file, is when the backups starts to get too big. In that
case you suddenly get some quite big backup files that can be a pain to
"administer". The backup of one of our databases are currently around 85
GB and if you have a number of these in the same file, it will easily
get quite "un-handy".
I think the biggest advantage of having several files in one backup
file, is that it's easier to let SQL server delete old backups in the
file. If you have seperate files with naming by date, you'll have to do
some scripting to delete the files.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--070701000301070106090101
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:davconts@.gmail.com">davconts@.gmail.com</a> wrote:
<blockquote
cite="mid1151992078.629506.142340@.p79g2000cwp.googlegroups.com"
type="cite">
<pre wrap="">Hi all,
Our client wants some guidance on how to handle backup files created by
SQL Server 2005 for our product.
I was thinking that we would create
1) a nightly backup to say e:\backup
2) create a transaction log backup every hour to e:\backup
Now is it best to create a new file each time - or is it best to
overwrite the original file?
Reason I ask is the client will need to them back these files up to an
external tape drive.
It will probably be easier for them if they have the same 20+ files
each time (ie. one full backup, and hourly transaction files)
If the filenames incremented, then they would need to delete older
files that have been backed up (which can invite user error)
What are peoples opinions on this? Is there a "good way" to do this'
Cheers
David
</pre>
</blockquote>
<font size="-1"><font face="Arial">Hi David<br>
<br>
I don't think there's a single "good way" to do it. It pretty much
depends on personal preferences and I don't think one mtethod is better
than others.<br>
Bottomline is that no matter how you decide to do it, it will require
some degree of knowledge on how it works in order to maintain it and
restore a backup in case of a failure.<br>
I prefer to only have one backup in each file and then name the files
with a name that are composed by date and a forth running number( for
the log files). This is because I like to know that each file only
contains one backup so when I need to restore I have to find the full
backup file and then the corresponding logfile 1, 2, 3... up to the
point in time I need. If I had more backups in the same file, I'd first
have to find out which file number I had to restore for the fulle
backup and then I'd also have to find out from which filenumber the
logfile backup would start. Another thing that I don't like about
having many backups in one file, is when the backups starts to get too
big. In that case you suddenly get some quite big backup files that can
be a pain to "administer". The backup of one of our databases are
currently around 85 GB and if you have a number of these in the same
file, it will easily get quite "un-handy".<br>
I think the biggest advantage of having several files in one backup
file, is that it's easier to let SQL server delete old backups in the
file. If you have seperate files with naming by date, you'll have to do
some scripting to delete the files.<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
<br>
</font></font>
</body>
</html>
--070701000301070106090101--|||In addition to the other posts:
I agree that there is no "best way" for this. Understand the backup command and the options (mainly
INIT and NOINIT). Then you can consider:
Maintenance plans (to do backups). It creates a new file for each time a backup is taken and also
remove files older than X days.
Do above with your own TSQL code. This opens the ability for further customization.
Have several backups on same file. For instance, have a Monday backup file which you backup to on
Mondays and overwrite each time. This will give you 7 days worth of backup generations. Or some
variation of this. One advantage is reduced number of files. Just make sure that the people doing
restore know about RESTORE HEADERONLY and FILELISTONY and the FILE option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<davconts@.gmail.com> wrote in message news:1151992078.629506.142340@.p79g2000cwp.googlegroups.com...
> Hi all,
> Our client wants some guidance on how to handle backup files created by
> SQL Server 2005 for our product.
> I was thinking that we would create
> 1) a nightly backup to say e:\backup
> 2) create a transaction log backup every hour to e:\backup
> Now is it best to create a new file each time - or is it best to
> overwrite the original file?
> Reason I ask is the client will need to them back these files up to an
> external tape drive.
> It will probably be easier for them if they have the same 20+ files
> each time (ie. one full backup, and hourly transaction files)
> If the filenames incremented, then they would need to delete older
> files that have been backed up (which can invite user error)
> What are peoples opinions on this? Is there a "good way" to do this'
> Cheers
> David
>

Advice on how to handle backup files

Hi all,
Our client wants some guidance on how to handle backup files created by
SQL Server 2005 for our product.
I was thinking that we would create
1) a nightly backup to say e:\backup
2) create a transaction log backup every hour to e:\backup
Now is it best to create a new file each time - or is it best to
overwrite the original file?
Reason I ask is the client will need to them back these files up to an
external tape drive.
It will probably be easier for them if they have the same 20+ files
each time (ie. one full backup, and hourly transaction files)
If the filenames incremented, then they would need to delete older
files that have been backed up (which can invite user error)
What are peoples opinions on this? Is there a "good way" to do this'
Cheers
DavidHi
Well, in our shop we do a full backup every night to c:\ and log backup
every 15 minutes to d:\
A log file may contain many files so you'll need to specify i like
drop database test
go
create database test
GO
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'd:\db.bak' WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak'WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak' WITH NOINIT
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery
...............
.............
<davconts@.gmail.com> wrote in message
news:1151992078.629506.142340@.p79g2000cwp.googlegroups.com...
> Hi all,
> Our client wants some guidance on how to handle backup files created by
> SQL Server 2005 for our product.
> I was thinking that we would create
> 1) a nightly backup to say e:\backup
> 2) create a transaction log backup every hour to e:\backup
> Now is it best to create a new file each time - or is it best to
> overwrite the original file?
> Reason I ask is the client will need to them back these files up to an
> external tape drive.
> It will probably be easier for them if they have the same 20+ files
> each time (ie. one full backup, and hourly transaction files)
> If the filenames incremented, then they would need to delete older
> files that have been backed up (which can invite user error)
> What are peoples opinions on this? Is there a "good way" to do this'
> Cheers
> David
>|||davconts@.gmail.com wrote:
> Hi all,
> Our client wants some guidance on how to handle backup files created by
> SQL Server 2005 for our product.
> I was thinking that we would create
> 1) a nightly backup to say e:\backup
> 2) create a transaction log backup every hour to e:\backup
> Now is it best to create a new file each time - or is it best to
> overwrite the original file?
> Reason I ask is the client will need to them back these files up to an
> external tape drive.
> It will probably be easier for them if they have the same 20+ files
> each time (ie. one full backup, and hourly transaction files)
> If the filenames incremented, then they would need to delete older
> files that have been backed up (which can invite user error)
> What are peoples opinions on this? Is there a "good way" to do this'
> Cheers
> David
>
Hi David
I don't think there's a single "good way" to do it. It pretty much
depends on personal preferences and I don't think one mtethod is better
than others.
Bottomline is that no matter how you decide to do it, it will require
some degree of knowledge on how it works in order to maintain it and
restore a backup in case of a failure.
I prefer to only have one backup in each file and then name the files
with a name that are composed by date and a forth running number( for
the log files). This is because I like to know that each file only
contains one backup so when I need to restore I have to find the full
backup file and then the corresponding logfile 1, 2, 3... up to the
point in time I need. If I had more backups in the same file, I'd first
have to find out which file number I had to restore for the fulle backup
and then I'd also have to find out from which filenumber the logfile
backup would start. Another thing that I don't like about having many
backups in one file, is when the backups starts to get too big. In that
case you suddenly get some quite big backup files that can be a pain to
"administer". The backup of one of our databases are currently around 85
GB and if you have a number of these in the same file, it will easily
get quite "un-handy".
I think the biggest advantage of having several files in one backup
file, is that it's easier to let SQL server delete old backups in the
file. If you have seperate files with naming by date, you'll have to do
some scripting to delete the files.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||In addition to the other posts:
I agree that there is no "best way" for this. Understand the backup command
and the options (mainly
INIT and NOINIT). Then you can consider:
Maintenance plans (to do backups). It creates a new file for each time a bac
kup is taken and also
remove files older than X days.
Do above with your own TSQL code. This opens the ability for further customi
zation.
Have several backups on same file. For instance, have a Monday backup file w
hich you backup to on
Mondays and overwrite each time. This will give you 7 days worth of backup g
enerations. Or some
variation of this. One advantage is reduced number of files. Just make sure
that the people doing
restore know about RESTORE HEADERONLY and FILELISTONY and the FILE option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<davconts@.gmail.com> wrote in message news:1151992078.629506.142340@.p79g2000cwp.googlegroups
.com...
> Hi all,
> Our client wants some guidance on how to handle backup files created by
> SQL Server 2005 for our product.
> I was thinking that we would create
> 1) a nightly backup to say e:\backup
> 2) create a transaction log backup every hour to e:\backup
> Now is it best to create a new file each time - or is it best to
> overwrite the original file?
> Reason I ask is the client will need to them back these files up to an
> external tape drive.
> It will probably be easier for them if they have the same 20+ files
> each time (ie. one full backup, and hourly transaction files)
> If the filenames incremented, then they would need to delete older
> files that have been backed up (which can invite user error)
> What are peoples opinions on this? Is there a "good way" to do this'
> Cheers
> David
>