SQL Server Hints

SQL Server 2008 R2 Profiler

2012-02-12T08:15:00.000-08:00

In this post :

-Introduction

-Where SQL Server Profiler

-SQL Server Profiler Keywords

-When using SQL Server Profiler

-Using SQL Server Profiler

-About Next step

Introduction

SQL Server Profiler is a tool that let administrator to monitoring an instance of database engine or analysis services. The Result of monitoring is data saved in file (trace file) or stored in database (database table) to analyze it later .

Where SQL Server Profiler

· From start > All Programs > Microsoft SQL Server 2008 R2 > Performance Tools > SQL Server Profiler

· login to SQL server database engine

o Select SQL Server Profiler from Tools menu

SQL Server Profiler Keywords

Event

it's about specific action fired in an instance of SQL Server. for example :

- Login connections, failures, and disconnections.

- The start or end of a stored procedure.

- Transact-SQL SELECT, INSERT, UPDATE, and DELETE statements.

EventClass

is a type of event that can be traced .it's contains all the data that can be reported by an event .It's determines the type of data that can be collected . for example :

- SQL:BatchCompleted

- Audit Login

- Audit Logout

EventCategory

it's a category classification for an event.

DataColumn

It's attribute of an event class . for example Lock:Acquired event class, the BinaryData data column contains the value of the locked page ID or row.

Template

is a selected collection of configuration for a trace. you can make a template with your selection of specific events and data columns.

Trace

is a process of recording data about specific events and data columns. SQL Server let to make a trace by SQL Server Profiler or using System Stored Procedures.

Filter

It's a way to filter the result of tracing . trace need to be filtered if you need a thin trace file that contains what you need only .

When using SQL Server Profiler.

Microsoft says, it's good to use SQL Server Profiler when one of the following case needed .

· Find the worst-performing queries.

· Identify the cause of a deadlock.

· Monitor stored procedure performance.

· Audit SQL Server activity.

· Monitoring Transact-SQL activity per user.

· Collect a representative sample of events for stress testing.

· Collect a sample of events for tuning the physical database design by using Database Engine Tuning Advisor.

Using SQL Server Profiler

- Open SQL Server Profiler.

- Select New Trace from File menu

- Connect to your instance that you want to trace .

- The trace properties window, will appear.

try to insert data in the following fields in the General Tab:

· Trace name : The name of your trace

· Use the template : Select the template that your want to use. in this example leave the default selection.

· check in the Save to file or Save to table or both to let profiler to know how your tracing data will be store.

in the example : check in both option .

· if you check Save to file option you will need to specify file name and location .

· if you check Save to table option you will need to specify your server and authentication that you want tracing data to store it .

o The Destination Table form will be appear .

§ Try to specify the following :

· Database : the database the host your tracing data .

· Schema : the location of your table inside your database selected .

· Table : the name of the table that you want to store your tracing data .

in this example, type MyTable_Tbl in the Table field .

- the Enable trace stop time checkbox enable to end the tracing in the specific date and time .

- Select the next tab, Events Selection tab. in this tab all events and attributes that can be traced by SQL Server Profiler.

Note that, there are default selection events . it's not all events. the show all events and show all columns checkbox can display all events with its attributes that available .

Note also the following :

1- Button Column filters ... to filter the result of tracing . for example it's applicable to filter the result of tracing only in the specific database name .

2- Button Organize Columns ... to sort columns in a certain way .

- After selecting all events wanted in tracing, Press Run button .

- The new window will appear .

it's a window of tracing . SQL Server Profiler now trace the activity of SQL Server in the selected events and store the data of tracing in the My_Trace file and MyTable_Tbl table that is found in the My_Test_DB database .

by clicking on any row in the grid of tracing, the details of the event will appear in the bottom of grid .

- SQL Server Profiler can pause the activity of tracing or stop it by the buttons in the toolbox of SQL server Profiler.

- After a little time, Press Stop Button . the tracing will stop now and the task of SQL Server Profiler finish now also .

- all Traced data now stored in the file trace and table database .

(File Trace)

(Structure of table trace)

(Data stored in the Trace table)

About Next step

Database Engine Tuning Advisor is a performance tool that let administrator to analyze and view recommendations about specific issues. this tool need to data to be analyze, this data can be prepared from SQL Server Profiler .

Follow the following steps, in order to analyze and view recommendation bout traced data .

- From Start > Microsoft SQL Server 2008 R2 > Performance Tools > Database Engine Tuning Advisor

- Try connect to SQL Server

- In the Workload section, select File option or Table option depending on the result of SQL server Profiler .

- from Database for workload analysis option, select the database that will do the analysis of workload .

- Then, simply select databases and tables to analyze it .

- from tool bar, select Start Analysis button. the Progress tab will appear.

- After Progress tab finish, the Recommendations and Reports tabs will appear.

and

there are a set of reports in Tuning Reports Section

Security Scenario: Creating login object with User object only in the specific database.

2012-02-09T10:44:00.000-08:00

This scenario will create the following only in SQL Server .

- Login object in selected SQL Server instance.

-User Object in specific database

1- Problem

Need to prove that creating Login and user object only not sufficient to let user access to objects of database .

2- Solution

· Logon to SQL Server by administrator account.

· Click in the New Query button and typethe following lines.

· From SQL Server instance open Security folder and then Logins folder

· Right Click on the Logins folder and then select New Login ... Option

· The Login - New form will appear.

· In the General tab of Login - New form type the Login Name 'My_Test_Account'

· And then choose SQL Server authentication option to enter password and confirm it. type password 'abc'

· Remove the check of Enforce password policy

· in the User Mapping tab, check on 'My_Test_Account' Database. this check mean SQL Server will create user account for this Login and will put it in the selected database and it's mean also this login can connect to selected database but until now without any permission to working with selected database objects. the login will not be able to connect to other databases that not check in the User Mapping tab.

· Then Press OK Button . login now ready to use.

(Login created)

(User created depending on Login)

· To check that My_Test_Account can access My_Test_DB database, try to Right Click on the My_Test_DB database and then select Properties .

