Lock pages in memory.

If SQL Server is running on Local System account, does it automatically
have the "Lock Pages in memory" right or does this have to be manually
set?
If manual, which account is the Local System, in the list of domain accounts
that appears in gpedit.msc.
Any help greatly appreciated.Help me here<g>
I need to know this, in order to tell whether my sql server local
system account is managing awe memory properly.
Please...
"Stressed" <k@.c.co.uk> wrote in message
news:eV5Du$BYDHA.736@.TK2MSFTNGP09.phx.gbl...
> If SQL Server is running on Local System account, does it automatically
> have the "Lock Pages in memory" right or does this have to be manually
> set?
> If manual, which account is the Local System, in the list of domain
accounts
> that appears in gpedit.msc.
> Any help greatly appreciated.
>|||One more thing,
If the "lock pages in memory " permission is not granted you should have
seen error in the errorlog.
In the SQL Server log, you will see the following message:
Cannot use Address Windowing Extensions because lock memory privilege was
not granted.
Sincerely,
Yih-Yoon Lee [Microsoft]
Microsoft SQL Server Support
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Thanks for the reply, was very helpful.
"Yih-Yoon Lee [MS]" <yihyoonl@.online.microsoft.com> wrote in message
news:doqK3sTYDHA.304@.cpmsftngxa06.phx.gbl...
> One more thing,
> If the "lock pages in memory " permission is not granted you should have
> seen error in the errorlog.
> In the SQL Server log, you will see the following message:
> Cannot use Address Windowing Extensions because lock memory privilege was
> not granted.
> Sincerely,
> Yih-Yoon Lee [Microsoft]
> Microsoft SQL Server Support
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>

Location of MDF / LDF / Backup / Log

For a finance application, we use a Windows 2003 Server
with C and D are local HD (They are mirrored as RAID 1).
On the other hand, it is connected to a SAN with drive
letter J.
We find that the consultant has installed the SQLS Server
2000 DB MDF and LDF in the D drive. The finance
application is installed on the J drive.
We will backup both C,D and J drive daily. They haven't
setup the database maintenance plan yet.
For both performance purpose, we would like to get your
advie where is the best place to put the backup files and
log files ? Is it D or J drive ? Should we suggest them
to put the transaction log file from D to J as well ?
Thank you for your advice
Stephen wrote:
> For a finance application, we use a Windows 2003 Server
> with C and D are local HD (They are mirrored as RAID 1).
> On the other hand, it is connected to a SAN with drive
> letter J.
> We find that the consultant has installed the SQLS Server
> 2000 DB MDF and LDF in the D drive. The finance
> application is installed on the J drive.
> We will backup both C,D and J drive daily. They haven't
> setup the database maintenance plan yet.
> For both performance purpose, we would like to get your
> advie where is the best place to put the backup files and
> log files ? Is it D or J drive ? Should we suggest them
> to put the transaction log file from D to J as well ?
> Thank you for your advice
If you've paid for a SAN, it seems silly not use it for the data and
t-logs. Are you saying that C is a mirrored set and drive D is a
mirrored set (so that makes four local hard drives)? You don't have to
back up the drives themselves for SQL Server backup and recovery. What
you need to back up are the full, differential, and t-log backups you
make throughout the day. Generally, you want to put the backup files on
different spindles than the drive on which the data originates to
maximize bandwidth. So if the data is on the SAN, you can back up to one
of the local arrays.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Dear David,
Thank you for your advice. Yes, both C and D are mirrored set.
Someone suggest to put the transaction log file in the RAID 1 for recovery
and data in RAID 5. Is it OK from your point of view ?
Where should I put the backup file ? RAID 1 or RAID 5 array ?
Thanks
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
> Stephen wrote:
> If you've paid for a SAN, it seems silly not use it for the data and
> t-logs. Are you saying that C is a mirrored set and drive D is a mirrored
> set (so that makes four local hard drives)? You don't have to back up the
> drives themselves for SQL Server backup and recovery. What you need to
> back up are the full, differential, and t-log backups you make throughout
> the day. Generally, you want to put the backup files on different spindles
> than the drive on which the data originates to maximize bandwidth. So if
> the data is on the SAN, you can back up to one of the local arrays.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||Stephen
Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
buy RAID 10&1) put the log on
RAID1 device and data on RAID-5
(this option is appropriate if the writes activites are moderate)
Note: The appropriate decision depends on your perfomance charctiristics
and fault tolerance needs.
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Dear David,
> Thank you for your advice. Yes, both C and D are mirrored set.
> Someone suggest to put the transaction log file in the RAID 1 for recovery
> and data in RAID 5. Is it OK from your point of view ?
> Where should I put the backup file ? RAID 1 or RAID 5 array ?
> Thanks
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
mirrored[vbcol=seagreen]
the[vbcol=seagreen]
throughout[vbcol=seagreen]
spindles
>
|||Dear Uri,
Thank you for your advice. However, I am new to RAID array. I would like to
know the underlying reason to put the log on RAID 1 and data on RAID 5.
On the other hand, should we put the Database and Transaction log backup on
RAID 5 array ?
Thank you again.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> Stephen
> Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
> buy RAID 10&1) put the log on
> RAID1 device and data on RAID-5
> (this option is appropriate if the writes activites are moderate)
>
> Note: The appropriate decision depends on your perfomance charctiristics
> and fault tolerance needs.
>
>
> "Stephen" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> mirrored
> the
> throughout
> spindles
>
|||Stepfen
I'd recommend you buy "Inside SQL Server 2000" written by Kalen Delaney (See
topic about RAID solutions)
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:e2dG$6kdFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Dear Uri,
> Thank you for your advice. However, I am new to RAID array. I would like
to
> know the underlying reason to put the log on RAID 1 and data on RAID 5.
> On the other hand, should we put the Database and Transaction log backup
on[vbcol=seagreen]
> RAID 5 array ?
> Thank you again.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
to[vbcol=seagreen]
charctiristics[vbcol=seagreen]
to
>
|||Stephen wrote:
> Dear Uri,
> Thank you for your advice. However, I am new to RAID array. I would
> like to know the underlying reason to put the log on RAID 1 and data
> on RAID 5.
> On the other hand, should we put the Database and Transaction log
> backup on RAID 5 array ?
>
I didn't think RAID 5 was one of your installed options given you only
had 4 local drives...
In any case, for small and medium sized databases with a low percentage
of writes, RAID 5 is an inexpensive solution. RAID 5 writes data very
slowly, so it's only appropriate for data files. Transaction logs and
tempdb should reside on a mirrored set (which you have). It's best for
performance to keep data and t-logs on different drives, which are also
separate from the OS. In your environment, you have a SAN (J), and 2
local mirrored sets (C and D).
I would recommend you use C for the OS and applications like SQL Server.
Use D for the t-logs and tempdb, and use the SAN for the data.
Everything needs to backed up in some fashion.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||This website has a good intro to RAID levels and the basic pros & cons
(even if they are basically trying to sell you their products):
http://www.acnc.com/raid.html
(for those that can't afford Kalen's book _Inside SQL Server_, which in
an excellent book that I thoroughly recommend)
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Stephen wrote:

