SQLServerPedia

This is a very general question that someone could certainly write a book on but I’ll go at answering it by providing a few options and explaining the pro’s and con’s of each solution.  The following list is by no means comprehensive.

The first question you need to ask yourself before going on this journey is where does the encryption need to occur and who are you protecting the data from.  These two questions will drive the amount of effort and complexity required in building a solution.

1)  In database encryption.  The database is encrypted in the database and is viewable to no-one unless they have access to the encryption key.  The data could be encrypted at the application level by first running the data through an encryption algorithm before inserting into the database or the data can be encrypted using SQL Server 2005’s column level encryption or SQL Server 2008’s transparent data encryption.  Using in database encryption is the most secure option in that it can be leveraged to protect sensitive data from anyone that gains access to the database engine itself (whether valid or invalid access) however it has significant performance and scalability caveats.  Encrypting at the application level basically ensures that SQL Server will not be able properly index the data and therefore query perofmance may be dreadful with large data sets.  Leveraging column level encryption or TDE also will introduce query processing overhead and adversely affects the performance of your queries but not to the same extent.   Another drawback of In Database encrption is that it renders backup compression (whether through SQL Server 2008 or a third party backup product or winzip) obsolete.  Encrypted data is not compressible, therefore the size of your encrypted database will roughly equal the size of your backup files regardless of the compression technology used.

2)  If you do not need to secure data inside of the database but want to secure data once it leaves the database, a good solution would be to leverage a third party backup solution such as LiteSpeed.  These solutions will compress and also encrypt backup data in memory so that it is secured as soon as it leaves the database.  This is an attractive option to companies that store backups on network devices and are worried about internal or external users gaining access to network resources and therefore having direct access to backup files.  If you think that native backup files are secured, try opening a small database backup file in notepad.  You will see that all of the data is stored in plain text.  If you have a copy of pubs lying around from the SQL 2000 days you can back it up, open the backup file in notepad, search for “Smith” and you will see Smith’s social security number right there.  This method of database encryption will not secure the data in the database, so anyone who gains database access has free reign on your data, but thats what DBA’s are for right???

3)  A third option, and the last that we will discuss here, is to leverage encryption at the tape level.  Most organizations take their disk based backups and eventually migrate them to tape, where the backups files are then encrypted and eventually moved to offsite storage.  This should be a bare minimum for any SQL Server as once a tape leaves offsite anything could happen to it.  There have been many major news stories recently discussing companies that have had backup tapes lost or stolen and without leveraging at a minimum the tape systems encryption, that data is available to anyone that may “find” a backup tape.  Tape level encryption does nothing to protect the database info inside of the database or the backup data inside of a companies network, but it does protect the data once it is transported offsite.  The nice advantage of this as well as the previous option is that they impose no overhead on standard database workload, they simply may add some overhead to your backup time depending on the level of encyption that you are performing.

Hopefully this brief rundown of database encryption options has given you enough info to get started decideing which implentation strategy is best for you, and as always if you have any further questions, feel free to submit them.

I ran into an interresting situation today while working with a customer and thought it worthwhile to blog on the subject since my internet searching proved lengthy on the subject.  While trying to run a RECONFIGURE statement after making an sp_configure change I received the following message:

Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.

I’m running on SQL Server 2005 SP2+ so my first thought was… obviously, but I’m not trying to update system catalogs, I’m trying to make an sp_configure change.  After doing some digging I found that the culript was the sp_configure ‘allow updates’ parameter.  This configuration in SQL Server 2000 allowed or dissalowed direct system table updates.  In SQL Server 2005, this configuration item still exists but it is obsolete since direct access to system tables in always prohibited.  While the configuration item is obsolete, having it set to 1 in SQL Server 2005 requires you to run the RECONFIGURE statement using WITH OVERRIDE, otherwise you will get the message above.

If you ever see the message above when trying to run RECONFIGURE you will either need to run RECONFIGURE WITH OVERRIDE, or first run:

EXEC sp_configure ‘allow updates’, 0
RECONFIGURE

I find it confusing that an “obsolete” configuration parameter can have an effect on instance behavior, but oh well.  I’d love to hear comments if anyone has any insight into this or similar issues.

Thanks to Jasper Smith, who posted here and pointed me in the right direction.

Before I begin, I want to mention that there is a wealth of information on this topic on quest.com including the Don’t Hate – Consolidate and How Do I Know When to Defragment My Database? webcasts, the Tips on SQL Server Consolidation podcast and more.

Consolidation projects should answer the following questions (and this is not intended to be a complete list):

1. What is the deadline for the consolidation?
2. What is involved in the consolidation?
3. What are the performance characteristics of the environments you’re consolidating?
4. What are the growth trends of the environments?
5. What kind of maintenance windows will you have once you’ve consolidated?

What is the deadline for the consolidation? How much time do you have? This information will drive how you proceed. Ideally, you’ll want a minimum of 3-6 months to determine all the characteristics of both the source and the target environments so you can accurately determine the nuances of all of the environments involved. Information is your best friend here, but be aware that the information is only as good as your ability (read: time and resources) to process it.

