SQL Server encryption and decryption data with keys and certificate

create database mydb

-----------------------------------------------------------------------------

CREATE TABLE Employee

(Emp_ID int NOT NULL,

Emp_Date datetime DEFAULT GETDATE() NOT NULL,

Emp_Comments varchar(2000) NOT NULL)

-----------------------------------------------------------------------------

USE [master]

GO

CREATE LOGIN [User1] WITH PASSWORD=N'Pa$$w0rd'

GO

USE [mydb]

GO

CREATE USER [User1] FOR LOGIN [User1]

GO

-----------------------------------------------------------------------------

GRANT SELECT, INSERT, UPDATE ON Employee

TO User1

-- Create database master key

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'Pa$$w0rd'

-- Create certificate

CREATE CERTIFICATE MyCert

AUTHORIZATION User1

WITH SUBJECT = 'test certificate'

GO

-- Create symmetric key

CREATE SYMMETRIC KEY MyKey

AUTHORIZATION User1

WITH ALGORITHM = TRIPLE_DES

ENCRYPTION BY CERTIFICATE myCert

GO

-- Insert encrypted data

EXECUTE AS USER = 'User1'

OPEN SYMMETRIC KEY MyKey DECRYPTION BY CERTIFICATE MyCert

INSERT INTO Employee

VALUES

(1, DEFAULT, EncryptByKey(Key_GUID('MyKey'),'Employee comment data'))

CLOSE ALL SYMMETRIC KEYS

REVERT

-- Read the decrypted data

EXECUTE AS USER = 'User1'

OPEN SYMMETRIC KEY MyKey DECRYPTION BY CERTIFICATE MyCert

SELECT CONVERT(varchar,DecryptByKey(Emp_Comments)) AS Comments FROM Employee

CLOSE ALL SYMMETRIC KEYS

REVERT

Inserting files to database

inserting data from files (jpg, xml, avi ....) into database, easy by the follwing code.

INSERT INTO Photos (FileName, FileType, Picture)

SELECT 'Photo1.jpg' AS FileName,
'.jpg' AS FileType,
* FROM OPENROWSET(BULK N'C:\Photo1.jpg', SINGLE_BLOB) AS Picture

as you see, in the section of bulk you put the path od the file that you want to put it in your database. any kind of file you can put. but you need to specify your data type column to varbinary(max) .

the following code try to insert multiple pics into database.

Declare @imgNumber int

DECLARE @imgString varchar(80)
DECLARE @insertString varchar(3000)
SET @imgNumber = 1
WHILE @imgNumber > 101
BEGIN
SET @imgString = 'E:\images\Picture' + CONVERT(varchar,@imgNumber) + '.jpg'
SET @insertString = N'INSERT INTO images(imageData)
SELECT * FROM OPENROWSET(BULK N''' + @imgString + ''', SINGLE_BLOB) as tempImg'
EXEC(@insertString)
SET @imgNumber = @imgNumber + 1
END
GO

UNIQUEIDENTIFIER with ROWGUIDCOL

Example 1 :

CREATE TABLE MyTable (ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(), nam varchar(max))


insert into MyTable (nam) values ('mohamed')
insert into MyTable (nam) values ('tareq')
insert into MyTable (nam) values ('emad')
insert into MyTable (nam) values ('ahmed')
select * from MyTable

Example 2 :

CREATE TABLE MyTable2 (ID UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY DEFAULT NEWSEQUENTIALID() , nam varchar(max))


insert into MyTable2 (nam) values ('mohamed')
insert into MyTable2 (nam) values ('tareq')
insert into MyTable2 (nam) values ('emad')
insert into MyTable2 (nam) values ('ahmed')
select * from MyTable2
select ROWGUIDCOL from MyTable2
select OBJECTPROPERTY (object_id('table1'),'TableHasRowGuidCol')

--Notes


--1) The UNIQUEIDENTIFIER along with ROWGUIDCOL NEWSEQUENTIALID() is far more efficient than normal UNIQUEIDENTIFIER along with NEWID().

--2) The Unique Ids generated with the second approach are sequential in nature, similar to IDENTITY values.

--3) There can be max of one ROWGUIDCOL for a table.

--4) You can query the table for ROWGUIDCOL. Example: SELECT ROWGUIDCOL FROM MyTable

SQL Server 2008 Streaming

it's new feature in sql server 2008. in the installation steps you can support you server to working with stream .

before working with database stream, goto configuration manager and select properties of your instance that you want to act with streaming. 



 then select filestream tab, and click on you need from this tab .
  

then open new query in sql server and write the following code to full access enabed file stream on server level .

-- filestream configuration

-- The last parameter to sp_configure specifies the Access Level,
-- where 0 means ‘Disabled’,
-- 1 means ‘Transact-SQL Access Enabled’ and
-- 2 means ‘Full Access Enabled’
EXEC sp_configure filestream_access_level, 2
GO
RECONFIGURE
GO

Now, we can create database with streaming option by following code.

CREATE DATABASE NorthPole
ON
PRIMARY (
NAME = NorthPoleDB,
FILENAME = 'C:\NorthPoleDB.mdf'
), FILEGROUP NorthPoleFS CONTAINS FILESTREAM(
NAME = NorthPoleFS,
FILENAME = 'C:\NorthPoleFS')
LOG ON (
NAME = NorthPoleLOG,
FILENAME = 'C:\NorthPoleLOG.ldf')
GO



