Reason #1 to Upgrade to SQL 2012

Reason #1 to Upgrade to SQL 2012

17:48 by Gourav G.

AlwaysOn + Availability Groups = “load balanced” SQL

Have I piqued your interest yet?  SQL 2012 introduces many new features that will make life more efficient for developers, administrators, and report writers, but what is the big, bottom line, “Why do I upgrade” feature?  The answer is AlwaysOn. Here are some details on how you can implement a load balancing design.

AlwaysOn in default configuration greatly streamlines setting up a traditional mirrored cluster, but we need to move beyond that to see this application truly shine.  SQL 2005 through 2008 R2 clustering utilized a single server for all workloads, but you still had to buy another equally powerful server for failover replica (see Figure 1).  In SQL Server 2012 you can redirect some of the day to day workload (ready only operations OR Availability Groups) to the secondary server to utilize that resource from Day 1.   In other words, you don’t have to watch the second server contribute zero day-to-day operations workload while waiting for a failover event.

Using AlwaysOn for load balancing, then, you have two options:

Option 1: Load balancing through Read Only replica:

How to: Add a synchronous replica to the AlwaysOn group and specify it as a read-only replica.  See figure 2.  The AlwaysOn listener knows to redirect queries and reports that are read-only to that 2nd replica instead of to the primary instance.  SELECT queries pulling data to CRM applications also get offloaded to the secondary server further alleviating load from production.

Why this method: This approach transfers the load from that accounting manager who wants to run a tweaked report every thirty minutes away from your production server.

Option 2: Load balancing through Availability Groups (AGs):

How to: Availability Groups allow individual databases to be grouped together for failover purposes.  This way, a database (or group of databases) can failover together instead of failing over the entire instance.  You can group databases together by application or combine them by usage.  To load balance your AGs, split up your heavy use databases in the same AlwaysOn instance to separate AGs.   Set the AG’s primary servers to be different nodes.

For example , AG1 and AG2 are in AlwaysOn instance given a virtual name.  They each contain a dozen different databases for different applications and share the primary node SERVER1 in the Windows Server Failover Cluster (back to Figure 1).  Applications for 1 and 2 are heavily utilized, so you want to spread the load.  You can do that by moving the primary node for AG2 to the replica instance and immediately the load on SERVER1 falls (Figure 3).

AlwaysOn benefits for techies: Utilizing AlwaysOn, the cluster as a whole has a significant amount of breathing room for more capacity growth whereas previously a significant hardware investment had to be made to meet demands.  SQL Server 2012 AlwaysOn + Availability Groups is a combination of clustering and mirroring that creates many exciting scenarios that we have not had before in SQL.  It also includes a performance dashboard in SQL Server Management Studio to graphically view the state of your groups.

AlwaysOn benefits for the business: For the cost to license the old passive server, you can immediately double (theoretically) the capacity of your SQL server by using strategically distributed Availability Groups.  The return on that license investment begins to be realized on Day 1 as the load is spread across your expensive database servers.

No Comments

SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database

SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database

17:04 by Seema

In this article, I discuss about the Database Mail which is used to send the email using SQL Server.

Database mail is the replacement for SQLMail with many enhancements. So one should stop using SQLMail and upgrade to the Database Mail.

In order to send mail using Database Mail in SQL Server, there are 3 basic steps that need to be carried out:

1. Create Profile and Account
2. Configure Email
3. Send Email

Step 1: Create Profile and Account

You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node.

This wizard is used to manage accounts, profiles, and Database Mail global settings which are shown below:

step 1.1

step 1.2

step 1.3

step 1.4

step 1.5

step 1.6

step 1.7

step 1.8

step 1.9

step 1.10

step 1.11

Step 2: Configure Email

After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:

sp_CONFIGURE 'show advanced', 1 GO RECONFIGURE GO sp_CONFIGURE _
 'Database Mail XPs', 1 GO RECONFIGURE GO 

Step 2

Step 3: Send Email

After all configurations are done, we are now ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:

USE msdb GO EXEC sp_send_dbmail @profile_name='PinalProfile', _
@recipients='test@Example.com', @subject='Test message', _
@body='This is the body of the test message. _
Congrats Database Mail Received By you Successfully.' 

Step 3.1

After all validations of the parameters entered are done, certain stored procedures are executed and the mail is queued by Service Broker. Read more at SQL SERVER - Introduction to Service Broker.

Database Mail keeps copies of outgoing e-mail messages and displays them in the sysmail_allitems,sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems. The status of the mail sent can be seen in sysmail_mailitems table. When the mail is sent successfully, the sent_status field of thesysmail_mailitems table is set to 1 which can again be seen in sysmail_sentitems table. The mails that have failed will have the sent_status field value to 2 and those are unsent will have value 3. The log can be checked in sysmail_log table as shown below:

SELECT * FROM sysmail_mailitems GO SELECT * FROM sysmail_log GO 

Step 3.2

Status can be verified using sysmail_sentitems table.

Step 3.3

After sending mail, you can check the mail received in your inbox.

Important Searches :-

• How to send email from sql server 2008
• How to send email from sql serve
• How to send email from sql 
• How to Configure Mail in Database 
• How to Configure Database Mail

credits - codeproject

No Comments

SQL Server Transaction Log Backups

SQL Server Transaction Log Backups

17:01 by Seema

OverviewIf your database is set to the "Full" or "Bulk-logged" recovery model then you will be able to issue "Transaction Log" backups.  By having transaction log backups along with full backups you have the ability to do a point in time restore, so if someone accidently deletes all data in a database you can recover the database to the point in time right before the delete occurred.  The only caveat to this is if your database is set to the "Bulk-logged" recovery model and a bulk operation was issued, you will need to restore the entire transaction log.

ExplanationA transaction log backup allows you to backup the active part of the transaction log.  So after you issue a "Full" or "Differential" backup the transaction log backup will have any transactions that were created after those other backups completed.  After the transaction log backup is issued, the space within the transaction log can be reused for other processes.  If a transaction log backup is not taken, the transaction log will continue to grow.

A transaction log backup can be completed either using T-SQL or by using SSMS.  The following examples show you how to create a transaction log backup.

---

Create a transaction log backup of the AdventureWorks database to one disk file

T-SQL

BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks.TRN' GO

SQL Server Management Studio

• Right click on the database name
• Select Tasks > Backup
• Select "Transaction Log" as the backup type
• Select "Disk" as the destination
• Click on "Add..." to add a backup file and type "C:\AdventureWorks.TRN" and click "OK"
• Click "OK" again to create the backup

No Comments

How to take File Backups in SQL Server ?

How to take File Backups in SQL Server ?

16:39 by Seema

OverviewAnother option for backing up your databases is to use "File" backups. This allows you to backup each file independently instead of having to backup the entire database. This is only relevant when you have created multiple data files for your database.  One reason for this type of backup is if you have a very large files and need to back them up individually. For the most part you probably only have one data file, so this is option is not relevant.

ExplanationAs mentioned above you can back up each data file individually. If you have a very large database and have large data files this option may be relevant.

A file backup can be completed either using T-SQL or by using SSMS. The following examples show you how to create a transaction log backup.

---

Create a file backup of the TestBackup database

For this example I created a new database called TestBackup that has two data files and one log file. The two data files are called 'TestBackup' and 'TestBackup2'. The code below shows how to backup each file separately.

T-SQL

BACKUP DATABASE TestBackup FILE = 'TestBackup' TO DISK = 'C:\TestBackup_TestBackup.FIL' GO BACKUP DATABASE TestBackup FILE = 'TestBackup2' TO DISK = 'C:\TestBackup_TestBackup2.FIL' GO

SQL Server Management Studio

• Right click on the database name
• Select Tasks > Backup
• Select either "Full" or "Differential" as the backup type
• Select "Files and filegroups"
• Select the appropriate file and click "OK"

• Select "Disk" as the destination
• Click on "Add..." to add a backup file and type "C:\TestBackup_TestBackup.FIL" and click "OK"
• Click "OK" again to create the backup and repeat for other files

No Comments

SQL Server Differential Backups

SQL Server Differential Backups

16:34 by Seema

OverviewAnother option to assist with your recovery is to create "Differential" backups.  A "Differential" backup is a backup of any extent that has changed since the last "Full" backup was created.

ExplanationThe way differential backups work is that they will backup all extents that have changed since the last full backup.  An extent is made up of eight 8KB pages, so an extent is 64KB of data.  Each time any data has been changed a flag is turned on to let SQL Server know that if a "Differential" backup is created it should include the data from this extent.  When a "Full" backup is taken these flags are turned off.

So if you do a full backup and then do a differential backup, the differential backup will contain only the extents that have changed.  If you wait some time and do another differential backup, this new differential backup will contain all extents that have changed since the last full backup.  Each time you create a new differential backup it will contain every extent changed since the last full backup.  When you go to restore your database, to get to the most current time you only need to restore the full backup and the most recent differential backup.  All of the other differential backups can be ignored.

If your database is in the Simple recovery model, you can still use full and differential backups. This does not allow you to do point in time recovery, but it will allow you to restore your data to a more current point in time then if you only had a full backup.

