Backing up secondary DB in log shipping ...

Hello can someone clear this up for me?
In BOL "Log Shipping Tips and Best Practices" is the following
recommendation:
"Generating Database Backups from the Secondary
Since the log shipped database is only a small increment of time out
of sync with the primary database, it is a good idea to use it to
generate the full database backups. This would keep the primary
production server free of the potential overhead and contention
incurred by a daily (or scheduled) backup. "
In my experience and from searching other threads in this group this
is actually not true - backing up the log shipped database will not
work, it will fail with the error:
"Database is in warm-standby state (set by executing RESTORE WITH
STANDBY) and cannot be backed up until the entire load sequence is
completed."
Am I missing something or is BOL just being misleading?
thanks,
Peter
Peter,
http://support.microsoft.com/kb/311115 contains the correction.
Note: With SQL Server 2005, you it might be possible to create a snapshot
database over the secondary and back that up. (But I have no experience
with trying.)
RLF
<peter.wismer@.gmail.com> wrote in message
news:1187280994.613717.77730@.l22g2000prc.googlegro ups.com...
> Hello can someone clear this up for me?
> In BOL "Log Shipping Tips and Best Practices" is the following
> recommendation:
> "Generating Database Backups from the Secondary
> Since the log shipped database is only a small increment of time out
> of sync with the primary database, it is a good idea to use it to
> generate the full database backups. This would keep the primary
> production server free of the potential overhead and contention
> incurred by a daily (or scheduled) backup. "
> In my experience and from searching other threads in this group this
> is actually not true - backing up the log shipped database will not
> work, it will fail with the error:
> "Database is in warm-standby state (set by executing RESTORE WITH
> STANDBY) and cannot be backed up until the entire load sequence is
> completed."
> Am I missing something or is BOL just being misleading?
> thanks,
> Peter
>
|||On Aug 16, 1:12 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> Peter,
> http://support.microsoft.com/kb/311115contains the correction.
> Note: With SQL Server 2005, you it might be possible to create a snapshot
> database over the secondary and back that up. (But I have no experience
> with trying.)
> RLF<peter.wis...@.gmail.com> wrote in message
> news:1187280994.613717.77730@.l22g2000prc.googlegro ups.com...
>
>
>
>
>
> - Show quoted text -
Thanks very much Russell (I was beginning to mistrust my command of
the english language - thought I might need to find a Canadian
translation of BOL...)
Peter
|||I think it was a case of wishes versus reality. - RLF
<peter.wismer@.gmail.com> wrote in message
news:1187288061.528821.290710@.j4g2000prf.googlegro ups.com...
> On Aug 16, 1:12 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> Thanks very much Russell (I was beginning to mistrust my command of
> the english language - thought I might need to find a Canadian
> translation of BOL...)
> Peter
>

Backing up secondary DB in log shipping ...

