ACID Properties

In the context of database transaction processing, the acronym ACID refers to the four key properties of a transaction:

Atomicity
• Consistency
• Isolation
• Durability

Atomicity : All changes to data are performed as if they are a single operation. That is, all the changes are performed, or none of them are.For example, in an application that transfers funds from one account to another, the atomicity property ensures that, if a debit is made successfully from one account, the corresponding credit is made to the other account.

Consistency :  data is in a consistent state when a transaction starts and when it ends.For example, in an application that transfers funds from one account to another, the consistency property ensures that the total value of funds in both the accounts is the same at the start and end of each transaction.

Isolation :  The intermediate state of a transaction is invisible to other transactions. As a result, transactions that run concurrently appear to be serialized.For example, in an application that transfers funds from one account to another, the isolation property ensures that another transaction sees the transferred funds in one account or the other, but not in both, nor in neither.

Durability : After a transaction successfully completes, changes to data persist and are not undone, even in the event of a system failure.For example, in an application that transfers funds from one account to another, the durability property ensures that the changes made to each account will not be reversed.

Advertisements

SSIS – Non Blocking , Partially Blocking and Full Blocking

Data flow transformations in SSIS use memory/buffers in different ways.  The way transformation uses memory can impact the performance of your package.  Transformations memory/Buffer usage are classified into 3 categories:

1.Non Blocking  2.Semi Blocking  3. Full Blocking

All the dataflow components can be categorized to be either Synchronous or Asynchronous.

Synchronous vs Asynchronous : 

  • Synchronous components The output of an synchronous component uses the same buffer as the input.  Reusing of the input buffer is possible because the output of an synchronous component always contain exactly the same number of records as the input.                                                                                                                               Number of records IN == Number of records OUT.
  • Asynchronous components The output of an asynchronous component uses a new buffer. It’s not possible to reuse the input buffer because an asynchronous component can have more or less output records than input records.

One Main thing is that synchronous components reuse buffers and therefore are generally faster than asynchronous components.

All source adapters are asynchronous, they create two buffers; one for the success output and one for the error output.

All destination adapters on the other hand, are synchronous.

Here are some tips that will help you minimize your use of asynchronous transformations:

  • Instead of using a Merge Join in your Data Flow, perform the joins in the source query or in a transform environment.
  • If you absolutely have to use Merge Join, make sure that you sort the data in your source query. In the Advanced Editor, set the “IsSorted” property to true, and set the Sort Key Position on the Output Columns to the appropriate values.
  • Instead of using an Aggregate transform do perform a count, consider using Row Count, which is a synchronous transformation.
  • If you are using a Pivot or Unpivot transformation and it is performing poorly, consider using staging tables in your solution in order to leverage your SQL server environment to perform these transformations instead of doing it in SSIS.
  • Even though Union All is a semi-blocking asynchronous transformation, you will probably not achieve better performance by replacing the Union All with multiple inserts.
Non-blocking Semi-blocking Fully-blocking
Synchronous or asynchronous Synchronous Asynchronous Asynchronous
Number of rows in == number of rows out True Usually False Usually False
Must read all input before they can output False False True
New buffer created? False True True

SSIS transformations categorized:

Non-Blocking transformations Semi-blocking transformations Blocking transformations
Audit Data Mining Query Aggregate
Character Map Merge Fuzzy Grouping
Conditional Split Merge Join Fuzzy Lookup
Copy Column Pivot Row Sampling
Data Conversion Unpivot Sort
Derived Column Term Lookup Term Extraction
Lookup Union All
Multicast
Percent Sampling
Row Count
Script Component
Export Column
Import Column
Slowly Changing Dimension
OLE DB Command

SSIS to SQL Server Data Types Matching

SSIS Data Type

SSIS Expression

SQL Server

single-byte signed integer

(DT_I1)

 

two-byte signed integer

(DT_I2)

smallint

four-byte signed integer

(DT_I4)

int

eight-byte signed integer

(DT_I8)

bigint

single-byte unsigned integer

(DT_UI1)

tinyint

two-byte unsigned integer

(DT_UI2)

 

four-byte unsigned integer

(DT_UI4)

 

eight-byte unsigned integer

(DT_UI8)

 

float

(DT_R4)

real

double-precision float

(DT_R8)

float

string

(DT_STR, «length», «code_page»)

char, varchar

Unicode text stream

(DT_WSTR, «length»)

nchar, nvarchar, sql_variant, xml

date

(DT_DATE)

date

Boolean

(DT_BOOL)

bit

numeric

(DT_NUMERIC, «precision», «scale»)

decimal, numeric

decimal

(DT_DECIMAL, «scale»)

decimal

currency

(DT_CY)

smallmoney, money

unique identifier

(DT_GUID)