If your database is in the Full or Bulk-Logged recovery model you can also use differential backups to eliminate the number of transaction logs that will need to be restored.  Since the differential will backup all extents since the last full backup, at restore time you can restore your full backup, your most recent differential backup and then any transaction log backups that were created after the most recent differential backup.  This cuts down on the number of files that need to be restored.

---

Create a differential backup of the AdventureWorks database to one disk file

T-SQL

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.DIF' WITH DIFFERENTIAL GO

SQL Server Management Studio

• Right click on the database name
• Select Tasks > Backup
• Select "Differential" as the backup type
• Select "Disk" as the destination
• Click on "Add..." to add a backup file and type "C:\AdventureWorks.DIF" and click "OK"
• Click "OK" again to create the backup

No Comments

SQL Server - Generate MD5 hash string with T-SQL

SQL Server - Generate MD5 hash string with T-SQL

16:28 by Seema

SQL Server : Generate MD5 hash string with T-SQL

Here is the code to generate MD5 hash of an email address:

select convert(varchar(32), hashbytes('MD5', 'Test@email.com'), 2)

It will return a 128 bit hash
F71EC72738666D7E6177DFF360BBFA0F

No Comments

Database Management System (DBMS) Definition

Database Management System (DBMS) Definition

11:43 by Seema

A database management system (DBMS) is system software for creating and managing databases. The DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data.

A DBMS makes it possible for end users to create, read, update and delete data in a database. The DBMS essentially serves as an interface between the database and end users or application programs, ensuring that data is consistently organized and remains easily accessible.


The DBMS manages three important things: the data, the database engine that allows data to be accessed, locked and modified -- and the database schema, which defines the database’s logical structure. These three foundational elements help provide concurrency, security, data integrity and uniform administration procedures. Typical database administration tasks supported by the DBMS include change management, performance monitoring/tuning and backup and recovery. Many database management systems are also responsible for automated rollbacks, restarts and recovery as well as the logging and auditing of activity.

The DBMS is perhaps most useful for providing a centralized view of data that can be accessed by multiple users, from multiple locations, in a controlled manner. A DBMS can limit what data the end user sees, as well as how that end user can view the data, providing many views of a single database schema. End users and software programs are free from having to understand where the data is physically located or on what type of storage media it resides because the DBMS handles all requests.

The DBMS can offer both logical and physical data independence. That means it can protect users and applications from needing to know where data is stored or having to be concerned about changes to the physical structure of data (storage and hardware). As long as programs use the application programming interface (API) for the database that is provided by the DBMS, developers won't have to modify programs just because changes have been made to the database.

With relational DBMSs (RDBMSs), this API is SQL, a standard programming language for defining, protecting and accessing data in a RDBMS.



Popular types of DBMSes

Popular database models and their management systems include:

Relational database management system (RDMS)  - adaptable to most use cases, but RDBMS Tier-1 products can be quite expensive.

NoSQL DBMS - well-suited for loosely defined data structures that may evolve over time.

In-memory database management system (IMDBMS) - provides faster response times and better performance.

Columnar database management system (CDBMS) - well-suited for data warehouses that have a large number of similar data items.

Cloud-based data management system - the cloud service provider is responsible for providing and maintaining the DBMS.

Advantages of a DBMS

Using a DBMS to store and manage data comes with advantages, but also overhead. One of the biggest advantages of using a DBMS is that it lets end users and application programmers access and use the same data while managing data integrity. Data is better protected and maintained when it can be shared using a DBMS instead of creating new iterations of the same data stored in new files for every new application. The DBMS provides a central store of data that can be accessed by multiple users in a controlled manner.

No Comments

How to get Database Backup History for a Single Database ?

How to get Database Backup History for a Single Database ?

16:09 by Gourav G.

Here is the script suggested by SQL Expert, who has written excellent script which goes back and retrieves the history of any single database.

USE AdventureWorks
GO-- Get Backup History for required databaseSELECT TOP 100
s.database_name,m.physical_device_name,CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB'AS bkSize, CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,s.backup_start_date,CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,CASE s.[type] WHEN 'D' THEN 'Full'WHEN 'I' THEN 'Differential'WHEN 'L' THEN 'Transaction Log'END AS BackupType,s.server_name,s.recovery_modelFROM msdb.dbo.backupset sINNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id =m.media_set_idWHERE s.database_name = DB_NAME() -- Remove this line for all the databaseORDER BY backup_start_date DESC, backup_finish_date
GO

Very neat script and in my above example I have ran that for single database adventureworks and you can see following results. The same can be ran for multiple database as well if you just remove the WHERE condition.

No Comments