>Dear Uri,
>Thank you for your advice. However, I am new to RAID array. I would like to
>know the underlying reason to put the log on RAID 1 and data on RAID 5.
>On the other hand, should we put the Database and Transaction log backup on
>RAID 5 array ?
>Thank you again.
>
>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
>
>
>

Location of MDF / LDF / Backup / Log

For a finance application, we use a Windows 2003 Server
with C and D are local HD (They are mirrored as RAID 1).
On the other hand, it is connected to a SAN with drive
letter J.
We find that the consultant has installed the SQLS Server
2000 DB MDF and LDF in the D drive. The finance
application is installed on the J drive.
We will backup both C,D and J drive daily. They haven't
setup the database maintenance plan yet.
For both performance purpose, we would like to get your
advie where is the best place to put the backup files and
log files ? Is it D or J drive ? Should we suggest them
to put the transaction log file from D to J as well ?
Thank you for your adviceStephen wrote:
> For a finance application, we use a Windows 2003 Server
> with C and D are local HD (They are mirrored as RAID 1).
> On the other hand, it is connected to a SAN with drive
> letter J.
> We find that the consultant has installed the SQLS Server
> 2000 DB MDF and LDF in the D drive. The finance
> application is installed on the J drive.
> We will backup both C,D and J drive daily. They haven't
> setup the database maintenance plan yet.
> For both performance purpose, we would like to get your
> advie where is the best place to put the backup files and
> log files ? Is it D or J drive ? Should we suggest them
> to put the transaction log file from D to J as well ?
> Thank you for your advice
If you've paid for a SAN, it seems silly not use it for the data and
t-logs. Are you saying that C is a mirrored set and drive D is a
mirrored set (so that makes four local hard drives)? You don't have to
back up the drives themselves for SQL Server backup and recovery. What
you need to back up are the full, differential, and t-log backups you
make throughout the day. Generally, you want to put the backup files on
different spindles than the drive on which the data originates to
maximize bandwidth. So if the data is on the SAN, you can back up to one
of the local arrays.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Dear David,
Thank you for your advice. Yes, both C and D are mirrored set.
Someone suggest to put the transaction log file in the RAID 1 for recovery
and data in RAID 5. Is it OK from your point of view ?
Where should I put the backup file ? RAID 1 or RAID 5 array ?
Thanks
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
> Stephen wrote:
>> For a finance application, we use a Windows 2003 Server
>> with C and D are local HD (They are mirrored as RAID 1).
>> On the other hand, it is connected to a SAN with drive
>> letter J.
>> We find that the consultant has installed the SQLS Server
>> 2000 DB MDF and LDF in the D drive. The finance
>> application is installed on the J drive.
>> We will backup both C,D and J drive daily. They haven't
>> setup the database maintenance plan yet.
>> For both performance purpose, we would like to get your
>> advie where is the best place to put the backup files and
>> log files ? Is it D or J drive ? Should we suggest them
>> to put the transaction log file from D to J as well ?
>> Thank you for your advice
> If you've paid for a SAN, it seems silly not use it for the data and
> t-logs. Are you saying that C is a mirrored set and drive D is a mirrored
> set (so that makes four local hard drives)? You don't have to back up the
> drives themselves for SQL Server backup and recovery. What you need to
> back up are the full, differential, and t-log backups you make throughout
> the day. Generally, you want to put the backup files on different spindles
> than the drive on which the data originates to maximize bandwidth. So if
> the data is on the SAN, you can back up to one of the local arrays.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Stephen
Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
buy RAID 10&1) put the log on
RAID1 device and data on RAID-5
(this option is appropriate if the writes activites are moderate)
Note: The appropriate decision depends on your perfomance charctiristics
and fault tolerance needs.
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> Dear David,
> Thank you for your advice. Yes, both C and D are mirrored set.
> Someone suggest to put the transaction log file in the RAID 1 for recovery
> and data in RAID 5. Is it OK from your point of view ?
> Where should I put the backup file ? RAID 1 or RAID 5 array ?
> Thanks
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
> > Stephen wrote:
> >> For a finance application, we use a Windows 2003 Server
> >> with C and D are local HD (They are mirrored as RAID 1).
> >> On the other hand, it is connected to a SAN with drive
> >> letter J.
> >>
> >> We find that the consultant has installed the SQLS Server
> >> 2000 DB MDF and LDF in the D drive. The finance
> >> application is installed on the J drive.
> >>
> >> We will backup both C,D and J drive daily. They haven't
> >> setup the database maintenance plan yet.
> >>
> >> For both performance purpose, we would like to get your
> >> advie where is the best place to put the backup files and
> >> log files ? Is it D or J drive ? Should we suggest them
> >> to put the transaction log file from D to J as well ?
> >>
> >> Thank you for your advice
> >
> > If you've paid for a SAN, it seems silly not use it for the data and
> > t-logs. Are you saying that C is a mirrored set and drive D is a
mirrored
> > set (so that makes four local hard drives)? You don't have to back up
the
> > drives themselves for SQL Server backup and recovery. What you need to
> > back up are the full, differential, and t-log backups you make
throughout
> > the day. Generally, you want to put the backup files on different
spindles
> > than the drive on which the data originates to maximize bandwidth. So if
> > the data is on the SAN, you can back up to one of the local arrays.
> >
> > --
> > David Gugick
> > Quest Software
> > www.imceda.com
> > www.quest.com
>|||Dear Uri,
Thank you for your advice. However, I am new to RAID array. I would like to
know the underlying reason to put the log on RAID 1 and data on RAID 5.
On the other hand, should we put the Database and Transaction log backup on
RAID 5 array ?
Thank you again.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> Stephen
> Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
> buy RAID 10&1) put the log on
> RAID1 device and data on RAID-5
> (this option is appropriate if the writes activites are moderate)
>
> Note: The appropriate decision depends on your perfomance charctiristics
> and fault tolerance needs.
>
>
> "Stephen" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
>> Dear David,
>> Thank you for your advice. Yes, both C and D are mirrored set.
>> Someone suggest to put the transaction log file in the RAID 1 for
>> recovery
>> and data in RAID 5. Is it OK from your point of view ?
>> Where should I put the backup file ? RAID 1 or RAID 5 array ?
>> Thanks
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
>> > Stephen wrote:
>> >> For a finance application, we use a Windows 2003 Server
>> >> with C and D are local HD (They are mirrored as RAID 1).
>> >> On the other hand, it is connected to a SAN with drive
>> >> letter J.
>> >>
>> >> We find that the consultant has installed the SQLS Server
>> >> 2000 DB MDF and LDF in the D drive. The finance
>> >> application is installed on the J drive.
>> >>
>> >> We will backup both C,D and J drive daily. They haven't
>> >> setup the database maintenance plan yet.
>> >>
>> >> For both performance purpose, we would like to get your
>> >> advie where is the best place to put the backup files and
>> >> log files ? Is it D or J drive ? Should we suggest them
>> >> to put the transaction log file from D to J as well ?
>> >>
>> >> Thank you for your advice
>> >
>> > If you've paid for a SAN, it seems silly not use it for the data and
>> > t-logs. Are you saying that C is a mirrored set and drive D is a
> mirrored
>> > set (so that makes four local hard drives)? You don't have to back up
> the
>> > drives themselves for SQL Server backup and recovery. What you need to
>> > back up are the full, differential, and t-log backups you make
> throughout
>> > the day. Generally, you want to put the backup files on different
> spindles
>> > than the drive on which the data originates to maximize bandwidth. So
>> > if
>> > the data is on the SAN, you can back up to one of the local arrays.
>> >
>> > --
>> > David Gugick
>> > Quest Software
>> > www.imceda.com
>> > www.quest.com
>>
>|||Stepfen
I'd recommend you buy "Inside SQL Server 2000" written by Kalen Delaney (See
topic about RAID solutions)
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:e2dG$6kdFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Dear Uri,
> Thank you for your advice. However, I am new to RAID array. I would like
to
> know the underlying reason to put the log on RAID 1 and data on RAID 5.
> On the other hand, should we put the Database and Transaction log backup
on
> RAID 5 array ?
> Thank you again.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> > Stephen
> > Most of SQL Server DBA/Programmers ( yep if the budget does not permit
to
> > buy RAID 10&1) put the log on
> > RAID1 device and data on RAID-5
> > (this option is appropriate if the writes activites are moderate)
> >
> >
> >
> > Note: The appropriate decision depends on your perfomance
charctiristics
> > and fault tolerance needs.
> >
> >
> >
> >
> > "Stephen" <anonymous@.discussions.microsoft.com> wrote in message
> > news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> >> Dear David,
> >>
> >> Thank you for your advice. Yes, both C and D are mirrored set.
> >>
> >> Someone suggest to put the transaction log file in the RAID 1 for
> >> recovery
> >> and data in RAID 5. Is it OK from your point of view ?
> >>
> >> Where should I put the backup file ? RAID 1 or RAID 5 array ?
> >>
> >> Thanks
> >>
> >> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> >> news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
> >> > Stephen wrote:
> >> >> For a finance application, we use a Windows 2003 Server
> >> >> with C and D are local HD (They are mirrored as RAID 1).
> >> >> On the other hand, it is connected to a SAN with drive
> >> >> letter J.
> >> >>
> >> >> We find that the consultant has installed the SQLS Server
> >> >> 2000 DB MDF and LDF in the D drive. The finance
> >> >> application is installed on the J drive.
> >> >>
> >> >> We will backup both C,D and J drive daily. They haven't
> >> >> setup the database maintenance plan yet.
> >> >>
> >> >> For both performance purpose, we would like to get your
> >> >> advie where is the best place to put the backup files and
> >> >> log files ? Is it D or J drive ? Should we suggest them
> >> >> to put the transaction log file from D to J as well ?
> >> >>
> >> >> Thank you for your advice
> >> >
> >> > If you've paid for a SAN, it seems silly not use it for the data and
> >> > t-logs. Are you saying that C is a mirrored set and drive D is a
> > mirrored
> >> > set (so that makes four local hard drives)? You don't have to back up
> > the
> >> > drives themselves for SQL Server backup and recovery. What you need
to
> >> > back up are the full, differential, and t-log backups you make
> > throughout
> >> > the day. Generally, you want to put the backup files on different
> > spindles
> >> > than the drive on which the data originates to maximize bandwidth. So
> >> > if
> >> > the data is on the SAN, you can back up to one of the local arrays.
> >> >
> >> > --
> >> > David Gugick
> >> > Quest Software
> >> > www.imceda.com
> >> > www.quest.com
> >>
> >>
> >
> >
>|||Stephen wrote:
> Dear Uri,
> Thank you for your advice. However, I am new to RAID array. I would
> like to know the underlying reason to put the log on RAID 1 and data
> on RAID 5.
> On the other hand, should we put the Database and Transaction log
> backup on RAID 5 array ?
>
I didn't think RAID 5 was one of your installed options given you only
had 4 local drives...
In any case, for small and medium sized databases with a low percentage
of writes, RAID 5 is an inexpensive solution. RAID 5 writes data very
slowly, so it's only appropriate for data files. Transaction logs and
tempdb should reside on a mirrored set (which you have). It's best for
performance to keep data and t-logs on different drives, which are also
separate from the OS. In your environment, you have a SAN (J), and 2
local mirrored sets (C and D).
I would recommend you use C for the OS and applications like SQL Server.
Use D for the t-logs and tempdb, and use the SAN for the data.
Everything needs to backed up in some fashion.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||This is a multi-part message in MIME format.
--030209030201000601050508
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
This website has a good intro to RAID levels and the basic pros & cons
(even if they are basically trying to sell you their products):
http://www.acnc.com/raid.html
(for those that can't afford Kalen's book _Inside SQL Server_, which in
an excellent book that I thoroughly recommend)
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Stephen wrote:
>Dear Uri,
>Thank you for your advice. However, I am new to RAID array. I would like to
>know the underlying reason to put the log on RAID 1 and data on RAID 5.
>On the other hand, should we put the Database and Transaction log backup on
>RAID 5 array ?
>Thank you again.
>
>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
>
>>Stephen
>>Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
>>buy RAID 10&1) put the log on
>>RAID1 device and data on RAID-5
>>(this option is appropriate if the writes activites are moderate)
>>
>>Note: The appropriate decision depends on your perfomance charctiristics
>>and fault tolerance needs.
>>
>>
>>"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
>>news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
>>
>>Dear David,
>>Thank you for your advice. Yes, both C and D are mirrored set.
>>Someone suggest to put the transaction log file in the RAID 1 for
>>recovery
>>and data in RAID 5. Is it OK from your point of view ?
>>Where should I put the backup file ? RAID 1 or RAID 5 array ?
>>Thanks
>>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>>news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
>>
>>Stephen wrote:
>>
>>For a finance application, we use a Windows 2003 Server
>>with C and D are local HD (They are mirrored as RAID 1).
>>On the other hand, it is connected to a SAN with drive
>>letter J.
>>We find that the consultant has installed the SQLS Server
>>2000 DB MDF and LDF in the D drive. The finance
>>application is installed on the J drive.
>>We will backup both C,D and J drive daily. They haven't
>>setup the database maintenance plan yet.
>>For both performance purpose, we would like to get your
>>advie where is the best place to put the backup files and
>>log files ? Is it D or J drive ? Should we suggest them
>>to put the transaction log file from D to J as well ?
>>Thank you for your advice
>>
>>If you've paid for a SAN, it seems silly not use it for the data and
>>t-logs. Are you saying that C is a mirrored set and drive D is a
>>
>>mirrored
>>
>>set (so that makes four local hard drives)? You don't have to back up
>>
>>the
>>
>>drives themselves for SQL Server backup and recovery. What you need to
>>back up are the full, differential, and t-log backups you make
>>
>>throughout
>>
>>the day. Generally, you want to put the backup files on different
>>
>>spindles
>>
>>than the drive on which the data originates to maximize bandwidth. So
>>if
>>the data is on the SAN, you can back up to one of the local arrays.
>>--
>>David Gugick
>>Quest Software
>>www.imceda.com
>>www.quest.com
>>
>>
>>
>
>
--030209030201000601050508
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">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>This website has a good intro to RAID levels and the basic pros
& cons (even if they are basically trying to sell you their
products):<br>
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.acnc.com/raid.html</a><br>">http://www.acnc.com/raid.html">http://www.acnc.com/raid.html</a><br>
<br>
(for those that can't afford Kalen's book <u>Inside SQL Server</u>,
which in an excellent book that I thoroughly recommend)<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Stephen wrote:
<blockquote cite="mide2dG$6kdFHA.3040@.TK2MSFTNGP14.phx.gbl" type="cite">
<pre wrap="">Dear Uri,
Thank you for your advice. However, I am new to RAID array. I would like to
know the underlying reason to put the log on RAID 1 and data on RAID 5.
On the other hand, should we put the Database and Transaction log backup on
RAID 5 array ?
Thank you again.
"Uri Dimant" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:urid@.iscar.co.il"><urid@.iscar.co.il></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl">news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Stephen
Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
buy RAID 10&1) put the log on
RAID1 device and data on RAID-5
(this option is appropriate if the writes activites are moderate)
Note: The appropriate decision depends on your perfomance charctiristics
and fault tolerance needs.
"Stephen" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:anonymous@.discussions.microsoft.com"><anonymous@.discussions.microsoft.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl">news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Dear David,
Thank you for your advice. Yes, both C and D are mirrored set.
Someone suggest to put the transaction log file in the RAID 1 for
recovery
and data in RAID 5. Is it OK from your point of view ?
Where should I put the backup file ? RAID 1 or RAID 5 array ?
Thanks
"David Gugick" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:david.gugick-nospam@.quest.com"><david.gugick-nospam@.quest.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl">news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Stephen wrote:
</pre>
<blockquote type="cite">
<pre wrap="">For a finance application, we use a Windows 2003 Server
with C and D are local HD (They are mirrored as RAID 1).
On the other hand, it is connected to a SAN with drive
letter J.
We find that the consultant has installed the SQLS Server
2000 DB MDF and LDF in the D drive. The finance
application is installed on the J drive.
We will backup both C,D and J drive daily. They haven't
setup the database maintenance plan yet.
For both performance purpose, we would like to get your
advie where is the best place to put the backup files and
log files ? Is it D or J drive ? Should we suggest them
to put the transaction log file from D to J as well ?
Thank you for your advice
</pre>
</blockquote>
<pre wrap="">If you've paid for a SAN, it seems silly not use it for the data and
t-logs. Are you saying that C is a mirrored set and drive D is a
</pre>
</blockquote>
</blockquote>
<pre wrap="">mirrored
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">set (so that makes four local hard drives)? You don't have to back up
</pre>
</blockquote>
</blockquote>
<pre wrap="">the
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">drives themselves for SQL Server backup and recovery. What you need to
back up are the full, differential, and t-log backups you make
</pre>
</blockquote>
</blockquote>
<pre wrap="">throughout
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">the day. Generally, you want to put the backup files on different
</pre>
</blockquote>
</blockquote>
<pre wrap="">spindles
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">than the drive on which the data originates to maximize bandwidth. So
if
the data is on the SAN, you can back up to one of the local arrays.
--
David Gugick
Quest Software
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.imceda.com</a>">http://www.imceda.com">www.imceda.com</a>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.quest.com</a>">http://www.quest.com">www.quest.com</a>
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--030209030201000601050508--