What is involved in the consolidation? When you look to consolidate SQL Server environments, you have to bear in mind that you’re dealing with more than just the SQL Server instances and their databases. There are always a number of people and applications that rely on an instance of SQL Server, including websites, internal applications, third-party tools, and existing maintenance plans to name a few. The best way to determine what uses the SQL Server is to set a period of time when that SQL Server environment will be monitored to determine exactly who or what uses the SQL Server that will be moved or migrated before the project is undertaken. You’re going to need time to plan for outages, to schedule migrations and to simply communicate the venture to all of the appropriate parties.

What are the performance characteristics of the environments you’re consolidating? If you consolidate high-traffic environments, its possible that you’ll be creating performance bottlenecks where there weren’t any before. Again, your best defense here is to gather enough data about those environments to ascertain these types of issues. Also, what about the hardware on the consolidation target? Having good, thorough performance data that includes the specs of the hardware you were monitoring is critical in defining the subsystem of the consolidation target environment. And crunching numbers is just the first step here – once you have the hardware configured, you’d be well-advised to stress test that environment, preferably using real databases and real application code. There are some good products in the marketplace from Microsoft, Quest and others that will help you simulate user loads using the application SQL you captured during your monitoring period.

What are the growth trends of the environments? Whenever you deal with a SQL Server database, you’ll inevitably have to deal with database growth trends. Are you still allowing data and log files to auto-grow? This could pose a fatal problem in a consolidated environment where it was just a periodic performance-killer in the past. The 3-6 month monitoring period I suggested should also be used to determine, pretty accurately if you’re allowing that much time, how your databases grow. Whether you use complex algorithms or simple linear plotting, having an idea of your consolidated environments disk storage needs will help you order the right hardware and save a lot of time and hassle with Systems Administrators, Storage Administrators and management in the future.

What kind of maintenance windows will you have once you’ve consolidated? Database backups, index maintenance, ensuring up-to-date statistics, and maybe creating data-marts are activities all DBAs are familiar with. But even though a DBA is familiar with maintenance plans, there is usually some degree of superstition or other “don’t touch it unless something breaks” attitude in every environment. Well, when you’re gearing up to perform a consolidation you’d better get familiar with what’s running and when. Conflicting backups are just one of many issues you could run into. The monitoring you’re performing will tell you when different activities are being performed and should expose when certain additional or different maintenance windows will be available. If you realize you won’t have time for maintenance under the current application and environment settings, you’ll need to adjust your consolidation plan because this is definitely a step you can’t ignore. Missing a backup is something every DBA understands the criticality of, but not maintaining indexes or statistics can ultimately lead to decreased performance, throughput and even to shorter maintenance windows.

In summary, you need to plan, monitor, diagnose, and test before you start consolidating environments. Good communication is important throughout the process, both to ensure that everyone that needs to be in the loop is, in fact aware, but also because this is one of the best ways to expose potential problems before they arise. I hope this helps!

Question: I am setting myself up a Test Server for my eTail store and I’m getting these SQL errors.
My production Server (hosted externally, works just fine and always has, so it shouldn’t be the ASP code)
Here’s the error:

Number: -2147217865
Page: /Scripts/default.asp
Desc: Invalid object name
SQL: SELECT configValLong FROM storeAdmin WHERE configVar = controlRec’ AND adminType = ‘C’

- SQL 2005

- Locally, the table does exist

- If I paste the SQL statement into SQL Management Studio I get the same error BUT yet if slightly change it to ‘FROM nextinline.storeAdmin’ it works fine
and returns properly.

- If I open the ‘storeAdmin’ table then show the SQL and past in the above without changing it also works.

- Connection string defaults to the Database name and uses SQL authentication

- My guess is it’s a Server setting which (if changed) allows SQL statements to run without the Fully qualified dB name…however that is over my head.

James Delve says: SQL Server 2005 introduces the notion of “schema”, and a schema is a collection of database objects that form a single namespace. A namespace is a set in which every element has a unique name.

In SQL Server 2005, each user has a default schema, which specifies the first schema that will be searched by the server when it resolves the names of objects. The default schema can be set and changed using the DEFAULT_SCHEMA option of CREATE USER and ALTER USER.

If DEFAULT_SCHEMA is left undefined, the database user will have DBO as its default schema. This is why when you specify the “nextinline.storeAdmin” wording it works.

To resolve this, you can continue to use the “nextinline.storeAdmin” in your code, or simply change the user to have a default schema of “nextinline” and this will save you having to be explicit in your code.

The code to change the default schema is:

ALTER USER user_name
WITH <set_item>  [   ,...n  ]

<set_item>   : : =
NAME = new_user_name
|    DEFAULT_SCHEMA = schema_name

More Information About Schemas and Invalid Objects

Ari Weil blogged about whether to create a SQL Server schema.