jump to navigation

Sybase to SQL Server migration – Part II October 24, 2008

Posted by msrviking in Migration.
add a comment

In continuation to my earlier posting on Sybase to SQL Server migration – Part I here is what I did with the wealth of information I found.

I prepared a list of issues that would come up during the migration or probably the issues I should look out for while migrating.

But before all these I decided to prepare a list of migration steps that would be involved by any DBA. Here is the list you guys want to have a look at.

Category

Activity

Work on

What work?

Instance

Gather and map the parameters, configurations wrt new instance

Old Instance

Admin

Create scripts for parameters, configurations from old instance

Old Instance

Admin

Create new SQL Server Instance with mapped parameters, configurations

New Instance

Admin

Create scripts for logins from old instance

Old Instance

Admin

Gather and map logins, roles wrt new instance

Old Instance

Admin

Create scripts for application role, user defined roles from old instance

Old Instance

Admin

Create logins on new instance

New Instance

Admin

Create roles (application, user define) on new instance

New Instance

Admin

Gather and map backup devices wrt new instance

Old Instance

Admin

Create scripts for backup devices from old instance

Old Instance

Admin

Create backup devices on new instance

New Instance

Admin

Gather configuration details of linked servers wrt new instance

Old Instance

Admin

Create linked servers on new instance

New Instance

Admin

Database

Gather and map database parameters, configrurations wrt new instance

Old Instance

Admin

Create scripts for database parameters, configurations from old instance

Old Instance

Admin

Create database on new instance

New Instance

Admin

Gather and map user defined datatypes wrt new instance

Old Instance

Object definition

Create scripts for user defined data types from old instance

Old Instance

Object definition

Create user defined data types on new instance

New Instance

Object definition

Gather and map partitions wrt new instance

Old Instance

Object definition

Create scripts for partitions from old instance

Old Instance

Object definition

Create partitions on new instance

New Instance

Object definition

Gather and map datatypes, defaults, constraints (check, primary, foreign), ruels wrt new instance

Old Instance

Object definition

Gather, identify and replace reserved keywords wrt new instance

Old Instance

Object definition

Create tables with appropriate datatypes, defaults, constraints (check, primary, foreign key), rules from old instance

Old Instance

Object definition

Create tables with appropriate datatypes, defaults, constraints (check, primary, foreign key), rules on new instance

New Instance

Object definition

Gather, identify indexes and replace appropriate indexes (index names) wrt new instance

Old Instance

Object definition

Create scripts with indexes (with, without partitions) from old instance

Old Instance

Object definition

Create indexes (with, without partitions) on new instance

New Instance

Object definition

Create scripts for stored procedures from old instance

Old Instance

Object definition

Create stored procedures on new instance

New Instance

Object definition

Create scripts for views from old instance

Old Instance

Object definition

Gather, identify and replace appropriate keywords, syntax, un-supported functions, datatypes, transaction handling, etc. wrt new instance

Old Instance

Object definition

Create views on new instance

New Instance

Object definition

Gather, identify and replace appropriate keywords, syntax, un-supported functions, datatypes, transaction handling, etc. wrt new instance

Old Instance

Object definition

Create scripts for triggers from old instance

Old Instance

Object definition

Create triggers on new instance

New Instance

Object definition

Gather, identify and replace appropriate keywords, syntax, un-supported functions, datatypes, transaction handling, etc. wrt new instance

Old Instance

Object definition

Create scripts for functions (scalar, in-line) from old instance

Old Instance

Object definition

Create functions (scalar, in-line) on new instance

New Instance

Object definition

Migrating data

Entity mapping, Attribute mapping, Relationship mapping, GAP analysis

Old – New Instance

Data

Disable constraints (primary, foreign key, check)

New Instance

Data

Disable indexes

New Instance

Data

Extract data out of old database using BCP, SSIS packages

Old Instance

Data

Load data in to new database using Bulk Insert, BCP, SSIS packages (Dry run, Validate, Refine)

New Instance

Data

Enable constraints (primary, foreign key, check)

New Instance

