Question on restoring encrypted databases on different servers

I have run into problems trying to use backed-up encrypted databases. I have an ASP.Net/SqlServer 2005 website for which I have production, test, and development environments, each on its own box. I have set up encryption using Master Keys, Certificates, and Symmetric Keys using the following commands:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'

CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.

All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.

I am running into problems when I back up the development database and try to move it to the test environment. When I do the move, and then try to access the encrypted data, I get an error "An error occurred during decryption", even though they are the "same" database (I originally detached and copied the development database to the test box when I set up the test environment) with the same password.

I then try the following commands, which I used when I brought the development database over to the test box the first time:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

This caused the following error:

"An error occurred while decrypting certificate 'CERTIFICATE_NAME' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable."

So, how do you set up encrypted databases so that you can backup from one database server and restore on another? This problem will be very important when I set up a backup production database server.

From the error message and your description, I am guessing that somehow while moving the DB from one environment to another the metadata for either the master key (DBMK) or the certificate got somehow corrupted, or rather out of sync.

I am assuming that on the test environment the DB and an earlier copy of both the cert and DBMK (or only the DBMK) exist; based on that, this is my guess. On the dev environment you have DBMK_1 protecting cert_pvk_1, but when the DB is moved to the test environment, one of the following situations happened:

1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

Can you please verify if my assumption is correct? If so, I can try to reproduce the scenario in my environment to verify. If my assumption is not correct we will need more information. What are the OS versions running in both environments? Is there any significant difference between both environments (i.e. OS language, any third party software that may affect the crypto-API calls, etc)?

Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Also, the commands that you need to execute when moving a database to another server are:

OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

The REGENERATE option should be used only if you want to have a new master key in the database.

Thanks
Laurentiu

|||

This solves my problem. Thank you for your help.

However, it has opened up a new line of thought for me. If you don't use the ALTER MASTER KEY command in the backup database, you have to open the master key every time you want to access the encrypted data. I think I would like that requirement in the original database(it always bothered me that if you can decrypt at will if you know the name of the symmetric key and the certificate).

So, how do you make the original database require the Open Master Key Statement?

|||

You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

Thanks
Laurentiu

|||

You can use the following statement:

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

Just to verify it:

-- Should fail with error 15581:

-- Please create a master key in the database or open the master key in the session before performing this operation.

create certificate foo with subject = 'foo'

go

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'

go

-- Will succeed

create certificate foo with subject = 'foo'

go

CLOSE MASTER KEY

Go

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.

I execute this command and get:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'

Msg 15313, Level 16, State 1, Line 1

The key is not encrypted using the specified decryptor.

I try this on my original database and I get the same result - what have i done?! I'm guessing my password is wrong, but i do have the script I used to create it right in front of me, and written: and they match.

|||

Hi Sam,

I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?

thanks...

|||

I just answered a similar problem in the following thread of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Question on restoring encrypted databases on different servers

I have run into problems trying to use backed-up encrypted databases. I have an ASP.Net/SqlServer 2005 website for which I have production, test, and development environments, each on its own box. I have set up encryption using Master Keys, Certificates, and Symmetric Keys using the following commands:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'

CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.

All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.

I am running into problems when I back up the development database and try to move it to the test environment. When I do the move, and then try to access the encrypted data, I get an error "An error occurred during decryption", even though they are the "same" database (I originally detached and copied the development database to the test box when I set up the test environment) with the same password.

I then try the following commands, which I used when I brought the development database over to the test box the first time:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

This caused the following error:

"An error occurred while decrypting certificate 'CERTIFICATE_NAME' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable."

So, how do you set up encrypted databases so that you can backup from one database server and restore on another? This problem will be very important when I set up a backup production database server.

From the error message and your description, I am guessing that somehow while moving the DB from one environment to another the metadata for either the master key (DBMK) or the certificate got somehow corrupted, or rather out of sync.

I am assuming that on the test environment the DB and an earlier copy of both the cert and DBMK (or only the DBMK) exist; based on that, this is my guess. On the dev environment you have DBMK_1 protecting cert_pvk_1, but when the DB is moved to the test environment, one of the following situations happened:

1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