· In the Permissions tab, search for Connect permission in the Explicit tab .note that this permission has Grant by default.

· Before testing the login object, try to check Server authentication that let My_Test_Account Login to access SQL Server .

· Right Click on Instance Server, and then click Properties.

· The Server Properties form will appear .

· Select the Security Tab, and then select SQL Server and Windows authentication mode option.

· Now click OK button. SQL Server will need to restart the SQL Server Engine, help SQL to do that !.

· After SQL server restart SQL Server Engine, try to log off from administrator login account.

· try to logon again by New Login account.

· Try to browse My_Test_DB Database. it will open successfully .

· Try to browse any database else My_Test_DB Database, it will make the following message.

· click on New Query button and then type the following.

it will make the following result.

3-conclusion

In this scenario, the login account without user object in the database can't connect to database . administrator need to make a set of permissions for users objet in the database in order to ensure the ability to use objects of database. The new Login Account with new User Account can connect to database only without any access to any object of that database.

Fixed Server role for SQL Server 2008

2012-02-01T05:36:00.000-08:00

The following is description of fixed server roles that exists in SQL Server.

• Public

o Any login created in SQL Server must be a member of public role.

• sysadmin

o If login is a member of sysadmin, then login can do anything in the SQL Server even if this login blocked from doing something.

o If you need to check login which is a member of specific role, type the following statement in a new SQL Server query

SELECT IS_SRVROLEMEMBER('sysadmin');

• bulkadmin

o transferring data from out of SQL Server to inside it (bulk insert operation) or other operation such as service accounts that are performing Extract, Transform, and Load (ETL) need to be performed by a login that a member of bulkadmin.

o To be a member of bulkadmin not enough to do bulk insert operation for example but it’s need also to has insert permission to complete operation successfully.

• dbcreator

o This role allows to its members the ability of creating databases.

• diskadmin

o This role allows to its members the ability of managing backup devices.

• processadmin

o This role allows to its members the ability of alter any connections.

• securityadmin

o This role allows to its members the ability of managing and controlling logins properties for SQL Server.

o The member of securityadmin role can manage other server roles but not sysadmin role.

o This role can’t define any internal security within a database.

• serveradmin

o This role to manage the SQL Server configuration.

o The Member of this role can shutdown SQL Server .

 Shutdown SQL Server by command SHUTDOWN or SHUTDOWN WITH NOWAIT

 To display a list of all active processes and locks, run exec sp_who and exec sp_lock.

• setupadmin

o This role allows to its members the ability of controlling linked servers.

Overview of Logins in SQL Server 2008

2012-01-30T05:59:00.000-08:00

Logins is the major object in SQL Server and it’s a first security step to secure your database Server with granted users. There are many security objects Depending on the login object. The following is an overview of how to creating logins in SQL Server and some basic knowledge about security principals.

• Right click on Logins folder that exists in the Security Folder.

• and then select New Login

• the Login – new form will appear

• General is the first tab in the Login – new form that need Login name

o Login name option may be one of the following.

 Existing Windows user account that found in the same server of SQL Server or in the another one according with network capability and some administration option. The user account in this case not needs a password because SQL Server will ask the operating system that hosts this account for that password.

 New user account that create in the first time inside SQL Server. The user account in this case needs a user name and password.

• Server Roles tab contains all fixed server groups in the SQL Server. Each group has set of server permissions. By default the new login must a member in the public server role. And also this new login may include in other server roles. It’s mean that this new login have all permission of assigned server roles. for more details about fixed server roles please click here .

• In User Mapping tab, assign all the databases for new login. SQL Server will create user for each database assigned to targeted new login. for example if administrator create new login and assign three Databases for that login, it’s mean SQL Server has four object the first object for the login created and other three user object for each database assigned to this login .

o Also in the User Mapping tab, new login may include in one or more database role membership. Database role membership is a set of groups inside each database created. Each group in the database has a set of permissions in the database scope.

By default, the new login must a member in the public database role in case that this login mapped in specific database. Administrator can assign another roles for a login created.

• Securables tab is one that gives permission for a new login. But what is Securable mean. Its mean all SQL Server objects that will assigned for a specific login with specific permission.
In Securables tab, the Securable objects that allowed here is one of the following types:

o Servers

o Endpoints

o Logins

To select a specific Securable,

o Click on Search … Button.

o Add Objects form will appear.

o Select one of the above options to specify the object type wanted. And then press OK button.
When all selected Securable object appear In the Securables table, then Select one of them from table to specify all permission needed for that securable from Permissions table.

Each of Securable objects has a set of permissions in the permissions table. Each permission may has Grant, with grant or deny permission.
• The last tab in the Login – New form, is Status tab. This tab specify the following :

o Permission to connect to database engine

o Login status

SQL Server 2012 steps installation

2012-01-12T10:50:00.000-08:00

Platform and services installation.

- Windows Server 2008 with SP2.

- Install IIS.

>> Some Prerequisites for install 'SQL Server 2012' .

1- install .NET Framework 3.5 SP1

2-Microsoft .NET Framework 3.5 SP1 Update for Windows Vista and Windows Server 2008.

3- Windows PowerShell 2.0 and WinRM 2.0 for Windows Server 2008 (KB968930)

>> SQL Server 2012 Resource Center

http://msdn.microsoft.com/en-us/sqlserver/ff898410

>> SQL Server 2012 Download

http://www.microsoft.com/sqlserver/en/us/future-editions.aspx

>> What's New in SQL Server 2012 RC 0

http://social.technet.microsoft.com/wiki/contents/articles/3783.aspx

SQL Server Code Name 'Denali' steps installation

2011-09-24T06:50:00.000-07:00

Platform and services installation.

- Windows Server 2008 with SP2.

- Install IIS.

Some Prerequisites for install 'Denali' ,

1- install .NET Framework 3.5 SP1

2-Microsoft .NET Framework 3.5 SP1 Update for Windows Vista and Windows Server 2008.