uniqueidentifier

byte stream

(DT_BYTES, «length»)

binary, varbinary, timestamp

database date

(DT_DBDATE)

date

database time

(DT_DBTIME)

 

database time with precision

(DT_DBTIME2, «scale»)

time(p)

database timestamp

(DT_DBTIMESTAMP)

datetime, smalldatetime

database timestamp with precision

(DT_DBTIMESTAMP2, «scale»)

datetime2

database timestamp with timezone

(DT_DBTIMESTAMPOFFSET, «scale»)

datetimeoffset(p)

file timestamp

(DT_FILETIME)

 

image

(DT_IMAGE)

image

text stream

(DT_TEXT, «code_page»)

text

Unicode string

(DT_NTEXT)

ntext

SSIS Data Flow Tasks with Descriptions

S No Transformation Description
1 Aggregate Summing / Averaging a total of the products purchased by a customer online to produce the final amount.
2 Audit When you want to load audit information like (Created Date, UserName , server Name..etc) to your Destination table.
3 Character Map String Manipulation, like Lowercase, Uppercase etc..
4 Conditional Split When you want to split your data to many destinations based on conditions.
5 Copy Column To have same column data with alias name
6 Data Conversion To convert the data type, (Unicode String àString…)
7 Data Mining Query Evaluating the input data against the analysis model to get a proper set.
8 Derived Column Adding a title of courtesy (Mr., Mrs., Dr, etc) before the name and removing the trailing and ending spaces.
9 Export Column When we get the normal files/pdf files/image files from different systems and save it under a particular folder and map it to the table master
10 Fuzzy Grouping Matching the name of a customer with master and child table and use it to group and get the desired set
11 Fuzzy Lookup Matching the name of a customer with master and child table and use it to group and get the desired set
12 Import Column When we get the normal files/pdf files/image files from different systems and save it under a particular folder and map it to the table master
13 Lookup Employee table information saved in a master file and the region wise data available across the table which can be mapped and joined to perform a joined querying operation
14 Merge Combine data from multiple data source like master and child employee table and get result in single dataset.
15 Merge Join Combine data from multiple data source like master and child employee table and get result in single dataset. Can use any type of join like inner, outer, left , right etc
16 Multicast Similar to the conditional split but this splits across all the parts
17 OLE DB Command Used when we need to do updates to all the rows of a table like update If a message sent to the entire customer who have made a payment today.
18 Percentage Sampling Similar to “SELECT TOP 10 PERCENT” in TSQL
19 Pivot To Convert Rows to Column and Column to Rows
20 Row Count Gives the Count of Rows
21 Row Sampling Similar to “SELECT TOP 10 ” in TSQL
22 Script Component Used for places where we need to use .net framework specific assemblies.
23 Slowly Changing Dimension When we need to use some historic dimensions of data
24 Sort To make some sorting to get the desired result. Sorting like customer who made the highest payment in a particular day.
25 Term Extraction Used to get a data from a large set of data and get the extracted output in a formatted set.
26 Term Lookup Used to get a data from a large set of data and get the extracted output in a formatted set.
27 Union All Used to get data from different data sources and get in a single dimensional format.
28 Unpivot Reverse of PIVOT Operation

Log Shipping Complete Tutorial Part 2 Step by Step Log shipping

Permissions

To setup a log-shipping you must have sysadmin rights on the server.

Minimum Requirements

  1. SQL Server 2005 or later
  2. Standard, Workgroup or Enterprise editions must be installed on all server instances involved in log shipping.
  3. The servers involved in log shipping should have the same case sensitivity settings.
  4. The database must use the full recovery or bulk-logged recovery model
  5. A shared folder for copying T-Log backup files
  6. SQL Server Agent Service must be configured properly

In addition, you should use the same version of SQL Server on both ends. It is possible to Log Ship from SQL 2005 to SQL 2008, but you can not do it the opposite way. Also, since Log Shipping will be primarly used for failover if you have the same versions on each end and there is a need to failover you at least know you are running the same version of SQL Server.

Steps to Configure Log-Shipping:

1. Make sure your database is in full or bulk-logged recovery model. You can change the database recovery model using the below query. You can check the database recovery model by querying sys.databases

SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'DBName'

USE [master]
GO
ALTER DATABASE [DBName] SET RECOVERY FULL WITH NO_WAIT
GO

2. On the primary server, right click on the database in SSMS and select Properties. Then select the Transaction Log Shipping Page. Check the “Enable this as primary database in a log shipping configuration” check box.

setting up log shipping for sql server

3. The next step is to configure and schedule a transaction log backup. Click on Backup Settings… to do this.

right click on the database in ssms