Can you please verify if my assumption is correct? If so, I can try to reproduce the scenario in my environment to verify. If my assumption is not correct we will need more information. What are the OS versions running in both environments? Is there any significant difference between both environments (i.e. OS language, any third party software that may affect the crypto-API calls, etc)?

Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Also, the commands that you need to execute when moving a database to another server are:

OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

The REGENERATE option should be used only if you want to have a new master key in the database.

Thanks
Laurentiu

|||

This solves my problem. Thank you for your help.

However, it has opened up a new line of thought for me. If you don't use the ALTER MASTER KEY command in the backup database, you have to open the master key every time you want to access the encrypted data. I think I would like that requirement in the original database(it always bothered me that if you can decrypt at will if you know the name of the symmetric key and the certificate).

So, how do you make the original database require the Open Master Key Statement?

|||

You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

Thanks
Laurentiu

|||

You can use the following statement:

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

Just to verify it:

-- Should fail with error 15581:

-- Please create a master key in the database or open the master key in the session before performing this operation.

create certificate foo with subject = 'foo'

go

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'

go

-- Will succeed

create certificate foo with subject = 'foo'

go

CLOSE MASTER KEY

Go

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.

I execute this command and get:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'

Msg 15313, Level 16, State 1, Line 1

The key is not encrypted using the specified decryptor.

I try this on my original database and I get the same result - what have i done?! I'm guessing my password is wrong, but i do have the script I used to create it right in front of me, and written: and they match.

|||

Hi Sam,

I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?

thanks...

|||

I just answered a similar problem in the following thread of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Question on restoring encrypted databases on different servers

I have run into problems trying to use backed-up encrypted databases. I have an ASP.Net/SqlServer 2005 website for which I have production, test, and development environments, each on its own box. I have set up encryption using Master Keys, Certificates, and Symmetric Keys using the following commands:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'

CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.

All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.

I am running into problems when I back up the development database and try to move it to the test environment. When I do the move, and then try to access the encrypted data, I get an error "An error occurred during decryption", even though they are the "same" database (I originally detached and copied the development database to the test box when I set up the test environment) with the same password.

I then try the following commands, which I used when I brought the development database over to the test box the first time:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

This caused the following error:

"An error occurred while decrypting certificate 'CERTIFICATE_NAME' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable."

So, how do you set up encrypted databases so that you can backup from one database server and restore on another? This problem will be very important when I set up a backup production database server.

From the error message and your description, I am guessing that somehow while moving the DB from one environment to another the metadata for either the master key (DBMK) or the certificate got somehow corrupted, or rather out of sync.

I am assuming that on the test environment the DB and an earlier copy of both the cert and DBMK (or only the DBMK) exist; based on that, this is my guess. On the dev environment you have DBMK_1 protecting cert_pvk_1, but when the DB is moved to the test environment, one of the following situations happened:

1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

Can you please verify if my assumption is correct? If so, I can try to reproduce the scenario in my environment to verify. If my assumption is not correct we will need more information. What are the OS versions running in both environments? Is there any significant difference between both environments (i.e. OS language, any third party software that may affect the crypto-API calls, etc)?

Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Also, the commands that you need to execute when moving a database to another server are:

OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

The REGENERATE option should be used only if you want to have a new master key in the database.

Thanks
Laurentiu

|||

This solves my problem. Thank you for your help.

However, it has opened up a new line of thought for me. If you don't use the ALTER MASTER KEY command in the backup database, you have to open the master key every time you want to access the encrypted data. I think I would like that requirement in the original database(it always bothered me that if you can decrypt at will if you know the name of the symmetric key and the certificate).

So, how do you make the original database require the Open Master Key Statement?

|||

You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

Thanks
Laurentiu

|||

You can use the following statement:

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

Just to verify it:

-- Should fail with error 15581:

-- Please create a master key in the database or open the master key in the session before performing this operation.

create certificate foo with subject = 'foo'

go

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'

go

-- Will succeed

create certificate foo with subject = 'foo'

go

CLOSE MASTER KEY

Go

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.

I execute this command and get:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'

Msg 15313, Level 16, State 1, Line 1

The key is not encrypted using the specified decryptor.

I try this on my original database and I get the same result - what have i done?! I'm guessing my password is wrong, but i do have the script I used to create it right in front of me, and written: and they match.

|||

Hi Sam,

I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?

thanks...

|||

I just answered a similar problem in the following thread of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Question on restoring encrypted databases on different servers