3- Windows PowerShell 2.0 and WinRM 2.0 for Windows Server 2008 (KB968930)

SQL Server code-named "Denali" Resource Center

http://msdn.microsoft.com/en-us/sqlserver/denali_resource_center.aspx

SQL Server 2008 Database Mail, step by step.

2011-08-02T09:13:00.000-07:00

it's simple steps for working with database mail .

1- Enable Database mail feature.

2- Configure database mail .

3- Sending E-mail.

Step One : Enable Database mail feature.

the following code will enable database mail feature .

sp_configure 'show advanced options', 1;

RECONFIGURE;

sp_configure 'Database Mail XPs', 1;

RECONFIGURE

the result is

Step Two : Configure database mail .

1- Right click on database mail icon in the management folder, this folder found on server .

2- Select Configure Database Mail option on context menu. the welcome wizard will appear, click Next .

3- In the Select Configuration Task step, select Set up Database Mail by performing the following tasks :

this step will let to create Profile, account . and then click Next

check mail, you find your message there.

4- In the New Profile step, write on the following fields :

Profile name : MyProfile

Description : My Desc...

after that, press the Add button to add SMTP accounts that will be associated with previous profile .

the New Database Mail Account window will appear.

write on the following fields.

Account name : MyAccount.

Description : My Account Desc.

Email Address : Type your mail here.

Display Name : your name.

Server name : SMTP site for your mail server. for example (smtp.gmail.com for GMAIL mail server)

Port number : enter the port number belongs to Server name (G-mail: 587) .

check on the checkbox of the This sever requires a secure connection (SSL) option.

after that in the SMTP Authentication section, select basic authentication and enter User name, Password.

note: the factor of success sending mail is the right configuration that entered in this step .

Press OK button to return again in the New Profile step . Press again to Add button if needed to create new account for the same profile.

5- Press Next , the Manage Profile Security step will appear.

in the first tab Public Profiles, select the profile from list to be a public profile . Or select the second tab Private Profiles.

in the Private Profiles, select the specific user with selected profiles that can be accessed by this user. and then Press Next.

6- Configure system parameter step, specify some system parameter if needed and then press Next .

7- The screen confirmation will appear, check your data again and then click Finish.

If all steps of Configuring form working fine, then click Close.

Step Three : Sending E-mail.

for send E-Mail type the following code :

USE msdb

EXEC sp_send_dbmail @profile_name='MyProfile',

@recipients='xxx@xxx.com', -- email that will be receive DB mail.

@subject='Test message subject !',

@body='Body of the test message.

Database Mail work successfully.'

and the result is,

Database Mirroring in SQL Server 2008

2011-06-30T02:29:00.000-07:00

In this post

-Introduction
-Database mirroring Terminology
-Database Mirroring Topology
-Facts about database mirroring
-Modes of Database Mirroring
-Mirroring States
-Example Database Mirroring

- Conclusion

Introduction

Database mirroring is a solution for increasing availability of data. Database Mirroring apply availability by at least two sides of server (SQL Server) with power of synchronous or asynchronous methodology techniques. It's let user to select one of two mode for availability (high performance or high safety).modes of availability will effects for how can failover server depending on database mirroring topology.

by the simple words, the idea of database mirroring is the way of coping data from the basic server(Principle
Server) to put it into another server (Mirror Server). if any disaster will occur for the basic server, then the second one can lead the dataflow of clients.

Database mirroring increase data protection by resolving specific kinds of errors that make corruption of reading data page (read that).

The advantage of the database mirroring is failures are automatically detected and failovers can be made automatic, these are not applicable for log shipping or transactional replication solutions .

Database mirroring can be used with the following in SQL Server.

-Log shipping

-Full-text catalogs

-Database snapshots

-Replication

Database mirroring Terminology

· Principal Server : is the server that host a main database which need to increase high availability.

· Principal Database : database selected that has access by clients.

· Mirror Server : this server maintain a copy of data for principal database into second database.

· Mirror Database : database that is created in the mirror server to save all transaction that occurred in the principal database.

· Witness Server : this server monitor principal and mirror server, it's optional.

· Endpoint : connectivity object that communicate server together across network protocol .

Database Mirroring Topology

The simplest topology can be drawn is two server one of them is principal server and the second named mirror server. each of these servers install SQL Server. the principal server has a database that clients will connect to, and mirror server has a copy of principal server database into another database called mirror database.

Each of these server communicate together by endpoint object that created in both servers. the principal server is only online database that can accessed by clients and mirror server has many states according with conditions that occurs between two servers but no way to read the data in the mirror server except the snapshot database that can be taken from mirror database.

The another topology, is add witness server between principal and mirror server . witness server can monitor activity between other servers and can make automatic failover if any disasters occurs for principal server.

The previous topology can't control disaster by automatic failover, the administrator will do his activity to solve that (manual failover)

Facts about database mirroring

1- Database mirroring supported with full Recovery mode only . Simple and bulk-logged recovery model not supported.

2- All database compatibility level can be working with Database mirroring .

3- Database mirroring need to be same name in both of two sides servers.

4- Can't use Detach with database mirroring .

5- Administrator must check for allowance of Ports that is used in Endpoint objects of database mirroring .

6- Insure that account specified in database mirroring configuration is right.
7- No mirroring with streaming configuration database, it must be disabled from database level.

Modes of Database Mirroring

· Synchronous Mirroring (High Safety)

o Transaction can't commit on principal server until all transaction log records have been successfully copied to the mirror.

o High Safety can be

§ With automatic failover

Require a Witness Server

§ Without automatic failover

No Witness Server Found

· Asynchronous Mirroring (High Performance)

o Commit changes at the principal server and transfer them to mirror server .

Mirroring States

After the configuration of mirroring between two servers and starting the mirror action between them , each server has a specific state which describe the communication state, data flow and also differences of data between servers.

in the simplest topology of database mirroring we can draw data flow between two server which named (principal server and mirror server). each of them have a SQL instance that has a monitor action against each other using mirroring state of each database in them.

