Hi Guys,

This is my first foray into SQL Server. We are using SQL server. At first
an Access Application will be used through ODBC and then we will be building
a .NET web interface to act as the frontend. It will be run on our intranet.
First I would like to ask for any inside advice on Setup, and gotchas that
have created issues. Maybe a checklist to walk through.

Second, We have a Live server and Testbed server. What process would I use
to transfer the live data to the testbed server. I have done some reading and
I think the detach and attach commands would accomplish this.

Third, what is the best backup setup. I.E where the backup logs are stored
etc.

Any advice is greatly appriceated. Thanks.

-Rick

I'll assume SQL 2000...see responses inline below...

--
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm




"Rick Vooys" <RickVooys@discussions.microsoft.com> wrote in message
news:0428899F-D1F3-4E22-9371-83DEB00686A5@microsoft.com...
> Hi Guys,
>
> This is my first foray into SQL Server. We are using SQL server. At first
> an Access Application will be used through ODBC and then we will be
> building
> a .NET web interface to act as the frontend. It will be run on our
> intranet.
> First I would like to ask for any inside advice on Setup, and gotchas that
> have created issues. Maybe a checklist to walk through.

SP4. or higher.
Data and Transaction log files on a different spindle (or set of spindles).
Backup files on a different set of spindles as well. Note that I don't say
partitions here, since if the raid array crashes, all partitions on it are
toast

>
> Second, We have a Live server and Testbed server. What process would I use
> to transfer the live data to the testbed server. I have done some reading
> and
> I think the detach and attach commands would accomplish this.

If you can take the database offline for the length of time it takes to
copy the file, this works fine. If not, use backup and restore. If the db
will be large use SQL Backup (RedGate) or LiteSpeed (Quest)


> Third, what is the best backup setup. I.E where the backup logs are stored
> etc.

Anywhere on a 'local' drive (local to the server, or SAN). Then take them
to tape or fileserver daily. NOT on the same drive as the database file
(.mdf/.ldf)

>
> Any advice is greatly appriceated. Thanks.

You're welcome.

>
> -Rick
Kevin3NF

Thanks! It is SQL Server 2005 ... does that change any answers?

You wrote: If you can take the database offline for the length of time it
takes to
copy the file, this works fine. If not, use backup and restore. If the db
will be large use SQL Backup (RedGate) or LiteSpeed (Quest)

Pardon my ignorance, but you are saying there are files we can just copy
from one server to another. In out current system, we copy the Access mdb we
use as our backends from the Live server to the Test Server once a week when
all the users log off. This will be the same? Which files do I need to copy?

You wrote: Anywhere on a 'local' drive (local to the server, or SAN). Then
take them to tape or fileserver daily. NOT on the same drive as the database
file (.mdf/.ldf)

I believe that my network guy is going to set up the server with one
partition for the data, one for the logs and one for the backup. This will
work correct? He was asking me what RAID level works best? Any preference?

"Kevin3NF" wrote:

> I'll assume SQL 2000...see responses inline below...
>
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
>
>
>
> "Rick Vooys" <RickVooys@discussions.microsoft.com> wrote in message
> news:0428899F-D1F3-4E22-9371-83DEB00686A5@microsoft.com...
> > Hi Guys,
> >
> > This is my first foray into SQL Server. We are using SQL server. At first
> > an Access Application will be used through ODBC and then we will be
> > building
> > a .NET web interface to act as the frontend. It will be run on our
> > intranet.
> > First I would like to ask for any inside advice on Setup, and gotchas that
> > have created issues. Maybe a checklist to walk through.
>
> SP4. or higher.
> Data and Transaction log files on a different spindle (or set of spindles).
> Backup files on a different set of spindles as well. Note that I don't say
> partitions here, since if the raid array crashes, all partitions on it are
> toast
>
> >
> > Second, We have a Live server and Testbed server. What process would I use
> > to transfer the live data to the testbed server. I have done some reading
> > and
> > I think the detach and attach commands would accomplish this.
>
> If you can take the database offline for the length of time it takes to
> copy the file, this works fine. If not, use backup and restore. If the db
> will be large use SQL Backup (RedGate) or LiteSpeed (Quest)
>
>
> > Third, what is the best backup setup. I.E where the backup logs are stored
> > etc.
>
> Anywhere on a 'local' drive (local to the server, or SAN). Then take them
> to tape or fileserver daily. NOT on the same drive as the database file
> (.mdf/.ldf)
>
> >
> > Any advice is greatly appriceated. Thanks.
>
> You're welcome.
>
> >
> > -Rick
> Kevin3NF
>
>
>