Location of MDF / LDF / Backup / Log

For a finance application, we use a Windows 2003 Server
with C and D are local HD (They are mirrored as RAID 1).
On the other hand, it is connected to a SAN with drive
letter J.
We find that the consultant has installed the SQLS Server
2000 DB MDF and LDF in the D drive. The finance
application is installed on the J drive.
We will backup both C,D and J drive daily. They haven't
setup the database maintenance plan yet.
For both performance purpose, we would like to get your
advie where is the best place to put the backup files and
log files ? Is it D or J drive ? Should we suggest them
to put the transaction log file from D to J as well ?
Thank you for your adviceStephen wrote:
> For a finance application, we use a Windows 2003 Server
> with C and D are local HD (They are mirrored as RAID 1).
> On the other hand, it is connected to a SAN with drive
> letter J.
> We find that the consultant has installed the SQLS Server
> 2000 DB MDF and LDF in the D drive. The finance
> application is installed on the J drive.
> We will backup both C,D and J drive daily. They haven't
> setup the database maintenance plan yet.
> For both performance purpose, we would like to get your
> advie where is the best place to put the backup files and
> log files ? Is it D or J drive ? Should we suggest them
> to put the transaction log file from D to J as well ?
> Thank you for your advice
If you've paid for a SAN, it seems silly not use it for the data and
t-logs. Are you saying that C is a mirrored set and drive D is a
mirrored set (so that makes four local hard drives)? You don't have to
back up the drives themselves for SQL Server backup and recovery. What
you need to back up are the full, differential, and t-log backups you
make throughout the day. Generally, you want to put the backup files on
different spindles than the drive on which the data originates to
maximize bandwidth. So if the data is on the SAN, you can back up to one
of the local arrays.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Dear David,
Thank you for your advice. Yes, both C and D are mirrored set.
Someone suggest to put the transaction log file in the RAID 1 for recovery
and data in RAID 5. Is it OK from your point of view ?
Where should I put the backup file ? RAID 1 or RAID 5 array ?
Thanks
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
> Stephen wrote:
> If you've paid for a SAN, it seems silly not use it for the data and
> t-logs. Are you saying that C is a mirrored set and drive D is a mirrored
> set (so that makes four local hard drives)? You don't have to back up the
> drives themselves for SQL Server backup and recovery. What you need to
> back up are the full, differential, and t-log backups you make throughout
> the day. Generally, you want to put the backup files on different spindles
> than the drive on which the data originates to maximize bandwidth. So if
> the data is on the SAN, you can back up to one of the local arrays.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Stephen
Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
buy RAID 10&1) put the log on
RAID1 device and data on RAID-5
(this option is appropriate if the writes activites are moderate)
Note: The appropriate decision depends on your perfomance charctiristics
and fault tolerance needs.
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> Dear David,
> Thank you for your advice. Yes, both C and D are mirrored set.
> Someone suggest to put the transaction log file in the RAID 1 for recovery
> and data in RAID 5. Is it OK from your point of view ?
> Where should I put the backup file ? RAID 1 or RAID 5 array ?
> Thanks
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
mirrored[vbcol=seagreen]
the[vbcol=seagreen]
throughout[vbcol=seagreen]
spindles[vbcol=seagreen]
>|||Dear Uri,
Thank you for your advice. However, I am new to RAID array. I would like to
know the underlying reason to put the log on RAID 1 and data on RAID 5.
On the other hand, should we put the Database and Transaction log backup on
RAID 5 array ?
Thank you again.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> Stephen
> Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
> buy RAID 10&1) put the log on
> RAID1 device and data on RAID-5
> (this option is appropriate if the writes activites are moderate)
>
> Note: The appropriate decision depends on your perfomance charctiristics
> and fault tolerance needs.
>
>
> "Stephen" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> mirrored
> the
> throughout
> spindles
>|||Stepfen
I'd recommend you buy "Inside SQL Server 2000" written by Kalen Delaney (See
topic about RAID solutions)
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:e2dG$6kdFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Dear Uri,
> Thank you for your advice. However, I am new to RAID array. I would like
to
> know the underlying reason to put the log on RAID 1 and data on RAID 5.
> On the other hand, should we put the Database and Transaction log backup
on
> RAID 5 array ?
> Thank you again.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
to[vbcol=seagreen]
charctiristics[vbcol=seagreen]
to[vbcol=seagreen]
>|||Stephen wrote:
> Dear Uri,
> Thank you for your advice. However, I am new to RAID array. I would
> like to know the underlying reason to put the log on RAID 1 and data
> on RAID 5.
> On the other hand, should we put the Database and Transaction log
> backup on RAID 5 array ?
>
I didn't think RAID 5 was one of your installed options given you only
had 4 local drives...
In any case, for small and medium sized databases with a low percentage
of writes, RAID 5 is an inexpensive solution. RAID 5 writes data very
slowly, so it's only appropriate for data files. Transaction logs and
tempdb should reside on a mirrored set (which you have). It's best for
performance to keep data and t-logs on different drives, which are also
separate from the OS. In your environment, you have a SAN (J), and 2
local mirrored sets (C and D).
I would recommend you use C for the OS and applications like SQL Server.
Use D for the t-logs and tempdb, and use the SAN for the data.
Everything needs to backed up in some fashion.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||This website has a good intro to RAID levels and the basic pros & cons
(even if they are basically trying to sell you their products):
http://www.acnc.com/raid.html
(for those that can't afford Kalen's book _Inside SQL Server_, which in
an excellent book that I thoroughly recommend)
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Stephen wrote:

>Dear Uri,
>Thank you for your advice. However, I am new to RAID array. I would like t
o
>know the underlying reason to put the log on RAID 1 and data on RAID 5.
>On the other hand, should we put the Database and Transaction log backup on
>RAID 5 array ?
>Thank you again.
>
>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
>
>
>sql

LocalSqlServer

I finally got my website to run on the local machine. I thought it was going to run on the server but I just get my login. After I enter my info and click "login" I get the following error. Do I need to enable the SQL Server Browser service?

Server Error in '/' Application.

---

Configuration Error

Description:An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message:The connection name 'LocalSqlServer' was not found in the applications configuration or the connection string is empty.

Source Error:

Line 138: <providers>Line 139: <add name="AspNetSqlMembershipProvider"Line 140: type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"Line 141: connectionStringName="LocalSqlServer"Line 142: enablePasswordRetrieval="false"

Source File:C:\WINNT\Microsoft.NET\Framework\v2.0.50727\Config\machine.config Line:140

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

Perhaps, but that isn't what is causing the error. You haven't configured a connection string with the name "LocalSqLServer".

|||

Do I do this in the solutions web config file?

|||

Yes

|||

Here is my web.config so far. I've tried putting the add tag and assigning LocalSqlServer to the default provider but I can't get anything to work. So what exactly do I need to do and where?