if we enlarge our topology, then we can set third server between principal and mirror server, it's name is witness server. in this case each of other servers monitor witness using connection state (connected/disconnected)

the following is the list of mirroring states in SQL Server.

SYNCHRONIZING state

· In this state, there are differences between contents of principal server and mirror server.

· principal server send log records to mirror server and then mirror server send that to mirror DB to apply it.

· starting of DB mirror session the principal DB is in the SYNCHRONIZING state and the mirror DB try to catch up that .

SYNCHRONIZED state

· In the case of SYNCHRONIZING state if mirror DB reach to completely caught up all that is sent from principal DB then the state will change to SYNCHRONIZED state.

· SYNCHRONIZED state mean principal server continues to send changes to mirror server and the mirror server continues applying changes to mirror DB.

· if transaction safety set to full, then automatic and manual failover are supported and no data loss after failover.

· if transaction safety set to off, then some data will be loss.

SUSPENDED state

· this state mean mirror DB is not available .

· principal server is running without sending and log records to mirror server.

· this state occur after failover or administrator pauses the session.

· this state will work until mirror server will be start up again.

PENDING_FAILOVER state

· this state found only on the principal server after failover has begun but the server has not transitioned into the mirror role.

DISCONNECTED

· principal server has lost communication with mirrored server.

Example Database Mirroring

Assume that database 'northwind' in server 'Server08' need to be mirrored on server 'Server08\INST2' .

then consider the folwoing facts :

- Principal Server : 'Server08'

- Principal Database : 'northwind'

- Mirror Server : 'Server08\INST2'

- Mirror Database : Must be named 'northwind'

To create Database Mirroring, It's need to create Full backup from Principal Database but Before doing backup action ensure that your database in Recovery Model Full .

Note : Don't conflict between the need of make a Full Backup and Full Recovery model option on database.

The screen below show how to create backup action.

After that, goto the Mirrror Server 'Server08\INST2'. Right click on Databases folder and select Restore Database .

Now, the Mirror Database on Mirror Server will look like this.

Return back to Principal Server, in the Database Properties window of the Principal Database. select Mirroring tab option in the left pane .

Click on Configure Security Button, the following wizand will appear .

This step ask about Witness Server, which is not found in current example therefore select No option.

the Princopal Server instance is selected by default 'server08' because of the example begin the Database Mirroring configuration from this instance.

In this step, the configuration will create Endpoint object for Principal Server with the following information :

- Endpoint Name : Mirroring

- Listener Port : 5022

This endpoint let the Mirror Server to allow access to Princopal Server.

In the Mirror Server Instance step, Click on Connect button to select the second server that wanted to be Mirror Server. This step will create Endpoint object for Mirror Server with name Mirroring and Listener port 5023.

If two server (Principal and Mirror) in the same doamin, specify the service account for each server.

Press Finish button to start the configuration process.

Press Start Mirroring

Because of the Principal and Mirror servers in the same machine (there are two instance in the same windows server), it's not important to write servers nework addresses as a fully qualified name. but if the two server in separate servers, it's high recommended to specify every TCP/IP for servers as Fully qualified TCP address.

in the corrent situations press Yes

The Configuration of Database Mirroring finished now, in the Screen below note that both the Principal and Mirror endpoint are specified now by TCP://ServerName:Port_Number template .

Also note that in the section of Operating mode, there are two mode only available forg mirroring. the High safety with automatic failover is the only option not supported in the current situations, because of it's required a Witness Server which is not configured.

The results of Database Mirroring configuration is shown below.

the principal server

the mirror server

End point for Principal and Mirror server

Finally, if need now to Failover the Principal Server then, any of the following scenario should be met.

scenario 1

The Principal and Mirror server havn't any disaster (there are in good state), but the administrator in specific time need to failover the Principal because of any issue related with troubleshooting and performance for current Principal Server .

In this case, goto the Database Properties for Principal Database and from Mirroring Tab in the left pane Click on the Failover button .

The failover by this way will switch the principal to mirror server and mirror to Principal one .

scenario 2

If the administrator need to swich the Principal Database to new Principal Server, then from Mirroring tab in the Database Properties windows click on Remove Mirroring button and try to prepare full backup from Principal Database to restore it into the new Principal Server and then make a new Mirroring with Mirror Server.

scenario 3

The Principal Server has a risk or disaster, try to make a full backup and transactional log backup for Principal database and restore it to Mirror Database (Restore with Recovery) .

This way will swith Mirror Database to online database. after that think about any new strategy mirorring for this online database.

Conclusion

For more details please checkout the following links,

Database Mirroring

http://msdn.microsoft.com/en-us/library/bb934127.aspx

Database Mirroring Best Practices and Performance Considerations

http://technet.microsoft.com/en-us/library/cc917681.aspx

Data Collection SQL Server 2008

2011-06-28T05:50:00.000-07:00

Introduction

Disk usage, Query Statistics and Server Activity are most important information for helping administrators to identify SQL Server performance problems. SQL Server 2008 has a feature of Data Collection that can gather information about Disk usage, Query Statistics and Server Activity and take a chance for view these result by set of specific reports.

Actually, Data Collection creates integration services package that is used to gather data and then put it to specific database (database whcih created by data collection configuration.) this database sometimes called Managment Data Warehouse (MDW).
Data Collection also create a set of jobs for gathering required information and add new tables also in MSDB database to save logging and other configuration information.

Benefits of Data Collection

Central Data Repository : One database store all information related with performance.

Selected data that needed to collect : It can select specific collection set to collect and upload to database warehouse.

Display Performance Reports : View specific report for speciafic field of performance (Disk usage, Query Statistics and Server Activity)

Configuration Data Collection

Before starting configuration of data collection, Sart SQL Server Agent service. then goto Managment folder in SQL Server instance and then right click on Data collection icon .

from the list of Data Collection select Configure Managment Data Warehouse.

Click the Next button

before working with Data Collection, it must be configured first by choosing the first option.

This form need the database that will store Data Collection information (MDW), select one of listed database or create new one.