Hello can someone clear this up for me?
In BOL "Log Shipping Tips and Best Practices" is the following
recommendation:
"Generating Database Backups from the Secondary
Since the log shipped database is only a small increment of time out
of sync with the primary database, it is a good idea to use it to
generate the full database backups. This would keep the primary
production server free of the potential overhead and contention
incurred by a daily (or scheduled) backup. "
In my experience and from searching other threads in this group this
is actually not true - backing up the log shipped database will not
work, it will fail with the error:
"Database is in warm-standby state (set by executing RESTORE WITH
STANDBY) and cannot be backed up until the entire load sequence is
completed."
Am I missing something or is BOL just being misleading?
thanks,
PeterPeter,
http://support.microsoft.com/kb/311115 contains the correction.
Note: With SQL Server 2005, you it might be possible to create a snapshot
database over the secondary and back that up. (But I have no experience
with trying.)
RLF
<peter.wismer@.gmail.com> wrote in message
news:1187280994.613717.77730@.l22g2000prc.googlegroups.com...
> Hello can someone clear this up for me?
> In BOL "Log Shipping Tips and Best Practices" is the following
> recommendation:
> "Generating Database Backups from the Secondary
> Since the log shipped database is only a small increment of time out
> of sync with the primary database, it is a good idea to use it to
> generate the full database backups. This would keep the primary
> production server free of the potential overhead and contention
> incurred by a daily (or scheduled) backup. "
> In my experience and from searching other threads in this group this
> is actually not true - backing up the log shipped database will not
> work, it will fail with the error:
> "Database is in warm-standby state (set by executing RESTORE WITH
> STANDBY) and cannot be backed up until the entire load sequence is
> completed."
> Am I missing something or is BOL just being misleading?
> thanks,
> Peter
>|||On Aug 16, 1:12 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> Peter,
> http://support.microsoft.com/kb/311115contains the correction.
> Note: With SQL Server 2005, you it might be possible to create a snapshot
> database over the secondary and back that up. (But I have no experience
> with trying.)
> RLF<peter.wis...@.gmail.com> wrote in message
> news:1187280994.613717.77730@.l22g2000prc.googlegroups.com...
>
> > Hello can someone clear this up for me?
> > In BOL "Log Shipping Tips and Best Practices" is the following
> > recommendation:
> > "Generating Database Backups from the Secondary
> > Since the log shipped database is only a small increment of time out
> > of sync with the primary database, it is a good idea to use it to
> > generate the full database backups. This would keep the primary
> > production server free of the potential overhead and contention
> > incurred by a daily (or scheduled) backup. "
> > In my experience and from searching other threads in this group this
> > is actually not true - backing up the log shipped database will not
> > work, it will fail with the error:
> > "Database is in warm-standby state (set by executing RESTORE WITH
> > STANDBY) and cannot be backed up until the entire load sequence is
> > completed."
> > Am I missing something or is BOL just being misleading?
> > thanks,
> > Peter- Hide quoted text -
> - Show quoted text -
Thanks very much Russell (I was beginning to mistrust my command of
the english language - thought I might need to find a Canadian
translation of BOL...)
Peter|||I think it was a case of wishes versus reality. - RLF
<peter.wismer@.gmail.com> wrote in message
news:1187288061.528821.290710@.j4g2000prf.googlegroups.com...
> On Aug 16, 1:12 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
>> Peter,
>> http://support.microsoft.com/kb/311115contains the correction.
>> Note: With SQL Server 2005, you it might be possible to create a snapshot
>> database over the secondary and back that up. (But I have no experience
>> with trying.)
>> RLF<peter.wis...@.gmail.com> wrote in message
>> news:1187280994.613717.77730@.l22g2000prc.googlegroups.com...
>>
>> > Hello can someone clear this up for me?
>> > In BOL "Log Shipping Tips and Best Practices" is the following
>> > recommendation:
>> > "Generating Database Backups from the Secondary
>> > Since the log shipped database is only a small increment of time out
>> > of sync with the primary database, it is a good idea to use it to
>> > generate the full database backups. This would keep the primary
>> > production server free of the potential overhead and contention
>> > incurred by a daily (or scheduled) backup. "
>> > In my experience and from searching other threads in this group this
>> > is actually not true - backing up the log shipped database will not
>> > work, it will fail with the error:
>> > "Database is in warm-standby state (set by executing RESTORE WITH
>> > STANDBY) and cannot be backed up until the entire load sequence is
>> > completed."
>> > Am I missing something or is BOL just being misleading?
>> > thanks,
>> > Peter- Hide quoted text -
>> - Show quoted text -
> Thanks very much Russell (I was beginning to mistrust my command of
> the english language - thought I might need to find a Canadian
> translation of BOL...)
> Peter
>

Backing up secondary DB in log shipping ...