Thanks,

Nick

<?xmlversion="1.0"?>

<configurationxmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

<connectionStrings>

<addname="ConnectionString"connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\OpenAssess.mdb;Persist Security Info=True"providerName="System.Data.OleDb"/>

</connectionStrings>

<system.web>

<traceenabled="true"requestLimit="15" />

<authenticationmode="Forms"/>

<roleManagerenabled="true"/>

<compilationdebug="true"/>

</system.web>

</configuration>

|||

Can I make this connectin straight to Access with out using sql at all? Our host server doesn't support SQL Server.

Thanks,

Nick

|||

Looks like you are using the built-in Login controls. they use SQL Server 2005 Express by default, not access.

SQL Server 2005 Express is a free download from the same place as VWD EXpress, why not just use it?

|||

Where is your aspnetdb going to be?

|||

ASPNETDB.MDF is in my App_Data folder. I was trying to avoid SQL Server because the server we are on does not support it and I am trying to input data from the website to a database. Do you recommend we buy a package that supports it? I have SQL Server Management Studio Express on my local machine.

Thanks,

Nick

|||

These are things wrong so far:

Your web.config file does not turn membership on.

Your web.config file does not have a membership provider defined (Which is fine if you want to use the default one).

You do not have a connection string that the default membership provider is configured to use (LocalSqlServer) defined in the connection strings section.

