I've noticed that when I take stable SQL 2000 engines that are at SP3A where
the sqlservr executable is at build 8.00.760 and install SRS I get a new
build of the sql engine.
SQLServr.EXE build is 8.00.859 in SRS and things that used to work don't.
For example, stored procedure debug doesn't work at all on an SRS SQL Server.
I know I have seen a few other things that are now broke.
Does anyone know of any hotfixes that repair features that SRS breaks?RS doesn't do anything to the SQL Engine. If you are talkin about the 859
hotfix that we require in certain situations, there is a newer hotfix that
resolves the problem. You need to request it from product support. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;831997.
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:22305D8C-975B-4283-BC11-8264CDDEEB1E@.microsoft.com...
> I've noticed that when I take stable SQL 2000 engines that are at SP3A
> where
> the sqlservr executable is at build 8.00.760 and install SRS I get a new
> build of the sql engine.
> SQLServr.EXE build is 8.00.859 in SRS and things that used to work don't.
> For example, stored procedure debug doesn't work at all on an SRS SQL
> Server.
> I know I have seen a few other things that are now broke.
> Does anyone know of any hotfixes that repair features that SRS breaks?|||Thank-you for the quick reponse Brian.
I think maybe I wasn't too clear on my question.
If I install SQL Server 2000 Standard and then take it up to SP3A the build
level of my SQL engine is 760.
However, if I then apply SQL Reporting Services on top of this installation
my SQL engine gets taken up to build 859. Maybe this is not directly related
to SRS, but it certainly takes place upon installation of SRS.
At that point I no longer have the capability to debug stored procs (I
believe thru the sp_dbidbg xp).
So the downside is that I have to make a choice between having SRS and then
losing the capability of stored proc debug or not having SRS.
If there is a hotfix that addresses that I would love to have it.
thanks,
dan
"Brian Welcker [MS]" wrote:
> RS doesn't do anything to the SQL Engine. If you are talkin about the 859
> hotfix that we require in certain situations, there is a newer hotfix that
> resolves the problem. You need to request it from product support. See
> http://support.microsoft.com/default.aspx?scid=kb;en-us;831997.
>
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:22305D8C-975B-4283-BC11-8264CDDEEB1E@.microsoft.com...
> > I've noticed that when I take stable SQL 2000 engines that are at SP3A
> > where
> > the sqlservr executable is at build 8.00.760 and install SRS I get a new
> > build of the sql engine.
> >
> > SQLServr.EXE build is 8.00.859 in SRS and things that used to work don't.
> >
> > For example, stored procedure debug doesn't work at all on an SRS SQL
> > Server.
> >
> > I know I have seen a few other things that are now broke.
> >
> > Does anyone know of any hotfixes that repair features that SRS breaks?
>
>|||The RS setup proces does not touch the version of the SQL engine. We really
don't. We recommend a specific SQL engine hotfix during the installation
process that does do this. You would need to call product support for the
hotfix. I don't think it is generally downloadable.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:DA304849-A7E9-4A92-A39D-5E338227EF52@.microsoft.com...
> Thank-you for the quick reponse Brian.
> I think maybe I wasn't too clear on my question.
> If I install SQL Server 2000 Standard and then take it up to SP3A the
> build
> level of my SQL engine is 760.
> However, if I then apply SQL Reporting Services on top of this
> installation
> my SQL engine gets taken up to build 859. Maybe this is not directly
> related
> to SRS, but it certainly takes place upon installation of SRS.
> At that point I no longer have the capability to debug stored procs (I
> believe thru the sp_dbidbg xp).
> So the downside is that I have to make a choice between having SRS and
> then
> losing the capability of stored proc debug or not having SRS.
> If there is a hotfix that addresses that I would love to have it.
> thanks,
> dan
>
> "Brian Welcker [MS]" wrote:
>> RS doesn't do anything to the SQL Engine. If you are talkin about the 859
>> hotfix that we require in certain situations, there is a newer hotfix
>> that
>> resolves the problem. You need to request it from product support. See
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;831997.
>>
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Dan" <Dan@.discussions.microsoft.com> wrote in message
>> news:22305D8C-975B-4283-BC11-8264CDDEEB1E@.microsoft.com...
>> > I've noticed that when I take stable SQL 2000 engines that are at SP3A
>> > where
>> > the sqlservr executable is at build 8.00.760 and install SRS I get a
>> > new
>> > build of the sql engine.
>> >
>> > SQLServr.EXE build is 8.00.859 in SRS and things that used to work
>> > don't.
>> >
>> > For example, stored procedure debug doesn't work at all on an SRS SQL
>> > Server.
>> >
>> > I know I have seen a few other things that are now broke.
>> >
>> > Does anyone know of any hotfixes that repair features that SRS breaks?
>>sql
Showing posts with label break. Show all posts
Showing posts with label break. Show all posts
Thursday, March 29, 2012
Thursday, March 22, 2012
does restore operation break tlog bckup chain?
Hi all!
I'm thinking of switching my production db to full recovery model.
Now, I've some questions regarding tlog backups chain. What can
possibly break chain of transaction log backups?
And yet another question. Does restore operation break transaction log
backups chain? I mean, should every restore operation be followed by
full db backup?
with regards
Maciej Szymanski> What can possibly break chain of transaction log backups?
In the FULL recovery model, truncating the log without creating a log backup
file (BACKUP LOG WITH TRUNCATE_ONLY or NO_LOG) will break the log backup
sequence.
> And yet another question. Does restore operation break transaction log
> backups chain? I mean, should every restore operation be followed by
> full db backup?
The script below illustrates that a backup isn't needed following a restore.
CREATE DATABASE MyDatabase
ALTER DATABASE MyDatabase
SET RECOVERY FULL
GO
USE MyDatabase
CREATE TABLE MyTable(Col1 int NOT NULL)
INSERT INTO MyTable VALUES(1)
BACKUP DATABASE MyDatabase
TO DISK='C:\Backups\MyDatabase.bak'
WITH INIT
INSERT INTO MyTable VALUES(2)
BACKUP LOG MyDatabase
TO DISK='C:\Backups\MyDatabase_Log1.bak'
WITH INIT
GO
USE master
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log1.bak'
WITH RECOVERY
GO
USE MyDatabase
INSERT INTO MyTable VALUES(3)
BACKUP LOG MyDatabase
TO DISK='C:\Backups\MyDatabase_Log2.bak'
WITH INIT
GO
USE master
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log1.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log2.bak'
WITH RECOVERY
GO
USE MyDatabase
SELECT * FROM MyTable -- 3 rows returned
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Maciej Szymanski" <maciek@.kolobrzeg.com.pl> wrote in message
news:85a5f2.0401170757.4de3ca20@.posting.google.com...
> Hi all!
> I'm thinking of switching my production db to full recovery model.
> Now, I've some questions regarding tlog backups chain. What can
> possibly break chain of transaction log backups?
> And yet another question. Does restore operation break transaction log
> backups chain? I mean, should every restore operation be followed by
> full db backup?
> with regards
> Maciej Szymanski
I'm thinking of switching my production db to full recovery model.
Now, I've some questions regarding tlog backups chain. What can
possibly break chain of transaction log backups?
And yet another question. Does restore operation break transaction log
backups chain? I mean, should every restore operation be followed by
full db backup?
with regards
Maciej Szymanski> What can possibly break chain of transaction log backups?
In the FULL recovery model, truncating the log without creating a log backup
file (BACKUP LOG WITH TRUNCATE_ONLY or NO_LOG) will break the log backup
sequence.
> And yet another question. Does restore operation break transaction log
> backups chain? I mean, should every restore operation be followed by
> full db backup?
The script below illustrates that a backup isn't needed following a restore.
CREATE DATABASE MyDatabase
ALTER DATABASE MyDatabase
SET RECOVERY FULL
GO
USE MyDatabase
CREATE TABLE MyTable(Col1 int NOT NULL)
INSERT INTO MyTable VALUES(1)
BACKUP DATABASE MyDatabase
TO DISK='C:\Backups\MyDatabase.bak'
WITH INIT
INSERT INTO MyTable VALUES(2)
BACKUP LOG MyDatabase
TO DISK='C:\Backups\MyDatabase_Log1.bak'
WITH INIT
GO
USE master
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log1.bak'
WITH RECOVERY
GO
USE MyDatabase
INSERT INTO MyTable VALUES(3)
BACKUP LOG MyDatabase
TO DISK='C:\Backups\MyDatabase_Log2.bak'
WITH INIT
GO
USE master
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log1.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log2.bak'
WITH RECOVERY
GO
USE MyDatabase
SELECT * FROM MyTable -- 3 rows returned
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Maciej Szymanski" <maciek@.kolobrzeg.com.pl> wrote in message
news:85a5f2.0401170757.4de3ca20@.posting.google.com...
> Hi all!
> I'm thinking of switching my production db to full recovery model.
> Now, I've some questions regarding tlog backups chain. What can
> possibly break chain of transaction log backups?
> And yet another question. Does restore operation break transaction log
> backups chain? I mean, should every restore operation be followed by
> full db backup?
> with regards
> Maciej Szymanski
does restore operation break tlog bckup chain?
Hi all!
I'm thinking of switching my production db to full recovery model.
Now, I've some questions regarding tlog backups chain. What can
possibly break chain of transaction log backups?
And yet another question. Does restore operation break transaction log
backups chain? I mean, should every restore operation be followed by
full db backup?
with regards
Maciej Szymanski> What can possibly break chain of transaction log backups?
In the FULL recovery model, truncating the log without creating a log backup
file (BACKUP LOG WITH TRUNCATE_ONLY or NO_LOG) will break the log backup
sequence.
The script below illustrates that a backup isn't needed following a restore.
CREATE DATABASE MyDatabase
ALTER DATABASE MyDatabase
SET RECOVERY FULL
GO
USE MyDatabase
CREATE TABLE MyTable(Col1 int NOT NULL)
INSERT INTO MyTable VALUES(1)
BACKUP DATABASE MyDatabase
TO DISK='C:\Backups\MyDatabase.bak'
WITH INIT
INSERT INTO MyTable VALUES(2)
BACKUP LOG MyDatabase
TO DISK='C:\Backups\MyDatabase_Log1.bak'
WITH INIT
GO
USE master
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log1.bak'
WITH RECOVERY
GO
USE MyDatabase
INSERT INTO MyTable VALUES(3)
BACKUP LOG MyDatabase
TO DISK='C:\Backups\MyDatabase_Log2.bak'
WITH INIT
GO
USE master
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log1.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log2.bak'
WITH RECOVERY
GO
USE MyDatabase
SELECT * FROM MyTable -- 3 rows returned
Hope this helps.
Dan Guzman
SQL Server MVP
"Maciej Szymanski" <maciek@.kolobrzeg.com.pl> wrote in message
news:85a5f2.0401170757.4de3ca20@.posting.google.com...
I'm thinking of switching my production db to full recovery model.
Now, I've some questions regarding tlog backups chain. What can
possibly break chain of transaction log backups?
And yet another question. Does restore operation break transaction log
backups chain? I mean, should every restore operation be followed by
full db backup?
with regards
Maciej Szymanski> What can possibly break chain of transaction log backups?
In the FULL recovery model, truncating the log without creating a log backup
file (BACKUP LOG WITH TRUNCATE_ONLY or NO_LOG) will break the log backup
sequence.
quote:
> And yet another question. Does restore operation break transaction log
> backups chain? I mean, should every restore operation be followed by
> full db backup?
The script below illustrates that a backup isn't needed following a restore.
CREATE DATABASE MyDatabase
ALTER DATABASE MyDatabase
SET RECOVERY FULL
GO
USE MyDatabase
CREATE TABLE MyTable(Col1 int NOT NULL)
INSERT INTO MyTable VALUES(1)
BACKUP DATABASE MyDatabase
TO DISK='C:\Backups\MyDatabase.bak'
WITH INIT
INSERT INTO MyTable VALUES(2)
BACKUP LOG MyDatabase
TO DISK='C:\Backups\MyDatabase_Log1.bak'
WITH INIT
GO
USE master
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log1.bak'
WITH RECOVERY
GO
USE MyDatabase
INSERT INTO MyTable VALUES(3)
BACKUP LOG MyDatabase
TO DISK='C:\Backups\MyDatabase_Log2.bak'
WITH INIT
GO
USE master
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log1.bak'
WITH NORECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\MyDatabase_Log2.bak'
WITH RECOVERY
GO
USE MyDatabase
SELECT * FROM MyTable -- 3 rows returned
Hope this helps.
Dan Guzman
SQL Server MVP
"Maciej Szymanski" <maciek@.kolobrzeg.com.pl> wrote in message
news:85a5f2.0401170757.4de3ca20@.posting.google.com...
quote:
> Hi all!
> I'm thinking of switching my production db to full recovery model.
> Now, I've some questions regarding tlog backups chain. What can
> possibly break chain of transaction log backups?
> And yet another question. Does restore operation break transaction log
> backups chain? I mean, should every restore operation be followed by
> full db backup?
> with regards
> Maciej Szymanski
Friday, February 17, 2012
Document Management - SQL Server 2005
I am researching a server for ProSystem fx Document Management.
System requirements break down the servers 40+ GB drive like this:
c:\ system = 10 GB
d:\ log files = 10 GB
e:\SQL DB = 15 GB
Would c, d and e be one RAID 5 array?
This is my first SQL server, so I'm learning as I go forward.
Thank you for your thoughts!First off 40GB is pretty small these days and doesn't go very far. It's hard
to say what you need without a lot more info. How much and what kind of
activity do you expect? Is it mostly reads or a lot of writes as well? You
generally want to separate your log files from your data files if you have
lots of writes or the transactions are large. But placing them all one
physical Raid drive and separating them into smaller logical drives does
nothing for performance and limits what can fit in any single partition.
--
Andrew J. Kelly SQL MVP
"Gene" <Gene@.discussions.microsoft.com> wrote in message
news:1CCE910E-6419-472C-AA98-00B249AF4579@.microsoft.com...
>I am researching a server for ProSystem fx Document Management.
> System requirements break down the servers 40+ GB drive like this:
> c:\ system = 10 GB
> d:\ log files = 10 GB
> e:\SQL DB = 15 GB
> Would c, d and e be one RAID 5 array?
> This is my first SQL server, so I'm learning as I go forward.
> Thank you for your thoughts!
>
>
>|||Thank you, Andrew,
I really suspected that would be the case. The drive sizes I mentioned were
the minimum recommended sizes for the specific document management program
I'm researching.
Just as a hypothetical, would it be appropriate to mirror the system drive
and put the log and SQL drives each on three or more drives using RAID 5?
In other words:
c:\ system - mirrored (pagefile.sys here, as well)
d:\ log - RAID 5 (three drives+)
e:\ SQL DB - RAID 5 (three drives+)
Or is there a better approach? Obviously, I've never configured a SQL server
before but do want good performance... I'm not sure if I can answer your
read/write question at this point. Let's say 50/50.
I appreciate any direction you can offer.
"Andrew J. Kelly" wrote:
> First off 40GB is pretty small these days and doesn't go very far. It's hard
> to say what you need without a lot more info. How much and what kind of
> activity do you expect? Is it mostly reads or a lot of writes as well? You
> generally want to separate your log files from your data files if you have
> lots of writes or the transactions are large. But placing them all one
> physical Raid drive and separating them into smaller logical drives does
> nothing for performance and limits what can fit in any single partition.
> --
> Andrew J. Kelly SQL MVP
> "Gene" <Gene@.discussions.microsoft.com> wrote in message
> news:1CCE910E-6419-472C-AA98-00B249AF4579@.microsoft.com...
> >I am researching a server for ProSystem fx Document Management.
> >
> > System requirements break down the servers 40+ GB drive like this:
> >
> > c:\ system = 10 GB
> > d:\ log files = 10 GB
> > e:\SQL DB = 15 GB
> >
> > Would c, d and e be one RAID 5 array?
> >
> > This is my first SQL server, so I'm learning as I go forward.
> >
> > Thank you for your thoughts!
> >
> >
> >
> >
> >
>
>|||RAID 5 has a sever penalty for writes and as such is the worst to put Log
files on. Raid 1 or 10 is great for log files. What you go with really
depends a lot on what you are going to do. 50 / 50 is a lot of writes in
relation to reads but if you only do 5 transactions a second it doesn't
matter as much as if it were 500 or 5000 a second. I would go with a Raid 1
for the OS / Swap file and a Raid 1 for the Logs. Then either a 4 disk Raid
5 or a 4 disk Raid 10 if your controller supports it. A Raid 10 will usually
outperform a comparable Raid 5 for heavy write operations.
--
Andrew J. Kelly SQL MVP
"Gene" <Gene@.discussions.microsoft.com> wrote in message
news:8ED269A3-AAF6-4712-85D6-15A5B62C5DCB@.microsoft.com...
> Thank you, Andrew,
> I really suspected that would be the case. The drive sizes I mentioned
> were
> the minimum recommended sizes for the specific document management program
> I'm researching.
> Just as a hypothetical, would it be appropriate to mirror the system drive
> and put the log and SQL drives each on three or more drives using RAID 5?
> In other words:
> c:\ system - mirrored (pagefile.sys here, as well)
> d:\ log - RAID 5 (three drives+)
> e:\ SQL DB - RAID 5 (three drives+)
> Or is there a better approach? Obviously, I've never configured a SQL
> server
> before but do want good performance... I'm not sure if I can answer your
> read/write question at this point. Let's say 50/50.
> I appreciate any direction you can offer.
>
> "Andrew J. Kelly" wrote:
>> First off 40GB is pretty small these days and doesn't go very far. It's
>> hard
>> to say what you need without a lot more info. How much and what kind of
>> activity do you expect? Is it mostly reads or a lot of writes as well?
>> You
>> generally want to separate your log files from your data files if you
>> have
>> lots of writes or the transactions are large. But placing them all one
>> physical Raid drive and separating them into smaller logical drives does
>> nothing for performance and limits what can fit in any single partition.
>> --
>> Andrew J. Kelly SQL MVP
>> "Gene" <Gene@.discussions.microsoft.com> wrote in message
>> news:1CCE910E-6419-472C-AA98-00B249AF4579@.microsoft.com...
>> >I am researching a server for ProSystem fx Document Management.
>> >
>> > System requirements break down the servers 40+ GB drive like this:
>> >
>> > c:\ system = 10 GB
>> > d:\ log files = 10 GB
>> > e:\SQL DB = 15 GB
>> >
>> > Would c, d and e be one RAID 5 array?
>> >
>> > This is my first SQL server, so I'm learning as I go forward.
>> >
>> > Thank you for your thoughts!
>> >
>> >
>> >
>> >
>> >
>>|||Thanks a bunch Andrew. That's very helpful and exactly what I need to know.
"Andrew J. Kelly" wrote:
> RAID 5 has a sever penalty for writes and as such is the worst to put Log
> files on. Raid 1 or 10 is great for log files. What you go with really
> depends a lot on what you are going to do. 50 / 50 is a lot of writes in
> relation to reads but if you only do 5 transactions a second it doesn't
> matter as much as if it were 500 or 5000 a second. I would go with a Raid 1
> for the OS / Swap file and a Raid 1 for the Logs. Then either a 4 disk Raid
> 5 or a 4 disk Raid 10 if your controller supports it. A Raid 10 will usually
> outperform a comparable Raid 5 for heavy write operations.
> --
> Andrew J. Kelly SQL MVP
> "Gene" <Gene@.discussions.microsoft.com> wrote in message
> news:8ED269A3-AAF6-4712-85D6-15A5B62C5DCB@.microsoft.com...
> > Thank you, Andrew,
> >
> > I really suspected that would be the case. The drive sizes I mentioned
> > were
> > the minimum recommended sizes for the specific document management program
> > I'm researching.
> >
> > Just as a hypothetical, would it be appropriate to mirror the system drive
> > and put the log and SQL drives each on three or more drives using RAID 5?
> >
> > In other words:
> >
> > c:\ system - mirrored (pagefile.sys here, as well)
> > d:\ log - RAID 5 (three drives+)
> > e:\ SQL DB - RAID 5 (three drives+)
> >
> > Or is there a better approach? Obviously, I've never configured a SQL
> > server
> > before but do want good performance... I'm not sure if I can answer your
> > read/write question at this point. Let's say 50/50.
> >
> > I appreciate any direction you can offer.
> >
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> First off 40GB is pretty small these days and doesn't go very far. It's
> >> hard
> >> to say what you need without a lot more info. How much and what kind of
> >> activity do you expect? Is it mostly reads or a lot of writes as well?
> >> You
> >> generally want to separate your log files from your data files if you
> >> have
> >> lots of writes or the transactions are large. But placing them all one
> >> physical Raid drive and separating them into smaller logical drives does
> >> nothing for performance and limits what can fit in any single partition.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >> "Gene" <Gene@.discussions.microsoft.com> wrote in message
> >> news:1CCE910E-6419-472C-AA98-00B249AF4579@.microsoft.com...
> >> >I am researching a server for ProSystem fx Document Management.
> >> >
> >> > System requirements break down the servers 40+ GB drive like this:
> >> >
> >> > c:\ system = 10 GB
> >> > d:\ log files = 10 GB
> >> > e:\SQL DB = 15 GB
> >> >
> >> > Would c, d and e be one RAID 5 array?
> >> >
> >> > This is my first SQL server, so I'm learning as I go forward.
> >> >
> >> > Thank you for your thoughts!
> >> >
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>
System requirements break down the servers 40+ GB drive like this:
c:\ system = 10 GB
d:\ log files = 10 GB
e:\SQL DB = 15 GB
Would c, d and e be one RAID 5 array?
This is my first SQL server, so I'm learning as I go forward.
Thank you for your thoughts!First off 40GB is pretty small these days and doesn't go very far. It's hard
to say what you need without a lot more info. How much and what kind of
activity do you expect? Is it mostly reads or a lot of writes as well? You
generally want to separate your log files from your data files if you have
lots of writes or the transactions are large. But placing them all one
physical Raid drive and separating them into smaller logical drives does
nothing for performance and limits what can fit in any single partition.
--
Andrew J. Kelly SQL MVP
"Gene" <Gene@.discussions.microsoft.com> wrote in message
news:1CCE910E-6419-472C-AA98-00B249AF4579@.microsoft.com...
>I am researching a server for ProSystem fx Document Management.
> System requirements break down the servers 40+ GB drive like this:
> c:\ system = 10 GB
> d:\ log files = 10 GB
> e:\SQL DB = 15 GB
> Would c, d and e be one RAID 5 array?
> This is my first SQL server, so I'm learning as I go forward.
> Thank you for your thoughts!
>
>
>|||Thank you, Andrew,
I really suspected that would be the case. The drive sizes I mentioned were
the minimum recommended sizes for the specific document management program
I'm researching.
Just as a hypothetical, would it be appropriate to mirror the system drive
and put the log and SQL drives each on three or more drives using RAID 5?
In other words:
c:\ system - mirrored (pagefile.sys here, as well)
d:\ log - RAID 5 (three drives+)
e:\ SQL DB - RAID 5 (three drives+)
Or is there a better approach? Obviously, I've never configured a SQL server
before but do want good performance... I'm not sure if I can answer your
read/write question at this point. Let's say 50/50.
I appreciate any direction you can offer.
"Andrew J. Kelly" wrote:
> First off 40GB is pretty small these days and doesn't go very far. It's hard
> to say what you need without a lot more info. How much and what kind of
> activity do you expect? Is it mostly reads or a lot of writes as well? You
> generally want to separate your log files from your data files if you have
> lots of writes or the transactions are large. But placing them all one
> physical Raid drive and separating them into smaller logical drives does
> nothing for performance and limits what can fit in any single partition.
> --
> Andrew J. Kelly SQL MVP
> "Gene" <Gene@.discussions.microsoft.com> wrote in message
> news:1CCE910E-6419-472C-AA98-00B249AF4579@.microsoft.com...
> >I am researching a server for ProSystem fx Document Management.
> >
> > System requirements break down the servers 40+ GB drive like this:
> >
> > c:\ system = 10 GB
> > d:\ log files = 10 GB
> > e:\SQL DB = 15 GB
> >
> > Would c, d and e be one RAID 5 array?
> >
> > This is my first SQL server, so I'm learning as I go forward.
> >
> > Thank you for your thoughts!
> >
> >
> >
> >
> >
>
>|||RAID 5 has a sever penalty for writes and as such is the worst to put Log
files on. Raid 1 or 10 is great for log files. What you go with really
depends a lot on what you are going to do. 50 / 50 is a lot of writes in
relation to reads but if you only do 5 transactions a second it doesn't
matter as much as if it were 500 or 5000 a second. I would go with a Raid 1
for the OS / Swap file and a Raid 1 for the Logs. Then either a 4 disk Raid
5 or a 4 disk Raid 10 if your controller supports it. A Raid 10 will usually
outperform a comparable Raid 5 for heavy write operations.
--
Andrew J. Kelly SQL MVP
"Gene" <Gene@.discussions.microsoft.com> wrote in message
news:8ED269A3-AAF6-4712-85D6-15A5B62C5DCB@.microsoft.com...
> Thank you, Andrew,
> I really suspected that would be the case. The drive sizes I mentioned
> were
> the minimum recommended sizes for the specific document management program
> I'm researching.
> Just as a hypothetical, would it be appropriate to mirror the system drive
> and put the log and SQL drives each on three or more drives using RAID 5?
> In other words:
> c:\ system - mirrored (pagefile.sys here, as well)
> d:\ log - RAID 5 (three drives+)
> e:\ SQL DB - RAID 5 (three drives+)
> Or is there a better approach? Obviously, I've never configured a SQL
> server
> before but do want good performance... I'm not sure if I can answer your
> read/write question at this point. Let's say 50/50.
> I appreciate any direction you can offer.
>
> "Andrew J. Kelly" wrote:
>> First off 40GB is pretty small these days and doesn't go very far. It's
>> hard
>> to say what you need without a lot more info. How much and what kind of
>> activity do you expect? Is it mostly reads or a lot of writes as well?
>> You
>> generally want to separate your log files from your data files if you
>> have
>> lots of writes or the transactions are large. But placing them all one
>> physical Raid drive and separating them into smaller logical drives does
>> nothing for performance and limits what can fit in any single partition.
>> --
>> Andrew J. Kelly SQL MVP
>> "Gene" <Gene@.discussions.microsoft.com> wrote in message
>> news:1CCE910E-6419-472C-AA98-00B249AF4579@.microsoft.com...
>> >I am researching a server for ProSystem fx Document Management.
>> >
>> > System requirements break down the servers 40+ GB drive like this:
>> >
>> > c:\ system = 10 GB
>> > d:\ log files = 10 GB
>> > e:\SQL DB = 15 GB
>> >
>> > Would c, d and e be one RAID 5 array?
>> >
>> > This is my first SQL server, so I'm learning as I go forward.
>> >
>> > Thank you for your thoughts!
>> >
>> >
>> >
>> >
>> >
>>|||Thanks a bunch Andrew. That's very helpful and exactly what I need to know.
"Andrew J. Kelly" wrote:
> RAID 5 has a sever penalty for writes and as such is the worst to put Log
> files on. Raid 1 or 10 is great for log files. What you go with really
> depends a lot on what you are going to do. 50 / 50 is a lot of writes in
> relation to reads but if you only do 5 transactions a second it doesn't
> matter as much as if it were 500 or 5000 a second. I would go with a Raid 1
> for the OS / Swap file and a Raid 1 for the Logs. Then either a 4 disk Raid
> 5 or a 4 disk Raid 10 if your controller supports it. A Raid 10 will usually
> outperform a comparable Raid 5 for heavy write operations.
> --
> Andrew J. Kelly SQL MVP
> "Gene" <Gene@.discussions.microsoft.com> wrote in message
> news:8ED269A3-AAF6-4712-85D6-15A5B62C5DCB@.microsoft.com...
> > Thank you, Andrew,
> >
> > I really suspected that would be the case. The drive sizes I mentioned
> > were
> > the minimum recommended sizes for the specific document management program
> > I'm researching.
> >
> > Just as a hypothetical, would it be appropriate to mirror the system drive
> > and put the log and SQL drives each on three or more drives using RAID 5?
> >
> > In other words:
> >
> > c:\ system - mirrored (pagefile.sys here, as well)
> > d:\ log - RAID 5 (three drives+)
> > e:\ SQL DB - RAID 5 (three drives+)
> >
> > Or is there a better approach? Obviously, I've never configured a SQL
> > server
> > before but do want good performance... I'm not sure if I can answer your
> > read/write question at this point. Let's say 50/50.
> >
> > I appreciate any direction you can offer.
> >
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> First off 40GB is pretty small these days and doesn't go very far. It's
> >> hard
> >> to say what you need without a lot more info. How much and what kind of
> >> activity do you expect? Is it mostly reads or a lot of writes as well?
> >> You
> >> generally want to separate your log files from your data files if you
> >> have
> >> lots of writes or the transactions are large. But placing them all one
> >> physical Raid drive and separating them into smaller logical drives does
> >> nothing for performance and limits what can fit in any single partition.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >> "Gene" <Gene@.discussions.microsoft.com> wrote in message
> >> news:1CCE910E-6419-472C-AA98-00B249AF4579@.microsoft.com...
> >> >I am researching a server for ProSystem fx Document Management.
> >> >
> >> > System requirements break down the servers 40+ GB drive like this:
> >> >
> >> > c:\ system = 10 GB
> >> > d:\ log files = 10 GB
> >> > e:\SQL DB = 15 GB
> >> >
> >> > Would c, d and e be one RAID 5 array?
> >> >
> >> > This is my first SQL server, so I'm learning as I go forward.
> >> >
> >> > Thank you for your thoughts!
> >> >
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>
Document Management - SQL Server 2005
I am researching a server for ProSystem fx Document Management.
System requirements break down the servers 40+ GB drive like this:
c:\ system = 10 GB
d:\ log files = 10 GB
e:\SQL DB = 15 GB
Would c, d and e be one RAID 5 array?
This is my first SQL server, so I'm learning as I go forward.
Thank you for your thoughts!First off 40GB is pretty small these days and doesn't go very far. It's hard
to say what you need without a lot more info. How much and what kind of
activity do you expect? Is it mostly reads or a lot of writes as well? You
generally want to separate your log files from your data files if you have
lots of writes or the transactions are large. But placing them all one
physical Raid drive and separating them into smaller logical drives does
nothing for performance and limits what can fit in any single partition.
Andrew J. Kelly SQL MVP
"Gene" <Gene@.discussions.microsoft.com> wrote in message
news:1CCE910E-6419-472C-AA98-00B249AF4579@.microsoft.com...
>I am researching a server for ProSystem fx Document Management.
> System requirements break down the servers 40+ GB drive like this:
> c:\ system = 10 GB
> d:\ log files = 10 GB
> e:\SQL DB = 15 GB
> Would c, d and e be one RAID 5 array?
> This is my first SQL server, so I'm learning as I go forward.
> Thank you for your thoughts!
>
>
>|||Thank you, Andrew,
I really suspected that would be the case. The drive sizes I mentioned were
the minimum recommended sizes for the specific document management program
I'm researching.
Just as a hypothetical, would it be appropriate to mirror the system drive
and put the log and SQL drives each on three or more drives using RAID 5?
In other words:
c:\ system - mirrored (pagefile.sys here, as well)
d:\ log - RAID 5 (three drives+)
e:\ SQL DB - RAID 5 (three drives+)
Or is there a better approach? Obviously, I've never configured a SQL server
before but do want good performance... I'm not sure if I can answer your
read/write question at this point. Let's say 50/50.
I appreciate any direction you can offer.
"Andrew J. Kelly" wrote:
> First off 40GB is pretty small these days and doesn't go very far. It's ha
rd
> to say what you need without a lot more info. How much and what kind of
> activity do you expect? Is it mostly reads or a lot of writes as well? Yo
u
> generally want to separate your log files from your data files if you have
> lots of writes or the transactions are large. But placing them all one
> physical Raid drive and separating them into smaller logical drives does
> nothing for performance and limits what can fit in any single partition.
> --
> Andrew J. Kelly SQL MVP
> "Gene" <Gene@.discussions.microsoft.com> wrote in message
> news:1CCE910E-6419-472C-AA98-00B249AF4579@.microsoft.com...
>
>|||RAID 5 has a sever penalty for writes and as such is the worst to put Log
files on. Raid 1 or 10 is great for log files. What you go with really
depends a lot on what you are going to do. 50 / 50 is a lot of writes in
relation to reads but if you only do 5 transactions a second it doesn't
matter as much as if it were 500 or 5000 a second. I would go with a Raid 1
for the OS / Swap file and a Raid 1 for the Logs. Then either a 4 disk Raid
5 or a 4 disk Raid 10 if your controller supports it. A Raid 10 will usually
outperform a comparable Raid 5 for heavy write operations.
Andrew J. Kelly SQL MVP
"Gene" <Gene@.discussions.microsoft.com> wrote in message
news:8ED269A3-AAF6-4712-85D6-15A5B62C5DCB@.microsoft.com...[vbcol=seagreen]
> Thank you, Andrew,
> I really suspected that would be the case. The drive sizes I mentioned
> were
> the minimum recommended sizes for the specific document management program
> I'm researching.
> Just as a hypothetical, would it be appropriate to mirror the system drive
> and put the log and SQL drives each on three or more drives using RAID 5?
> In other words:
> c:\ system - mirrored (pagefile.sys here, as well)
> d:\ log - RAID 5 (three drives+)
> e:\ SQL DB - RAID 5 (three drives+)
> Or is there a better approach? Obviously, I've never configured a SQL
> server
> before but do want good performance... I'm not sure if I can answer your
> read/write question at this point. Let's say 50/50.
> I appreciate any direction you can offer.
>
> "Andrew J. Kelly" wrote:
>|||Thanks a bunch Andrew. That's very helpful and exactly what I need to know.
"Andrew J. Kelly" wrote:
> RAID 5 has a sever penalty for writes and as such is the worst to put Log
> files on. Raid 1 or 10 is great for log files. What you go with really
> depends a lot on what you are going to do. 50 / 50 is a lot of writes in
> relation to reads but if you only do 5 transactions a second it doesn't
> matter as much as if it were 500 or 5000 a second. I would go with a Raid
1
> for the OS / Swap file and a Raid 1 for the Logs. Then either a 4 disk Rai
d
> 5 or a 4 disk Raid 10 if your controller supports it. A Raid 10 will usual
ly
> outperform a comparable Raid 5 for heavy write operations.
> --
> Andrew J. Kelly SQL MVP
> "Gene" <Gene@.discussions.microsoft.com> wrote in message
> news:8ED269A3-AAF6-4712-85D6-15A5B62C5DCB@.microsoft.com...
>
>
System requirements break down the servers 40+ GB drive like this:
c:\ system = 10 GB
d:\ log files = 10 GB
e:\SQL DB = 15 GB
Would c, d and e be one RAID 5 array?
This is my first SQL server, so I'm learning as I go forward.
Thank you for your thoughts!First off 40GB is pretty small these days and doesn't go very far. It's hard
to say what you need without a lot more info. How much and what kind of
activity do you expect? Is it mostly reads or a lot of writes as well? You
generally want to separate your log files from your data files if you have
lots of writes or the transactions are large. But placing them all one
physical Raid drive and separating them into smaller logical drives does
nothing for performance and limits what can fit in any single partition.
Andrew J. Kelly SQL MVP
"Gene" <Gene@.discussions.microsoft.com> wrote in message
news:1CCE910E-6419-472C-AA98-00B249AF4579@.microsoft.com...
>I am researching a server for ProSystem fx Document Management.
> System requirements break down the servers 40+ GB drive like this:
> c:\ system = 10 GB
> d:\ log files = 10 GB
> e:\SQL DB = 15 GB
> Would c, d and e be one RAID 5 array?
> This is my first SQL server, so I'm learning as I go forward.
> Thank you for your thoughts!
>
>
>|||Thank you, Andrew,
I really suspected that would be the case. The drive sizes I mentioned were
the minimum recommended sizes for the specific document management program
I'm researching.
Just as a hypothetical, would it be appropriate to mirror the system drive
and put the log and SQL drives each on three or more drives using RAID 5?
In other words:
c:\ system - mirrored (pagefile.sys here, as well)
d:\ log - RAID 5 (three drives+)
e:\ SQL DB - RAID 5 (three drives+)
Or is there a better approach? Obviously, I've never configured a SQL server
before but do want good performance... I'm not sure if I can answer your
read/write question at this point. Let's say 50/50.
I appreciate any direction you can offer.
"Andrew J. Kelly" wrote:
> First off 40GB is pretty small these days and doesn't go very far. It's ha
rd
> to say what you need without a lot more info. How much and what kind of
> activity do you expect? Is it mostly reads or a lot of writes as well? Yo
u
> generally want to separate your log files from your data files if you have
> lots of writes or the transactions are large. But placing them all one
> physical Raid drive and separating them into smaller logical drives does
> nothing for performance and limits what can fit in any single partition.
> --
> Andrew J. Kelly SQL MVP
> "Gene" <Gene@.discussions.microsoft.com> wrote in message
> news:1CCE910E-6419-472C-AA98-00B249AF4579@.microsoft.com...
>
>|||RAID 5 has a sever penalty for writes and as such is the worst to put Log
files on. Raid 1 or 10 is great for log files. What you go with really
depends a lot on what you are going to do. 50 / 50 is a lot of writes in
relation to reads but if you only do 5 transactions a second it doesn't
matter as much as if it were 500 or 5000 a second. I would go with a Raid 1
for the OS / Swap file and a Raid 1 for the Logs. Then either a 4 disk Raid
5 or a 4 disk Raid 10 if your controller supports it. A Raid 10 will usually
outperform a comparable Raid 5 for heavy write operations.
Andrew J. Kelly SQL MVP
"Gene" <Gene@.discussions.microsoft.com> wrote in message
news:8ED269A3-AAF6-4712-85D6-15A5B62C5DCB@.microsoft.com...[vbcol=seagreen]
> Thank you, Andrew,
> I really suspected that would be the case. The drive sizes I mentioned
> were
> the minimum recommended sizes for the specific document management program
> I'm researching.
> Just as a hypothetical, would it be appropriate to mirror the system drive
> and put the log and SQL drives each on three or more drives using RAID 5?
> In other words:
> c:\ system - mirrored (pagefile.sys here, as well)
> d:\ log - RAID 5 (three drives+)
> e:\ SQL DB - RAID 5 (three drives+)
> Or is there a better approach? Obviously, I've never configured a SQL
> server
> before but do want good performance... I'm not sure if I can answer your
> read/write question at this point. Let's say 50/50.
> I appreciate any direction you can offer.
>
> "Andrew J. Kelly" wrote:
>|||Thanks a bunch Andrew. That's very helpful and exactly what I need to know.
"Andrew J. Kelly" wrote:
> RAID 5 has a sever penalty for writes and as such is the worst to put Log
> files on. Raid 1 or 10 is great for log files. What you go with really
> depends a lot on what you are going to do. 50 / 50 is a lot of writes in
> relation to reads but if you only do 5 transactions a second it doesn't
> matter as much as if it were 500 or 5000 a second. I would go with a Raid
1
> for the OS / Swap file and a Raid 1 for the Logs. Then either a 4 disk Rai
d
> 5 or a 4 disk Raid 10 if your controller supports it. A Raid 10 will usual
ly
> outperform a comparable Raid 5 for heavy write operations.
> --
> Andrew J. Kelly SQL MVP
> "Gene" <Gene@.discussions.microsoft.com> wrote in message
> news:8ED269A3-AAF6-4712-85D6-15A5B62C5DCB@.microsoft.com...
>
>
Tuesday, February 14, 2012
Do you like to break the rules ?
Not long ago I accountered this situation: I had two databases on "MS SQL Server". In one of the databases there was a nomenclature with very large primary key.
I had to transport that nomenclature and transform the wide PK into single identity column into the other database.
I decided to use a function for that transformation. BUT that function had to mark somewhere which combination of the PK columns is relative to which identity value. BUT functions CAN'T WRITE under MSSQL.
So I took the challenge and mine all sources of information. The result was a function "Exec4Fun" that breaks the rule.
I suppose that with this function it's possible to avoid the restriction for triggers, which prevents writing in the triggering table? (not tested yet)
If someone needs such tools, just write back your e-mail and I'll send some code.
All the best and have fun :)Oh yeah? Wow!!! Let's see if it's a copy of what Itzik Ben Gan talks about in his "back doors" to UDF's. And here's the code from his article (http://www.winnetmag.com/Files/09/41845/41845.zip)|||My Fun has the same origins as "Listing_04.UsingOPENQUERY()toPerformanUpdate.txt". There was used the linked-server approach which lies on ADODB. But I got deeper. I use directly ADODB through "sp_OACreate" procedures and also handle the errors.
If you like tricky code I can send you how to roll a cursor on an EXEC('')
Have a nice code :)|||There's no trick there, I've seen too much of that, and re-written most of it. I am glad I am out of that dirty business ;)|||That's it - sysadmins . . .
You all forgot the Mother ASseMbler (MASM) and how HARD is the planting for the SOFT to be neet and tidy ;)
All the best. Thanks for the company.
I had to transport that nomenclature and transform the wide PK into single identity column into the other database.
I decided to use a function for that transformation. BUT that function had to mark somewhere which combination of the PK columns is relative to which identity value. BUT functions CAN'T WRITE under MSSQL.
So I took the challenge and mine all sources of information. The result was a function "Exec4Fun" that breaks the rule.
I suppose that with this function it's possible to avoid the restriction for triggers, which prevents writing in the triggering table? (not tested yet)
If someone needs such tools, just write back your e-mail and I'll send some code.
All the best and have fun :)Oh yeah? Wow!!! Let's see if it's a copy of what Itzik Ben Gan talks about in his "back doors" to UDF's. And here's the code from his article (http://www.winnetmag.com/Files/09/41845/41845.zip)|||My Fun has the same origins as "Listing_04.UsingOPENQUERY()toPerformanUpdate.txt". There was used the linked-server approach which lies on ADODB. But I got deeper. I use directly ADODB through "sp_OACreate" procedures and also handle the errors.
If you like tricky code I can send you how to roll a cursor on an EXEC('')
Have a nice code :)|||There's no trick there, I've seen too much of that, and re-written most of it. I am glad I am out of that dirty business ;)|||That's it - sysadmins . . .
You all forgot the Mother ASseMbler (MASM) and how HARD is the planting for the SOFT to be neet and tidy ;)
All the best. Thanks for the company.
Subscribe to:
Posts (Atom)