I have run into problems trying to use backed-up encrypted databases. I have an ASP.Net/SqlServer 2005 website for which I have production, test, and development environments, each on its own box. I have set up encryption using Master Keys, Certificates, and Symmetric Keys using the following commands:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'

CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.

All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.

I am running into problems when I back up the development database and try to move it to the test environment. When I do the move, and then try to access the encrypted data, I get an error "An error occurred during decryption", even though they are the "same" database (I originally detached and copied the development database to the test box when I set up the test environment) with the same password.

I then try the following commands, which I used when I brought the development database over to the test box the first time:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

This caused the following error:

"An error occurred while decrypting certificate 'CERTIFICATE_NAME' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable."

So, how do you set up encrypted databases so that you can backup from one database server and restore on another? This problem will be very important when I set up a backup production database server.

From the error message and your description, I am guessing that somehow while moving the DB from one environment to another the metadata for either the master key (DBMK) or the certificate got somehow corrupted, or rather out of sync.

I am assuming that on the test environment the DB and an earlier copy of both the cert and DBMK (or only the DBMK) exist; based on that, this is my guess. On the dev environment you have DBMK_1 protecting cert_pvk_1, but when the DB is moved to the test environment, one of the following situations happened:

1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

Can you please verify if my assumption is correct? If so, I can try to reproduce the scenario in my environment to verify. If my assumption is not correct we will need more information. What are the OS versions running in both environments? Is there any significant difference between both environments (i.e. OS language, any third party software that may affect the crypto-API calls, etc)?

Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Also, the commands that you need to execute when moving a database to another server are:

OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

The REGENERATE option should be used only if you want to have a new master key in the database.

Thanks
Laurentiu

|||

This solves my problem. Thank you for your help.

However, it has opened up a new line of thought for me. If you don't use the ALTER MASTER KEY command in the backup database, you have to open the master key every time you want to access the encrypted data. I think I would like that requirement in the original database(it always bothered me that if you can decrypt at will if you know the name of the symmetric key and the certificate).

So, how do you make the original database require the Open Master Key Statement?

|||

You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

Thanks
Laurentiu

|||

You can use the following statement:

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

Just to verify it:

-- Should fail with error 15581:

-- Please create a master key in the database or open the master key in the session before performing this operation.

create certificate foo with subject = 'foo'

go

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'

go

-- Will succeed

create certificate foo with subject = 'foo'

go

CLOSE MASTER KEY

Go

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.

I execute this command and get:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'

Msg 15313, Level 16, State 1, Line 1

The key is not encrypted using the specified decryptor.

I try this on my original database and I get the same result - what have i done?! I'm guessing my password is wrong, but i do have the script I used to create it right in front of me, and written: and they match.

|||

Hi Sam,

I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?

thanks...

|||

I just answered a similar problem in the following thread of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

question on reindex

Hi,
i have several tables in production whose contents are renewd totally in 1
week. So everyd day we delete ~15% records and then insert 15% new.
And after a few days, the performances drops :

TABLE level scan performed.
- Pages Scanned........................: 169617
- Extents Scanned.......................: 21630
- Extent Switches.......................: 153827
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 13.78% [21203:153828]
- Logical Scan Fragmentation ..............: 45.06%
- Extent Scan Fragmentation ...............: 52.66%
- Avg. Bytes Free per Page................: 5042.5
- Avg. Page Density (full)................: 37.70%

I can't program a dbcc reindex every day because of concurrent access (it
locks the tables too long), actually i can only program it on sunday.

What else can i do ? I can adjust the fill factor but how to find the good
value if i don't want to waste space.

The total size of the database is ~150GB.

ThxHave you tried defragmenting by using DBCC INDEXDEFRAG?

Unlike DBCC DBREINDEX (or the index building operation in general),
DBCC INDEXDEFRAG is an online operation. It does not hold locks long
term and thus will not block running queries or updates

Denis the SQL Menace
http://sqlservercode.blogspot.com/
paul wrote:

Quote:

Originally Posted by