A SQL Server database in its simplest form is 2 files: .mdf (data), .ldf
(transaction log). You can go through a process called Detach from the main
server, and Attach it to the Live Server.

Backup and restore is better, since you do not have to take the database
offline.

Regarding Partitions vs. Drives: 2 partitions on the same drive (or RAID
set) is no different than one partition. The key thing I am pointing out is
to have the I/O operations of Data and Logs on different physical spindles.
Backups need to be separate from data so if you lose the data drive, you
haven't also lost the most recent backup file.

Raid 5 is fine for the data files. Raid 1 for the O/S, SQL Server program
files (typically the C drive). Haven't really formed an opinion on raid for
log files :-)

--
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm




"Rick Vooys" <RickVooys@discussions.microsoft.com> wrote in message
news2D13958-0304-4C0B-84B1-16DA8AE0D7E4@microsoft.com...
> Thanks! It is SQL Server 2005 ... does that change any answers?
>
> You wrote: If you can take the database offline for the length of time it
> takes to
> copy the file, this works fine. If not, use backup and restore. If the
> db
> will be large use SQL Backup (RedGate) or LiteSpeed (Quest)
>
> Pardon my ignorance, but you are saying there are files we can just copy
> from one server to another. In out current system, we copy the Access mdb
> we
> use as our backends from the Live server to the Test Server once a week
> when
> all the users log off. This will be the same? Which files do I need to
> copy?
>
> You wrote: Anywhere on a 'local' drive (local to the server, or SAN).
> Then
> take them to tape or fileserver daily. NOT on the same drive as the
> database
> file (.mdf/.ldf)
>
> I believe that my network guy is going to set up the server with one
> partition for the data, one for the logs and one for the backup. This will
> work correct? He was asking me what RAID level works best? Any preference?
>
> "Kevin3NF" wrote:
>
>> I'll assume SQL 2000...see responses inline below...
>>
>> --
>> Kevin Hill
>> 3NF Consulting
>> www.3nf-inc.com/NewsGroups.htm
>>
>>
>>
>>
>> "Rick Vooys" <RickVooys@discussions.microsoft.com> wrote in message
>> news:0428899F-D1F3-4E22-9371-83DEB00686A5@microsoft.com...
>> > Hi Guys,
>> >
>> > This is my first foray into SQL Server. We are using SQL server. At
>> > first
>> > an Access Application will be used through ODBC and then we will be
>> > building
>> > a .NET web interface to act as the frontend. It will be run on our
>> > intranet.
>> > First I would like to ask for any inside advice on Setup, and gotchas
>> > that
>> > have created issues. Maybe a checklist to walk through.
>>
>> SP4. or higher.
>> Data and Transaction log files on a different spindle (or set of
>> spindles).
>> Backup files on a different set of spindles as well. Note that I don't
>> say
>> partitions here, since if the raid array crashes, all partitions on it
>> are
>> toast
>>
>> >
>> > Second, We have a Live server and Testbed server. What process would I
>> > use
>> > to transfer the live data to the testbed server. I have done some
>> > reading
>> > and
>> > I think the detach and attach commands would accomplish this.
>>
>> If you can take the database offline for the length of time it takes to
>> copy the file, this works fine. If not, use backup and restore. If the
>> db
>> will be large use SQL Backup (RedGate) or LiteSpeed (Quest)
>>
>>
>> > Third, what is the best backup setup. I.E where the backup logs are
>> > stored
>> > etc.
>>
>> Anywhere on a 'local' drive (local to the server, or SAN). Then take
>> them
>> to tape or fileserver daily. NOT on the same drive as the database file
>> (.mdf/.ldf)
>>
>> >
>> > Any advice is greatly appriceated. Thanks.
>>
>> You're welcome.
>>
>> >
>> > -Rick
>> Kevin3NF
>>
>>
>>

