Integration Services server cannot be configured because there are active operations. Wait until there are no active operations, and then try to configure the server again

SSISDB.internal.cleanup_server_log will fail with

{

Integration Services server cannot be configured because there are active operations. Wait until there are no active operations, and then try to configure the server again.

}

SSISDB.internal.cleanup_server_log will fail with below error

{

Integration Services server cannot be configured because there are active operations. Wait until there are no active operations, and then try to configure the server again.

}

When you have pending or active operations in [internal].[operations] table.

Resolution

To resolve the issue identify all the operations with status 2 (running) or status 5 (pending) from Internal.operations table and then stop the running operations (SSMS->Integration Services CatalogsàSSISDBàRight click active operations). If there are any rows with status 5 they might be orphan records so check when they are started and delete them if they are orphan. You can use below query to identify running and pending operations.

SELECT * FROM [internal].[operations] where status=2 or status=5

Once all the rows with status 2 or 5 is removed/updated we can run SSISDB.internal.cleanup_server_log

SQL Server setup fails with “Failed to retrieve data for this request”

 

SQL Server setup would fail with below error in message box “Failed to retrieve data for this request”

{

TITLE: SQL Server Setup failure.

——————————

SQL Server Setup has encountered the following error:

Failed to retrieve data for this request..

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&EvtType=0xE8A0C283%25400xAC7B1A58%25401233%254053

——————————

BUTTONS:

OK

}

In Summary.txt you will notice error similar to one below

Overall summary:

Final result: Failed: see details below

Exit code (Decimal): -2146233088

Exit facility code: 19

Exit error code: 5376

Exit message: Failed to retrieve data for this request.

Start time: 2015-02-19 19:48:58

End time: 2015-02-19 19:50:47

Requested action: Install

Exception help link: http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=12.0.2342.0&EvtType=0xE8A0C283%400xAC7B1A58%401233%4053&EvtType=0xE8A0C283%400xAC7B1A58%401233%4053

Exception summary:

The following is an exception stack listing the exceptions in outermost to innermost order

Inner exceptions are being indented

Exception type: Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorException

Message:

Failed to retrieve data for this request.

HResult : 0x80131500

Data:

HelpLink.ProdName = Microsoft SQL Server

HelpLink.BaseHelpUrl = http://go.microsoft.com/fwlink

HelpLink.LinkId = 20476

HelpLink.EvtType = 0xE8A0C283@0xAC7B1A58@1233@53

DisableWatson = true

Stack:

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

at Microsoft.SqlServer.Chainer.Infrastructure.SqlDiscoveryDatastoreInterface.ProcessDTbl(DataTable dt, Int32 level)

at Microsoft.SqlServer.Chainer.Infrastructure.SqlDiscoveryDatastoreInterface.CollectSqlDiscoveryData(String machineName)

at Microsoft.SqlServer.Chainer.Infrastructure.SqlDiscoveryDatastoreInterface.CollectDiscoveryData(String machineName)