Hi,
i have several tables in production whose contents are renewd totally in 1
week. So everyd day we delete ~15% records and then insert 15% new.
And after a few days, the performances drops :
>
TABLE level scan performed.
- Pages Scanned........................: 169617
- Extents Scanned.......................: 21630
- Extent Switches.......................: 153827
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 13.78% [21203:153828]
- Logical Scan Fragmentation ..............: 45.06%
- Extent Scan Fragmentation ...............: 52.66%
- Avg. Bytes Free per Page................: 5042.5
- Avg. Page Density (full)................: 37.70%
>
I can't program a dbcc reindex every day because of concurrent access (it
locks the tables too long), actually i can only program it on sunday.
>
What else can i do ? I can adjust the fill factor but how to find the good
value if i don't want to waste space.
>
The total size of the database is ~150GB.
>
Thx

question on memory

Win2K Advanced Server
SQL2K Enterprise
I need to set up 1 big fat Disaster Recovery box for my 6 production boxes.
I will do this by putting 6 instances of SQL on the DR box. I know that
there is an 8 gig max (using AWE) limit. But what I don't know is can each
instance have its own 8 gigs, or do all 6 instances need to share the same 8
gigs? Also, would switching to Win2K3 help me to be able to use more RAM?
Basically, I dont want to tell anyone to order 48 gigs or RAM (6 instances
with 8 gigs each) when Im only going to be able to use 8.
TIA, ChrisR
Chris,
Each instance can use it's own block of memory but I don't think Win2K Adv
server can use more than 8GB. I believe you need Data Center for more than
8GB. Certainly for 48GB. Do you need 8GB for each instance? Do each of
the 6 Prod boxes have 8GB now? Does your DR plan say you need to run all 6
instances on the one box at the same time? Seems kind of silly to have 6
individual boxes in prod and expect to run all 6 instances at once on one
box. In any case Win2K3 will not allow you to use any more ram but it does
have certain performance enhancements over 2K that you should take advantage
of.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:euDyuWiYFHA.2380@.tk2msftngp13.phx.gbl...
> Win2K Advanced Server
> SQL2K Enterprise
> I need to set up 1 big fat Disaster Recovery box for my 6 production
> boxes. I will do this by putting 6 instances of SQL on the DR box. I know
> that there is an 8 gig max (using AWE) limit. But what I don't know is can
> each instance have its own 8 gigs, or do all 6 instances need to share the
> same 8 gigs? Also, would switching to Win2K3 help me to be able to use
> more RAM? Basically, I dont want to tell anyone to order 48 gigs or RAM (6
> instances with 8 gigs each) when Im only going to be able to use 8.
>
> TIA, ChrisR
>
|||I typed out a reply yesterday but I guess I forgot to hit the reply button.
No I don't need 8 gigs per instance, but 6 boxes sharing 8 gigs wont do
either. Yes, all of them may need to be on at once.
Thanks Andrew.
CR
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23MHGH$jYFHA.1152@.tk2msftngp13.phx.gbl...
> Chris,
> Each instance can use it's own block of memory but I don't think Win2K Adv
> server can use more than 8GB. I believe you need Data Center for more
> than 8GB. Certainly for 48GB. Do you need 8GB for each instance? Do
> each of the 6 Prod boxes have 8GB now? Does your DR plan say you need to
> run all 6 instances on the one box at the same time? Seems kind of silly
> to have 6 individual boxes in prod and expect to run all 6 instances at
> once on one box. In any case Win2K3 will not allow you to use any more
> ram but it does have certain performance enhancements over 2K that you
> should take advantage of.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:euDyuWiYFHA.2380@.tk2msftngp13.phx.gbl...
>

question on memory