If you are creating backups on a network share enter the network path or for the local machine you can specify the local folder path. The backup compression feature was introduced in SQL Server 2008 Enterprise edition. While configuring log shipping, we can control the backup compression behavior of log backups by specifying the compression option. When this step is completed it will create the backup job on the Primary Server.

transaction log backup settings in ssms

4. In this step we will configure the secondary instance and database. Click on the Add… button to configure the Secondary Server instance and database. You can add multiple servers if you want to setup one to many server log-shipping.

add a secondary server

When you click the Add… button it will take you to the below screen where you have to configure the Secondary Server and database. Click on the Connect… button to connect to the secondary server. Once you connect to the secondary server you can access the three tabs as shown below.

Initialize Secondary Database tab

In this step you can specify how to create the data on the secondary server. You have three options: create a backup and restore it, use an existing backup and restore or do nothing because you have manually restored the database and have put it into the correct state to receive additional backups.

intialize secondary database

Copy Files Tab

In this tab you have to specify the path of the Destination Shared Folder where the Log Shipping Copy job will copy the T-Log backup files. This step will create the Copy job on the secondary server.

specify where the log shipping copy job will copy the t-log backup files

Restore Transaction Log Tab

Here you have to specify the database restoring state information and restore schedule. This will create the restore job on the secondary server.

create the restore on the secondary server

5. In this step we will configure Log Shipping Monitoring which will notify us in case of any failure. Please note Log Shipping monitoring configuration is optional.

log shipping monitoring will notify us in case of any faulures

Click on Settings… button which will take you to the “Log Shipping Monitor Settings” screen. Click on Connect …button to setup a monitor server. Monitoring can be done from the source server, target server or a separate SQL Server instance. We can configure alerts on source / destination server if respective jobs fail. Lastly we can also configure how long job history records are retained in the MSDB database. Please note that you cannot add a monitor instance once log shipping is configured.

monitoring can be done from the source server, target server or a separate SQL Server instance.

6. Click on the OK button to finish the Log Shipping configuration and it will show you the below screen.

Happy practicing ……

Log Shipping Complete Tutorial Part 1

What is SQL Server log shipping?

SQL Server log shipping is a technique which involves two or more SQL Server instances and copying of a transaction log file from one SQL Server instance to another. The process of transferring the transaction log files and restoring is automated across the SQL Servers. As the process result there are two copies of the data on two separate locations

A log shipping session involves the following steps:

  • Backing up the transaction log file on the primary SQL Server instance
  • Copying the transaction log backup file across the network to one or more secondary SQL Server instances
  • Restoring the transaction log backup file on the secondary SQL Server instances

Operating modes

There are two available modes and they are related to the state in which the secondary, log shipped, SQL Server database will be:

  • Standby mode – the database is available for querying and users can access it, but in read-only mode
    • The database is not available only while the restore process is running
      • Users can be forced to disconnect when the restore job commence
      • The restore job can be delayed until all users disconnect themselves
  • Restore mode – the database is not accessible

Advantages and disadvantages of using SQL Server log shipping

SQL Server log shipping is primarily used as a disaster recovery solution. Using SQL Server log shipping has multiple benefits: it’s reliable and tested in details, it’s relatively easy to set up and maintain, there is a possibility for failover between SQL Servers, data can be copied on more than one location etc.

Log shipping can be combined with other disaster recovery options such as AlwaysOn Availability Groups, database mirroring, and database replication. Also, SQL Server log shipping has low cost in human and server resources

The main disadvantages in the SQL Server log shipping technique are: need to manage all the databases separately, there isn’t possibility for an automatic failover, and secondary database isn’t fully readable while the restore process is running.

SQL Server 2008 Editions

The editions of SQL Server 2008 are as follows:

  • Enterprise: this is the high-end edition with the full feature set. It supports systems up to 8 CPU and 2 TB RAM. The maximum size of the database is 524 PB.
  • Standard it has a bit less features than Enterprise, but it is a good choice when advanced functions (such as data compression, compressed backups, indexed views, etc.) are not required for the business. It supports systems up to 4 CPU and 64 GB RAM.
  • Workgroup: it is suitable for remote offices of a larger company. It supports systems up to 2 CPU and 4 GB RAM.
  • Web: it is designed for web applications. It supports 4 CPU without memory limitations.
  • Developer: similar to Enterprise, but licensed to only one user for development, testing and demo. It can be easily upgraded to Enterprise without reinstallation.
  • Express: free entry level database. It can utilize only 1 CPU and 1 GB memory, the maximum size of the database is 10 GB.
  • Compact: free embedded database for mobile application development. The maximum size of the database is 4 GB.

Another point of interest is that SQL Server Analysis Services and SQL Server Integration Services are included only in Enterprise and Standard Edition. SQL Server Reporting Services is also very limited in the free editions.