Setting Up Encryption For Large Databases

Jun 19, 2007

We are going to be getting a new system to house our large databases. Some of these databases will need to be encrypted. Ideally we'd like to set up encryption in SQL server rather then have to purchase an appliance to encrypt the data. What is the trade off of using SQL server 2005 to encrypt data? Does performance suffer a great deal with a large database (500g-4tb)? Also, how much more/less sure is the encryption for SQL Server? Some of our users need to be able to read the data as unencrypted while working, but we need to keep the system as secure as possible. Answers to any of these questions would be incredible helpful.

Thanks,

Robb S.

View 4 Replies

Setting Up SQL Server Encryption Between Multiple Servers And Databases

Feb 23, 2007

Greetings.

I have a setup/deployment question regarding SQL Server Encryption.

Internal database encrypts data in 3 different tables. This could execute on any one of 6 different servers.
The tables with encrypted data are replicated to another database on different servers (3)

How should the keys/certificates be created so that the data in the replicated database can be decrypted?

In my test scenario so far, I have been unable to have the second database decrypt the data that was encrypted on the first database (currently on the same server).

-- Create Database Master Key
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'p@ssw0rd'
GO

-- Create Certificate
CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'My Data Encryption Certificate',
EXPIRY_DATE = '10/31/2010';
GO

CREATE SYMMETRIC KEY MyKey
WITH
ALGORITHM = AES_256,
IDENTITY_VALUE = 'My Symmetric Key',
KEY_SOURCE = 'Unique phrase that will be used to secure the key'
ENCRYPTION BY CERTIFICATE MyCertificate;
GO

View 3 Replies View Related

Performance - Automatic Expansion Vs Setting Large Initial Size.

Aug 25, 2004

Hi,

We currently have a fairly new SQL server 2000 db (currently about 18mb is size) as a backend to an application (Navision). Performance seems to be below what it should be.

The db is increasing quite rapidly in size, with a lot of data scheduled to be loaded onto the db and also more and more shops and users coming onto the system with alot more transactions going onto the db.

The initial setup of the db has the database File properties set to "Automatically grow file" by "30%" and has an unrestricted file growth.

The server that the db sits on is high spec and very large disk space.

Because the database will be expanding alot and thus reaching its maximum space allocation and then performing a 30% increase in size (which I guess affects performance quite a bit??) quite regularly.

Is it best to set the intitial size of the db to a alot bigger size in the first place as we have large disk space availiable and also set the % increase bigger also.

any advice on best performance would be much appreicated.

Regards,
David

View 1 Replies View Related

Some Advices For Large Databases

Feb 12, 2006

Hi,
My MSQSQL 2000 application inone company has a backup file of more than 6 GB. The total number of tables are more than 280 while the number of fields are more than 8000.
The last months I noticed that the backup database file is increasing rapidly. Is there a particular reason or anyone fas an idea why?
My largest table consists of more than 160 fields and has amore than 1,2 million records and 32 indexes. I know this is not very good, and I'm thinking methods to fix it since I have noticed performance problems. Can you have some advices?
I read that with MSSQL 2005 I can partition my table horizontally and vertically. However I think that this option is available with the enterprise edition which costs more than 10,000 Euros. Is this correct?
Is there another way to increase performance? My server is Windows 2003 with 4 GB memory. I think that Windows 2003 doesn't support more than 4 GB memory. Is this true?
Are there some advices for my case from the experienced users of this forum??
Regards,
Manolis

View 5 Replies View Related

Administering Large Databases

Dec 30, 2007

Hello,

I have been working as a SQL Server 2000/2005 db administrator (small-medium dbs), in the near future I am going to work as a DBA for large DB on SQL Server 2005 (200 - 300 GB and over) but I have not experences.
Do you have any suggestions, Recommendations or useful docs, books or web links and everything else to suggest me on how to administer large databases.

Thank a lot

View 4 Replies View Related

Large Number Of Databases On 2005

Feb 23, 2007

For anyone with a larger number of databases (500+): How many do you have in a single instance. If you are using multiple instance on a single server, how many dbs per instance. This is why I'm asking

We are experiencing 701 "out of system memory" and temporary (usually) system freezes when the error occurs. We have 32bit 2005 version 9.00.2153.00, 32GB of memory, AWE enabled, quad dual-core 3GHz hyperthreaded server. Nether the bPool or VAS show any pressure when the "out of system memory error" occurs. Since this error usually indicates a VAS problem we tried increasing VAS to 1GB w/the -g flag. It made no difference. PSS has been working on the case for 3 weeks. They dont seem to be finding any evidince of memory pressure either. When I last spole to the escalation engineer yesterday it seemed that they are going to recommend reducing the number of databases on the server. I asked for clarification as to whether we are hitting a 32 bit barrior, an instance limitation, or both. I am awaiting the answer. How many databases do you have on your server? We had between 1700 and 1900 (the number varies) at times when the error occured. We are now at 1500, and have not had the error in the 2 days since reducing the number of databases...

View 4 Replies View Related

Mirroring Large Amounts Of Databases

Mar 6, 2008

I have been looking into mirroring a large amount of small databases approx 150 databases.

As I understand this won't be feasible because of the way mirroring threading works, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=441900&SiteID=1

As I understand it for every database being mirrored sql will ping the mirror second, causing a network bottleneck?.