Win2K Advanced Server
SQL2K Enterprise
I need to set up 1 big fat Disaster Recovery box for my 6 production boxes.
I will do this by putting 6 instances of SQL on the DR box. I know that
there is an 8 gig max (using AWE) limit. But what I don't know is can each
instance have its own 8 gigs, or do all 6 instances need to share the same 8
gigs? Also, would switching to Win2K3 help me to be able to use more RAM?
Basically, I dont want to tell anyone to order 48 gigs or RAM (6 instances
with 8 gigs each) when Im only going to be able to use 8.
TIA, ChrisRChris,
Each instance can use it's own block of memory but I don't think Win2K Adv
server can use more than 8GB. I believe you need Data Center for more than
8GB. Certainly for 48GB. Do you need 8GB for each instance? Do each of
the 6 Prod boxes have 8GB now? Does your DR plan say you need to run all 6
instances on the one box at the same time? Seems kind of silly to have 6
individual boxes in prod and expect to run all 6 instances at once on one
box. In any case Win2K3 will not allow you to use any more ram but it does
have certain performance enhancements over 2K that you should take advantage
of.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:euDyuWiYFHA.2380@.tk2msftngp13.phx.gbl...
> Win2K Advanced Server
> SQL2K Enterprise
> I need to set up 1 big fat Disaster Recovery box for my 6 production
> boxes. I will do this by putting 6 instances of SQL on the DR box. I know
> that there is an 8 gig max (using AWE) limit. But what I don't know is can
> each instance have its own 8 gigs, or do all 6 instances need to share the
> same 8 gigs? Also, would switching to Win2K3 help me to be able to use
> more RAM? Basically, I dont want to tell anyone to order 48 gigs or RAM (6
> instances with 8 gigs each) when Im only going to be able to use 8.
>
> TIA, ChrisR
>|||I typed out a reply yesterday but I guess I forgot to hit the reply button.
No I don't need 8 gigs per instance, but 6 boxes sharing 8 gigs wont do
either. Yes, all of them may need to be on at once.
Thanks Andrew.
CR
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23MHGH$jYFHA.1152@.tk2msftngp13.phx.gbl...
> Chris,
> Each instance can use it's own block of memory but I don't think Win2K Adv
> server can use more than 8GB. I believe you need Data Center for more
> than 8GB. Certainly for 48GB. Do you need 8GB for each instance? Do
> each of the 6 Prod boxes have 8GB now? Does your DR plan say you need to
> run all 6 instances on the one box at the same time? Seems kind of silly
> to have 6 individual boxes in prod and expect to run all 6 instances at
> once on one box. In any case Win2K3 will not allow you to use any more
> ram but it does have certain performance enhancements over 2K that you
> should take advantage of.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:euDyuWiYFHA.2380@.tk2msftngp13.phx.gbl...
>> Win2K Advanced Server
>> SQL2K Enterprise
>> I need to set up 1 big fat Disaster Recovery box for my 6 production
>> boxes. I will do this by putting 6 instances of SQL on the DR box. I know
>> that there is an 8 gig max (using AWE) limit. But what I don't know is
>> can each instance have its own 8 gigs, or do all 6 instances need to
>> share the same 8 gigs? Also, would switching to Win2K3 help me to be able
>> to use more RAM? Basically, I dont want to tell anyone to order 48 gigs
>> or RAM (6 instances with 8 gigs each) when Im only going to be able to
>> use 8.
>>
>> TIA, ChrisR
>

question on memory

Win2K Advanced Server
SQL2K Enterprise
I need to set up 1 big fat Disaster Recovery box for my 6 production boxes.
I will do this by putting 6 instances of SQL on the DR box. I know that
there is an 8 gig max (using AWE) limit. But what I don't know is can each
instance have its own 8 gigs, or do all 6 instances need to share the same 8
gigs? Also, would switching to Win2K3 help me to be able to use more RAM?
Basically, I dont want to tell anyone to order 48 gigs or RAM (6 instances
with 8 gigs each) when Im only going to be able to use 8.
TIA, ChrisRChris,
Each instance can use it's own block of memory but I don't think Win2K Adv
server can use more than 8GB. I believe you need Data Center for more than
8GB. Certainly for 48GB. Do you need 8GB for each instance? Do each of
the 6 Prod boxes have 8GB now? Does your DR plan say you need to run all 6
instances on the one box at the same time? Seems kind of silly to have 6
individual boxes in prod and expect to run all 6 instances at once on one
box. In any case Win2K3 will not allow you to use any more ram but it does
have certain performance enhancements over 2K that you should take advantage
of.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:euDyuWiYFHA.2380@.tk2msftngp13.phx.gbl...
> Win2K Advanced Server
> SQL2K Enterprise
> I need to set up 1 big fat Disaster Recovery box for my 6 production
> boxes. I will do this by putting 6 instances of SQL on the DR box. I know
> that there is an 8 gig max (using AWE) limit. But what I don't know is can
> each instance have its own 8 gigs, or do all 6 instances need to share the
> same 8 gigs? Also, would switching to Win2K3 help me to be able to use
> more RAM? Basically, I dont want to tell anyone to order 48 gigs or RAM (6
> instances with 8 gigs each) when Im only going to be able to use 8.
>
> TIA, ChrisR
>|||I typed out a reply yesterday but I guess I forgot to hit the reply button.
No I don't need 8 gigs per instance, but 6 boxes sharing 8 gigs wont do
either. Yes, all of them may need to be on at once.
Thanks Andrew.
CR
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23MHGH$jYFHA.1152@.tk2msftngp13.phx.gbl...
> Chris,
> Each instance can use it's own block of memory but I don't think Win2K Adv
> server can use more than 8GB. I believe you need Data Center for more
> than 8GB. Certainly for 48GB. Do you need 8GB for each instance? Do
> each of the 6 Prod boxes have 8GB now? Does your DR plan say you need to
> run all 6 instances on the one box at the same time? Seems kind of silly
> to have 6 individual boxes in prod and expect to run all 6 instances at
> once on one box. In any case Win2K3 will not allow you to use any more
> ram but it does have certain performance enhancements over 2K that you
> should take advantage of.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:euDyuWiYFHA.2380@.tk2msftngp13.phx.gbl...
>

