Saturday, September 24, 2011

SQL Server Code Name 'Denali' steps installation

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

Tuesday, August 2, 2011

SQL Server 2008 Database Mail, step by step.

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;
sp_configure 'Database Mail XPs', 1;
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 ( 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='', -- 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,

Thursday, June 30, 2011

Database Mirroring in SQL Server 2008

In this post

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


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

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.
·         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 .
·         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.
·         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.
·         this state found only on the principal server after failover has begun but the server has not transitioned into the mirror role.
·         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.


For more details please checkout the following links,

Database Mirroring

Database Mirroring Best Practices and Performance Considerations

Follow by Email