Apparently your host doesn't support Sql Server, yet you are trying to use a .MDB file (That's a Sql Server file). Perhaps you meant that your host only supports Sql Server Express (BTW, that's backwards from most hosting services -- Most support Sql Server but not Sql Server Express).

localhost, local, . question

Do you know the difference between
(a)localhost
(b) local
(c) .
I've heard that they use different network layers but can't get any info on it.
TIA,
FS
This is my understanding:
Localhost is resolved by the IP stack.
(local) is resolved by the netlib, which means that it can use any network protocol (NetBEUI, IP, IPX etc)
I recall someone mentioning that . will only be resolved through Named Pipes. Not sure about this, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Frank Spencer" <anonymous@.discussions.microsoft.com> wrote in message
news:C182FD96-9B7D-42F8-9274-CBA2D56464A7@.microsoft.com...
> Do you know the difference between
> (a)localhost
> (b) local
> (c) .
> I've heard that they use different network layers but can't get any info on it.
> TIA,
> FS
>
|||localhost uses TCPIP. It just resolves to the loop back address
(127.0.0.1). Try "ping localhost" from the command prompt.
(local) and . use a protocol called shared memory and does not use any
network protocols.
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Frank Spencer" <anonymous@.discussions.microsoft.com> wrote in message
news:C182FD96-9B7D-42F8-9274-CBA2D56464A7@.microsoft.com...
> Do you know the difference between
> (a)localhost
> (b) local
> (c) .
> I've heard that they use different network layers but can't get any info
on it.
> TIA,
> FS
>

localhost, local, . question

Do you know the difference between
(a)localhos
(b) local
(c)
I've heard that they use different network layers but can't get any info on it
TIA
FThis is my understanding:
Localhost is resolved by the IP stack.
(local) is resolved by the netlib, which means that it can use any network protocol (NetBEUI, IP, IPX etc)
I recall someone mentioning that . will only be resolved through Named Pipes. Not sure about this, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Frank Spencer" <anonymous@.discussions.microsoft.com> wrote in message
news:C182FD96-9B7D-42F8-9274-CBA2D56464A7@.microsoft.com...
> Do you know the difference between
> (a)localhost
> (b) local
> (c) .
> I've heard that they use different network layers but can't get any info on it.
> TIA,
> FS
>|||localhost uses TCPIP. It just resolves to the loop back address
(127.0.0.1). Try "ping localhost" from the command prompt.
(local) and . use a protocol called shared memory and does not use any
network protocols.
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Frank Spencer" <anonymous@.discussions.microsoft.com> wrote in message
news:C182FD96-9B7D-42F8-9274-CBA2D56464A7@.microsoft.com...
> Do you know the difference between
> (a)localhost
> (b) local
> (c) .
> I've heard that they use different network layers but can't get any info
on it.
> TIA,
> FS
>

localhost, local, . question

Do you know the difference between
(a)localhost
(b) local
(c) .
I've heard that they use different network layers but can't get any info on
it.
TIA,
FSThis is my understanding:
Localhost is resolved by the IP stack.
(local) is resolved by the netlib, which means that it can use any network p
rotocol (NetBEUI, IP, IPX etc)
I recall someone mentioning that . will only be resolved through Named Pipes
. Not sure about this, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Frank Spencer" <anonymous@.discussions.microsoft.com> wrote in message
news:C182FD96-9B7D-42F8-9274-CBA2D56464A7@.microsoft.com...
> Do you know the difference between
> (a)localhost
> (b) local
> (c) .
> I've heard that they use different network layers but can't get any info o
n it.
> TIA,
> FS
>|||localhost uses TCPIP. It just resolves to the loop back address
(127.0.0.1). Try "ping localhost" from the command prompt.
(local) and . use a protocol called shared memory and does not use any
network protocols.
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Frank Spencer" <anonymous@.discussions.microsoft.com> wrote in message
news:C182FD96-9B7D-42F8-9274-CBA2D56464A7@.microsoft.com...
> Do you know the difference between
> (a)localhost
> (b) local
> (c) .
> I've heard that they use different network layers but can't get any info
on it.
> TIA,
> FS
>

LocalAdmins, LocalSystem, and the sysadmin role

During SQL Server 2005 installation, several logins are added to the
sysadmin role including:
- local administrator group (BUILTIN\Administrators),
- Local System (NT AUTHORITY\SYSTEM), and
- sa.
A common hardening practice is to later remove the local administrator group
from the sysadmin role, thereby separating server administration from DBMS
administration. However, I don't recall ever seeing the recommendation to
also remove the LocalSystem account from the sysadmin role.
Has anyone seen recommendations to remove BOTH local administrators and
LocalSystem from the sysadmin role for hardening purposes, and - if this
were to be done - what are the consequences?
Thanks in advance (and apologies for re-posting in hopes of a response),
DrewHello Drew,
Yes, generally we remove local administrator group from sysadmin role which
actually prevent accessing sql server who is having system admini privilages
on the server.
Now personally I prefer not to remove Local System as I was facing problem
while using the full-text search. Please refer the following Microsoft
article.
http://support.microsoft.com/kb/317746
Hope this will help you.
Regards,
MB
"DHamre" <dhamre@.comcast.net> wrote in message
news:%23Au%23cyMMHHA.3424@.TK2MSFTNGP02.phx.gbl...
> During SQL Server 2005 installation, several logins are added to the
> sysadmin role including:
> - local administrator group (BUILTIN\Administrators),
> - Local System (NT AUTHORITY\SYSTEM), and
> - sa.
> A common hardening practice is to later remove the local administrator
> group from the sysadmin role, thereby separating server administration
> from DBMS administration. However, I don't recall ever seeing the
> recommendation to also remove the LocalSystem account from the sysadmin
> role.
> Has anyone seen recommendations to remove BOTH local administrators and
> LocalSystem from the sysadmin role for hardening purposes, and - if this
> were to be done - what are the consequences?
> Thanks in advance (and apologies for re-posting in hopes of a response),
> Drew
>

Local vs. domain account

Hi All,
How can I tell how SQL Agent is configured to start up with? Is it with the local system account or domain account?
Thanks.from install? If you don't specify anything during the install, I believe it's local system|||You can see what are the log on properties by
go to services -> right click Properties sqlAgent --> log on

Local VB.NET app connection to remote SQL server

I know this is strickly not a website question, but dunno where else to post...

To remotely admin and monitor some functions of the website, I wish to use a local application to connect to the MSSQL DB which is held on the remote webhosting server

I have the following code:

Dim StrSQLUNAs String ="[UN]"Dim StrSQLPWAs String ="[PW]"Dim StrServerAs String ="[IP]\[INSTANCE]"Dim StrDBAs String ="[DB]"Dim strTimeOutAs String ="Connection Timeout=0;"Dim pStrSQLConnAs String ="Server=" & StrServer &";Database=" & StrDB &";User Id=" & StrSQLUN &";Password=" & StrSQLPW &";" & strTimeOutDim sqlConnAs New SqlClient.SqlConnection(pStrSQLConn)If sqlConn.State = ConnectionState.ClosedThen sqlConn.Open()

This has basically been take from the existing code on the website, but changing to the server details. I had just started dev'ing this app when the admins decided to move the SQL server over to a different server. It was working on the old one, but the new one doesn't. It just times out after whatever time you put in the timeout variable. 0=unlimited, and so just sits there.

I am also using the MSSQL Server Management Studio locally to connect to the same database, and although slow, does connect after about a minute or so. I thought they would be using the same type of underlying connection to access the server and database? Is this correct?

Can they put restrictions in place for this specific sort of data access?

Does anyone have any suggestions on how to resolve this issue??

Thanks for any help

Adam.

does the new server allow external connections? A lot of hosts I've used in the past only allow you to connect to the SQL database from an Asp.Net app on their webservers.

|||

Yeah it does allow external access as I am using MSSQL Mgmt Studio.

Anyway I think I have found the cause of the problem - my Cisco router. I needed to put in IP Inspect rules in for the MSSQL. Thing that confuses me still is that I could use the Mgnt Studio and I thought it would be accessing it in the same way. Anyway - it still seems to work - albeit with random connection times (eg last night it was connecting in a second or to and now taking about 5mins!)

Anyway have experience in this area??

Thanks

Adam.

Local Variables in User Defined Functions

I'm having a problem declaring variables in UDFs. Are they allowed? Can someone send me some syntax to see what I am doing wrong?You can only declare local variables in scalar and table-valued functions. To understand the difference go to Templates tab of Object Browser in QA and select Functions folder.|||I am using a table function with the following syntax:

create function dbo.TestFunction (@.InputVariable int)
returns table
with encryption
as

declare @.LocalVariable smalldatetime

select @.LocalVariable = ABCDate from ABCTable where rowid = @.InputVariable

return(
select * from XYZTable where XYZDate < @.LocalVariable
)

Any thoughts?

Originally posted by rdjabarov
You can only declare local variables in scalar and table-valued functions. To understand the difference go to Templates tab of Object Browser in QA and select Functions folder.|||It appears you're confusing the syntax of inline function with table-valued function. If you want "returns table" then you can't have any other statement except for "return (select...)", while if you want to have local variables then you need "returns @.tbl table (<structure>) with encryption as begin...<other statements>...end" Review the templates in QA and make a decision.|||gtocha...thanks

Originally posted by rdjabarov
It appears you're confusing the syntax of inline function with table-valued function. If you want "returns table" then you can't have any other statement except for "return (select...)", while if you want to have local variables then you need "returns @.tbl table (<structure>) with encryption as begin...<other statements>...end" Review the templates in QA and make a decision.

Local variables in stored procedures

Hi!
I'm using SQL Server 7.0 and it is a multiuser application.
My problem is that sometimes during parallel calls to a stored procedure it
produces wrong results. It takes between 5 to 20 sec to execute the stored
procedure.
To be able to know why the result sometimes is wrong I will log some values
from local variables.
My guess is that my part-result from some lookups are overwritten.
But until I get enough log-results to analyse I have a couple of questions:
- Are local variables overwritten by another call to the same procedure?
- Can I save part-result in a more secure way? I still want to have the
possibilty to call the procedure in a parallel way.
- As a last option. Is there a simple way to forbid parallel calls to a
procedure? (I have read some about "set transaction isolation level
serializable" but I'm afraid it has too large impact on other calls that
questions the same tables that are in use in the stored procedure)
Hope someone has a godd answer to give.
Best regards
SvenneSvenne
Can you show us your SP's call and how do you handle local variables within
SP?
If you have SELECT/UPDATE/DELETE/INSERT operations within a SP try to wrap
it into BEGIN TRAN ...COMMIT commands
"Svenne" <sasodergren@.hotmail.com> wrote in message
news:%23Y0MMBp7FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I'm using SQL Server 7.0 and it is a multiuser application.
> My problem is that sometimes during parallel calls to a stored procedure
> it produces wrong results. It takes between 5 to 20 sec to execute the
> stored procedure.
> To be able to know why the result sometimes is wrong I will log some
> values from local variables.
> My guess is that my part-result from some lookups are overwritten.
> But until I get enough log-results to analyse I have a couple of
> questions:
> - Are local variables overwritten by another call to the same procedure?
> - Can I save part-result in a more secure way? I still want to have the
> possibilty to call the procedure in a parallel way.
> - As a last option. Is there a simple way to forbid parallel calls to a
> procedure? (I have read some about "set transaction isolation level
> serializable" but I'm afraid it has too large impact on other calls that
> questions the same tables that are in use in the stored procedure)
> Hope someone has a godd answer to give.
> Best regards
> Svenne
>

Local variables in stored proc

How do you declare and then SELECT a value for a local variable within
stored procedure, increment the value and then use in an Insert
statement? Thanks

Any sites that explain this syntax for SQL Server 2000? Thanks
hals_left

CREATE PROCEDURE [dbo].[InsertQualUnit]
@.QualRef tinyint,
@.UnitRef tinyint,
@.UnitGroupRef tinyint,

// this needs to be a local var not an output param, how ?
@.UnitPosition tinyint Output

AS

// Assign a value to the the variable from a SELECT query, how ?
SELECT @.UnitPosition= SELECT MAX(UnitPosition) FROM tblUnitGroup
WHERE QualRef=@.QualRef AND UnitRef=@.UnitRef AND
UnitGroupRef=@.UnitGroupRef

// inc the value
@.UnitPosition+=1

// Use the new value in another SQL statement
INSERT INTO tblQualUnits ( QualRef, UnitRef, UnitGroupRef ,
UnitPosition )
VALUES ( @.QualRef, @.UnitRef, @.UnitGroupRef , @.UnitPosition)
GO-- MODIFIED STORED PROC:
CREATE PROCEDURE [dbo].[InsertQualUnit]
@.QualRef tinyint,
@.UnitRef tinyint,
@.UnitGroupRef tinyint

AS

-- this needs to be a local var not an output param, how ?
declare @.UnitPosition tinyint

-- Assign a value to the the variable from a SELECT query, how ?
SELECT @.UnitPosition= MAX(UnitPosition)
FROM tblUnitGroup
WHERE QualRef=@.QualRef AND UnitRef=@.UnitRef AND
UnitGroupRef=@.UnitGroupRef

-- inc the value
select @.UnitPosition=@.UnitPosition+1

-- Use the new value in another SQL statement
INSERT INTO tblQualUnits ( QualRef, UnitRef, UnitGroupRef ,
UnitPosition )
VALUES ( @.QualRef, @.UnitRef, @.UnitGroupRef , @.UnitPosition)|||See below and the following link.
http://www.google.co.uk/groups?selm...%40giganews.com

But your proc looks a bit strange. Why not just make the key (qualref,
unitref, unitgroupref) and then increment a quantity column? Like:

UPDATE tblQualUnits
SET unit_quantity = unit_quantity + 1
WHERE qualref = @.qualref
AND unitref = @.unitref
AND unitgroupref = @.unitgroupref

Otherwise your table is just an accumulator of redundant rows.

CREATE PROCEDURE [dbo].[InsertQualUnit]
@.qualref TINYINT,
@.unitref TINYINT,
@.unitgroupref TINYINT
AS

INSERT INTO tblQualUnits (qualref, unitref, unitgroupref, unitposition)

SELECT @.qualref, @.unitref, @.unitgroupref,
COALESCE(MAX(unitposition),0)+1
FROM tblUnitGroup
WHERE qualref = @.qualref
AND unitref = @.unitref
AND unitgroupref = @.unitgroupref
GO

--
David Portas
SQL Server MVP
--|||Thankyou for the quick reply.|||Thanks

Local Variable in stored procedure

Hi ,
I need to store a variable which has length more than 8000 bytes..Is there
is a workaround as varchar column takes only 8000 bytes/chracters..Text
cannot be used as local variable..
Pls suggest..
Regards
rectHave you tried creating a temporary table in tempdb, one
column of text ?
Peter
"It's true hard work never killed anybody, but I figure,
why take the chance?"
Ronald Reagan
>--Original Message--
>Hi ,
>I need to store a variable which has length more than
8000 bytes..Is there
>is a workaround as varchar column takes only 8000
bytes/chracters..Text
>cannot be used as local variable..
>Pls suggest..
>Regards
>rect
>.
>

local variable in select statement

Hello to all,
are there that have already used a select like the
following to catch backup history rows from different SQL
server defined as linked server
select *
from @.my_system.msdb.dbo.sysdbmaintplan_history
where .........
the local variable @...... set using a cursor seams to be
not sintactically correct.
Any idea
Thanks marinoYou need to use dynamic sql if the servername changes
e.g.
declare @.cmd nvarchar(500)
set @.cmd = N'select * from ' + @.my_system +
N'.msdb.dbo.sysdbmaintplan_history'
exec sp_executesql @.cmd
For more on dynamic sql see
http://www.algonet.se/~sommar/dynamic_sql.html
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Marino Prandini" <marino_prandini@.hotmail.com> wrote in message
news:181101c3aeb4$3af029b0$a601280a@.phx.gbl...
Hello to all,
are there that have already used a select like the
following to catch backup history rows from different SQL
server defined as linked server
select *
from @.my_system.msdb.dbo.sysdbmaintplan_history
where .........
the local variable @...... set using a cursor seams to be
not sintactically correct.
Any idea
Thanks marino

local variable assignment in CREATE TRIGGER

Hi Guys,

i'm batttling with the below Trigger creation

__________________________________________________ _
CREATE TRIGGER dbo.Fochini_Insert ON dbo.FochiniTable AFTER INSERT AS
BEGIN
DECLARE @.v_object_key VARCHAR(80)
DECLARE @.v_object_name VARCHAR(40)
DECLARE @.v_object_verb VARCHAR(40)
DECLARE @.v_datetime DATETIME

SELECT ins.Cust_Id INTO @.v_object_key FROM inserted ins <-- my problem area!!
SET @.v_object_name = 'FochiniTable'
SET @.v_object_verb = 'Create'
SET @.v_datetime = GETDATE()

IF ( USER <> 'webuser' )
INSERT INTO dbo.xworlds_events (connector_id, object_key, object_name, object_verb, event_priority, event_time, event_status, event_comment)
VALUES ('Fochini', @.v_object_key, @.v_object_name, @.v_object_verb, '1', @.v_datetime,'0', 'Triggered by Customer CREATE')

END
________________________________________________

i'm trying to get the INSERTED variable from table FochiniTable on colomn Cust_Id

and the statement: SELECT ins.Cust_Id INTO @.v_object_key FROM inserted ins - is failing [still a newbie on mssql server 2000]

any help will be appreciated
lehare.solved by modifying the errored line with

SELECT @.v_object_key = ins.Cust_Id FROM inserted ins

thanx any wayz

local VARCHAR and memory allocation in SP

I have an sp that includes something like this:
DECLARE @.s varchar(8000), @.sw varchar(128)
SET @.s = ''
WHILE ([some condition]) BEGIN
SELECT @.sw = [some value]
SET @.s = @.s + @.sw
END
Question: When I declare @.s, are all possible 8000(+) bytes allocated?
And every time I append to it's value, is the memory freed, and another
8000+ bytes are allocated? Obviously this would not bode well for
something that runs often, with many iterations, which it is.My concern, by the way, is memory fragmentation in the server due to
the large number of allocations/frees that are happening. However, I
would imagine that using a larger block for each alloc/free would be
better than a smaller block, in this respect. But, I'm no guru on such
things.
Does anyone have advice?

Local user XP -> remote XP

HI Guys,
Got the following scenario:
Client machine: Windows XP Pro (SP2), logged in as local user
Server machine: Windows XP Pro (SP2), running SQL Server 2000
both machines in same Domain.
I am attempting to connect from the client to the server using SQL
Server authentication, which is definitely enabled as an option on the
remote machine. SQL Client Network lists TCP/IP and Named Pipes
enabled, with TCP/IP highest. Neither connecting through TCP/IP nor
name pipes (using isql) succeeds, and I get a generic "SQL Server does
not exist" type message through query analyzer.
This started with a problem running an ASP.NET website against a remote
database, but I've verified I can 1) Connect to the server using
Windows Authentication, when I'm logged in as a domain user, 2) Connect
using the SQL Server credentials, when I'm logged in as a domain user.
I wouldn't be surprised for NP to fail when I'm a local user, but
TCP/IP shouldn't be affected by who's logged in, should it?
Okay, normal bits now of "this used to work, and I'm sure I haven't
changed anything". Only thing I have done recently was install the
latest MS updates as delivered through Automatic Updates. I'm not sure
if anything has changed on the server, since it's another devs
development machine, but I wouldn't expect him to have been "playing"
in this area.
I was initially unconcerned by this problem, since our live webserver
talks directly to a local instance of SQL, so all should be well. Until
I remembered that as part of the next rollout, we plan to move the
databases onto a separate box. So I'd like to understand this problem
in the development area in case it happens on live as well.
Sorry for rambling on. And I've probably forgotten something important
too. Thanks in advance for any help.
DamienHi,
I guess secuzrity is blocking your request on the other machine. YOu
have to open the ports of SQL Server on the hosting machine that the
client can connect to an instance. The default port of SQL Server is
1433, but that can be different on your machine if you changed another
one on installation time. For more information about Windows XP SP2 and
SQL Server read the following article.
http://support.microsoft.com/defaul...kb;en-us;841249
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Jens wrote:
> Hi,
> I guess secuzrity is blocking your request on the other machine. YOu
> have to open the ports of SQL Server on the hosting machine that the
> client can connect to an instance. The default port of SQL Server is
> 1433, but that can be different on your machine if you changed another
> one on installation time. For more information about Windows XP SP2 and
> SQL Server read the following article.
> http://support.microsoft.com/defaul...kb;en-us;841249
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
Doh! The thing I forgot to do was... Check that TCP connections worked
directly. Turned out that all my previous succesful connections were NP
connections. So I'll be looking at the firewall then...
Cheers,
Damien

LOCAL USER vs GLOBAL USER set in agent service

My replication is not working. I was able to create a transactional replication successfully. However, when i tried starting the agent on the snapshot, it wouldn't work. I figure that it was because my agent service login is different from that of the subscriber. my publisher and distributor is on local user whereas my subsciber sql services use our global login.

Thank you in advance. Good day!

Can you post the error message you're seeing while trying to start snapshot agent job? From that we can confirm whether the issue you're seeing is due to agent account setup. Sometimes the issue may be caused by local user doesn't have permission on network resources.

Thanks,

Zhiqiang Feng

|||

mongol wrote:

My replication is not working. I was able to create a transactional replication successfully. However, when i tried starting the agent on the snapshot, it wouldn't work. I figure that it was because my agent service login is different from that of the subscriber. my publisher and distributor is on local user whereas my subsciber sql services use our global login.

Thank you in advance. Good day!

hi mongol

be sure the the login used by the service has an NTFS permission in the

replication shared folder

and the services has also write permission to the replication target database