Question on cube and dimension deployment in production

Hi, all experts here,

Would any of you here shed me any light on how could we deploy cubes and dimensions to other server rather than the one where they are created? And also what permissions at least are needed to be set up for them to be accessed in production enviornment?

Hope it is clear for your help.

Thanks a lot and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Hi Helen,

Take a look at the functionality of the Deployment Wizard: http://msdn2.microsoft.com/en-us/library/ms176121.aspx

Regarding permissions, do you mean administrative access or the ability to query data? In either case BOL is again the best place to start: http://msdn2.microsoft.com/en-us/library/ms174517.aspx. Or do you mean the ability just to connect to the cube at all? In which case this new paper on connectivity problems might help - it discusses everything might might stop you from connecting:

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/CISQL2005ASCS.mspx

Regards,

Chris

|||

Hi, Chris,

Thanks a lot for your very kind help. Definitely they've been very helpful to clear up my confusions and questions on that.

Yes, I am concerning about being able to access the cubes and dimensions after they are deployed to other production environment.

Thanks a lot again.

With best regards,

Yours sincerely,

question on changing recovery model on DB

Hi,
I've got several production DBs that I want to change the recovery model
on from simple to full. Are there any caveats I need to be aware of in
making this change? Or is it just a matter of selecting the 'Full' recovery
model under the 'Options' tab of the DBs Properties and hitting the 'OK'
button? Thanks.
Make sure you set up log backup jobs for the databases to prevent the log
file sizes getting out of hand.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"mb" <mb@.discussions.microsoft.com> wrote in message
news:2547FAF9-8BB6-407C-AFCA-E5D2F9082EB0@.microsoft.com...
> Hi,
> I've got several production DBs that I want to change the recovery model
> on from simple to full. Are there any caveats I need to be aware of in
> making this change? Or is it just a matter of selecting the 'Full'
> recovery
> model under the 'Options' tab of the DBs Properties and hitting the 'OK'
> button? Thanks.
|||mb wrote:
> Hi,
> I've got several production DBs that I want to change the recovery
> model on from simple to full. Are there any caveats I need to be
> aware of in making this change? Or is it just a matter of selecting
> the 'Full' recovery model under the 'Options' tab of the DBs
> Properties and hitting the 'OK' button? Thanks.
You must immediately perform a Full Backup or your t-logs will still get
truncated every checkpoint.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Hi,
Once you change your database from SIMPLE to FULL recovery model, you need
to recreate your backup chain by doing:-
1. A Full databaase backup
2. Schedule a transaction log backup frequently
In FULL recovery you need to perform the log backup, this will be helpful to
recover the database point-in-time as
well as help you to keep your LDF file size in control.
Have a look into Recovery model topic in books online for more detail.
Thanks
Hari
SQL SERVER MVP
"mb" <mb@.discussions.microsoft.com> wrote in message
news:2547FAF9-8BB6-407C-AFCA-E5D2F9082EB0@.microsoft.com...
> Hi,
> I've got several production DBs that I want to change the recovery model
> on from simple to full. Are there any caveats I need to be aware of in
> making this change? Or is it just a matter of selecting the 'Full'
> recovery
> model under the 'Options' tab of the DBs Properties and hitting the 'OK'
> button? Thanks.