Data

Enable indexes

New Instance

Data

Testing

Check functionality of the database thro’ the application

New Instance

Test

Check functionality of the instance involving linked servers

New Instance

Test

Check functionaility of the instance, database performance using applications

New Instance

Test

Deployment

Release for end-user use

New Instance

Go live

And after this listing on step by step methodology I went ahead and identified the issues that could put the whole migration in difficulty. Due the exhaustiveness and size of the issue list I am unable to share it in this post. I shall however try fitting it into some other posts or share the critical ones with you guys!

Until then happy reading!

Advertisements

What can SQL Server 2008 do, for BI? October 8, 2008

Posted by msrviking in SQL Server 2008.
add a comment

Read thro’ SQL Server 2008 performs 1 trillion row query in seconds at BI Conference and also you will love hearing what did SQL Server 2008 do and on next versions of SQL Server and its capabilities.

Enjoy!

First post on WordPress – Database Encryption October 6, 2008

Posted by msrviking in Security.
add a comment

Alright this is my first posting on wordpress.com and I am little excited. Okay, let me leave my feelings behind and come to the topic – Database Encryption.

One of my client had come up with a question on SQL Server 2005 Database Encryption. Although, I haven’t worked extensively (exhaustively!) in designing encryption policies for any databases I gathered my thoughts and had this for him. The context was set before I could get back and it was in the form of questions

When we have data is stored in columns of different data types with different length in an encrypted format (except the image values),

1) What will be the performance impact of encryption and decryption of these columns? Should all the columns be encrypted?
2) What methodology of encryption should be used?
3) Would performance be impacted having the service layer calling the stored procedure that would do encryption and decryption of the values?
4) What should be compromised for columns that have encryption enabled (Performance or Security)?
5) Are there any options or solutions to get Performance and Security together?
6) What are the guidelines to backup and restore the encrypted keys?

And before I could answer the questions, I wanted to make a note that some of the points in the below points are contentious and keeping requirements in mind we agreed upon, this is what I could think of.

–       What is Database Encryption and what does this mean to us?

Database encryption protects data from internal and external attacks. However, database encryption isn’t a one-size-fits-all solution and we need to evaluate what data needs protection, where the encryption should be performed (e.g., in the database, in the application where the data lies, above the database), and how much performance would be traded for increased security. Well, we may also have no choice in these factors if we must encrypt data to comply with regulatory standards.

–       What level of encryption should be implemented?

Having the option of only cell level encryption and with the hierarchical encryption, key management infrastructure for encrypting data we have 3 choices of implementation.

·         Certificates (quoted from BOL)

A public key certificate, usually just called a certificate, is a digitally-signed statement that binds the value of a public key to the identity of the person, device, or service that holds the corresponding private key.

·         Asymmetric keys (quoted from BOL)

An asymmetric key is made up of a private key and the corresponding public key. Each key can decrypt data encrypted by the other. Asymmetric encryption and decryption are relatively resource-intensive, but provide a higher level of security than symmetric encryption.

·         Symmetric keys (quoted from BOL)

A symmetric key is one key that is used for both encryption and decryption. Encryption and decryption by using a symmetric key is fast, and suitable for routine use with sensitive data in the database.

With all these points in mind I moved onto next level of questions based on our discussion in the call. The context was when we have data is stored in columns of different data types with different length in an encrypted format (except the image values),

–       What will be the performance impact of encryption and decryption of these columns? Should all the columns be encrypted?

Due to encryption the value of the column attributes changes and therefore the data type of the column is changed to varbinary and then is recast back to the original value after decryption. The manual process means that none of the automatic techniques used by the SQL Server query optimizer will work and becomes cpu intensive operation. The performance impact could be felt significantly if the system has high cpu load and high i/o.

Now the larger the data type length of the encrypted column, the poorer the performance of the system during decryption of these column values. Here is a link (you probably would have had look at this article) which talks about how much data is stored in each data type defined.

With these points in mind we would need to identify columns that need to be encrypted based on type data (sensitivity) or based on data type defined for the column.