return to database to see the effective of above code .

Return to your query again, and past the following code.

CREATE TABLE [dbo].[Items](

[ItemID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
[ItemNumber] VARCHAR(20),
[ItemDescription] VARCHAR(50),
[ItemImage] VARBINARY(MAX) FILESTREAM NULL
)

the above code is about create table Items, note the column ItemImage it's varbinary(max) datatype and also filestream. filestrem keyword just attribute behinde varbinary datatype , it's note a datdatype. filestrem keyword found here for working with streaming database.
ItemID column in previous code with datatype UNIQUEIDENTIFIER, for working with streaming in your datdabase add attribute ROWGUIDCOL. this attribute keep UNIQUEIDENTIFIER with sequential result as you know with normal identity column result.
the final step now is to insert binary data to the previous table .
-- Declare a variable to store the image data

DECLARE @img AS VARBINARY(MAX)
-- Load the image data
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(
BULK 'C:\img1.jpg',
SINGLE_BLOB ) AS x
-- Insert the data to the table
INSERT INTO Items (ItemID, ItemNumber, ItemDescription, ItemImage)
SELECT NEWID(), 'MS1001','Microsoft Mouse', @img

the previous code try to insert image file named 'img1.jpg' to table items.
 
but what about streaming, the above code can be run withtout any previous configuration . it's mean that the streaming database not mean inserting data to database, but it's mean how to read data binary from database by synchronise manner.
 
all previous configuration let you working with streaming database, to read your data by stream manner you can do that by programming language such as vb.net or c#.

Notes about extents - SQL Server 2008

SQL Server has two types of extents:

1. Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.
2. Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

A new table or index is generally allocated pages from mixed extents. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations. If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents.

 

 Understanding Pages and Extents : http://msdn.microsoft.com/en-us/library/ms190969(SQL.100).aspx

Note about install and upgrade SQL 2008

- Cross-version instances of SQL Server 2008 are not supported. Version numbers of the Database Engine, Analysis Services, and Reporting Services components must be the same within an instance of SQL Server 2008.

- Before upgrading SQL Server, enable Windows Authentication for SQL Server Agent and verify that the SQL Server Agent service account is a member of the SQL Server sysadmin group.

- Before upgrading from one edition of SQL Server 2008 to another, verify that the functionality you are currently using is supported in the edition to which you are upgrading.

- Cross-platform upgrade is not supported. You cannot upgrade a 32-bit instance of SQL Server to native 64-bit. However, you can upgrade a 32-bit instance of SQL Server to the WOW64: the 32-bit subsystem on a 64-bit server. You can also back up or detach databases from a 32-bit instance of SQL Server, and then restore or attach them to an instance of SQL Server (64-bit) if the databases are not published in replication. In this case, you must also re-create any logins and other user objects in master, msdb, and model system databases.

Note That !:

- The Developer Edition contains the same functionality as Enterprise Edition, but is restricted, by the license, to testing and development only.

- Windows Essential Business Server Premium Edition® (which will replace Small Business Server®) comes with a license for SQL Server 2008 Standard Edition®.

- SQL Server 2008 Web Edition enables hosting companies to provide low cost, highly scalable hosting for developers, SMBs, and consumers, all with a low monthly licensing fee, four CPU support, and no limits on memory and database size for increased scalability.

To upgrade to SQL Server 2008, you must be running a supported operating system.

- The installer will check a number of conditions and block the upgrade if certain conditions are not met:
- Upgrade will be blocked if there is a pending restart.
- Upgrade will be blocked if the Windows Installer service is not running.
- Upgrade will be blocked if performance counters are corrupt.

To upgrade an instance of SQL Server to a SQL Server failover cluster,

the instance being upgraded must be a failover cluster. To upgrade a stand-alone instance of SQL Server to a SQL Server failover cluster, install a new SQL Server failover cluster and then migrate user databases from the stand-alone instance by using the Copy Database Wizard.

More Links

- For more information about Preparing to Install SQL Server 2008, see "Version and Edition Upgrades", http://go.microsoft.com/fwlink/?LinkID=127393

- For more information about Preparing to Install SQL Server 2008, see "SQL Server 2005 Features Comparison", http://go.microsoft.com/fwlink/?LinkID=127394

- For more information about Preparing to Install SQL Server 2008, see "Windows Essential Business Server Overview", http://go.microsoft.com/fwlink/?LinkID=127395


_______________________________________________________
main source : 6231A Maintaining a Microsoft SQL Server 2008 Database

What is WoW64 ?

WoW64 (Windows 32-bit On Windows 64-bit) is a subsystem of the Windows operating system that is capable of running 32-bit applications and is included on all 64-bit versions of Windows — including Windows 2000 Limited Edition, Windows XP Professional x64 Edition, IA-64 and x64 versions of Windows Server 2003, 64-bit versions of Windows Vista and Windows Server 2008, as well as the 64-bit edition of Windows 7. In Windows Server 2008 R2 Server Core, it's an optional component. WoW64 is designed to take care of many of the differences between 32-bit Windows and 64-bit Windows, particularly involving structural changes to Windows itself.

______________________________________________________
main source : http://en.wikipedia.org/wiki/WoW64