Click New Button to create new database (if needed)

What is the logins that need to maped in MDW database that is specified in prevous step . select logins from the first grid and map it to suitable database role membership in the second grid.

Now the configuration of Data Collection Successfully finished .

Set Up Data Collection

It is now the time of set up Data Collection to enable it work . To do that return again to Data Collection icon in the Managment folder in you instance server. Right click on it select Configure Managment Data Warehouse .

in the Select Configuration Task form select the second option to set up Data collection .

Specify the previous database created for Data Collection (MDW database). the collection data by default cached to Temp directory before upload it to MDW database. if needed to change temp directory, type the alternative path in Cache direcroty field that shown below.

after review all steps click Finish to start set up Data Collection.

Now Data Collection ready to Collect and upload result to created MDW database that let to administrator to review result by reports of Data Collection .

The results of set up data collection .

the set up od Data Collection create three Collection Sets named (Disk usage, Query Statistics and Server Activity) each of them collect and upload specific information in MDW database .

Note that all collection sets start by default, it can be stoped by right click on specific collection set and then select Stop data Collection Set option.

It can specify also certain data collection set to do it's collection and upload job by option of Collect and Upload Now .

Also note the creation of the following jobs in SQL Server Agent. Each of collection set has specific jobs for collect and upload data.

Reports of Data Collection

Right Click on Data Collection, Select Report > Managment Data Warehouse. then the list of report will show three kinds of report (Disk usage, Query Statistics, Server Activity)

Disk usage

Query Statistics

Server Activity

Using Transparent Data Encryption Feature of SQL Server 2008

2011-06-27T06:19:00.000-07:00

The following example describe how to protect you database backup by using Transparent Data Encryption

-- create database
create database mydb1
go
use mydb1
go

-- create table mytable in database mydb1
create table mytable
(
id int primary key,
nam char(10)
)

-- insert record to table
insert into mytable values (1,'Mohamed')

-- make a backup for database mydb1
backup database mydb1 to disk ='c:\myback.bak'

goto file myback.bak that is found in 'c:\', open it by Notepad program and try to search for a keyword 'Mohamed'. note that it's can clearly see data into backup file.

to protect data in backup, do the following steps.

-- create master key for your instanceUSE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mY_P@$$w0rd'

-- create certificate object for your instance
CREATE CERTIFICATE mycert
WITH SUBJECT = 'My Certificate for database'

-- in specific database 'mydb1' create encryption key that associated with
-- certificate object of server that created in previous block.
USE mydb1
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE mycert
GO

-- open feature of encryption in database scope
ALTER DATABASE mydb1
SET ENCRYPTION ON
-- try to make backup again
backup database mydb1 to disk ='c:\myback2.bak'

goto again to 'c:\' try to open file 'myback2.bak' by Notepad program, note that all content of backup encrypted now.

now if need to restore this encrypted backup into another database in diffirence instance, the following error will be occurs.

To restore this backup without any error, wite the following code in the first server (the server which hold the main database)

USE master
GO
BACKUP CERTIFICATE mycert
TO FILE = 'C:\mydb_File.cer'
WITH PRIVATE KEY (FILE = 'C:\mydb_Key.pvk' ,
ENCRYPTION BY PASSWORD = 'mY_P@$$w0rd' )
Go

The above code create backup for the exist server certificate to file named 'mydb_file.cer' and backup key of database also (private key) in file 'mydb_key.pvk'

Now go to another server (which want to restore encypted backup) and write the following code.

-- creating master key for another server instance
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'EnKrYpt3d_P@$$w0rd'
Go

-- create certificate object for this server instance, this certificate
-- restored from file 'pvk', 'cer'
CREATE CERTIFICATE Inst2Cert
FROM FILE = 'C:\mydb_File.cer'
WITH PRIVATE KEY (FILE = 'C:\mydb_Key.pvk',
DECRYPTION BY PASSWORD = 'mY_P@$$w0rd');
GO

The data retored successfuly now in the second server.

** Some Verification for SQL object

* Verify Master key on the server

USE master
GO
SELECT * FROM sys.symmetric_keys WHERE name LIKE '%MS_DatabaseMasterKey%'
GO

* Verify Certifications for master key

SELECT * FROM sys.certificates

* Verify database encryption states

SELECT
DB_NAME(database_id) AS DatabaseName
,Encryption_State AS EncryptionState
,key_algorithm AS Algorithm
,key_length AS KeyLength
FROM sys.dm_database_encryption_keys
GO

and also

SELECT
NAME AS DatabaseName
,IS_ENCRYPTED AS IsEncrypted
FROM sys.databases
GO

SQL Server 2008 Implementing Log Shipping

2011-06-20T05:18:00.000-07:00

In this post

- Introduction
- Process of Log Shipping
- Log Shipping topology
- Log Shipping Prerequisites
- Step by step example
- How to Failing Over
- Conclusion
- Resources

Introduction

SQL Server provide a way to backups automation that let to others servers to maintain these backups in the scale out of the main server. it's Log Shipping ! . Log Shipping is a way to make high availability to solutions by making backups from the main server and restore it to one or more other servers . administrator can recover the main SQL server database (for any fail) from other servers by restore the database from one of other server to main server.

Process of Log Shipping

Log Shipping focus on transaction log file of the database . Log Shipping make a backup for that type of file only . therefore the recovery mode for the main database must be in Full or Bullk-logged option.

the following is the main processes of log shipping work .
1- Backup transaction log file of the main database to a folder founded in the main server.
2- Coping the transaction log backup from main sql server to one or more other sql server
3- Restore the transaction log backup that is exists in others sql servers.

Log Shipping topology

Log shipping consists of the following servers.

- Primary Database Server : that contain the primary database the need to maintain high availability.
- Secondary Database Server : in this server the second database that save the transaction log data from the primary database server
- Monitor Server : track the process of log shipping process in other both servers (primary, secondary).

Log Shipping Prerequisites