In short my answers to the above questions will be – Performance is certainly impacted based on type of encryption method used, data type and length defined of the columns, and unfortunately I couldn’t quantify how much performance will be impacted. The above mentioned link could give us first insight of the performance issues rising. And definitely all columns can’t be encrypted, instead columns based on sensitivity of information should be encrypted and if all the columns have to be encrypted we need to justify the data types and column length to obtain best performance or at least not increase the performance problems.

–       What methodology of encryption should be used?

Going by most obvious reasons with the points discussed above and results from the link, we should prefer using encryption and decryption using symmetric keys.

–       Would performance be impacted having the service layer calling the stored procedure that would do encryption and decryption of the values?

My answer would be performance impact could be higher or lower based on where we embed the logic of identifying the type of key. And if we have an uniform key to be used across all types of columns then we don’t have to be worried about the service layer performance overhead.

To get into more of this situation let’s say –  if we create multiple symmetric keys for different type of columns like Name (First, Last) we have symmetric key created FSymKey and columns like SSN, Health Insurance Number we have symmetric key created SSymKey each key having its own encrypting mechanism.  Now while decryption we would use

/*To decrypt data for name columns*/

OPEN SYMMETRIC KEY FSymKey…… or OPEN SYMMETRIC KEY SSymKey ……

If logic to decrypt data is not embedded in stored procedure against specific column type, instead the logic is embedded in service layer then the overhead of identifying which key to be used could be a performance (overhead) hurdle. But now we have a catch in here, for sake of performance gain from service layer if we put in the logic into stored procedure we may breach the security by exposing the key names and other details, but we have solution for all problems. We probably would encrypt the stored procedure to protect the symmetric key that is used against specific columns, and again that could turn out to be a overhead for development and maintenance of encrypted sp’s, encrypted keys, and so on. We probably should have all wise thoughts together before implementing any of the options to gain performance and /or security.

–       What should be compromised for columns that have encryption enabled (Performance or Security)? Are there any options or solutions to get Performance and Security together?

I have clubbed the questions 4 and 5 for ease of answering.

Well, the first question is a tricky question because we need to do a trade-off between performance and security. We will have to identify what columns could cause performance issues, what basis should we decide that columns would be performance overheads, would we need to encrypt the column that would cause performance issues, if yes what should be the data type and length defined of the column.

What if we are unable to compromise on the data type, security and length then what are the other alternatives for getting the performance. This would be answer to the second question by using MAC (Message Authentication Codes) and there are few articles article 1, article 2 and article 3 that give a insight on how to gain performance by using Message Authentication Codes and indexing encrypted data. We could also use SQL CLR procedures to gain better performance (although not tested one) for encryption and decryption for columns that will have large data types.

–       What are the guidelines to backup and restore the encrypted keys?

There is a good article on how should we backup and restore database that have objects with encrypted columns enabled. IMHO, we should stick the methodology defined in this article for efficient management of database that uses encryption.

I hope this post helps you all and do let me know your comments. I would like to Thank! Laurentiu, Raul and Rob Garrison for sharing there experienced thoughts on their blogs of which I could make best of benefit.

SQL Server 2008 being marketed aggressively October 1, 2008

Posted by msrviking in SQL Server 2008.
add a comment

I have never seen SQL Server being marketed aggressively before being released into the market full fledged. It sounds to me like Bollywood Movies aggressive  advertisement strategy, where the movie songs, trailors are advertised across all media channels. And there are movies that flop in the box office and are mega – hit.

I being an ardent lover of SQL Server I am sure and I have confidence that this is going to be a mega – hit in the IT (box office) industry.

Visit the link for experiencing the movie "SQL Server 2008".

Enjoy the movie!

Are you scared being a DBA? October 1, 2008

Posted by msrviking in General.
add a comment

If you feel that the axe is on you for being DBA during these bad times (World Economic and Financial Crisis) then I would advise not to be scared. There is an interesting post by Buck Woody (one of my favorite DBA’s in MS). Read through and you will feel comfortable.

Happy reading!