question on changing recovery model on DB

Hi,
I've got several production DBs that I want to change the recovery model
on from simple to full. Are there any caveats I need to be aware of in
making this change? Or is it just a matter of selecting the 'Full' recovery
model under the 'Options' tab of the DBs Properties and hitting the 'OK'
button? Thanks.Make sure you set up log backup jobs for the databases to prevent the log
file sizes getting out of hand.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"mb" <mb@.discussions.microsoft.com> wrote in message
news:2547FAF9-8BB6-407C-AFCA-E5D2F9082EB0@.microsoft.com...
> Hi,
> I've got several production DBs that I want to change the recovery model
> on from simple to full. Are there any caveats I need to be aware of in
> making this change? Or is it just a matter of selecting the 'Full'
> recovery
> model under the 'Options' tab of the DBs Properties and hitting the 'OK'
> button? Thanks.|||mb wrote:
> Hi,
> I've got several production DBs that I want to change the recovery
> model on from simple to full. Are there any caveats I need to be
> aware of in making this change? Or is it just a matter of selecting
> the 'Full' recovery model under the 'Options' tab of the DBs
> Properties and hitting the 'OK' button? Thanks.
You must immediately perform a Full Backup or your t-logs will still get
truncated every checkpoint.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi,
Once you change your database from SIMPLE to FULL recovery model, you need
to recreate your backup chain by doing:-
1. A Full databaase backup
2. Schedule a transaction log backup frequently
In FULL recovery you need to perform the log backup, this will be helpful to
recover the database point-in-time as
well as help you to keep your LDF file size in control.
Have a look into Recovery model topic in books online for more detail.
Thanks
Hari
SQL SERVER MVP
"mb" <mb@.discussions.microsoft.com> wrote in message
news:2547FAF9-8BB6-407C-AFCA-E5D2F9082EB0@.microsoft.com...
> Hi,
> I've got several production DBs that I want to change the recovery model
> on from simple to full. Are there any caveats I need to be aware of in
> making this change? Or is it just a matter of selecting the 'Full'
> recovery
> model under the 'Options' tab of the DBs Properties and hitting the 'OK'
> button? Thanks.

question on changing recovery model on DB

Hi,
I've got several production DBs that I want to change the recovery model
on from simple to full. Are there any caveats I need to be aware of in
making this change? Or is it just a matter of selecting the 'Full' recovery
model under the 'Options' tab of the DBs Properties and hitting the 'OK'
button? Thanks.Make sure you set up log backup jobs for the databases to prevent the log
file sizes getting out of hand.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"mb" <mb@.discussions.microsoft.com> wrote in message
news:2547FAF9-8BB6-407C-AFCA-E5D2F9082EB0@.microsoft.com...
> Hi,
> I've got several production DBs that I want to change the recovery model
> on from simple to full. Are there any caveats I need to be aware of in
> making this change? Or is it just a matter of selecting the 'Full'
> recovery
> model under the 'Options' tab of the DBs Properties and hitting the 'OK'
> button? Thanks.|||mb wrote:
> Hi,
> I've got several production DBs that I want to change the recovery
> model on from simple to full. Are there any caveats I need to be
> aware of in making this change? Or is it just a matter of selecting
> the 'Full' recovery model under the 'Options' tab of the DBs
> Properties and hitting the 'OK' button? Thanks.
You must immediately perform a Full Backup or your t-logs will still get
truncated every checkpoint.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi,
Once you change your database from SIMPLE to FULL recovery model, you need
to recreate your backup chain by doing:-
1. A Full databaase backup
2. Schedule a transaction log backup frequently
In FULL recovery you need to perform the log backup, this will be helpful to
recover the database point-in-time as
well as help you to keep your LDF file size in control.
Have a look into Recovery model topic in books online for more detail.
Thanks
Hari
SQL SERVER MVP
"mb" <mb@.discussions.microsoft.com> wrote in message
news:2547FAF9-8BB6-407C-AFCA-E5D2F9082EB0@.microsoft.com...
> Hi,
> I've got several production DBs that I want to change the recovery model
> on from simple to full. Are there any caveats I need to be aware of in
> making this change? Or is it just a matter of selecting the 'Full'
> recovery
> model under the 'Options' tab of the DBs Properties and hitting the 'OK'
> button? Thanks.