Create standby database without backup/restore

I would like to create a standby database without backup restore.

We currently have a continous access storage ( disk duplication)but I would like to change to logshipping instead.

The database is 1 TB large and backup and restoring would take at least 2 days.

And we already have the datafile duplicated , soI would like to use these datafiles.

How can this be done?

Have you tried to issue:

BACKUP DATABASE <mydatabase> WITH NORECOVERY

This will place the database in a recovering state where you can apply transaction logs, as long as you have log backups which contain the next LSN needing to be applied.

Create SQL cluster on 2003

I have 2 2003 servers each running a separate copy of SQL. I have purchased
a external storage Dell Powervault running RAID 5 to serve as the shared disk
space. I would like to create a SQL cluster with these 2 machines. Each SQL
server has databases that will need to be moved to the shared space. What is
the easiest way to accomplish this?
I was thinking I would need to do backup my databases from both SQL servers.
Create a cluster in 2003 cluster management
Uninstall SQL server from both SQL servers
Install SQL server as a virtual server from one of the 2003 servers.
Restore the SQL databases to the shared disk space
Am I missing anything?
First, your configuration is unsupported. A cluster must be purchased as a
cluster, not just assembled ad-hoc from components that may or may not be on
the cluster Hardware Compatibility list in order to be a supported
configuration. Some storage vendors will certify the entire platform if you
purchase installation services along with the storage device.
Second, your RAID-5 Powervault will run very slowly in a cluster. RAID-5
has significant overhead for writes. Normally a caching controller can
mitigate these issues but with clustering, all SCSI controllers for shared
storage must disable write cache. Since you have the PowerVault divided
into a single array, you will have to install SQL onto the Quorum partition,
again an unsupported configuration. Note that Clustering will work at the
RAID container level, not at the logical partition level. Data and
transaction logs will be on the same physical device so there goes another
bit of performance and recoverability. The whole purpose of SQL Clustering
is to increase availability. I don't see how this configuration will help
reach that goal.
I would talk to my Dell representative about their certified cluster
offerings rather than pursue this path.
Since you did ask for how to do something instead of whether it should be
done, here goes. Create a cluster and install an instance of SQL onto the
cluster (likely a named instance since I would guess that the local
machine(s) already use a default instance). After that, it is a simple
matter to move the databases as you would between any two SQL servers.
Windows 2003 Server has a really great clustering wizard that keeps you from
building a non-functional cluster. Once that is working, you can easily
install SQL clustering according to the instructions in BOL.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default...b;en-us;314546
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"Amy Lewis" <AmyLewis@.discussions.microsoft.com> wrote in message
news:EF12ECFD-4BA7-48DD-8605-46D045E39532@.microsoft.com...
>I have 2 2003 servers each running a separate copy of SQL. I have
>purchased
> a external storage Dell Powervault running RAID 5 to serve as the shared
> disk
> space. I would like to create a SQL cluster with these 2 machines. Each
> SQL
> server has databases that will need to be moved to the shared space. What
> is
> the easiest way to accomplish this?
> I was thinking I would need to do backup my databases from both SQL
> servers.
> Create a cluster in 2003 cluster management
> Uninstall SQL server from both SQL servers
> Install SQL server as a virtual server from one of the 2003 servers.
> Restore the SQL databases to the shared disk space
> Am I missing anything?
|||Thanks for the response. I have actually talked with Dell about this and
given the small volume of SQL database activity - they recommended this.
I have not configured my PowerVault yet - would Raid 1 be better relating
to performance? My current 2003 servers have a single RAID 5 configuration -
and the databases are stored in the normal c:\program files\.... and it
seems to be working fine for us. We only about about 20 databases - all
small (the largest is 500M) and all with less than 20 users connected at 1
time.
"Geoff N. Hiten" wrote:

> First, your configuration is unsupported. A cluster must be purchased as a
> cluster, not just assembled ad-hoc from components that may or may not be on
> the cluster Hardware Compatibility list in order to be a supported
> configuration. Some storage vendors will certify the entire platform if you
> purchase installation services along with the storage device.
> Second, your RAID-5 Powervault will run very slowly in a cluster. RAID-5
> has significant overhead for writes. Normally a caching controller can
> mitigate these issues but with clustering, all SCSI controllers for shared
> storage must disable write cache. Since you have the PowerVault divided
> into a single array, you will have to install SQL onto the Quorum partition,
> again an unsupported configuration. Note that Clustering will work at the
> RAID container level, not at the logical partition level. Data and
> transaction logs will be on the same physical device so there goes another
> bit of performance and recoverability. The whole purpose of SQL Clustering
> is to increase availability. I don't see how this configuration will help
> reach that goal.
> I would talk to my Dell representative about their certified cluster
> offerings rather than pursue this path.
> Since you did ask for how to do something instead of whether it should be
> done, here goes. Create a cluster and install an instance of SQL onto the
> cluster (likely a named instance since I would guess that the local
> machine(s) already use a default instance). After that, it is a simple
> matter to move the databases as you would between any two SQL servers.
> Windows 2003 Server has a really great clustering wizard that keeps you from
> building a non-functional cluster. Once that is working, you can easily
> install SQL clustering according to the instructions in BOL.
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/default...b;en-us;314546
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
>
> "Amy Lewis" <AmyLewis@.discussions.microsoft.com> wrote in message
> news:EF12ECFD-4BA7-48DD-8605-46D045E39532@.microsoft.com...
>
>
|||I am assuming a PV 220S with 14 slots.
2ea RAID-1 drives for Quorum and MSDTC (36GB 15KRPM) Normal best practices
has them apart but with your small scale combining them should be safe.
2ea RAID-1 drives for Logs (73GB 15KRPM)
2ea RAID-1 drives for Data (146GB 15KRPM)
That leaves 8 slots for future expansion. Make sure you have blanks so the
airflow works correctly. You can adjust the sizes of the drives to meet
your needs, but try to keep the Quorum and Logs drives at 15KRPM. The speed
definitely makes a difference. Since you are in a cluster configuration,
the physical location of the drives in the individual slots makes no
difference. This will give you a decent performing system that is also
pretty reliable and recoverable.
Geoff N. Hiten
Microsoft SQL Server MVP
"Amy Lewis" <AmyLewis@.discussions.microsoft.com> wrote in message
news:F0ECD371-DAB4-433C-8445-750EB2D45AA3@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. I have actually talked with Dell about this and
> given the small volume of SQL database activity - they recommended this.
> I have not configured my PowerVault yet - would Raid 1 be better relating
> to performance? My current 2003 servers have a single RAID 5
> configuration -
> and the databases are stored in the normal c:\program files\.... and it
> seems to be working fine for us. We only about about 20 databases - all
> small (the largest is 500M) and all with less than 20 users connected at 1
> time.
> "Geoff N. Hiten" wrote:

CREATE Credential Secret Storage

I am hoping to use SQL Server Agent 2k5 to run jobs in the security context of another account. I have successfully done this, so I know it works.

My question is: what mechanism does SQL 2k5 use to encrypt the secret that I enter when setting up the credentials for the job account using the CREATE CREDENTIAL statement? Is the secret protected using DPAPI? If so, what precautions must I take if I when managing the SQL Server Agent service account?

Many thanks, Kevin

The secret part of the credential is encrypted by the service master key (SMK). The SMK itself is protected by DPAPI using the SQL Server service account credentials. Changing the Agent service account shouldn't have any impact on the credentials, but changing the SQL Server service account will have an impact. If you change the service account manually, you will end up with a SMK that cannot be decrypted (in newer SQL Server builds we are mitigating this problem to some extent). My strong advice is to always have available an up-to-date backup of the SMK. This way, you can always restore the SMK if it becomes undecryptable. Losing the SMK is equivalent with losing all your encrypted data, so you should be extra careful about keeping backups of the SMK.

As a side note, SMK backups store the SMK encrypted with a password using 3DES and on Windows 2003 we enforce the password policy strength settings as we do for SQL Server logins.

For some additional information on the SMK, you can also look at the following:

http://blogs.msdn.com/lcris/archive/2005/07/08/437048.aspx

Thanks
Laurentiu

|||The credential secret is protected by service master key (see CREATE CREDENTIAL in BOL for details) which is in turm protected by DPAPI.

However encrypted credential secred as well as encrypted service master key is persisted in master database. Thus anybody who runs under the same windows account as SQLServer (i.e. NETWORK SERVICE by default) have an access to it. There is no published or unpublished interface to read these secrets, but this data can be eventually obtained from master.mdf file or even by implementing xp on a live server.

In short your job account credentials are protected by one or more ACLs granted to account under which SQLServer runs. It will be a good practice to run SQLServer under a unique account, so that no other machine task or service is using it. In that case only NT box admin has access to it. But this is normal, since any secret on local machine is available to NTBox admin.