1- Primary database must to be a Full or Bulk-Logged recovery model.
2- Configurations need high permissions on SQL server and SQL Server Agent Service also.
3- at least two Database Servers or two SQL Server instances.
4- Folder for copying the transaction logs must be Shared and has a suitable permissions access.

Step by step example

1- First, SQL Server Agent Service for both server (Primary, Secondary) must enabled (Started Service)

2-Right Click on primary database for primary server. and then select Properties. from the left pane select Transaction Log Shipping .

3- click on checkbox Enable this as a primary database in a log shipping configuration to enable log shipping in this database. and then click on Backup Settings button.

4- in Backup Settings window, set network path for backup folder . this path let sql server in primary server to save backup result on it. and also you can set path in local machine (in the second textbox) if backup result will be set on the primary server not in nework path.

it's mandatory to put network path to can leave this step. Consider also that network path must be a shared folder with suitable permission to let log shipping done task job well.

after that click on Schedule Button to plan the time of backup job implementation.

5- Returning again to Database Properties window to setting up the first secondary SQL Server. in the intermediate part of window ckick on Add Button .

Press Connect Button, and select your second SQL Server.

after selecting the second server, select the first option in the Initialize Secondary Database tab. this option mean that log shipping will generate a full backup of the primary database and restore it into the secondary database . log shipping will create the secondary database if it doesn't exist.

Now, goto second tab CopyFiles to set path folder for coping backup files after backup job finish. this coping process need to automate . for automate that press the Schedule button to set appropriate time job .

in the last tab Restore transaction log select the second option Standby mode . the Standby mode mean that second database in the second server will be read only database. it's suitable situation if need working with second database by select query only, it's let customers to connect to this database for reading but it's reading only.

there is second option No recovery mode that is mean the second database will not be available for any one , it's just working for log shipping processes.

select on Schedule button to set appropriate time job for recovering.

after finish three tab options press ok to return Database Properties window for primary database. the secondary database will appear like this.

press Add button to add another SQLServer that will act as others secondary database servers.

6- in the last part of Database Properties window, optionally click on use a monitor server instance checkbox option to determine a sql server that act Monitor Server.

7- in Database Properties window click ok, to take processes of log shipping work.

8- in the stage the log shipping work well . to check the effects of log shipping check the added jobs in the both SQL agent service for two servers.

Note that because of Standby mode that selected for second database server, the second database server will act a read only database as show below.

checkup also the result file in both paths that sets in the log shipping configuration .

How to Failing Over

1- After failing primary server, it's may be to find backup files in the backup folder not transfered to destination folder. copy all that files that is not transfered to destination folder(is the secondary server).

2- Restore all tranaction log backup files in the destination folder sequencelly (identify that by date of files) to secondary sql server.

3- If it's possible to access primary database server take a (Tail log) backup (BACKUP LOG-WITH NO_RECOVERY) this leaves the database in the inconsistent state (restoring state) . and then restore (tail log) backup to secondary SQL server with a (RESTORE LOG-WITH RECOVERY)

after these steps the primary database in unavailable and second database is online now, it's time for administrator now to redirect users to second server.

Conclusion

Log shipping is a way from ways that can achive a level of high availability, design a suitable topology with right configuration with prerequisites to have a higher power benefits of log shipping.

Resources

Failing Over to a Log Shipping Secondary
http://msdn.microsoft.com/en-us/library/ms191233.aspx

SQL Server 2008 Log Shipping
http://blogs.technet.com/b/josebda/archive/2009/04/02/sql-server-2008-log-shipping.aspx

An Overview of Log Shipping in SQL Server 2005
http://www.codeproject.com/KB/database/LogShipping.aspx

Microsoft Baseline Security Analyzer (MBSA)

2011-06-11T16:43:00.000-07:00

MBSA is a free tool for administrators to checkup the states of SQL Server in several aspects. it can scan multiple server to search about needed hotfixes and issues related to appling security on server in specific (password and user account) .

check the following link for more detail and download MBSA .

http://technet.microsoft.com/en-us/security/cc184924

Sample Replication Peer-To-Peer SQL Server 2008 .

2011-06-09T03:23:00.000-07:00

What is Peer-To-Peer Replication ?

By the declaration of MSDN for Peer-To-Peer Replication 'Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes.'

The benefits of the P2P Replication

1- Read Increase : any inserted data for any nodes will be spread to other nodes.
2- Maintains availability : if any node will be down then the application layer can redirect to a complete data for any other active node.
3- Online/Offline data availability : any node can work out side the replication (for administarative tasks or maintenance requirement) and then return back to P2P replication to get all lost data form other nodes.

The Topologies of P2P Replication

the following topology dislpay the interaction between clients and SQL Server databases in the IT comany infrastructure.

Another topologies.

our example assume working with two instance (SERVER08) and (SERVER08\INST2) . we need to have a replicate between two instances.

before anything run the following script, it's about creating database with table and insertions for this table. this database is our target of working with peer to peer (p2p) between two instances.

create database MySampleDB

go
use MySampleDB
go
create table Emp_Tbl
(
id int primary key ,
nam nvarchar(max)
)
go
insert into Emp_Tbl values(1,'ahmed')
insert into Emp_Tbl values(2,'mohamed')
insert into Emp_Tbl values(3,'emad')
insert into Emp_Tbl values(4,'tareq')

1- the first step to configure p2p for two instance, is check the instances services and agents in the SQL Server Configuration Manager

the highlight services in the last pic must to be run before working with p2p replication .

2- after that goto SQLServer database that is found in (SERVER08) and make full backup to c:\mybackup.bak.

3- restore this backup to another instance (SERVER08\INST2).

4- now we need to configure Distribution server for both instances. each inctance will have a separate Distribution server .

5- Configure Distribution server for (SERVER08)

6- Configure Distribution server for (SERVER08\INST2)

it's the same with previous step, but with (SERVER08\INST2) instance.

7- Return again to (SERVER08) .

form the local publication folder in the Replication folder . right click and select New Publication.

select your database from the list

for working with P2P replication you must select transactional replication from the list of publication type

select your objects that you want to replicate