Also that the amount of threads generated for each mirrored database will cause also cause a bottleneck?

At the moment our database servers are under very little pressure and as an estimate use about 10% of the resources allocated to them such as CPU utilization, memory, disk IO and network. Our server hardware is Dual Quad core Xeons with 4 - 8 gig of memory and variety of 10k SCSCI raid configurations from raid 5 or 1,0 and sql 2005 32bit.

Ive done some calculations on the log file generation rate compared to network bandwidth there is more than enough network bandwidth.

Has anybody had any luck in mirroring many small databases?

My concerns is how much traffic is caused by the pinging of the mirror for each database?,

How many threads will the mirroring cause and what is the max amount of threads sql can handle?

How much memory will be consumed by each one of these mirroring threads?

View 1 Replies View Related

Find Autogrowth Setting Of All Databases

Jul 1, 2015

give me query or script to find autogrowth setting of all databases in instance

View 5 Replies View Related

SQL Server 2008 :: Update Statistics With Large Databases

Feb 5, 2015

Currently our database size is around 350G. It will grow up to 1.5 TB

We have the

Auto create statistics option :True,
auto update statistics option :True,
auto update statistics asynchronously option : False

at database level

we have a weekly job, update statistics running very long time. It is created through maintenance plan using the option full scan.

Previously they tested with sampling but instead of full scan running with the sampling effected the queries.

Is there option to avoid the long time job duration.

If we didn't run the statistics manually what will happen? How do you maintain statistics with large databases

View 9 Replies View Related

Large Databases - Create Indexes For Fields That Are Used In Where Statements?

Aug 29, 2013

For large databases is it a good idea to create indexes for fields that are used in Where statements? Does that improve performance and reduce overhead?

View 4 Replies View Related

SQL Server Express Login Issue With Large Databases

Feb 6, 2007

Hello,

My company works with SQL Server 2005 express locally with Visual Studio to develop websites. Everything works very well.

We use SQL Server 2005 express on our production server as well. We change the database over to a non-User Instance when the site is ready to go live. Everything works fine here as well.

We run into issues when databases get near 100 MB. This is well below the stated database size limit for Express of 4GB.

At that point, about once a day, a site with a "large" database will stop responding. The error that we'll get is "Cannot open database 'DBNAME' requested by the login. The login failed. Login failed for user 'DBUSER'

The only way we've found to fix this is to restart the SQL Express service. Obviously, that isn't a very useful alternative.

Has anyone run into anything like this? Could we have some setting wrong?

Would moving to the full version of SQL Server 2005 fix this?

View 3 Replies View Related

Moving Tables Between Databases And Setting Alias

Apr 27, 2006

Due to bad programming techniques and legacy databases, my company has a large database with all the tables in it. I would like to start moving these tables out of the database and into more appropriately named databases.I was wondering if there was a way to set an alias up so that, when the table is moved, there will be no problems with out code still trying to access the table from the old database.Maybe an example is the best way to explain this. Lets say we have a DB_Company database with table T_Customers, T_Suppliers, etc.Now, as the company grows, we decide to create a dedicated database for all the customers. So we create a database DB_Customers and move the T_Customers table into it.The problem is that they may be some code that is still trying to access the DB_Company.T_Customers table. Is there a way of setting an alias up so that, when someone tries to access the DB_Company.T_ Customers table, he is automatically redirected to the DB_Customers.T_Customers?Hope that's not too confusing :-(Jag

View 2 Replies View Related

Jan 11, 2008

I am using server 2005 running on a windows server 2003 platform. I am trying to do field encryption using symmetric key implemented by the AES algorithm. I created an AES key and apparently it encrypts the data with no problem, however when I try to retrieve the data after decryption the field is always null. I tried using the same process while using the DES or RC2 algorithm and I could both encrypt and decrypt information, however it does not decrypt for any of the AES algorithm i.e. AES_128, AES_192 and AES_256. Attached is a simple query I used to test it.

create symmetric key CCardKeys
with algorithm = aes_192
encryption by password = 'P@s$w0rD'

open symmetric key CCardKeys
decryption by password = 'P@s$w0rD'

declare @Id varchar(50)
set @Id = encryptbykey(key_guid('CCardKeys'),'Test')

select convert(varchar(50),decryptbykey(@Id))

close symmetric key CCardKeys

Is there something I'm missing with AES.

Thanks for taking the time to help me.

View 3 Replies View Related

Encryption

Apr 19, 2007

Here is my goal please let me know if it is possible.

I have installed sql express on clients machines. I don't want them to be able to view the sp's or the functions. I would like to go as far as not allowing them to see the tables. I tried with encrption but this is still breakable by the user using the DAC.

Thanks

View 15 Replies View Related

Encryption

Mar 5, 2007

I have a user table with Fields:

UID, name, SSN, phone,address. I need to encrypt all the fields except UID. My company recently moved to sql server 2005 and I have to encrypt old data. We do perform query search based on username and SSN

I have very shallow knowledge of encryption and indexes. I did looked at one of the articles on encrypting data on existing application but could not understand the indexing part(:()

Do i have to define new fields with varbinary as datatype(for ssn, name,phone number) ? I am asking this because in the demo only ssn_index has varbinary and the ssn field is still nvarchar?

Any help will be appreciated.

Thanks,

K

View 11 Replies View Related