Oh...no idea if 2005 is different. I doubt it, as these are basic questions
that are not likely to have undergone major re-writes in 2005.

--
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm




"Rick Vooys" <RickVooys@discussions.microsoft.com> wrote in message
news2D13958-0304-4C0B-84B1-16DA8AE0D7E4@microsoft.com...
> Thanks! It is SQL Server 2005 ... does that change any answers?
>
> You wrote: If you can take the database offline for the length of time it
> takes to
> copy the file, this works fine. If not, use backup and restore. If the
> db
> will be large use SQL Backup (RedGate) or LiteSpeed (Quest)
>
> Pardon my ignorance, but you are saying there are files we can just copy
> from one server to another. In out current system, we copy the Access mdb
> we
> use as our backends from the Live server to the Test Server once a week
> when
> all the users log off. This will be the same? Which files do I need to
> copy?
>
> You wrote: Anywhere on a 'local' drive (local to the server, or SAN).
> Then
> take them to tape or fileserver daily. NOT on the same drive as the
> database
> file (.mdf/.ldf)
>
> I believe that my network guy is going to set up the server with one
> partition for the data, one for the logs and one for the backup. This will
> work correct? He was asking me what RAID level works best? Any preference?
>
> "Kevin3NF" wrote:
>
>> I'll assume SQL 2000...see responses inline below...
>>
>> --
>> Kevin Hill
>> 3NF Consulting
>> www.3nf-inc.com/NewsGroups.htm
>>
>>
>>
>>
>> "Rick Vooys" <RickVooys@discussions.microsoft.com> wrote in message
>> news:0428899F-D1F3-4E22-9371-83DEB00686A5@microsoft.com...
>> > Hi Guys,
>> >
>> > This is my first foray into SQL Server. We are using SQL server. At
>> > first
>> > an Access Application will be used through ODBC and then we will be
>> > building
>> > a .NET web interface to act as the frontend. It will be run on our
>> > intranet.
>> > First I would like to ask for any inside advice on Setup, and gotchas
>> > that
>> > have created issues. Maybe a checklist to walk through.
>>
>> SP4. or higher.
>> Data and Transaction log files on a different spindle (or set of
>> spindles).
>> Backup files on a different set of spindles as well. Note that I don't
>> say
>> partitions here, since if the raid array crashes, all partitions on it
>> are
>> toast
>>
>> >
>> > Second, We have a Live server and Testbed server. What process would I
>> > use
>> > to transfer the live data to the testbed server. I have done some
>> > reading
>> > and
>> > I think the detach and attach commands would accomplish this.
>>
>> If you can take the database offline for the length of time it takes to
>> copy the file, this works fine. If not, use backup and restore. If the
>> db
>> will be large use SQL Backup (RedGate) or LiteSpeed (Quest)
>>
>>
>> > Third, what is the best backup setup. I.E where the backup logs are
>> > stored
>> > etc.
>>
>> Anywhere on a 'local' drive (local to the server, or SAN). Then take
>> them
>> to tape or fileserver daily. NOT on the same drive as the database file
>> (.mdf/.ldf)
>>
>> >
>> > Any advice is greatly appriceated. Thanks.
>>
>> You're welcome.
>>
>> >
>> > -Rick
>> Kevin3NF
>>
>>
>>

Thanks for the infor Kevin, seems much clearer to me. My other concern right
now is setting up the secruity for the server.

Do you have any preference on Windows vs SQL Authentication? Any obvious
gotchas I might miss as a newbie?

"Kevin3NF" wrote:

> Oh...no idea if 2005 is different. I doubt it, as these are basic questions
> that are not likely to have undergone major re-writes in 2005.
>
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
>
>
>
> "Rick Vooys" <RickVooys@discussions.microsoft.com> wrote in message
> news2D13958-0304-4C0B-84B1-16DA8AE0D7E4@microsoft.com...
> > Thanks! It is SQL Server 2005 ... does that change any answers?
> >
> > You wrote: If you can take the database offline for the length of time it
> > takes to
> > copy the file, this works fine. If not, use backup and restore. If the
> > db
> > will be large use SQL Backup (RedGate) or LiteSpeed (Quest)
> >
> > Pardon my ignorance, but you are saying there are files we can just copy
> > from one server to another. In out current system, we copy the Access mdb
> > we
> > use as our backends from the Live server to the Test Server once a week
> > when
> > all the users log off. This will be the same? Which files do I need to
> > copy?
> >
> > You wrote: Anywhere on a 'local' drive (local to the server, or SAN).
> > Then
> > take them to tape or fileserver daily. NOT on the same drive as the
> > database
> > file (.mdf/.ldf)
> >
> > I believe that my network guy is going to set up the server with one
> > partition for the data, one for the logs and one for the backup. This will
> > work correct? He was asking me what RAID level works best? Any preference?
> >
> > "Kevin3NF" wrote:
> >
> >> I'll assume SQL 2000...see responses inline below...
> >>
> >> --
> >> Kevin Hill
> >> 3NF Consulting
> >> www.3nf-inc.com/NewsGroups.htm
> >>
> >>
> >>
> >>
> >> "Rick Vooys" <RickVooys@discussions.microsoft.com> wrote in message
> >> news:0428899F-D1F3-4E22-9371-83DEB00686A5@microsoft.com...
> >> > Hi Guys,
> >> >
> >> > This is my first foray into SQL Server. We are using SQL server. At
> >> > first
> >> > an Access Application will be used through ODBC and then we will be
> >> > building
> >> > a .NET web interface to act as the frontend. It will be run on our
> >> > intranet.
> >> > First I would like to ask for any inside advice on Setup, and gotchas
> >> > that
> >> > have created issues. Maybe a checklist to walk through.
> >>
> >> SP4. or higher.
> >> Data and Transaction log files on a different spindle (or set of
> >> spindles).
> >> Backup files on a different set of spindles as well. Note that I don't
> >> say
> >> partitions here, since if the raid array crashes, all partitions on it
> >> are
> >> toast
> >>
> >> >
> >> > Second, We have a Live server and Testbed server. What process would I
> >> > use
> >> > to transfer the live data to the testbed server. I have done some
> >> > reading
> >> > and
> >> > I think the detach and attach commands would accomplish this.
> >>
> >> If you can take the database offline for the length of time it takes to
> >> copy the file, this works fine. If not, use backup and restore. If the
> >> db
> >> will be large use SQL Backup (RedGate) or LiteSpeed (Quest)
> >>
> >>
> >> > Third, what is the best backup setup. I.E where the backup logs are
> >> > stored
> >> > etc.
> >>
> >> Anywhere on a 'local' drive (local to the server, or SAN). Then take
> >> them
> >> to tape or fileserver daily. NOT on the same drive as the database file
> >> (.mdf/.ldf)
> >>
> >> >
> >> > Any advice is greatly appriceated. Thanks.
> >>
> >> You're welcome.
> >>
> >> >
> >> > -Rick
> >> Kevin3NF
> >>
> >>
> >>
>
>
>

Windows is way stronger than SQL....'nuff said.

That's what I tell my customers.

--
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm




"Rick Vooys" <RickVooys@discussions.microsoft.com> wrote in message
news:B39E4356-7A04-41B5-A5EF-15A6A5E59342@microsoft.com...
> Thanks for the infor Kevin, seems much clearer to me. My other concern
> right
> now is setting up the secruity for the server.
>
> Do you have any preference on Windows vs SQL Authentication? Any obvious
> gotchas I might miss as a newbie?
>
> "Kevin3NF" wrote:
>
>> Oh...no idea if 2005 is different. I doubt it, as these are basic
>> questions
>> that are not likely to have undergone major re-writes in 2005.
>>
>> --
>> Kevin Hill
>> 3NF Consulting
>> www.3nf-inc.com/NewsGroups.htm
>>
>>
>>
>>
>> "Rick Vooys" <RickVooys@discussions.microsoft.com> wrote in message
>> news2D13958-0304-4C0B-84B1-16DA8AE0D7E4@microsoft.com...
>> > Thanks! It is SQL Server 2005 ... does that change any answers?
>> >
>> > You wrote: If you can take the database offline for the length of time
>> > it
>> > takes to
>> > copy the file, this works fine. If not, use backup and restore. If
>> > the
>> > db
>> > will be large use SQL Backup (RedGate) or LiteSpeed (Quest)
>> >
>> > Pardon my ignorance, but you are saying there are files we can just
>> > copy
>> > from one server to another. In out current system, we copy the Access
>> > mdb
>> > we
>> > use as our backends from the Live server to the Test Server once a week
>> > when
>> > all the users log off. This will be the same? Which files do I need to
>> > copy?
>> >
>> > You wrote: Anywhere on a 'local' drive (local to the server, or SAN).
>> > Then
>> > take them to tape or fileserver daily. NOT on the same drive as the
>> > database
>> > file (.mdf/.ldf)
>> >
>> > I believe that my network guy is going to set up the server with one
>> > partition for the data, one for the logs and one for the backup. This
>> > will
>> > work correct? He was asking me what RAID level works best? Any
>> > preference?
>> >
>> > "Kevin3NF" wrote:
>> >
>> >> I'll assume SQL 2000...see responses inline below...
>> >>
>> >> --
>> >> Kevin Hill
>> >> 3NF Consulting
>> >> www.3nf-inc.com/NewsGroups.htm
>> >>
>> >>
>> >>
>> >>
>> >> "Rick Vooys" <RickVooys@discussions.microsoft.com> wrote in message
>> >> news:0428899F-D1F3-4E22-9371-83DEB00686A5@microsoft.com...
>> >> > Hi Guys,
>> >> >
>> >> > This is my first foray into SQL Server. We are using SQL server. At
>> >> > first
>> >> > an Access Application will be used through ODBC and then we will be
>> >> > building
>> >> > a .NET web interface to act as the frontend. It will be run on our
>> >> > intranet.
>> >> > First I would like to ask for any inside advice on Setup, and
>> >> > gotchas
>> >> > that
>> >> > have created issues. Maybe a checklist to walk through.
>> >>
>> >> SP4. or higher.
>> >> Data and Transaction log files on a different spindle (or set of
>> >> spindles).
>> >> Backup files on a different set of spindles as well. Note that I
>> >> don't
>> >> say
>> >> partitions here, since if the raid array crashes, all partitions on it
>> >> are
>> >> toast
>> >>
>> >> >
>> >> > Second, We have a Live server and Testbed server. What process would
>> >> > I
>> >> > use
>> >> > to transfer the live data to the testbed server. I have done some
>> >> > reading
>> >> > and
>> >> > I think the detach and attach commands would accomplish this.
>> >>
>> >> If you can take the database offline for the length of time it takes
>> >> to
>> >> copy the file, this works fine. If not, use backup and restore. If
>> >> the
>> >> db
>> >> will be large use SQL Backup (RedGate) or LiteSpeed (Quest)
>> >>
>> >>
>> >> > Third, what is the best backup setup. I.E where the backup logs are
>> >> > stored
>> >> > etc.
>> >>
>> >> Anywhere on a 'local' drive (local to the server, or SAN). Then take
>> >> them
>> >> to tape or fileserver daily. NOT on the same drive as the database
>> >> file
>> >> (.mdf/.ldf)
>> >>
>> >> >
>> >> > Any advice is greatly appriceated. Thanks.
>> >>
>> >> You're welcome.
>> >>
>> >> >
>> >> > -Rick
>> >> Kevin3NF
>> >>
>> >>
>> >>
>>
>>
>>