if you need any filters about your data.

you can select from one or two option in Snapshot Agent , select the first option to create snapshot immediately . if you need to make scheduale you can select the second option also.

your agent need to set security, click on the Security Settings button

set administration account here by 'yourdomain\youraccount' template and set administrator password.

type your publication name.

8- after finish the publication creation, right click on it and then select properties

from subscription options tab, select Allow peer-to-peer subscriptions option and change value from Flase to True. and then click OK.

9- Return to publication item (MyPublication) again in the first server (Server08), then right click and select

Configure Peer-To-Perr Topology

this option will let chance to relate multi-nodes together to distribute data between them .

select your publication to configure there topology

this screen will lead to draw all your nodes that wanted to be added and links together. if you select any node you will see ToolTip contain infomarion about this node.

Remember that node mean SQLServer Device or instance that will be found in your Topology .

To add new node in your topology, right click on any empty area inside your topology and then select Add a New Peer Node .

Select the second instance you want to add (or second server) .

after that select your database that will act a replication in the second server (in our example you will select thr recovered database that you do that in previous steps)

if you need SQL Server to link your new node directly with the exist nodes in the topology , check on the option Connect to ALL displayed nodes. and take your new node a new rank by the Peer Originator ID

after that your topology will display your new node with other nodes. you can repeat this step again with new nodes .

all your new nodes will appear in the Log Reader Agent Security window. your need here to set security setting about agent to let replicate use log reader with a wright context security.

Here, set security agent for replicate data distribution. if you need to one context security for all nodes , check on below option.

In this step you need to tell thew wizard how can get data for new peers. if you have a backup, wizard can restore it for you [the second selection] . or you can do that manually (that is our situation) [the first selection]

your can check your affect of your P2P replication by view Replication folder for each instance. you can summarize that each node will act (Publication and Subscriber) for others.

10 - Now you test your replication between your inctances by inserting new records in both tables for each database instance .

In server 'Server08'
insert into dbo.Emp_Tbl values (5,'taha')

In server 'Server08\Inst2'
select * from MySampleDB.dbo.emp_tbl -- your will see new record added

In server 'Server08'

insert into dbo.Emp_Tbl values (5,'taha')

In server 'Server08\Inst2'
select * from MySampleDB.dbo.emp_tbl -- your will see new record added

Working with CURSOR

2011-06-04T08:08:00.000-07:00

it's old fasion but important in some cases

DECLARE @AuthorID char(11)

DECLARE c1 CURSOR READ_ONLY
FOR
SELECT au_id
FROM authors

OPEN c1
FETCH NEXT FROM c1 INTO @AuthorID

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AuthorID
FETCH NEXT FROM c1
INTO @AuthorID
END

CLOSE c1
DEALLOCATE c1

SQL Server 2008 (T-SQL) Creating FullText Index

2011-06-04T05:47:00.000-07:00

this example work on adventureworks database.

for working with fulltext index, we need to create index on table . the following code create table with DocID . this column datatype is int with primary kry. note also the column DocContent with varbinary(max) and column DocSummary with nvarchar(max) . the last two column is our target of creating fulltext index.

CREATE TABLE _My_ProductDocs (
DocID INT NOT NULL IDENTITY,
DocTitle NVARCHAR(50) NOT NULL,
DocFilename NVARCHAR(400) NOT NULL,
FileExtension NVARCHAR(8) NOT NULL,
DocSummary NVARCHAR(MAX) NULL,
DocContent VARBINARY(MAX) NULL,
CONSTRAINT [PK_ProductDocs_DocID] PRIMARY KEY CLUSTERED (DocID ASC)
)
GO

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

Now we need to filling data from Production.Document (adventureworks DB) to our created table.

INSERT INTO _My_ProductDocs
(DocTitle, DocFilename, FileExtension, DocSummary, DocContent)
SELECT Title, FileName, FileExtension, DocumentSummary, Document
FROM Production.Document
GO

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

Showing data inserted in the our table

select * from _My_ProductDocs

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

First step now is creating FULLTEXT Catalog.

--Creating the Full-Text Catalog
USE AdventureWorks
GO
CREATE FULLTEXT CATALOG My_ProductFTS
WITH ACCENT_SENSITIVITY = OFF

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

To know defalut database collation, you can select from system view sys.databases the column name collation_name will tell about the collation of the selected database.

SELECT name, collation_name FROM sys.databases
WHERE name = 'AdventureWorks'

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

Verify that the catalog has been created

SELECT fulltext_catalog_id, name FROM sys.fulltext_catalogs

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

define a full-text index . it's need in this step to specify your columns that you need to make your fulltext index. in the following code fulltext index will create on our table _My_ProductDocs and use the columns (DocSummary - DocContent) . because of the DocContent has varbinary(max) datatype you can specify TYPE COLUMN keyword, it's help SQL Server to determine the type file for each cell binary in the DocContent column . also you need to specify the type of your language you will use by your fulltext index it's make by keyword LANGUAGE with the number of your language.

at general, you can't create fulltext index without a primarykey in your table. the creation of your fulltext index will use the primarykey constraint of your table PK_ProductDocs_DocID . fulltext index need also to associate himself with the name of specific fulltext catalog My_ProductFTS .

finally, you can attach s stoplist items for you fulltext index. stoplist item will help SQL Server to detect noisy words that you don't like to be included in your fulltext index.
the line of code WITH STOPLIST = SYSTEM mean that your fulltext index will be use the system STOPLIST the is found in SQL Server. you can make you owen list and configure that .

CREATE FULLTEXT INDEX ON _My_ProductDocs
(DocSummary, DocContent TYPE COLUMN FileExtension LANGUAGE 1033)
KEY INDEX PK_ProductDocs_DocID
ON My_ProductFTS
WITH STOPLIST = SYSTEM

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

Create a stoplist

CREATE FULLTEXT STOPLIST ProductSL
FROM SYSTEM STOPLIST;

Verify that the stoplist has been created

SELECT stoplist_id, name FROM sys.fulltext_stoplists

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