Hello can someone clear this up for me?
In BOL "Log Shipping Tips and Best Practices" is the following
recommendation:
"Generating Database Backups from the Secondary
Since the log shipped database is only a small increment of time out
of sync with the primary database, it is a good idea to use it to
generate the full database backups. This would keep the primary
production server free of the potential overhead and contention
incurred by a daily (or scheduled) backup. "
In my experience and from searching other threads in this group this
is actually not true - backing up the log shipped database will not
work, it will fail with the error:
"Database is in warm-standby state (set by executing RESTORE WITH
STANDBY) and cannot be backed up until the entire load sequence is
completed."
Am I missing something or is BOL just being misleading?
thanks,
PeterPeter,
http://support.microsoft.com/kb/311115 contains the correction.
Note: With SQL Server 2005, you it might be possible to create a snapshot
database over the secondary and back that up. (But I have no experience
with trying.)
RLF
<peter.wismer@.gmail.com> wrote in message
news:1187280994.613717.77730@.l22g2000prc.googlegroups.com...
> Hello can someone clear this up for me?
> In BOL "Log Shipping Tips and Best Practices" is the following
> recommendation:
> "Generating Database Backups from the Secondary
> Since the log shipped database is only a small increment of time out
> of sync with the primary database, it is a good idea to use it to
> generate the full database backups. This would keep the primary
> production server free of the potential overhead and contention
> incurred by a daily (or scheduled) backup. "
> In my experience and from searching other threads in this group this
> is actually not true - backing up the log shipped database will not
> work, it will fail with the error:
> "Database is in warm-standby state (set by executing RESTORE WITH
> STANDBY) and cannot be backed up until the entire load sequence is
> completed."
> Am I missing something or is BOL just being misleading?
> thanks,
> Peter
>|||On Aug 16, 1:12 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> Peter,
> http://support.microsoft.com/kb/311115contains the correction.
> Note: With SQL Server 2005, you it might be possible to create a snapshot
> database over the secondary and back that up. (But I have no experience
> with trying.)
> RLF<peter.wis...@.gmail.com> wrote in message
> news:1187280994.613717.77730@.l22g2000prc.googlegroups.com...
>
>
>
>
>
>
>
>
> - Show quoted text -
Thanks very much Russell (I was beginning to mistrust my command of
the english language - thought I might need to find a Canadian
translation of BOL...)
Peter|||I think it was a case of wishes versus reality. - RLF
<peter.wismer@.gmail.com> wrote in message
news:1187288061.528821.290710@.j4g2000prf.googlegroups.com...
> On Aug 16, 1:12 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> Thanks very much Russell (I was beginning to mistrust my command of
> the english language - thought I might need to find a Canadian
> translation of BOL...)
> Peter
>

Avoiding clear text passwords and editing of packages

Hi!

I have an SQL Server where only a group of sysadmins have access to install DTSX packages. Those DTSX packages are developed by another team that does not have access to the production SQL Server. They use their own SQL Server.

In order to make it as simple as possible to install these packages by the sysadmins, I suggested the use of configuration files. The files are associated with the job that executes the package and all that has to be done to install the package is copy it to the file system or import it into the SQL Server. Developers use their configuration file, sysadmins user theirs. Nothing new here.

The problem is that some of the packages have to access some old systems and we cannot use integrated authentication. We have to use SQL authentication and therefore specify a user account and password in the connection string. If this is stored in the configuration file, it is available in clear text! If I store the configuration in the package itself using ProtectSensitiveWithPassword protection level, the sysadmins will have to edit every DTSX package to reset the connections to the production environment (the developers always send them with their development configurations) and I don't want that. If I store it in a SQL Server database, it seems the sysadmins also have to edit the package to point the package configuration to the correct database and set the configuration filter.

Another solution is to store the credentials in clear text in the configuration file but set the file system permissions on that file so only the account that executes the package can read them (this is what I'm implementing if nothing better comes up...)

Is there any other way to do this? Am I doing something wrong?

Thanks in advance.

Rui Covelo wrote:

Hi!
Another solution is to store the credentials in clear text in the configuration file but set the file system permissions on that file so only the account that executes the package can read them (this is what I'm implementing if nothing better comes up...)

Is there any other way to do this? Am I doing something wrong?

Thanks in advance.

I don't see any other way. Unless that you want to save you the time of oppening each package to change the credentials in every package; I think you are better dealing with the passwords in plain text and securing the place where they live. BTW, you can use Env. variables or a SQL table to place those connection strings if that makes you feel more secure.

This is just my opinion and not an entry for the 'best practice' book

Rafael Salas

|||

I don't see using configuration files is not that big issue that people seem to think of it. So what if they use clear text, all that means is that you need secure them correctly. You probably have a password store already. Does it have password complexity? Does it have group membership? Do passwords expire? Can you manage access centrally? If not it would seem obvious that AD is going to do a better job, so use it :)

The issue I think is more about how people have viewed file permissions in the past, or rather the lack of them. Use them correctly and will be more secure than a single password embedded in a package, and don't forget those jobs that also have the password too. AD is much harder to circumvent any controls around it. People just don't use file permissions, because it is "hard", well get it right and it is more secure, so what sounds better?

|||

I would re-iterate what Darren has said.

At some point someone somewhere needs to know what some password is to open up access to a securable. If that password is the password of a Windows AD account then so much the better. Security is one of the main things that AD is there for.

-Jamie

|||Thanks for your answers!