at Microsoft.SqlServer.Chainer.Infrastructure.SqlDiscoveryDatastoreInterface.LoadData(IEnumerable`1 machineNames, String discoveryDocRootPath, String clusterDiscoveryDocRootPath)

at Microsoft.SqlServer.Configuration.SetupExtension.RunDiscoveryAction.ExecuteAction(String actionId)

at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)

at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClasse.<ExecuteActionWithRetryHelper>b__b()

at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(ActionWorker workerDelegate)

Inner exception type: Microsoft.SqlServer.Configuration.Sco.SqlRegistryException

Message:

The network path was not found.

HResult : 0x84d10035

FacilityCode : 1233 (4d1)

ErrorCode : 53 (0035)

Data:

WatsonData = Microsoft.SqlServer.Configuration.Sco.SqlRegistryException@Win32Error

Stack:

at Microsoft.SqlServer.Configuration.Sco.SqlRegistry.CreateBaseKey(ServiceContainer ctx, String machineName, IntPtr hKey, String keyName, RegistryAccess access, RegistryView view)

at Microsoft.SqlServer.Configuration.Sco.SqlRegistry.GetLocalMachine(ServiceContainer ctx, String machineName, RegistryAccess access, RegistryView view)

at Microsoft.SqlServer.Discovery.DiscoveryUtils.GetLocalMachineRootKey(ServiceContainer ctx, String machineName, RegistryView registryView)

at Microsoft.SqlServer.Discovery.DiscoveryUtils.GetLocalMachineSubKey(ServiceContainer ctx, String machineName, RegistryView regView, String regPath, RegistryAccess registryAccess)

at Microsoft.SqlServer.Discovery.DiscoveryEnumObject.GetSql2kMsiInstanceListInHive(String machineName, RegistryView regView)

at Microsoft.SqlServer.Discovery.DiscoveryEnumObject.LoadSql2kInstanceList(String machineName)

at Microsoft.SqlServer.Discovery.Product.GetData(EnumResult erParent)

at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()

at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

Watson error

Watson bucket data:

Bucket param 1: SQL Server 2014@RTM@

Bucket param 2: 0x6785B09D

Bucket param 3: 0xE8A0C283

Bucket param 4: 0x74E34741

Bucket param 5: 0xAC7B1A58@1233@53

Bucket param 6: RunRemoteDiscoveryAction

Bucket param 7:

Bucket param 8:

Bucket param 9: 0xD195CE25

Bucket param 10:

From detail.txt

(01) 2015-02-19 19:49:25 Slp: Discovery on nodename failed due to exception

(01) 2015-02-19 19:49:25 Slp: Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorException: Failed to retrieve data for this request. —> Microsoft.SqlServer.Configuration.Sco.SqlRegistryException: The network path was not found.

at Microsoft.SqlServer.Configuration.Sco.SqlRegistry.CreateBaseKey(ServiceContainer ctx, String machineName, IntPtr hKey, String keyName, RegistryAccess access, RegistryView view)

at Microsoft.SqlServer.Configuration.Sco.SqlRegistry.GetLocalMachine(ServiceContainer ctx, String machineName, RegistryAccess access, RegistryView view)

at Microsoft.SqlServer.Discovery.DiscoveryUtils.GetLocalMachineRootKey(ServiceContainer ctx, String machineName, RegistryView registryView)

at Microsoft.SqlServer.Discovery.DiscoveryUtils.GetLocalMachineSubKey(ServiceContainer ctx, String machineName, RegistryView regView, String regPath, RegistryAccess registryAccess)

at Microsoft.SqlServer.Discovery.DiscoveryEnumObject.GetSql2kMsiInstanceListInHive(String machineName, RegistryView regView)

at Microsoft.SqlServer.Discovery.DiscoveryEnumObject.LoadSql2kInstanceList(String machineName)

at Microsoft.SqlServer.Discovery.Product.GetData(EnumResult erParent)

at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()

at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

— End of inner exception stack trace —

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

 

Resolution

1. Check the connectivity between node from which you are installing SQL Server to other nodes. (\\Othernodes should work when you are installing SQL on box which is part of windows cluster regardless of SQL Cluster).

The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication.

When you configure replication subscriber and if your subscriber instance is in higher version than publisher instance you might get below error

Example when you configuring subscriber on SQL Server 2014 instance for SQL Server 2012 Publisher  using SQL Server 2012 SSMS you would get below error.

Error:

The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication.

Resolution:

Create subscriber using higher version SSMS (SSMS has to be subscriber version or higher) or create subscriber using TSQL.

Sp_rename fails : Either the parameter @objname is ambiguous or the claimed @objtype (object) is wrong.

Sp_rename fails with

Msg 15248, Level 11, State 1, Procedure sp_rename, Line 357

Either the parameter @objname is ambiguous or the claimed @objtype (object) is wrong.

You would get this error if you have ‘DOT’ in between your constraint name. SP_rename fails because object_id(ContraintnamewithDOT) returns NULL for constraint objects with DOT (.) in their name.

Solution

Drop and recreate the constraint without DOT (.) in name.

SSIS package fails with out of memory errors

You might get below out of memory errors when you run SSIS packages 

Errors

A buffer failed while allocating n bytes. The system reports n percent memory load. There are n bytes of physical memory with n bytes free.

There are 2147352576 bytes of virtual memory with 44814336 bytes free. The paging file has 687069143034 bytes with 35348451328 bytes free.

A buffer failed while allocating 63160 bytes. The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E. 

SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component “Read Results” (975) returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure. 

The system reports 2 percent memory load. There are 206147428352 bytes of physical memory with 24692375552 bytes free. There are 8796092891136 bytes of virtual memory with 8713333325824 bytes free. The paging file has 205966053376 bytes with 15835136 bytes free.

Executed as user: Domain\Password . …9.00.4035.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  6:06:21 AM  Error:     Code: 0xC0047012     Source: Load Daily Run Piece DTS.Pipeline     Description: A buffer failed while allocating 6356480 bytes.  End Error  Error: 2009-12-29 06:19:41.56     Code: 0xC0047011     Source: Load Daily Run Piece DTS.Pipeline     Description: The system reports 82 percent memory load. There are 4294656000 bytes of physical memory with 756637696 bytes free. There are 2147352576 bytes of virtual memory with 399495168 bytes free. The paging file has 8418062336 bytes with 5051555840 bytes free.

The system reports 24 percent memory load. There are 68716240896 bytes of physical memory with 51754737664 bytes free. There are 2147352576 bytes of virtual memory with 146911232 bytes free. The paging file has 137430540288 bytes with 120562003968 bytes free.

Below are options you can use to solve the above errors

1. Check if the Page file for the system is configured properly, you might get this error if there is no page file or if page file is very small

2. Change the lookup cache mode from Full to Partial or no cache mode.

3. Migrate to 64-Bit SSIS if you are in 32-BIT. Remember you have to migrate all the drivers and providers used in connections. If you are in 64 BIT system and getting above errors while executing packages from BIDS then check if you have set package to run in 64bit run time ( Run64bitruntime = True in  Integration services project properties page).

4.  Divide the package in to multiple child packages (Execute package task –> ExecuteOutOfProcess =True)

5. Tweak DefaultBufferMaxRows and DefaultBufferMaxSize size.

Cannot bring the Windows Server Failover Clustering (WSFC) resource (ID ‘ ‘) online (Error code 5018).

Problem description

You might get below error when configuring Always on availability group If the cluster services startup account is not part of SQL Server logins.

Error:

TITLE: Microsoft SQL Server Management Studio

——————————

Creating availability group resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)

——————————

ADDITIONAL INFORMATION:

Create failed for Availability Group ‘KK_AG’.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3373.0+((SQL11_SP1_QFE-CU).130629-2102+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+AvailabilityGroup&LinkId=20476

——————————

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

Cannot bring the Windows Server Failover Clustering (WSFC) resource (ID ’74eaaa74-4e7d-470a-96b4-29459cb4516e’) online (Error code 5018).  The WSFC service may not be running or may not be accessible in its current state, or the WSFC resource may not be in a state that could accept the request.  For information about this error code, see “System Error Codes” in the Windows Development documentation.

Failed to designate the local availability replica of availability group ‘KK_AG’ as the primary replica.  The operation encountered SQL Server error 41066 and has been terminated.  Check the preceding error and the SQL Server error log for more details about the error and corrective actions.

Failed to create availability group ‘KK_AG’.  The operation encountered SQL Server error 41160 and has been rolled back.  Check the SQL Server error log for more details.  When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command. (Microsoft SQL Server, Error: 41066)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3373&EvtSrc=MSSQLServer&EvtID=41066&LinkId=20476

 

You would also see errors similar to one  below when you configure always on availability group using scripts.

 

Msg 41131, Level 16, State 0, Line 1

Failed to bring availability group ‘TDE_AG’ online.  The operation timed out. Verify that the local Windows Server Failover Clustering (WSFC) node is online. Then verify that the availability group resource exists in the WSFC cluster. If the problem persists, you might need to drop the availability group and create it again.

Msg 41152, Level 16, State 2, Line 1

Failed to create availability group ‘TDE_AG’.  The operation encountered SQL Server error 41131 and has been rolled back.  Check the SQL Server error log for more details.  When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command.

Msg 41131, Level 16, State 0, Line 1

Failed to bring availability group ‘TDE_AG’ online.  The operation timed out. Verify that the local Windows Server Failover Clustering (WSFC) node is online. Then verify that the availability group resource exists in the WSFC cluster. If the problem persists, you might need to drop the availability group and create it again.

Msg 41152, Level 16, State 2, Line 1

Failed to create availability group ‘TDE_AG’.  The operation encountered SQL Server error 41131 and has been rolled back.  Check the SQL Server error log for more details.  When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command.

Resolution:

Add start up account of cluster service to SQL Server login and grant sysadmin role (Start up account of cluster service will be nt authority\system by default).

SQL Server cluster installation checklist

List of checks you may need to perform before you install SQL Server Cluster.

1. Verify your system meets the minimum requirements
910228 SQL Server 2005 Readme and installation requirements
http://support.microsoft.com/default.aspx?scid=kb;EN-US;910228
907284 Changes to the readme file for SQL Server 2005
http://support.microsoft.com/default.aspx?scid=kb;EN-US;907284
327518 The Microsoft support policy for a SQL Server Failover cluster – Please note that ONLY Microsoft Failover Clusters are supported.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;327518
SQL Server 2008  Before Installing Failover Clustering
http://download.microsoft.com/download/4/5/3/453739ed-cb74-46b1-b7af-f157ee71db20/SQL2008Readme.htm.
http://msdn.microsoft.com/en-us/library/ms189910%28v=SQL.100%29.aspx

2. Log on as an Administrator.
SQL Server 2005/2008:  Log on to the cluster node as a domain administrator that is also local administrator on all nodes, but not necessarily an account that is to be used as a service startup account for the SQL Server services.

Pre create user groups in active directory for different SQL Server 2005/2008 services(SQL,Agent,FT).SQL Server setup will add the service accounts of SQL Services to this groups during setup. You have to pre create unique group name for each Service in AD and enter it when prompted during SQL Server2005/2008 Cluster setup.
SQL Server Setup grants the following Windows NT rights and privileges to the Group you enter during setup (If you can not run the setup as domain administrator then add the service logon accounts to the security groups created in AD before the setup).
For SQL Server group
Log on as a service (SeServiceLogonRight)
Act as part of the operating system (SeTcbPrivilege) (only on Windows 2000)
Log on as a batch job (SeBatchLogonRight)
Replace a process-level token (SeAssignPrimaryTokenPrivilege)
Bypass traverse checking (SeChangeNotifyPrivilege)
Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)
Permission to start SQL Server Active Directory Helper
Permission to start SQL Writer
and many more
For  SQL Server Agent group
Log on as a service (SeServiceLogonRight)
Act as part of the operating system (SeTcbPrivilege) (only on Windows 2000)
Log on as a batch job (SeBatchLogonRight)
Replace a process-level token (SeAssignPrimaryTokenPrivilege)
Bypass traverse checking (SeChangeNotifyPrivilege)
Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)

3. There should not be a pending Reboot requirement

Check pending Reboot requirement in following registry:
HKLM\SYSTEM\CurrentControlSet\Control\Session Manager
In the right-hand pane, is there a value of PendingFileRenameOperations?  If so, is it empty?
If empty close the registry editor.  If not empty, reboot the machine prior to running setup.  After logging in, re-check this registry value to make sure it is empty.

4. Verify Cluster “Heartbeat”
For a Failover Clustered Instance installation on Windows 2003 or earlier, verify cluster networking was setup per:
258750 Recommended private “Heartbeat” configuration on a cluster server
http://support.microsoft.com/?id=258750
NOTE: This needs to be followed explicitly in regards to the binding orders and your heartbeat adapter’s configuration.

5. Verify the TCP\IP Advanced DNS settings are identical on the “Public” network adapters between all Failover cluster nodes

6. Stop All Non- Microsoft Services  Excluding services required by SAN or network adapters online

7. Make sure  below services are running on all node’s
Cryptographic Services <====This must be running on all nodes.
DNS Client
Event Log
Remote Registry Service <====This must be running on all nodes.
Task Scheduler
Windows Management Instrumentation Driver Extensions
Windows Management Instrumentation

8.  Flush Cached DNS Entries
Flush the DSN cache.

Ipconfig /flushdns
then… Referesh te netbios name registered
Nbtstat –RR

9. Verify Logon Server
Open a command prompt on each node and enter the command:

set logonserver

Verify if logon server is same on all nodes.

10. Verify Admin Shares
IPC$
C$
Admin$
DriveLetter$
DriveLetter = Disk in which you have setup files.

Use the “Shared Folders” MMC snap-in  to verify the existing Administrative shares.

11. Only One RDP Session

Make sure no one else is logged in to any of the nodes. There should be only one logon session in active node which we use for setup.
12 . Make sure we are able to failover SQL Server groups to all nodes.

13. Cluster MSDTC
Create MSDTC resource.

14. If windows2008+ Grant  “Create Computer Objects” permission for  the computer object created for the cluster  (Computer Name object(CNO)).

http://mssqlwiki.com/2009/03/26/installation-of-sqlserver20052008-fails-on-windows2008-cluster/

15. For Windows 2008, see How to configure DTC for SQL Server in a Windows 2008 cluster http://blogs.msdn.com/cindygross/archive/2009/02/22/how-to-configure-dtc-for-sql-server-in-a-windows-2008-cluster.aspx
Use the following KB article to Cluster MSDTC if not already clustered on Windows 2003:
301600 How to configure Microsoft Distributed Transaction Coordinator on a Windows Server 2003 cluster
http://support.microsoft.com/default.aspx?scid=kb;EN-US;301600

16. Grant Read SPN and write SPN permission for startup account of SQL Server (or) Security group provided during installation for SQL Service startup account.

17) Verify Dependencies http://support.microsoft.com/default.aspx?scid=kb;EN-US;835185

PREEMPTIVE_OS_AUTHORIZATIONOPS waits in SQL Server

SQL Server threads which are controlled by SOS (SQL Server operating system) are Non preemptive but at times they switch preemptive when they can’t obey the rules of SOS. Some common places when SOS thread is switched preemptive are when we call extended proc’s, few Windows API etc.

 

Let us assume you use “execute as user x” in your job, SQL Server calls Windows functions like LookupAccountName  to get the credential of user. Windows functions interacts with AD services to get the credentials of account and return the info to the caller in SQL Server process and then SQL Server would build the logintoken. If there is a delay in AD and if it takes long time to respond to the windows function calls other threads in the same scheduler would get blocked so SQL Server thread would switch preemptive (Doesn’t follow SOS rules) before  making these  function  calls. PREEMPTIVE_OS_AUTHORIZATIONOPS wait type would occur when a thread is waiting on such windows functions (security) to return, So first thing which would have to do is to fix the performance of AD calls.

 

To narrow down and prove that this issue occurs because of Active directory performance. Login to SQL server using the startup account of SQL Server and execute below query when you notice PREEMPTIVE_OS_AUTHORIZATIONOPS wait type and compare the times printed. It will give you the time it takes for SQL Server to complete the AD calls.

 

 

create procedure PREEMPTIVEOSAUTHORIZATIONOPS  with execute as self

as

set nocount on

select CONVERT(varchar, getdate(), 126) PREEMPTIVEOSAUTHORIZATIONOPS

go

print convert(varchar, getdate(), 126)

exec dbo.PREEMPTIVEOSAUTHORIZATIONOPS;    

print convert(varchar, getdate(), 126)

go

SQL Server Backup compression

How to do Backup compression in SQL Server?

The backup compression  option determines whether SQL Server creates compressed or uncompressed backups .Backup compression option is off by default in SQL Server.The default behavior  can be modified by sp_configure option “backup compression default” .

Syntax:

USE master;
GO
EXEC sp_configure ‘backup compression default’, ‘1’;
RECONFIGURE WITH OVERRIDE;

To override the backup compression:

You can change the backup compression behavior for an individual backup by using WITH NO_COMPRESSION or WITH COMPRESSION in a BACKUP statement. We cannot take compressed and non-compressed backups on the same file. If we take COMPRESSION  backup on a file were  already non-compressed backup has taken,error will be shown.So we have to use different files for compressed and uncompressed backup.

Example to take backup for AdventureWorks with NO_COMPRESSION:

BACKUP DATABASE [AdventureWorks] TO  DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\uncompressed.bak’ WITH NO_COMPRESSION
GO

Example to take backup for AdventureWorks WITH COMPRESSION:

BACKUP DATABASE [AdventureWorks] TO  DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\compressed.bak’ WITH COMPRESSION
GO

To calculate the compression ratio of a backup:

After taking backup with compression and without compression the backup_size can be compared to see the difference.

Syntax:

select backup_size,compressed_backup_size,100- ((compressed_backup_size/backup_size)*100) as “compressed %”   from msdb..backupset

 image

Types of isolation levels in SQL Server

The ISO standard defines the following isolation levels in SQL Server Database Engine:  

Microsoft SQL Server supports these transaction isolation levels:

Read Committed

SQL Server acquires a share lock while reading a row into a cursor but frees the lock immediately after reading the row. Because shared lock requests are blocked by an exclusive lock, a cursor is prevented from reading a row that another task has updated but not yet committed. Read committed is the default isolation level setting for both SQL Server and ODBC.

Read Uncommitted

SQL Server requests no locks while reading a row into a cursor and honors no exclusive locks. Cursors can be populated with values that have already been updated but not yet committed. The user is bypassing all of the locking transaction control mechanisms in SQL Server.

Repeatable Read 

SQL Server requests a shared lock on each row as it is read into the cursor as in READ COMMITTED, but if the cursor is opened within a transaction, the shared locks are held until the end of the transaction instead of being freed after the row is read. So phantom rows are This has the same effect as specifying HOLDLOCK on a SELECT statement.

(Phantom read:Phantom reads occurs when an insert or delete action is performed against a row that is being read by a transaction.The second  transaction read shows a row that did not exist in the original read as the result of an insertion by a different transaction or due to deletion operation some rows  doesn’t appear)

Serializable

     In serializable read phantom reads are not allowed because while the first transaction is in progress other transaction wont execute.

Snapshot

SQL Server requests no locks while reading a row into a cursor and honors no exclusive locks. Cursor is populated with the values as of the time when the transaction first started. Scroll locks are still requested regardless of use of snapshot isolation.

 

Read uncommitted example: Uncommitted Read allows your transaction to read any data that is currently on a data page, whether that has been committed or not. For example,another user might have a transaction in progress that has updated data, and even though it’s holding exclusive locks on the data, your transaction can read it anyway.

image

If we execute the select query before the update transaction gets committed,it will not wait for the update transaction to commits.Query will be executed immediately without any time lapse.

image

Read Committed example

Read committed allows your transaction to read only if the data is committed.Read Committed operation never reads data that another application has changed but not yet committed.

image

If we execute the select query before the update transaction gets committed,it will wait till the update transaction gets committed.

image

Repeatable Read example :In Repeatable Read issuing the same query twice within a transaction will not make any changes to data values made by another user’s transaction.Repeatable Read allows phantom reads(Data getting changed in current transaction by other transactions is called Phantom Reads).So phantom rows will appear.

image

While the transaction(first query) is in progress,repeatable read allows another transaction(second query) to execute.It means it allow phantom reads.So second transaction(second query),need not wait till first transaction(first query) completes.Here values will be added before first query completes.

image

Serializable example : The Serializable isolation level adds to the properties of Repeatable Read by ensuring that if a query is reissued, rows will not have been added in the table. In other words, phantoms will not appear if the same query is issued twice within a transaction.

image

While the transaction(first query) is in progress,serializable read does not  allow another transaction(second query),It means it don’t allow phantom reads.So second transaction(second query), must wait till first transaction(first query) completes.Here values will be added only after first query completes.

image

snapshot example : To use the snapshot isolation level you need to enable it on the database by running the following command

ALTER DATABASE DBname
SET ALLOW_SNAPSHOT_ISOLATION ON

SQL Server database snapshot

What is the use of SQL Server database snapshot and how to create SQL Server database snapshot?

Database snapshots are available only in SQL Server 2005 Enterprise Edition and later versions. All recovery models support database snapshots.

A database snapshot is a read-only, static view of the source database.Multiple snapshots can exist on a source database and always reside on the same server instance as the database. Each database snapshot is transactionally consistent with the source database as of the moment of the snapshot’s creation.A snapshot persists until it is explicitly dropped by the database owner.

Snapshots can be used for reporting purposes.In the event of a user error on a source database, you can revert the source database to the state it was in when the snapshot was created. Data loss is confined to updates to the database since the snapshot’s creation.

Creating a series of snapshots over time captures sequential snapshots of the source database. Each snapshot exist until it is dropped.Each snapshot will continue to grow as  pages in original database are updated, you may want to conserve disk space by deleting an older snapshot after creating a new snapshot.

Steps to create database snapshot in SQL Server:

1.Create a new database or use existing database to create the database snapshots.

Syntax:

USE master
GO
— Create database
CREATE DATABASE test
GO
USE test
GO

2.Create table  and Insert values into table.

Syntax:

–create a Table
CREATE TABLE test1 (a INT, b INT,c INT)
—Insert values
declare @a nchar(10)
set @a=1
while (@a<5)
begin
insert into test1 values (@a,’1′,’1′)
set @a=@a+1
end

3.Create a snapshot  for a source database.

Syntax:

— Create Snapshot Database
CREATE DATABASE Snapshottest ON
(Name =’test’,
FileName=’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\testsnap.ss’)
AS SNAPSHOT OF test;
GO

image

Snapshottest database is created successfully.

4.Now let us view both the source database and snapshot database.

Syntax:

SELECT * FROM test.dbo.test1;
Go
SELECT * FROM Snapshottest.dbo.test1;
Go

Data’s in tables of source and snapshot database are same.

 image

Size on disk  of the testsnap .ss could be viewed by opening its properties window.Let us note the  size on disk  of the source database.

image

5.Let us update existing rows in the table.

Syntax:

update test1 set a=0,b=0,c=0

6.After updating rows ,let us view the source and snapshot database.The values are updated only in source database,not in snapshot database.Because snapshot database is consistent with the source database as of the moment of the snapshot’s creation.

Syntax:

— Select from test and Snapshottest Database
SELECT * FROM test.dbo.test1;
SELECT * FROM Snapshottest.dbo.test1;
GO

image 

But size on disk is increased,It clearly shows that when we update data in Source database, it copies the old/existing data pages to Snapshot database. This is the reason why the size of the snapshot database is increasing while updating the source database.

image

7.We could revert the source database.Reverting overwrites updates made to the source database since the snapshot was created by copying the pages from the sparse files back into the source database.

Syntax:

— Restore old data from Snapshot Database
USE master
GO
RESTORE DATABASE test
FROM DATABASE_SNAPSHOT = ‘Snapshottest’;

8.View the data in the tables from the source and the snapshot database after the restore from snapshot.

Syntax:

SELECT * FROM test.dbo.test1;
Go
SELECT * FROM Snapshottest.dbo.test1;
Go

image

9.We drop the snapshot database like any other database using drop database command.

Syntax:

— drop snapshottest
DROP DATABASE [snapshottest];

10. We can also create database snapshot on mirror database for load balancing.

How to create table with filestream column and Insert data

How to create table with filestream column and Insert data?

Filestream data type in SQL Server  can be used to store images,documents,etc., in database.

In this blog I will explain how to create table with filestream data type.

Before creating table with filestream  column,we have to enable filestream feature in SQL Server configuration manager.Follow “How to enable and configure Filestream in SQL SERVER 2008 / 2012”

Create a table and adding filestream data:

1.Create Filestream enabled database.

Syntax:

CREATE DATABASE test
ON
PRIMARY ( NAME = test1,
    FILENAME = ‘c:\data\testdat1.mdf’),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = test3,
    FILENAME = ‘c:\data\test1’)
LOG ON  ( NAME = testlog1,
    FILENAME = ‘c:\data\test1.ldf’)
GO

2.After you have a filestream enabled database.we now need to create a table and add data to it.

Syntax:

CREATE TABLE [dbo].[test](
   [ID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
   [Number] VARCHAR(20),
   [Description] VARCHAR(50),
   [Image] VARBINARY(MAX) FILESTREAM NULL
)

3.Insert values into table. In below example I am inserting image file in to the table. 

Syntax:

DECLARE @img AS VARBINARY(MAX)
— Load the image data
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
      FROM OPENROWSET(Bulk  ‘C:\image\jellyfish.jpg’, SINGLE_BLOB ) AS y
— Insert the data to the table         
INSERT INTO test (ID,Number,Description, Image)
SELECT NEWID(), ‘MS1001′,’jellyfish’, @img

clip_image001[8]

4.To view the table with filestream included column

image

How to enable and configure Filestream in SQL SERVER 2008 / 2012

How to enable Filestream in SQL SERVER 2008 and SQL Server 2012?

Filestream was introduced in SQL Server 2008 for the storage and management of unstructured data.

Follow the below steps to enable this filestream in SQL Server2008.Let us see one by one.

To enable filestream through SQL Server configuration manager:

1.Open SQL Server configuration manager.Open SQL Server services

2.Select the instance for which you want to enable Filestream.Right click the instance->properties.

3.In the SQL Server Properties dialog box, click the Filestream tab.

4.Select the Enable Filestream for Transact-SQL access.

5.If you want to read and write Filestream data from Windows, click Enable Filestream for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.

6.If remote clients must access the Filestream data that is stored on this share, select Allow remote clients to have streaming access to Filestream data.

7.Click Apply.

clip_image001[4]

Enable Filestream access level server configuration option:

In SQL Server Management Studio, click New Query.Execute the below query

[0 -Disables FILESTREAM,1 -Enables FILESTREAM for T-SQL,2 -Enables FILESTREAM for T-SQL and Win32 streaming access]

Syntax:

EXEC sp_configure filestream_access_level,2

RECONFIGURE with override

Create filestream enabled database:

  1. We can enable file stream while creating the database  (or) If the database is already created we can enable filestream using alter database.

To create file stream enable database you can use below query

CREATE DATABASE DBname
ON
PRIMARY ( NAME = test1,
    FILENAME = ‘c:\data\testdat1.mdf’),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = test3,
    FILENAME = ‘c:\data\test1’)
LOG ON  ( NAME = testlog1,
    FILENAME = ‘c:\data\test1.ldf’)
GO

 

Enable filestream on existing database:

To enable file stream on existing database you can use alter database  command with  SET FILESTREAM similar to the example below or SSMS 

ALTER DATABASE [DBNAME] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N’Directoryname’ ) WITH NO_WAIT
GO

 

Enable filestream for database using SQL Server 2008 Management Studio:

1. Connect to SQL Server Instance using SQL Server Management Studio

2. In the Object Explorer, right click the instance and select Properties.

3. On the left panel click on the Advanced tab, then click on the drop down list of Filestream Access Level and select Full access enabled option.

image

4. Click Ok to save the changes.

How to import/Export data in SQL Server

SQL Server Import and Export Wizard can be used to copy data from a source to a destination. source and destination can be sqlserver or any other connection.

Steps to export data in SQL Server 2008:

In Microsoft SQL Server Management Studio, expand the databases.

Right-Click on the database you want to copy to another database->Tasks->Export data/import data.

clip_image001

SQL Server Import and Export Wizard window will open.

clip_image001[4]

Click  Next to continue. Now, you will need to choose a Data Source. You can leave the Data source as SQL Server Native Client 11.0. Enter the Server name. Check windows Authentication  for your database. From the Database dropdown menu, select the name of your database.

clip_image001[22]

Click on Next to continue.

In the Choose a Destination window, you can select a different option to export to Microsoft Access, Microsoft Excel,SQL Server database,etc.,

clip_image001[26]

Then select the destination database,where you want to export your data.

clip_image001[24]

Specify whether to copy tables or to copy the results of a query from the data source.Click Next.

clip_image001[28]

Select source tables and click Next.

clip_image001[30]

Check Run immediately.Click Next.

clip_image001[32]

Click Finish to complete the wizard.

clip_image001[34]

Close the wizard after completing it successfully.

clip_image001[36]

Create script for all objects in database with data

You can create T-SQL scripts for database, objects in database and insert script for data in tables by using the Generate Scripts Wizard.

These scripts can be used to create database in secondary site with and with out data.

I this blog I will explain how to generate T-SQL script to create database, database objects and data.

Right click the database which you want to script—>Select tasks—>Generate scripts.

clip_image001

 Generate and Publish Scripts wizard screen appears.click Next to continue.

clip_image001[4]

In the Choose Objects wizard screen Select “Script entire database and all database objects” option if want to script all the objects in database or select specified objects which you would like to script and click Next to continue.

clip_image001[6]

In the Set Scripting Options wizard screen.Select the output type as “Save scripts to a specific location” and specify the path where you would like to save the database script file .Click the Advanced button  for specifying the schema and data scripting option.

clip_image001[8]

In Advanced Scripting Options screen; choose the option Schema and data for the Types of data to script option if you want to generate insert scripts for rows in tables  and click OK to save the changes and to return to Set Scripting Options wizard screen.

a) Data only – If this option is selected, it will only script out data within the tables
b) Schema and data – If this option is selected, it will script out Schema as well as the data within the selected objects
c) Schema only – If this option is selected, it will script out the Schema only

clip_image001[10]

In the Summary wizard screen; you will be able to see a quick summary of all the options which you have selected so far. Click Next to confirm and generate the scripts.

clip_image001[12]

In Save or Publish Scripts wizard screen; you will be able to see a Success or Failure message against each object for which the script was requested to be generated. Finally, click Finish to close the wizard.

clip_image001[14]

Once the script is generated you can view it like any other SQL Script. 

clip_image001[16]

Steps to enable Alwayson in SQL Server 2012

Steps to configure Always on availability groups in SQL Server 2012

1. Always on availability groups can be configured in standalone or clustered SQL Server instance.

2. All SQL Server instance (principal and all replicas) must reside on servers which are part of same Windows Server Failover Cluster.

3. The server instance must be running an edition of SQL Server that supports AlwaysOn Availability Groups

To install and configure windows failover cluster follow the steps in HOW TO CREATE CLUSTER USING HYPER-V

Ensure all the systems which has SQL Server instances and will host principal and all replica databases are part of same windows cluster.  

Note: Windows failover cluster can be installed and configured without shared disk starting from windows2008 unlike windows2003.

Once the cluster is configured follow the below step by step guide to setup always on

In SQL Server Configuration Manager, click SQL Server Services, right-click SQL Server for which you want to enable AlwaysOn Availability Groups, and click Properties.

clip_image001[3]

Select the AlwaysOn High Availability tab.

Verify that Windows failover cluster name field contains the name of the failover cluster. If this field is blank, this server instance currently does not support AlwaysOn Availability Groups. Either the local computer is not a cluster node, the Windows Server Failover cluster has been shut down, or this edition of SQL Server 2012 that does not support AlwaysOn Availability Groups.

Select the Enable AlwaysOn Availability Groups check box, and click OK.

clip_image001[5]

 

Open SQL Server Management Studio.

Choose any one instance to become PRIMARY. Expand Alwayson High availability->Right-click Availability Groups and select New Availability Group wizard.

clip_image001[4]

New Availability group introduction window will appear,Click Next.

clip_image001[6]

Specify availability group name and click Next.

clip_image001[8]

Select the user databases for the availability group(Database Should have Full backup) and click Next.

clip_image001[16]

Specify an instance of SQL Server to host a secondary replica by selecting Add replica.

clip_image001[18]

Add NODE2 as secondary replica by connecting it.

clip_image001[20]

After selecting secondary replica,Optionally, you can specify endpoint details or leave them default.

clip_image001[22]

Set the backup preference.

clip_image001[24]

Specify your preference for an availability group listener that will provide a client connection point or else select do not create an availability group listener now. 

clip_image001[26]

Select your data synchronization preference as Full and specify a shared network location accessible by all replicas.

clip_image001[28]

Summary of availability group validation will be displayed,Click Next.

clip_image001[30]

Verify the choice made in this wizard.Click Finish.

clip_image001[32]

Wizard completed successfully,Click close and finish the availability group wizard.

clip_image001[34]

Successfully we have created a availability group called AGroup.

clip_image001[38]

You can suspend and resume data movement by expanding Availability groups->Group name->Availability databases->Choose the database –>resume or suspend database.

clip_image001[6]

You can view the synchronization status through the dashboard reports.

clip_image001

If you want to do a failover,right click the available group->group name->Failover.

clip_image001[4]

How to create database mirroring

What is database mirroring and  How to create database mirroring?

Database mirroring is software solution  for increasing database availability.It maintains two copies of single database,one as principal database and other as mirror database.So during disaster if primary database server fails then mirror database can be used by application to access the data.This reduces the down time caused during disasters. Mirrored database can not be accessed by application unless roles are switched (Transferring principal role to mirror server) but database snapshot can be created on mirrored database which can be used for read only queries (Load balancing). Refer SQL Server database snapshot steps to create snapshot   

Database  Mirroring can be  implemented  only on databases,that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. 

Database mirroring is not applicable in system databases(master,msdb, tempdb, or model databases)

Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine.

There are two mirroring operating modes.

1.High-safety mode:Commit changes at the principal and then transfer them to the mirror.It is asynchronous.

2.High-performance mode:Always commit changes at both the principal and mirror server.It is synchronous

All database mirroring sessions support only one principal server and one mirror server.

Steps to create mirroring:

I have two instances SQLPC\PRADEEPA  which will act as principal server and SQLPC\PK92 will act as mirrored server.

Choose a database in the principal instance which will act as Principal database,

I have created database named “Mirrordatabase” for which we are going to start mirroring.

Create a backup for the Mirrordatabase in the principal server.

clip_image001

Set the backup type in full recovery mode.Select back up destination as disk.Select add to add the destination folder.

clip_image001[4]

  In destination,add the destination folder with .bak extension and click ok. 

clip_image002

Click ok and complete the backup. 

clip_image001[6]

Now restore the backup of the database in mirrored server (destination server) with same name.

clip_image001[8]

  Type the name of the database and restore the database from device.

clip_image002[4]

Select the backup file and click ok.

clip_image001[16]

Now backup location is added.click ok and continue.

clip_image001[18]

Select the backup sets to restore.Then click Options from select a page.

clip_image001[20]

Select the recovery state as RESTORE WITH NORECOVERY.Also change the restore file name in restore as if you are creating mirror on same server for testing.Click ok.

clip_image001[22]

Database restore completed successfully.

clip_image001[24]

Configure mirroring in primary database by selecting properties.

clip_image001[26]

In Database properties window select mirroring and configure it.

clip_image001[28]

Here we dont have witness server.But we can include witness server instance also.Click Next.

clip_image001[30]

Specify principal server instance,listener port and endpoint name.Click Next.

clip_image001[32]

Connect mirror server instance.

clip_image001[34]

Connect it.

clip_image001[36]

Click Next and continue.

clip_image001[38]

Service accounts could be created in this window.

clip_image001[40]

Click Finish and complete the wizard.

clip_image001[42]

Close the window after configuring database mirroring.

clip_image001[44]

Start the mirroring by clicking start mirroring.

clip_image001[46]

We could select either High performance mode or High safety mode based upon our application need from below window.

clip_image001[48]

Mirroring is successfully created. 

clip_image001[50]

You could perform failover between primary database and mirror database by selecting Failover option in properties window.

clip_image001[52]

How to create log shipping in SQL Server

What is log shipping and how to create log shipping in SQL Server 2008?

SQL Server Log shipping automatically allows you to send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases.

Log shipping can be used with databases using the full or bulk-logged recovery models.

Steps to create log shipping in SQL Server 2008:

On the primary server, right click on the database for which you want to enable log shipping and select Properties. Then select the Transaction Log Shipping Page. Check the “Enable this as primary database in a log shipping configuration” check box.

clip_image001

Select Backup settings.Specify the network path to backup folder and Click ok.

clip_image001[4]

Select secondary database settings.Connect secondary server instance.

clip_image001[6]

After connecting,select the secondary database.

Then initialize secondary database by Specifying how you want to restore the backup in secondary database.

clip_image001[8]

Select copy files option.Specify the destination folder for copied files.

clip_image001[10]

Select the Restore transaction log option.Select the database mode as standby mode.Click ok.

clip_image001[12]

View the configuration options we selected and click ok.

clip_image001[14]

successfully we have  configured Log shipping.Click ok and finish the configuration. 

clip_image001[16]

HOW TO INSTALL SQL Server CLUSTER IN HYPER-V

To install SQL Server failover cluster we need to have windows cluster configured with a shared disk.

Step 1

If you do not have a windows cluster configured you can create using Hyper-v

Follow the steps in “HOW TO CREATE CLUSTER USING HYPER-V” to create Domain controller and two member servers which will act as nodes of the cluster.

Once the domain controller and two member servers are created proceed to step 2

Step 2

Once the domain controller and nodes are created install failover cluster feature and create cluster 

To install the failover cluster feature and create a cluster follow the steps in “How to install cluster in windows 2008 and windows 2012“

Step 3

Once the cluster is created we have to create shared Disk for installing failover aware clustered application like SQL Server.

Follow the steps in ”How to create shared disk using iSCSI Software Target” to create a shared disk using iSCSI target software.

 

Once shared disk is configured invoke Setup from  SQL server installation media .Make sure the node in which you perform the  installation has shared disk online.

image

Run the setup.

Start the installation by selecting New SQL Server Failover Cluster Installation option.

image

Setup support rules will analyze and identify the problems that might occur during setup installation.

clip_image001

After setup validation ,you will get details about operations completed,Click Next and continue

clip_image001[4]

On the Product key page, specify the Product ID key of the product. Click Next.

image

Then read and select the check box to accept terms and agreements.Click Next.

image

Select the developer features which you want and click Next.

clip_image001[6] 

Perform instance configuration.Give SQL server network name.Either you could have  default instance or named instance.But you could have only one default instance.

Click Next.

clip_image001[28]

Allocate required disk space for your installation.Click Next.

clip_image001[12]

Select the SQL Server cluster resource group name.Click Next and continue.

clip_image001[14]

Cluster disk selection will help us to view the disk we have selected for SQL Server failover cluster.

clip_image001[16]

Configure the network resource for your SQL Server failover cluster.Select DHCP check box if you are going to use DHCP network.Otherwise give the IP address for your network(IP address which you provide not be in use currently).

clip_image001[18]

Select the database engine domain group and SQL Server agent domain group. These groups have to be pre created in AD before the installation and startup account of SQL Server services will be added to these groups.  You can also use service SIDs option.

clip_image001[20]

Specify the  account name and password for SQL server agent and SQL server database engine.Click Next.

clip_image001[24]

Specify the authentication mode for your SQL Server failover cluster.Click Next and continue your installation.Verify the cluster installation rules.

clip_image001[26]

  Enable error reporting service if you want or else,click Next and continue.

image

Cluster installation rules will run the setup to determine whether any operations will block the installation.After operation completes.Click Next and continue. 

image

Ready to install page will display the summary of options which we have selected during the setup process.If you want to make any changes,go back and make the required changes or else Click Install and start the installation.

image

Click Close and Complete the installation process.

clip_image001[1]

Successfully we have installed the SQL server failover cluster in first node.

clip_image001[30]

We can install the SQL server failover cluster in remaining nodes by selecting Add node to a SQL Server failover cluster in  installation centre window. To add node to existing SQL Server failover cluster follow the steps in “How to add node to SQL Server cluster”

 

How to create merge replication in SQL Server

How to create merge replication in SQL Server?

For all types of replication it is mandatory to create distribution for the publisher instance before configuring replication. For configuring distribution follow the steps in “How to configure distribution for replication”

Once the distribution is configured follow the below steps to create publisher

Let us create New publisher:

Right click Local publication->New Publication

clip_image001

New publication wizard appears.Click Next and continue.

 

clip_image001[4]

Select the database that contains the data or objects you want to publish.Click Next.

clip_image001[6]

There are different types of publication.

Select the publication type that supports your application.Click Next.

clip_image001[8]

Specify the SQL Server version that will be used by subscribers for this publication.Click Next.

clip_image001[10]

Select the tables to publish as articles.Click Next.

clip_image001[12]

If you don’t have unique identifier in the table.Then SQL Server will automatically add unique identifier to the table.Click Next.

clip_image001[14]

Add filters to exclude unwanted rows from published tables.Click Next.

clip_image001[16]

Specify when to run the snapshot agent and click Next.

clip_image001[18]

Select security settings.

clip_image001[20]

Specify the domain or machine account under which the snapshot agent process will run.

Select Run under the SQL Server agent service account.Click ok.

clip_image001[22]

After selecting the account for snapshot agent and log reader agent,click Next.

clip_image001[24]

Check create the publication.Click Next.

clip_image001[26]

Give the publication name and finish the wizard.

clip_image001[28]

Close the window after creating publication successfully.

clip_image001[30]

 

clip_image001[32]

Let us create the subscribers:

Before creating subscribers create one empty database where we could subscribe the publisher database.

Right click Local subscriptions->New subscriptions’

clip_image001[34]

 

New subscription wizard window appears.Click Next and continue.

clip_image001[36]

Choose the publisher and click next.

clip_image001[38]

Select the distributor agent location.Click Next.

clip_image001[40]

Select subscription database which you created.The database should be empty database.Click Next.

clip_image001[42]

Specify the process account and connection option for each distribution agent.

clip_image001[46]

Specify the domain or machines account under which the distribution agent process will run and click ok.

clip_image001[44]

 

Schedule the agent and click Next.

clip_image001[48]

Specify the type of subscription as client or server and click Next.

clip_image001[50]

Check create the subscription and click Next.

clip_image001[52]

Click Finish and complete the wizard.

clip_image001[54]

Close the window after creating subscription.

clip_image001[56]

Successfully we have created publisher,distributor and subscriber for merge replication.

clip_image001[58]

A SQL product other than SQL Server 2014 CTP1 is detected. You cannot install this release until the existing instances of SQL products are uninstalled

When you install SQL Server 2014 set support rules would fail in “SQL product installation rule” with below error

—————————
Rule Check Result
—————————
Rule “Previous SQL product installation” failed. Â
A SQL product other than SQL Server 2014 CTP1 is detected. You cannot install this release until the existing instances of SQL products are uninstalled.

—————————
OKÂ Â Â
——————–

image

Cause

SQL Server 2014 CTP 1 is prerelease software and should be installed only on a clean machine with no other SQL Server instance.

More details in http://msdn.microsoft.com/en-us/library/dn169381(v=sql.15).aspx

Extract from above link

{

1.1 Limitations and Restrictions in SQL Server CTP 1

Issue: The following sections define the limitations and restrictions that apply to SQL Server CTP 1.

1.1.1 General limitations and restrictions

  • SQL Server 2014 CTP 1 is prerelease software and should be installed only on a clean machine.

  • SQL Server 2014 CTP 1 is a NON-PRODUCTION release and should not be installed and used in production environments.

  • SQL Server 2014 CTP 1 may contain branding from Microsoft SQL Server 2012 and is versioned as 11.0.

  • SQL Server 2014 CTP 1 is only available in the English language.

  • Microsoft SQL Server 2014 CTP 1 is not supported by Microsoft Customer Services and Support (CSS).

}

How to start replication agent in command prompt.

In this blog I will explain how to run replication agents from command prompt and enable additional parameters for agent’s while running from command prompt.

All the replication agent’s  (Snapshot agent, Logreader agent, distributor agent, Merge agent) can be run from command prompt.

All these replication agents will have corresponding exe in C:\Program files\Microsoft SQL Server\100\COM  folder

Snapshot agent –>Snapshot.exe

Logreader agent—>Logread.exe

distributor agent  –>Distrib.exe

Merge agent  –>Replmerge.exe

Before we start running the replication agents from command prompt we have to stop and disable the corresponding agent job in management studio by expanding jobs->Right click job->Stop Job

clip_image001[20]  

After stopping the job.Disable it.

clip_image001[22]

Copy the folder path in which replication agent exe’s are located ( C:\Program files\Microsoft SQL Server\100\COM  I have highlighted the agent binaries for visibility)

clip_image001[4] 

Open a command prompt and Change the directory to C:\Program files\Microsoft Sql Server\100\COM in command prompt.

clip_image001

Running Logreader agent from command prompt:

Double click Log reader Job.

  clip_image001[6]

Select steps –>Double click Run agent.

clip_image001[10]

Copy the command and paste it in command prompt.

clip_image001[8]

To run Log read agent from cmd  type logread.exe in command prompt.Then paste the command that you copied from Job step properties.

clip_image001[12]

We can monitor the progress of agent from  command prompt.

clip_image001[14]

We can also add additional parameters when we run the agents from command prompt. I will show you how to add  verbose level parameter to increase the level of logging when running agent from CMD. (–Outputverboselevel 3 increases the level of logging by replication agents).

clip_image001[40]

Like below.

clip_image001[42]

Running Replication distributor agent:

Double click the distributor job.

clip_image001[16]

Then select steps->Run agent.

clip_image001[18]

Copy the command.

clip_image001[24]

Change the directory to C:\Program files\Microsoft SQL Server\100\COM.

Then give distrib.exe –>copy the command from Job step properties.

clip_image001[28]

Now we could start distributor agent in command prompt itself.

clip_image001[30]

Running Snapshot agent:

Double click snapshot agent.

clip_image001[32]

Select Steps->Run agent.

clip_image001[34]

Change the directory to C:\Program files\Microsoft SQL Server\100\COM.

Then give snapshot.exe –>copy the command from Job step properties.

clip_image001[36]

Then we could start the snapshot agent in command prompt.

clip_image001[38]

How to create transactional replication in SQL Server

How to create transactional replication in SQL Server?

Replication allows as to automatically distribute copies of data from one server to one or more distributor server.In replication we will have Distributor,Publisher and Subscriber.

In  transactional replication  snapshot of the publication database objects and data is saved.If there is any data changes and schema modifications made at the Publisher,it will be delivered to the Subscriber. The data changes are applied to the Subscriber in the same order and within the same transaction boundaries as they occurred at the Publisher.Therefore, within a publication, transactional consistency is guaranteed. 

Transaction replication has three agents namely,Snapshot agent,Log reader agent and distribution agent .

The Snapshot Agent is an executable file that prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder.

The  Log Reader Agent monitors the transaction log of each database that is configured for transactional replication and copies the transactions from the transaction log into the distribution database.

The  Distribution Agent is an executable that moves the snapshot and the transactions held in the distribution database tables to the destination tables at the Subscribers.

For all types of replication it is mandatory to create distribution for the publisher instance before configuring replication. For configuring distribution follow the steps in “How to configure distribution for replication”

Once the distribution is configured follow the below steps to create publisher

Let us create New publisher:

Right click Local publication->New Publication

 clip_image001[16]

New publication wizard appears.Click Next and continue.

clip_image001[18]

Select the database that contains the data or objects you want to publish.Click Next.

clip_image001[20]

There are different types of publication.

Select the publication type that supports your application.Click Next.

clip_image001[22]

Select the tables to publish  as articles.Click Next.

clip_image001[24]

Add filters to exclude unwanted rows from published tables.Click Next.

clip_image001[26]

Specify when to run the snapshot agent and click Next.

clip_image001[28]

Select  security settings

clip_image001[30]

Specify the domain or machine account under which the snapshot agent process will run.

Select Run under the SQL Server agent service account.Click ok.

clip_image001[32]

After selecting the account for snapshot agent and log reader agent,click Next.

clip_image001[34]

Check create the publication.Click Next.

clip_image001[36]

Give the publication name and finish the wizard.

clip_image001[38]

Close the window after creating publication successfully.

clip_image001[40]

Let us create the subscribers:

Before creating subscribers create one empty database where we could subscribe the publisher database.

Right click Local subscriptions->New subscriptions’

clip_image001[42]

New subscription wizard window appears.Click Next and continue.

clip_image001[44]

Choose the publisher and click next.

clip_image001[46]

Select the distributor agent location.Click Next.

clip_image001[48]

Select subscription database which you created.The database should be empty database.Click Next.

clip_image001[50]

Specify the process account and connection option for each distribution agent.

clip_image001[52]

Specify the domain or machines account under which the distribution agent process will run and click ok.

clip_image001[54]

Schedule the agent and click Next.

clip_image001[56]

Initialize the subscription as immediately and click next.

clip_image001[58]

Check  create the subscription and click Next.

clip_image001[60]

Click Finish and complete the wizard.

clip_image001[62]

Close the window after creating subscription.

clip_image001[64]

Launch replication monitor to view the connection.

To open Replication monitor Right click Replication->Launch replication monitor. 

clip_image001[66]

Successfully we have created publisher,distributor and subscriber.

clip_image001[68]

How to configure distribution for replication

Replication allows as to automatically distribute copies of data from one server to one or more distributor server.In replication we will have Distributor,Publisher and Subscriber. 

First we have to configure a Distributor to set up replication.Distributor is a server that contains the distribution database.It  stores all the transactions for transactional replication and meta data for all types of replication .  Each Publisher can have only one Distributor instance.But multiple publishers can share one Distributor.We can also Configure a centralized Distributor for multiple Publishers.

There are two types of distributor

1.Local distributor.

2.Remote distributor. 

Let us see how to configure distribution:

Right click Replication->Configure distribution

clip_image0014_thumb1

Configuration Distribution wizard window will appear.Click Next and continue.

clip_image0016_thumb2

Select the distributor which will act as server and responsible for storing replication information used during synchronizations. If you want to choose  remote  distributor select the second option.

image_thumb3

Configure SQL Server agent to start  automatically.To start the sever agent automatically, when computer is started.Click Next and continue.

image_thumb6

Specify the distribution database name.Also specify the valid path for database file and log file.Click Next

clip_image0018_thumb1

Enable the servers which will use the distributor when they become publishers.

clip_image00110_thumb1

In complete wizard window,we will get the options which we selected during the process.Click Finish.

image_thumb5

Close the wizard and complete the distribution database creation. 

clip_image00114_thumb1

by

How to add node to SQL Server cluster

We can add nodes to the existing SQL server failover cluster by following below steps. (Below method can be followed to add node in SQL Server 2008, SQL Server 2008r2 and SQL Server2012)

1. To add new node to existing SQL Server failover cluster, from new node invoke Setup.exe installation media . 

2.The Installation Wizard will show the SQL Server Installation Center as shown in below image.Click Installation an then, select Add node to a SQL Server failover cluster.

image

3.Install the setup support files.

clip_image001[36]_thumb[1]

4.After setup validation ,you will get details about operations completed,Click Next and continue.

clip_image001[38]_thumb[1]

5.On the Product key page, specify the Product ID key of the product. Click Next.

clip_image001

 

6.Then read and select the check box to accept terms and agreements.Click Next.

image

 

7.Then in cluster Node configuration choose the SQL Server instance name and Current Node which we are adding would automatically appear in “Name of this node” . Click Next and continue.

clip_image001[40]_thumb[1]

8.On the Service Accounts page, specify login accounts for SQL Server services.Specify strong password for each account.Click Next after finishing.

image

9.On the Reporting page by default  the option for error reporting is enabled.If you don’t want, disable it and continue.

10.Add Node rules page provides  the information about operation completed.

image

11.Ready to Add Node page displays the options that were specified during Setup. Click Install.

image

12.Add Node Progress page provides the status of the installation process.

13.After installation, the  page provides the summary for the installation.

image

To complete the SQL Server installation process, click Close.

Move services or application between nodes to verify if SQL Server resources are able to failover successfully:

1.After installation you could move the service between all nodes.

2.Open failover cluster manager->expand SQL Cluster->expand services and applications->select the SQL Server group –> Move this service or application to another node –>Choose the newly added node.

clip_image001[44]_thumb[1]

3.After moving the resources to newly added Node check if all the resources are able to come online on newly added node. 

clip_image001[46]_thumb[1]

by

SQL Server Error while enabling Windows feature : NetFx3, Error Code : -2146498298

SQL Server 2012 setup might fails with below error when you do not have .Net frame work 3.5 features.

{
TITLE: Microsoft SQL Server 2012 Setup
——————————
The following error has occurred:
Error while enabling Windows feature : NetFx3, Error Code : -2146498298 , Please try enabling Windows feature : NetFx3 from Windows management tools and then run setup again. For more information on how to enable Windows features , see http://go.microsoft.com/fwlink/?linkid=227143
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.2100.60&EvtType=0x681D636F%25401428%25401
——————————
BUTTONS:
}

 

To resolve the above error open server manager à Add roles and features wizard à select .Net frame work 3.5 feature and install as shown in below image.

clip_image002[9]

If the installation of.Net frame work 3.5 feature fails most probably it should be because of incorrect SXS folder. In such case choose an alternate SXS folder for installation in “confirm installation screen” (Which would appear as soon as you click install on above screen)
You will find SXS folder in Windows DVD under \sources\SXS

by

HOW TO CREATE CLUSTER USING HYPER-V

Hyper –V can be used to create virtualized computers to improve the hardware efficiency. In this blog post I will try to cover how to create windows failover cluster using Hyper-V with out using multiple physical computers. Cluster we are building can be used to install Clustered SQL Server and similar cluster aware applications.

1. We will create a Domain controller

2. We will create two member servers which will act as two nodes of the cluster.

We will start the installation by  Turning on window feature Hyper-V.

To enable Hyper-V Open control panel—>Programs –>Get Programs->Turn windows features on or off –>Enable Hyper-V  as shown in picture below

image

Once the Hyper-v feature is enabled open the hyper v manager and follow the below steps one by one.

Before creating virtual machine create virtual switch to configure network access between the computers which will create and name it.

clip_image001[1]

Select the type of virtual switch for your virtual machines.

clip_image001[3]

Give the name for your Virtual switch and click ok.

clip_image001[5]

Now create New virtual machine by Right clicking SQLPC –>New->Virtual machine.

image

2.New virtual machine wizard will appear.Click Next to continue.

 

clip_image001

Specify the name for your virtual machine.Let us Create domain controller  as DC.

image

Create a new folder or use the existing folder to store the virtual machine.Specify that location in location tab.Click Next 

image

Specify the amount of memory you want to allocate for this machine.Then,click Next.

image

 

Inorder to do OS installation ,Connect to virtual hard disk and specify the size for it.Click Next

image

Then install an Operating system from CD or as image file which you have in your system.

image

Browse and select the file for installation.Click open.

 

image

Now,click finish and complete the virtual machine wizard.

image

Similarly,Follow the above steps and create two cluster nodes as clusternode1 and clusternode2 .

Now Hyper –V manager will have three virtual machines which we created one as DC and others as clusternodes.

Right click DC->Start->connect.(similarly do for all)

image

Cluster nodes will start running.

image

DC virtual machine connection window will appear,click next.

image

Installation window will appear,click install now to start installation.

image

Select the operating system you want to install.Select  windows server 2008 R2 Enterprise(Full installation) for DC(domain controller).Click Next.

image

Accept the license term and click next

 

image

Select the type of installation as Custom(advanced)

clip_image001[5]

Click Next and continue

image

 

image

After installation restart your virtual machine to update with new setting

image

Give the password for login.

image

Domain controller is installed with OS.

image

Perform the task  which you want or else close the window.

image

Configure TCP/IP address and enable LAN connection.

To configure TCP/IP, open control panel->Network and internet->Network connections->click IPV4

clip_image001[7]

Give IP address ,subnet mask and default gateway ,click ok.

clip_image001[9]

Open  dcpromo .

image

Active directory domain service wizard will appear.Click Next and continue

image

Select create new domain in a new forest

clip_image001[11]

 

Give forest root domain which acts as domain.

clip_image001[13]

 

Set forest functional level .Default Functional level is windows sever 2003.

clip_image001[15]

 

Check DNS server and click Next.

clip_image001[17]

 

Click Yes and continue.

clip_image001[21]

 

Select the folder to store databases,log files and click Next.

clip_image001[23]

Give the administrator password and click Next.

clip_image001[25]

Summary of your selections will appear,review your selection and click next.

 

clip_image001[27]

 

DNS installation will be completed.

clip_image001[29]

 

Click Finish and complete domain services installation wizard.

clip_image001[31]

Restart your computer after installation.

 

clip_image001[33]

Now do the similar things for cluster nodes 1&2.

Select computer->properties->computer name.Change the computer name and give domain name  which you gave in DC.

clip_image001[35]

 

Configure IPV4 properties for cluster nodes.

clip_image001[37]

 

clip_image001[39]

Click OK.

clip_image001[41]

Restart your computer to apply the changes.

clip_image001[43]

Click Ok,to restart.

clip_image001[45]

Now we have domain controller and member servers. We have to install failover cluster feature and make this members servers as node of cluster which we are going to create.

To install the failover cluster feature and create a cluster follow the steps in https://sqlserverscribbles.com/2013/06/17/how-to-install-cluster-in-windows-2008-and-windows-2012/

Once the cluster is created we have to create  shared Disk for installing failover aware clustered application like SQL Server. Follow the steps in  https://sqlserverscribbles.com/2013/06/16/how-to-create-shared-disk-using-iscsi-software-target/  to create a shared disk using iSCSI target software.

by

windows cluster freezes at “waiting for notification that node ‘‘ is a fully functional member of the cluster”

Adding node in windows cluster freezes at “waiting for notification that node ‘‘ is a fully functional member of the cluster”

When you try to add a node to cluster in windows2008 or windows2008 R2 or windows 2012 it may get stuck in the below screen With message “waiting for notification that node is a fully functional member of the cluster”

After some time you will notice “operation is taking longer time than expected” and eventually after the time out add node wizard will fail with error “Unable to successfully cleanup”.

clip_image001

You may notice “the operation is taking longer than expected in some time”

If you look at cluster diagnostic log you may find

[RHS] RhsCall::Perform_NativeEH: ERROR_NOT_READY(21)’ because of ‘Startup routine for ResType MSMQTriggers returned 21.’

Failed to write SPNs to node’s computer object – status 5

Resolution

1. To resolve the exclude UDP port 3343 from fire wall.

2. If the issue persists after opening the UDP port 3343. Check failover clustering diagnostic event for error “Failed to write SPNs to node’s computer object – status 5”. If you find above error make sure http://support.microsoft.com/kb/976424 is applied in Domain controller.

3. If you find event ID 1289 “The Cluster Service was unable to access network adapter ‘Microsoft Failover Cluster Virtual Miniport’. Verify that other network adapters are functioning properly and check the device manager for errors associated with adapter ‘Microsoft Failover Cluster Virtual Miniport’. If the configuration for adapter ‘Microsoft Failover Cluster Virtual Miniport’ has been changed, it may become necessary to reinstall the failover clustering feature on this computer” follow the steps in http://support.microsoft.com/kb/973838

4. Make sure IPv6 is enabled on all NIC’s

5. Enabled Cluster communication and client access on the Public network http://technet.microsoft.com/en-us/library/cc728293%28v=ws.10%29.aspx

6. Make sure Symantec Endpoint Protection is uninstalled/disabled when you add the node.

7. Make sure there is no duplicate name computer objects for nodes in AD .follow http://blogs.technet.com/b/askcore/archive/2011/05/31/cluster-installation-time-out-issues.aspx

 

by

How to install cluster in windows 2008 and windows 2012

Failover cluster feature can be installed enterprise and datacenter edition of windows2008,windows2008R2 and windows2012.

There are changes to the quorum configuration in windows2008,windows2008R2 and windows2012 cluster compared with earlier versions in which it is mandatory to have a shared disk to install the cluster (Exceptions Geo clusters).  In this blog I will explain how to install failover cluster feature and create cluster. Shared disk is not mandatory for installing windows cluster but it is required for server applications like SQL Server when you are installing them on cluster.

If you would like to learn how to create shared disk for using in cluster follow the steps in How to create shared disk using iSCSI Software Target

To install failover cluster feature Open Sever Manager (Start—>Run—>Servermanager.msc),Expand Features.Select Add Features.

clip_image00178_thumb1

Then install Failover Clustering.

clip_image00180_thumb1

Confirm the installation and Click install.

clip_image00182_thumb1

Click close and complete the installation.

clip_image00184_thumb1

After installation ,open failover cluster manger.

Right click failover cluster manager->Create a cluster wizard.

clip_image00186_thumb1

Enter Server name of all the nodes which will be part of cluster you are creating.If you do not know the server name you can click on browse and select the servers. 

clip_image00188_thumb1

Below window appears when you click browse–>Advanced->Find now. Select the systems which you want to add in the cluster.

In the below example I have select Node1

clip_image00194_thumb1

Configure the validation warning and click Next.

clip_image00196_thumb1

Enter the IP address for cluster and Click Next.

clip_image00198_thumb1

Click Finish and complete the cluster creation. 

clip_image001100_thumb1

Once  you have created the cluster follow the steps in http://blogs.msdn.com/b/clustering/archive/2010/05/14/10012930.aspx to configure the Quorum setting for your cluster

 

If you liked this post, do like us on Face book at https://www.facebook.com/mssqlwiki and join our Facebook group

Thank you,

Pradeepa

by

How to create shared disk using iSCSI Software Target

One of the mandatory requirement to install SQL Server and similar application in cluster is to have shared disk.

I will explain how to create shared disk using iSCSI software target in this blog. You can download iSCSI Software Target from This link.

Installing Microsoft iSCSI software target.

When you invoke the self extracting package from downloaded above link it extracts  X64 and x86 version of installer.

  • Select iSCSI_target  for installation on the system which will act as iSCSI target. We will create iSCSI target disks in this machine and these disks will be shared by nodes using iSCSI initiator.
  • Select iSCSItargetClient_public and install in nodes in which you would like to have shared disk probably all nodes which is going to be part of cluster you are installing.

 

INSTALLATION IN CLUSTER NODES:(Follow same steps in all the nodes which will be part of cluster which you are installing)

Install iSCSItargetClient _public in all nodes which are going to be part of cluster which you are installing .

Run the file to start the installation.

clip_image001[6]

Click Next and continue

Select the type of installation as Typical.

clip_image001[12]

Specify users account that is member of administrators group in system which is acting iSCSI target and iSCSI initiator (current system).

clip_image001[16]

Configure other settings.

clip_image001[18]

Start the installation by clicking Install button.

clip_image001[20]

 

clip_image001[22]

 

Click finish to complete the installation

 

Install the  iSCSI_target on the system which will act as iSCSI target probably file server (or) Domain controller if you are installing failover clustering for testing.

 

CONFIGURE iSCSI INITIATOR IN CLUSTER NODE’S:

After installation,Open ISCSI initiator.

clip_image001[24]

  • Select yes  and continue.

clip_image001[26]

  • Type the target server name and click on Quick Connect

clip_image001[28]

                                       (Or)

  • Click Discover panel and select the target sever.

 

clip_image001[30]

  • Give the server IP address.Click ok.

clip_image001[32]

Similarly Follow the above steps and select the target in all nodes of cluster.

 

Configuring iSCSI target

Now after selecting the target,we have to add the all the iSCSI clients in iSCSI target.

Open MS iSCSI Target in system which is acting as iSCSI target.

 

clip_image001[34]

Right click iSCSI target->create ISCSI target.

clip_image001[36]

Create target wizard will open.

clip_image001[38]

 

Specify the target node name,click Next.

clip_image001[40]

Browse and select the iSCSI initiator,click ok. 

clip_image001[42]

Selected initiator will appear.Click Next.

clip_image001[44]

Click Finish and complete the target creation.

clip_image001[46]

Target Node1 is added.

clip_image001[48]

Follow the above steps in all nodes.

 

CREATING VIRTUAL DISK FOR  ALL NODES:

To create virtual disk in iSCSI target right click Node1->Create virtual disk.

Click Next.

 

clip_image001[52]

Specify the file name which will be used as virtual disk.Specify it with .vhd extension.Click Next.

clip_image001[54]

Specify the size for your virtual disk.Click Next.

clip_image001[56]

  Click Finish.

clip_image001[58]

For node2,Right click Node2->Add Existing virtual disk to iSCSI target.

clip_image001[60]

Click OK.

clip_image001[62]

CLUSTER NODES:

Now after adding the nodes as target in DC,we have to initiate the server in cluster nodes.

Select iSCSI initiator.Click Refresh,if you didn’t get any discovered target.

clip_image001[64]

Select the target  and connect it.Click OK.

clip_image001[66]

Open Disk management in cluster nodes:

clip_image001[68]

Right click Disk1->Online

clip_image001[70]

  Again ,Right click Disk1->New Simple Volume.

clip_image001[72]

New Simple Volume Wizard->Format this volume with the following setting.Click Next.

  clip_image001[76]

Then Finish the setting.

 

Open Failover cluster manager.Expand SQL Cluster.mssql.wiki.com.

Storage->Add a disk.

clip_image001

Click OK and add disk to a cluster.

clip_image001[4]

Disk is added. (If disk is created for one node,automatically it will be shared by other node)

clip_image001[8]

 

Now the shared disk is ready for use and can be used for installation of SQL Server cluster (or) any other cluster aware application which needs shared disk.

by

NON CLUSTERED COLUMNSTORE INDEX

What is non clustered column store index in SQL Server?

Column store index is a new feature introduced in SQL Sever 2012.In Non clustered column store index data is stored as column.Column order is not important in column store index.In this non clustered column store index we don’t have traditional row execution mode instead of that,we have batch execution mode.This improves query execution.Batch execution mode  executes several rows at a time as a batch.This reduces CPU consumption.

In column store execution, only columns that query needs will be read.This reduces I/O and memory utilization.

We cant have Unique,Primary or Foreign key constraints in column store index and also it does not have feature like INCLUDE.Once we have created the Non clustered column store index for a table,we can’t update the table with new values because the table is READ-ONLY.We could have only one non clustered column store index.Column store index could have only 1024 columns.And we don’t have clustered column store index only Non clustered is available.

This column store index uses the benefits of segment elimination based on some conditions.It gives faster performance for common data warehousing queries.

Create a Column Store Index in SQL Server by using below steps.

Using GUI:

1.In Object Explorer, expand Tables, right click -> Indexes and click New Index. Select non clustered column store index .

 

image

2.Add the column store columns.

image

  •   select the table column to be added to the index.

 

image

  • Selected Column store column will be added.

 

image

3.Set the options by selecting options from select page.In that give the value for Max degree of parallelism as greater than or equal to (2) to have batch execution.

Set other extended properties you need by clicking extended properties node.

 

image

4.Non clustered column store index is successfully created.

image

USING T-SQL:

NONCLUSTERED COLUMNSTORE index without options:

Create database product;

Use product;

CREATE TABLE Test

(ID [int] NOT NULL,

RecDate [int] NOT NULL,

DelDate [int] NOT NULL,

ExtDate[int] NOT NULL);

GO

CREATE CLUSTERED INDEX cl_test ON Test (ID);

GO

CREATE NONCLUSTERED COLUMNSTORE INDEX csindex_test ON Test (RecDate, DelDate, ExtDate);

GO                                

                                                (or)

NONCLUSTERED COLUMNSTORE index with options:

CREATE NONCLUSTERED COLUMNSTORE INDEX csindex_test ON Test

(RecDate, DelDate, ExtDate)

WITH

(DROP_EXISTING = ON, MAXDOP = 2)

ON “default”

GO

image

by

How to create clustered and non-clustered index

Index is a database object, which can be created on one or more columns(max 16 columns).The index will improve the performance of data retrieval and adding.indexes are created in an existing table to locate rows quickly and efficiently.

What is clustered index?

Table can have only one clustered index.In clustered index data’s in table is sorted in particular order based on index keys(either AESC/DESC).In clustered index page chain that holds data pages is also sorted in same order as index keys.So,SQL server follows the page chain in order to retrieve the data rows.By this new rows could be added just by adjusting the links in the page chain without moving entire pages.The leaf nodes of a clustered index contains the data pages with index keys.
What is Non clustered index?
For a table we could have more than one non clustered index because it doesn’t affect data pages organization.The leaf node of a non clustered index does not consist of data pages. Instead, the leaf nodes contain index rows.If we have both the clustered index and non clustered index for a table then index row will have Clustered index key columns that point to the data row. If there is no clustered index then the index row contains Non-Clustered index key columns which is stored along with row locator (or)  row identifier.The pointer from an index row to a data row is called a row locator.
Both clustered and non clustered indexes can be unique. That is no two rows can have the same value for the index key. Also we have one special type of index called Non clustered column store index.
 
Creating a clustered index  using Object Explorer:
1.In Object Explorer, expand the table for which you want to create a clustered index.

image

2.Following window will allow as to select the key columns for index.

image

3.We could add other options by selecting options node from select page.

 

image

4.Click ok.Clustered index will be created.

Create Non-Clustered index using object explorer:

1.Similarly Select Non clustered index from New index.Select the Key columns for an index.

image

2.In non clustered index we could add included columns for your index.In order to overcome existing index limits.After including needed included columns ,Click ok.

image

3.Similarly other options could be added by selecting different nodes from select page.

4.Click ok .Index will be created.

by

Bulk insert fails with error 4861 Cannot bulk load because the file could not be opened

When you do bulk insert in SQL Server it may fail with below error because of double hop .

Error :

Msg 4861, Level 16, State 1, Line 3

Cannot bulk load because the file “\\path\” could not be opened. Operating system error code 5(failed to retrieve text for this error. Reason: 15105).

Steps to fix the above error

1. Connect to SQL Server using SSMS (With account you run bulk insert) and execute below query and check if it is using Kerberos authentication

select net_transport,auth_scheme from sys.dm_exec_connections where session_id=@@spid

2. If the session is not using Kerberos authentication then fix the SPN issues (startup account of SQL Server should have read and write SPN permissions). Account which is running bulk inser should have read SPN permission. Setpn exe or adsiedit can be used to add or display all the SPN’s. If the SPn’s are registered properly and still connection fails to NTLM then get the output of SSPIClient.exe and verify why Kerberos authentication fails.

3. Make sure the account which is running bulk insert is trusted for delegation in Active Directory.

4. Account which is running bulk insert should have access to the shared directory in which BCP files are placed.

Reference  BULK INSERT (Transact-SQL) 

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

Security Account Delegation (Impersonation)

If a SQL Server user is logged in using Windows Authentication, the user can read only the files accessible to the user account, independent of the security profile of the SQL Server process.

When executing the BULK INSERT statement by using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.

To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.

For more information about this and other security considerations for using BULK INSERT, see Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK…) (SQL Server).

by

How to create SQL Server Agent Jobs

SQL Server agent can be used to create JOBS which can be scheduled to execute automatically. SQL Server Agent uses SQL Server to store job information. Jobs can have one or more job steps. Each step contains its own task.Each action in a job is a job step.We can schedule the job based on need as daily, weekly, monthly, hourly etc

For example, Let us create a job to back up a database automatically.

Steps to create Job in SQL Server Agent

1. Expand SQL Server Agent in Object Explorer.(If SQL Server Agent is stopped ,right click and start it)

image

2.Right click Job->New job.

image

3.Fill  the Name field with a  name for your job,Give description.

image

4. On the left side of the New Job window, you’ll see a Steps icon under the Select a page.Next add the individual steps for your job. Click the New button to create a new job step and you will see the New Job Step window.

image

  • Use the Step Name textbox to provide a name for the Step. select the database where the job has to be applied.
  • Use the Command textbox to give the Transact-SQL syntax corresponding to the desired action for this job step. Once you have completed entering the command, click the Parse button to verify the syntax.
  • Validate the syntax, click OK to create the step.

image

5.Schedule the job by clicking the Schedule icon in the Select a Page.Then schedule new job.

image

6.Create alerts for your job by Giving alert name and also select the database.Also select the type of alert and severity of your alert

image

7.Specify the actions to be performed when the job completes or when job fails.

image

Note :All the above steps can be performed by TSQL (with out the GUI) using follow stored procedures

sp_add_job to create a job.
sp_add_jobstep to create one or more job steps.
sp_add_schedule to create a schedule.
sp_attach_schedule to attach a schedule to the job.

by

How to create backups using database maintenance plan

You can create a database maintenance plan to automate the SQL Server database backups. SQL Server backup maintenance plan can be scheduled to backup the databases automatically or executed manually.

Follow the steps below to create a database backup maintenance plan and schedule it to execute automatically

1.Open SQL Server Management Studio, expand the Management node, and then expand the Management Plans node.

 

image

2.Right-click Maintenance Plans, click Maintenance Plan Wizard.

image 

3.SQL Server Maintenance Plan Wizard window will appear.click next.

image

4.Then type  a name for this database backup plan.

image

 

  • Select schedule according to your need.Generally daily,weekly,monthly or hourly.

 

image

 

5.Select  the maintenance tasks,which you wanted to plan.

image

6.select the order of your plan.Click Next.

image

7.On the Define Back Up Database (Full) Task dialog box, specify information about the full backup. Specify database where this plan has to be applied.Press ok.Then Click Next.

image

image

8.On the Define Back Up Database (Transaction Log) Task dialog box, configure the transaction log backup. Click Next.

image

9. Choose the backup destination

image

10.On the Define Maintenance Cleanup Task dialog box, configure the cleanup tasks.specify the folder name where you take backups.Then specify the backup folders extension.Click Next.

image

11.On the Select Report Options dialog box, select whether to write the report to text file or send the report through email. Click Next.

image

  • Plan is in progress

image

 

image

12.Maintenance plan wizard is successfully created.Click Finish

image

by

How to set Max degree of parallelism (MAXDOP)

Max degrees of parallelism (a.k.a MAXDOP) in SQL Server is used to control the maximum number of threads per execution plan operators work.MAXDOP does not restrict the number of CPU’s/Threads used by single batch or Query.

Ideally MAXDOP should be equal to number of online schedulers per node. You can use the below query to get the number of  online schedulers per node. All the parallel threads for the tasks of the query will be assigned from schedulers of same node so having MAXDOP beyond the number of online schedulers per node may not really improve the performance (With some exceptions).  

select count(*) as Maxdopcount, parent_node_id from sys.dm_os_schedulers
 where status='VISIBLE ONLINE' group by parent_node_id

Depending upon the workload in your environment you may increase or decrease the value.

Note: Always ensure you have same number of online schedulers in each node else you may face uneven workload and memory distribution among the SQL Server schedulers more details in SQL Server NUMA load distribution.

To configure Max degree of parallelism follow the below steps.

1.Connect to the Database Engine.

2.From the Standard bar, click New Query.

3.Then execute the following query.

Syntax:

</pre>
sp_configure 'show advanced options', 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

sp_configure 'max degree of parallelism', 1; /*Replace 1 with your preferred MAXDOP value */

GO

RECONFIGURE WITH OVERRIDE;

GO

<span style="font-family: Consolas, Monaco, monospace; font-size: 12px; line-height: 18px;">

 

image

Method2

1.In Object Explorer, right-click a server and select Properties.

2.Click  Advanced  from select a page.

3.In the Max Degree of Parallelism box, select the maximum number of processors to use in parallel plan execution.

image

by

SQL Server Database tuning advisor

Database Engine Tuning Advisor helps you select and create an optimal set of indexes, indexed views, statistics and partitions for the tables to improve the Query performance .

Database Engine Tuning Advisor uses trace files, trace tables, or plan cache as workload input when tuning databases or else it could use the query which you select for workload analysis.

The Database Engine Tuning Advisor (DTA) helps you to tune databases to improve query processing.

To start a Database Engine Tuning Advisor tuning session, follow these steps:

Before going into this session let us see how indexes (or)stats affects the performance of the query

1.I have created a new database(E.g..DTA).In the new database I have created a table named Test with 100000 rows in it. Let us see how to create necessary indexes and stats on tables to improve query performance based on the queries fired this table .

First let us enable statistics time on and statistics IO on to view the compile time,CPU time ,elapsed time and I/O’s performed by the query

set statistics time on
set statistics IO on

image

Example:Execute the query select * from Test where a=10,

 

Before creating index,it takes 1158 logical reads, cpu time=16ms and elapsed time=28ms.The performance is very low.In order to increase the performance we go for indexes.

image

 

 

After creating index, logical reads have become 329,cpu time=15ms and elapsed time= 11ms.

image

 

2. Let us see how to create this indexes and statistics using database tuning advisor. Copy the query which has to be tuned in SSMS new query windows.

 

image

 

3. select your query->right click->select Analyze query in database tuning advisor.

image

4.Then this window will appear .In that select the database  to do workload analysis.

image

5.Then click start analysis in tool bar.Tuning process will be started.

image

6.After tuning process completes, recommendations will be provided for your table to improve the query performance.

image

7.Either you could save the recommendation to apply later or you could apply it immediately .

image

 

8.We can also use  trace files as workload to tune the database. Let us create a trace file by using SQL Server Profiler  and see how that can be used to tune the database.

Open SQL Server Profiler

image

9.SQL Server Profiler window appears.In that give trace name.Then Run the profiler.

image

 

10. Now execute the queries which you would like to tune Trace file will capture the events.

For example: Let us execute the same select query which we used in the beginning.

 

image

 

11.  Save the trace file. 

 

12. Open the Database tuning advisor. In the Workload area, select the file that you created by using SQL Server Profiler.Select the database for workload analysis and to tune.Then click start analysis.

image

 

 

image

13.Index recommendations will be provided.By applying this recommendation the estimated improvement will be 56%.

image

14.Then apply the recommendation.Actions->Apply recommendation.You could apply  the recommendations immediately or you could schedule it for later.

image

15.Here I choose to apply the recommendations immediately.

image

 

image

16. Once the recommendations are applied performance of the query will improve.

by

How to configure maximum server memory

Follow the steps below to configure max server memory and minimum server memory for SQL Server.

If you would like to derive the value for max server memory and minimum server memory for sql server   follow  http://mssqlwiki.com/2013/04/22/max-server-memory-do-i-need-to-configure/

Method 1

1.In Object Explorer, right-click a server and select Properties.

2.Click on Memory .

3.Under Server properties –> Memory  enter the value that you want for Minimum server memory and Maximum server memory.

  • image

    •  

     

    Method 2

    sp_configure  ‘show advanced options’,1
    reconfigure with override
    sp_configure   ‘max server memory (MB)’,2000 — Specify the value here
    reconfigure with override

    sp_configure ‘min server memory (MB)’,100– Specify the value here
    reconfigure with override

    by

    CryptAcquireContext and CryptReleaseContext example

    CryptAcquireContext and CryptReleaseContext example

    #include <windows.h> 
#include <string> 
#include <winbase.h> 
#include <iostream> 
using namespace std;
#include <Wincrypt.h >
  
                                      
void main()
{
LPCSTR rgwchKeyContName = "Test123456";  
HCRYPTPROV m_hCryptoProviderFB;
BOOL ret;
BOOL ret2;

ret=CryptAcquireContext(&m_hCryptoProviderFB, rgwchKeyContName, MS_ENHANCED_PROV, PROV_RSA_FULL, CRYPT_SILENT);
	
if (!ret && GetLastError() == NTE_BAD_KEYSET)

{
	
	printf("\nUnable to open Keyset.CryptAcquireContext failed with error: 0x%X . \nWe will try creating key",GetLastError());

	ret2=CryptAcquireContext(&m_hCryptoProviderFB, rgwchKeyContName, MS_ENHANCED_PROV, PROV_RSA_FULL, CRYPT_NEWKEYSET | CRYPT_SILENT);
		if (!ret2)
		{
		printf("\nCryptAcquireContext failed creating key.Error: 0x%X",GetLastError());
		}
		else
		{
		printf("\nKey created");
		}
	exit;
}


else if (!ret && GetLastError() == NTE_BAD_KEYSET)
{
printf("CryptAcquireContext failed with error: 0x%X",GetLastError());
}

else
{

	printf("CryptAcquireContext opened key. Return value is 0x%X.",ret);
}


	if (CryptReleaseContext(m_hCryptoProviderFB,0))
	{
	//printf("\nHandle has been released.\n");
	}
	else
	{
	printf("\nHandle could not be released.\n");
	}

}
    by

    Criticalsection example

    Criticalsection and CreateThread example (EnterCriticalSection LeaveCriticalSection)

    #include <windows.h> 
#include <string> 
#include <iostream> 
#include <process.h>    /* _beginthread, _endthread */
long a=0;
long b=0;
int Threadcount=64;
int s=Threadcount;
CRITICAL_SECTION  gcs; 
void Submain(void *x)
{
	for (int L=0;L<1000;L++) 
		{

			a=a++;
			EnterCriticalSection(&gcs);
						b=b++;
			LeaveCriticalSection(&gcs);
		}

/*
    s=s-1;  //Simple synchronization technique. May be useful if you like to increase the thread count WaitForMultipleObjects support value defined for MAXIMUM_WAIT_OBJECTS 64
	if(s==0)
	{
		d=TRUE;
	}
*/
_endthread();
}

 
void main()

{

HANDLE *hThreads;
hThreads = new HANDLE[Threadcount] ;
InitializeCriticalSection(&gcs);
for (int i=0;i<Threadcount;i++)
{
hThreads[i]=	CreateThread(NULL,NULL,(LPTHREAD_START_ROUTINE  )Submain,  NULL,  0,  NULL);
 	
		if (hThreads[i]==NULL)
		{
			printf("\nThread creation failed for thread %d with error %d",i,GetLastError());
		}

}

DWORD rw=WaitForMultipleObjects(Threadcount,hThreads,true,INFINITE);
DeleteCriticalSection(&gcs);
//while(!d); //Simple synchronization technique 

printf("Value of a is:%d\n" ,a);
printf("Value of b is:%d\n" ,b);
system("pause");
}

    by

    CreateProcess example

    CreateProcess example

    #include <windows.h> 
#include <string> 
#include <winbase.h> 
#include <iostream> 
using namespace std;

void main()
{
	int N=5;	
	cout<<"Enter count for process:";
	cin>>N;

	PROCESS_INFORMATION *x;
	STARTUPINFO *startup_info;

	startup_info = new STARTUPINFO[N];
	x =new  PROCESS_INFORMATION[N]; 
    HANDLE *h;
	h = new HANDLE[N];
	for (int i=0;i<N;i++)
		{
			memset((char *)&startup_info[i], 0, sizeof(STARTUPINFO));
			startup_info[i].cb = sizeof(STARTUPINFO);
			startup_info[i].dwFlags = STARTF_USESTDHANDLES;
			startup_info[i].hStdInput = GetStdHandle(STD_INPUT_HANDLE);
			printf("\nProcess creation starting:%d",i);
			CreateProcess("c:\\windows\\notepad.exe",NULL,NULL,NULL,FALSE,0x00010000,NULL,NULL,startup_info,&x[i]);
			h[i]= x[i].hProcess;
		
		}
		
	WaitForMultipleObjects(N, h,TRUE,INFINITE);
	
	for (int i=0;i<N;i++)
		{
		CloseHandle(x[i].hProcess);
		CloseHandle(x[i].hThread);
		}

}

    by

    CreateFileMapping or MapViewOfFileEx example

    CreateFileMapping or MapViewOfFileEx example

    #include <windows.h> 
#include <string> 
#include <winbase.h> 
#include <iostream> 
using namespace std;

void main()
{
	HANDLE  h;

	CHAR *filename;
 
	filename =new CHAR[2500];
	wcout<<"enter the file name:";
	cin.getline (filename,2500);
	h= CreateFile( filename,GENERIC_READ,FILE_SHARE_READ,NULL,OPEN_EXISTING,FILE_FLAG_SEQUENTIAL_SCAN,NULL);
	cout<<filename;
	if (h!=INVALID_HANDLE_VALUE)
	{
	printf("\nFile is opened/created");
	DWORD size = GetFileSize(h, NULL);
	HANDLE hFileMapping = CreateFileMapping(h, NULL,PAGE_READONLY, 0, 0, NULL);	
	
	CloseHandle(h);

	MapViewOfFileEx(hFileMapping, FILE_MAP_READ, 0,  0,0,NULL);           
		system("Pause");

	UnmapViewOfFile(hFileMapping);
	
	}
	else
	{
	printf("\nUnable to open or create file");
	}
	system ("pause");

}

    by

    Capture context switches from dm_os_ring_buffers

    You can use the below query to extract the context switches information from ring buffers and time each thread spend owning the scheduler.

    SELECT  
dateadd (ms, (a.[timestamp] - tme.ms_ticks), GETDATE()) as Time_Stamp,
a.*
FROM
(SELECT 
	  y as timestamp,	
      x.value('(//Record/@id)[1]', 'bigint') AS [Record Id],
      x.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
      x.value('(//Record/@time)[1]', 'bigint') AS [Time],
      x.value('(//Record/Scheduler/@address)[1]', 'varchar(30)') AS [Scheduler Address],
      x.value('(//Record/Scheduler/Action)[1]', 'varchar(30)') AS [Scheduler Action],
      x.value('(//Record/Scheduler/CPUTicks)[1]', 'bigint') AS [Scheduler CPUTicks],
      x.value('(//Record/Scheduler/TickCount)[1]', 'bigint') AS [Scheduler TickCount],
      x.value('(//Record/Scheduler/SourceWorker)[1]', 'varchar(30)') AS [Scheduler SourceWorker],
      x.value('(//Record/Scheduler/TargetWorker)[1]', 'varchar(30)') AS [Scheduler TargetWorker],
      x.value('(//Record/Scheduler/WorkerSignalTime)[1]', 'bigint') AS [Scheduler WorkerSignalTime],
      x.value('(//Record/Scheduler/DiskIOCompleted)[1]', 'bigint') AS [Scheduler DiskIOCompleted],
      x.value('(//Record/Scheduler/TimersExpired)[1]', 'bigint') AS [Scheduler TimersExpired]
FROM
      (SELECT CAST (record as xml),timestamp  
      FROM sys.dm_os_ring_buffers 
      WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER' ) AS R(x,y)) a
	  cross join sys.dm_os_sys_info tme 
WHERE a.[Scheduler Action] = 'SCHEDULER_SWITCH_CONTEXT'
ORDER BY       a.[Scheduler Address] , [Time_stamp]

    by

    SSIS package fails when executed as job using proxy account

    Issue

    The SQL server Integration package which transfers the data from data source like excel to SQL Server database fails when executed from SQL Agent job using proxy account

    ERROR

    Date                      4/25/2013 4:16:34 PM

    Log                         Job History (SSISTest)

    Step ID                 1

    Server                   Myserver\SQL2008STD

    Job Name                            SSISTest

    Step Name                         SSISJob1

    Duration                              00:00:01

    Sql Severity                        0

    Sql Message ID                 0

    Operator Emailed                           

    Operator Net sent                          

    Operator Paged                               

    Retries Attempted                          0

    Message

    Executed as user: MyDomain\MyUser1. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.4276.0 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  4:16:34 PM  Info: 2013-04-25 16:16:34.81     Code: 0x4004300A     Source: Data Flow Task to move data from MS Excel to SQL server database SSIS.Pipeline     Description: Validation phase is beginning.  End Info  Error: 2013-04-25 16:16:35.04     Code: 0xC0202009     Source: Package Connection manager “Excel Connection Manager 1”     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: “Microsoft Access Database Engine”  Hresult: 0x80004005  Description: “The Microsoft Access database engine cannot open or write to the file ”. It is already opened exclusively by another user, or you need permission to view and write its data.”.  End Error  Error: 2013-04-25 16:16:35.05     Code: 0xC020801C     Source: Data Flow Task to move data from MS Excel to SQL server database Excel Source [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “Excel Connection Manager 1” failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2013-04-25 16:16:35.07     Code: 0xC0047017     Source: Data Flow Task to move data from MS Excel to SQL server database SSIS.Pipeline     Description: component “Excel Source” (1) failed validation and returned error code 0xC020801C.  End Error  Error: 2013-04-25 16:16:35.07     Code: 0xC004700C     Source: Data Flow Task to move data from MS Excel to SQL server database SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2013-04-25 16:16:35.09     Code: 0xC0024107     Source: Data Flow Task to move data from MS Excel to SQL server database      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  4:16:34 PM  Finished: 4:16:35 PM  Elapsed:  0.764 seconds.  Process Exit Code 1.  The step failed.

    Cause:

    Missing permission on PROFILE directory of SQL server Agent service account for Proxy account .

    BufferTEMPstorage path and BlobTempstoragepath are defaulted to TEMP and TMP environment variables for SQL Server agent start up account by default.

    When you use proxy account to execute a package then proxy account should have access to temp and TMP folder of SQL Server agents start up account profile.

    TEMP=C:\Users\StartupaccountofSQLAgent\AppData\Local\Temp

    TMP=C:\Users\ StartupaccountofSQLAgent\AppData\Local\Temp

    If your start up account of SQL Server agent is Local service or Network service then proxy account should have permission for TMP and TEMP folder located under C:\Windows\ServiceProfiles for Local service and Network service

    If the proxy account doesn’t have access to this location SSIS job would produce the below error:

    Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted

    04/08/2013 11:26:19,ExcelTC,Error,0,UKLONDT642483,ExcelTC,(Job outcome),,The job failed.  The Job was invoked by User MyDomain\MyUser1.  The last step to run was step 1 (ExcelTC\Package).,00:00:01,0,0,,,,0

    04/08/2013 11:26:19,ExcelTC,Error,1,UKLONDT642483,ExcelTC,ExcelTC\Package,,Executed as user: MyDomain\MyUser1. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.5500.0 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.   

    Started:  11:26:19  Info: 2013-04-08 11:26:19.94     Code: 0x4004300A     Source: Data Flow Task SSIS.Pipeline     Description: Validation phase is beginning.  End Info  Error: 2013-04-08 11:26:19.98     Code: 0xC0202009     Source: Package Connection manager “Excel Connection Manager”     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: “Microsoft Office Access Database Engine”  Hresult: 0x80004005  Description: “Unspecified error”.  End Error  Error: 2013-04-08 11:26:19.98     Code: 0xC020801C     Source: Data Flow Task Excel Source [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2013-04-08 11:26:19.98     Code: 0xC0047017     Source: Data Flow Task SSIS.Pipeline     Description: component “Excel Source” (1) failed validation and returned error code 0xC020801C.  End Error  Error: 2013-04-08 11:26:19.98     Code: 0xC004700C     Source: Data Flow Task SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2013-04-08 11:26:19.98     Code: 0xC0024107     Source: Data Flow Task      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:26:19  Finished: 11:26:19  Elapsed:  0.405 seconds.  Process Exit Code 1.  The step failed.,00:00:01,0,0,,,,0

    04/08/2013 11:10:11,ExcelTC,Error,0,UKLONDT642483,ExcelTC,(Job outcome),,The job failed.  The Job was invoked by User MyDomain\MyUser1.  The last step to run was step 1 (ExcelTC\Package).,00:00:01,0,0,,,,0

    04/08/2013 11:10:11,ExcelTC,Error,1,UKLONDT642483,ExcelTC,ExcelTC\Package,,Executed as user: MyDomain\MyUser1. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.5500.0 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  11:10:12  Info: 2013-04-08 11:10:12.65     Code: 0x4004300A     Source: Data Flow Task SSIS.Pipeline     Description: Validation phase is beginning.  End Info  Error: 2013-04-08 11:10:12.78     Code: 0xC0202009     Source: Package Connection manager “Excel Connection Manager”     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: “Microsoft Office Access Database Engine”  Hresult: 0x80004005  Description: “Unspecified error”.  End Error  Error: 2013-04-08 11:10:12.78     Code: 0xC020801C     Source: Data Flow Task Excel Source [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2013-04-08 11:10:12.79     Code: 0xC0047017     Source: Data Flow Task SSIS.Pipeline     Description: component “Excel Source” (1) failed validation and returned error code 0xC020801C.  End Error  Error: 2013-04-08 11:10:12.79     Code: 0xC004700C     Source: Data Flow Task SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2013-04-08 11:10:12.79     Code: 0xC0024107     Source: Data Flow Task      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:10:12  Finished: 11:10:12  Elapsed:  0.593 seconds.  Process Exit Code 1.  The step failed.,00:00:01,0,0,,,,0

    by

    How to stimulate a SQL Server resource failure in cluster

    How to stimulate a SQL Server resource  failure in cluster?

    If you like to perform a check in SQL Server cluster to identify if the SQL Server resources are failing over properly follow the below steps. you can use Step 2 to create a SQL Server hang and deadlocked scheduler dump in standalone server as well.

     

     

    1.       Create a instance failures scenario by  issuing “shutdown” command to SQL Server which will terminate/Shutdown SQL Server. SQL Server resource DLL in cluster should detect that the SQL Server have failed and start the SQL Server resource automatically.

     

    Steps

    a.       Connect to SQL Server using an account which has Sysadmin privilege and execute Shutdown command.

     

     

    2.       Create a SQL Server hang scenario by creating excessive blocking, once SQL Server exhaust all the available threads SQL Server will stop accepting connections and will go unresponsive. SQL Server resource in the cluster should detect that the SQL Server resource have failed and restart/failover SQL Server.

    Steps

    a.        Connect to SQL Server and below SQL Statements. (Do not close the session)

     

    
Create database test;
use test;
create table block  (a char(10));
insert into block values ('Test');
go 10
begin transaction;
update block set a ='Test1'

    b. Download the Ostress.exe

    c. Execute the following command in ostress.exe

    ostress.exe -Sservername\Instancename -E -d test -Q "select * from test..block" -n 2000

    Note: Replace servername\instancename with your server name and instance name. Above Ostress command creates 2000 connections and execute “select * from tempdb..block” which will in turn cause excessive blocking and make SQLServer exhaust all the available worker threads.

    Is Alive check will fail once the threads are exhausted causing SQL Server to reach hung state. SQL Server resource DLL’s should detect that SQL Server is hung and restart/failover SQL Server.

     If you do not know what is SQL Server LooksAlive and IsAlive Check follow This blog

    by

    Error 601 : Could not continue scan with NOLOCK due to data movement.

    When there is corruption in database (or) When scanning the data with the NOLOCK locking hint (or) with the transaction isolation level set to READ UNCOMMITTED, it is possible for the page at the current position of the scan would have deleted or moved by page splits caused by Inserts/Updates/Deletes making SQL Server not able to scan further and cause Error 601 : Could not continue scan with NOLOCK due to data movement.

    Resolution
    Unless the database has been explicitly marked as ‘read only’ there is no way to guarantee that there are no data modification operations going on.
    The possible solutions are:
    1. Check for 601 errors from the application and retry the query automatically if the error occurs.
    2. Improve the indexes supporting the query or modify the query so that it has a smaller lock footprint and runs more quickly. If the query touches less data it will be less likely to encounter the problem.
    3. Avoid use of NOLOCK hint and if necessary have a retry logic 601 error . Improving the indexes as mentioned above might make it possible to get this data without doing large scans that would be likely to cause blocking.
    If you don’t use NOLOCK hint or to READ UNCOMMITTED Isolation level then check the database for corruption (Dbcc checkdb)

    by

    SQL Server monitoring

    Every SQL Server DBA would have faced situations similar to SQL Server not accepting connections for few minutes, SQL Server not responding for few minute or Applications not able to connect with SQL Server for few minutes. Before DBA’s gets alerted about the situation and starts troubleshooting the issue. Everything becomes normal. Challenge in this situations is it becomes very difficult to understand where the underlying problem was, It could be a network connectivity, Application server problem or It might be an issue with SQL Server itself. How do we collect diagnostic data to prove that SQL Server was stable at the time of issue (or) If the issue is with SQL Server then how to collect data we need for diagnosing the issue when there is issue?

    SQL Monitor to monitor SQL Server Services

    SQL Monitor monitors the SQL Server services and creates log if SQL Server service is down (or) If SQL Server is not accepting (or) SQL Server is not responding to Queries

    How it works?

    SQL Monitor checks the SQL Server in 3-Phases

    1. Check the status of SQL Server service through the windows service control manager

    2. If the service is running then check if SQL Server is accepting connections

    3. If SQL Server is accepting Connections then probe to perform a simple query and see if SQL Server is responding properly.

    4. If SQL Server is not accepting Connections then connect to SQL Server using DAC and take a stack dump.

    How to Configure

    1. Create a folder call SQLMonitor in C:\

    2. Create a Text file called serverlist.txt to fill all the SQLServer information in your account.

    Format:

    Servername [TAB] Servicename;

    Ex:

    Server1 MSSQLServer;

    Server2 MSSQL$Prod;

    3. Invoke command prompt and open attached SQLmonitor.EXE.

    Advantage:

    1. Multi-threaded . Each server and service is verified using its own thread so retrieving information from one server will not affect the pooling interval to other server.

    2. Single exe can be scaled to monitor more than 1000 servers and 1000 services.

    3. Uses few MB of memory and system resources.

    You can Download SQL Monitor from this link

    by

    Removing database mirroring

    When you try to recover and open mirrored database using dbcc dbrecover([DBNAME])

    You would get below error

    {

    Msg 7930, Level 20, State 1, Line 1

    Mirroring must be removed from the database for this DBCC command.

    }

    When you try to recover and open the database using “restore database [DBNAME] with recovery”

    {

    Msg 3104, Level 16, State 1, Line 1

    RESTORE cannot operate on database dbname because it is configured for database mirroring. Use ALTER DATABASE to remove mirroring if you intend to restore the database.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    }

    When you try to drop the database

    Msg 3743, Level 16, State 1, Line 1

    The database ‘dbname’ is enabled for database mirroring. Database mirroring must be removed before you drop the database.

    Resolution

    1 . ALTER DATABASE [databasename] SET partner OFF

    Note: Above command will remove the mirroring .

    2. restore database [databasename] with recovery

    by

    SQLServer LooksAlive and IsAlive Check

    The resource DLL for the SQL Server service uses two functions that are used by MS Cluster service to check for availability of the SQL Server resource.

    A simple check LooksAlive and more rigorous check called IsAlive

    LooksAlive check: Cluster service calls looksAlive function every 5 seconds and LookAlive function Queries the service status by using the Windows NT Service Control Manager. When the LooksAlive test fails ISAlive test is called immediately.

    ISalive Check: A more rigorous IsAlive function is called every 60 second and monitors the health of the SQL Server by opening up a connection to SQL Server and issuing “select @@servername” query over the connection. If the checks fail the online Thread reports this failure to the Cluster Service.

    For example Assume SQL Server resource is initially in the offline state. Cluster service calls the routine in SQL Server resource DLL to bring the resource to an Online state. First the resource state is set as OnlinePending and then initiates the process of starting up the SQL Server resource. It starts the SQL Server, opens a connection to SQL Server and issues the “select @@servername” query, if this succeeds the resource is put in the Online state. If for some reason SQL Server is not able to start or the connection/query fails and SQL Server is not able to come Online within PendingTimeout, the cluster service tries to restart (or failover) the resource. If after repeated attempt SQL Server cannot be brought online, it is put in the failed state. If the SQLServer Resource DLL encounters an unrecoverable failure like failing to open cluster registry key etc. then resource DLL will put the SQL Server resource in Failed state. Once the resource is put in Failed state Cluster service will not attempt to restart/failover the resource and user intervention is required to diagnose and correct the issue.

    By default, LooksAlive is fired every 5 seconds and IsAlive is fired every 60 seconds. The LooksAlive and IsAlive polling intervals can be changed in Cluster Administrator or failover cluster manager from the Advanced tab for the SQL Server resource or using the cluster.exe command prompt utility.

    by

    Builtin\Administrators cannot login in to SQL Server

     

    If you add a windows login to administrators group and even though administrators group is part of SQL Server login you may not be able to login in to SQL Server using the windows login.

    Similarly

    1. You remove a windows login from a windows group and assume windows group is part of SQL server login had deny on certain objects in database.

    2. Now you add the windows login explicitly to SQL Server logins and grant permissions on objects which had deny for windows group. Still the login will not be able to access the objects which have deny for the group and may raise 229  similar to one below

    {The SELECT permission was denied on the object ”, database mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error: 229) }

     

    This can happen when SQL is creating the logintoken from LSACache.  (LSA Cache is not refreshed after the Admin2 is added to Administrators group)

    1. Disable LSACache on the machine. Steps are included in http://support.microsoft.com/kb/946358.
    2. Restart the machine.
    3. Connect using the problematic login and try again.