You can view stoplist contents

SELECT stopword FROM sys.fulltext_stopwords
WHERE stoplist_id = 5 AND language_id = 1033

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

Add words to stoplist

ALTER FULLTEXT STOPLIST ProductSL
ADD 'nuts' LANGUAGE 1033;

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

Test how SQL Server will tokenize a string based on a specific language and stoplist

SELECT special_term, display_term
FROM sys.dm_fts_parser
(' "testing for fruit and nuts, any type of nut" ', 1033, 5, 0)

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

Drop “nuts” from the stoplist

ALTER FULLTEXT STOPLIST ProductSL
DROP 'nuts' LANGUAGE 1033;
-----------------------------------------------------------------------------------

Modify FULTEXT INDEX with new STOPLIST

ALTER FULLTEXT INDEX ON _My_ProductDocs
SET STOPLIST ProductSL

--------------------------------------------------------------------------------
Return a list of the document types supported by full-text search

SELECT document_type, version, manufacturer
FROM sys.fulltext_document_types

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

View a list of the identifiers and their associated languages

SELECT * FROM sys.fulltext_languages
ORDER BY lcid

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

Verify the index has been created and is associated with the correct catalog

SELECT t.name AS TableName, c.name AS FTCatalogName
FROM sys.tables t JOIN sys.fulltext_indexes i
ON t.object_id = i.object_id
JOIN sys.fulltext_catalogs c
ON i.fulltext_catalog_id = c.fulltext_catalog_id

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

Lists the contents of the index

SELECT display_term, column_id, document_count
FROM sys.dm_fts_index_keywords
(DB_ID('AdventureWorks'), OBJECT_ID('_My_ProductDocs'))

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

Modifying the Full-Text Thesaurus

EXEC sys.sp_fulltext_load_thesaurus_file 1033

encryption and decryption without keys or certificate

2011-06-04T05:32:00.000-07:00

the following code will encrypt data and decrypt that with a simple two function EncryptByPassPhrase and DecryptByPassPhrase . the encryption with this function use Triple DES encryption algorithm by default.

by two side of encry or decry, you specify your keyword (the formula that will use for encry and decry). if you encry data by keyword 'cairo city' for example, then you must decry you data by the same keyword. if you use another keyword you will fail to decry your data.

Note that every time you make a encry for fixed string with same keyword encry the result will difference.

DECLARE @org_text nvarchar(1000), @enc_text varbinary(2000);
SET @org_text = N'Type you data here';
SET @enc_text = EncryptByPassPhrase(N'your keyword', @org_text);
SELECT 'Original text = ', @org_text;
SELECT 'Encrypted text = ', @enc_text;
SELECT 'Decrypted plain text = ',
CAST(DecryptByPassPhrase(N'your keyword', @enc_text) AS nvarchar(1000));

SQL Server encryption and decryption data with keys and certificate

2011-05-31T08:25:00.000-07:00

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

2011-05-30T13:24:00.000-07:00

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

2011-05-30T02:07:00.000-07:00

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

2011-05-29T07:30:00.000-07:00

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

2011-04-10T18:32:00.000-07:00

SQL Server has two types of extents:

Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.
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

2010-05-22T06:31:00.000-07:00

- 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 ?

2010-05-22T06:22:00.000-07:00

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

Examples About Spatial Data SQL08

2010-03-20T05:45:00.000-07:00

-- Execute a query that inserts the following values into the Store table by using the Parse method of the geography data type

INSERT INTO Store (StoreName, StoreAddress, StorePhone, StoreLocation) VALUES ('Parts Supply', '2532 Fairgrounds Road, West Kingston, RI 02892', '623-555-0153', geography::Parse('POINT(-71.548531 41.497478)'))

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

-- Execute a query that inserts the following values into the Store table by using the Point method of the geography data type

INSERT INTO Store (StoreName, StoreAddress, StorePhone, StoreLocation) VALUES ('Two Bike Shops', '4567 9th Street SW, Puyallup, WA 98371', '724-555-0161', geography::Point('47.156622', '-122.30383', 4326))
---------------------------------------------------------------------------------------

-- Execute a query that inserts the following values into the Store table by using the STGeomFromText method of the geography data type

INSERT INTO Store (StoreName, StoreAddress, StorePhone, StoreLocation) VALUES ('Eastside Department Store', '9992 Whipple Rd, Union City, CA 94587', '926-555-0164', geography::STGeomFromText('POINT(-122.084552 37.603936)', 4326))

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

-- Execute a query that inserts the following values into the Store table by using the STGeomFromWKB method of the geography data type

INSERT INTO Store (StoreName, StoreAddress, StorePhone, StoreLocation) VALUES ('Fitness Hotel', '7901 France Ave S, Edina, MN 55410', '377-555-0132', geography::STGeomFromWKB(0x0101000000DC4603780B5557C0EF535568206E4640, 4326))

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

-- Execute a query that inserts the following values into the Store table by using the GeomFromGml method of the geography data type .

Please note: try to replace the symble '<' or '>' to '[' or ']'

INSERT INTO Store (StoreName, StoreAddress, StorePhone, StoreLocation) VALUES ('Aerobic Exercise Company', '39933 Mission Oaks Blvd, Camarillo, CA 93010', '244-555-0112', geography::GeomFromGml('[point xmlns="[a href="]http://www.opengis.net/gml[/a]"> [pos]34.235504 -118.988734[/pos] [/point]', 4326))

How to View XML Schema Information

2009-11-16T16:47:00.000-08:00

You can view information about the schema collections in a database by querying the sys.xml_schema_collections catalog
view, as shown in the following example.

SELECT * FROM sys.xml_schema_collections

You can also retrieve the individual XML namespaces defined in the database’s schema collections by querying the
sys.xml_schema_namespaces catalog view, as shown in the following example.

SELECT * FROM sys.xml_schema_namespaces

You can view the XML components defined in the database by querying the sys.xml_schema_components catalog view, as
shown in the following example.

SELECT * FROM sys.xml_schema_components