Archive

Archive for February, 2015

Could not drop the primary key constraint ‘constraint name’ because the table has an XML or spatial index.

February 28, 2015 Leave a comment

Error Message

Msg 3734, Level 16, State 1, Line 187
Could not drop the primary key constraint ‘PK_Person_BusinessEntityID’ because the table has an XML or spatial index.
Msg 3727, Level 16, State 0, Line 187
Could not drop constraint. See previous errors.

Description:
This error message appears when you try to remove a PRIMARY KEY constraint from a table which has an XML index.

 

Troubleshooting

—Error Statement

ALTER TABLE [Person].[Person] DROP CONSTRAINT [PK_Person_BusinessEntityID]

–Troubleshooting
go
select kc.name ConstraintName,object_name(kc.parent_object_id) tablename,kc.type,si.name indexname
from sys.key_constraints kc inner join sys.indexes si on kc.parent_object_id=si.object_id where
kc.name=’PK_Person_BusinessEntityID’ and si.type_desc=’XML’

–//Drop the indexes first then the constraint

Msg 3734, Level 16, State 1, Line 5
Could not drop the primary key constraint ‘PK_Person_BusinessEntityID’ because the table has an XML or spatial index.
Msg 3727, Level 16, State 0, Line 5
Could not drop constraint. See previous errors.
ConstraintName                 tablename    type    indexname
PK_Person_BusinessEntityID    Person    PK    PXML_Person_AddContact
PK_Person_BusinessEntityID    Person    PK    PXML_Person_Demographics
PK_Person_BusinessEntityID    Person    PK    XMLPATH_Person_Demographics
PK_Person_BusinessEntityID    Person    PK    XMLPROPERTY_Person_Demographics
PK_Person_BusinessEntityID    Person    PK    XMLVALUE_Person_Demographics

Resolution

Drop the XML Index First and then drop the Primary Key Constraint

Categories: Debug

Cluster Core Resource Offline/failed after multiple failovers

February 22, 2015 Leave a comment

Cluster group was failed after multiple server reboots/failovers (moving of cluster resource group multiple times)

 

Get what all resources are failed / offline

PS:> Get-ClusterResource |Where-Object  {$.state –eq “Failed”}

or

PS:> Get-ClusterResource |Where-Object  {$.state –eq “offline”}

 

image

 

Check the cluster core resource owner

PS:> Get-ClusterGroup

image

Move the Cluster Core Resource to the logged in node (If the resource is in failed state , moving the resource will bring the resource online)

Ps:> Move-ClusterGroup “Cluster Group” –Node  SQL02

image

Bring the offline “Cluster Group” Online

PS:>Start-ClusterGroup “Cluster Group”

image

Categories: Windows Cluster

SQL Server AlwaysOn Troubleshooting Part -1

February 22, 2015 Leave a comment

 

Get Availability group information

 
T-SQL :

select name,group_id,health_check_timeout from master.sys.availability_groups

image

 

Get Availability group Replica information

T-SQL :

select replica_server_name,create_date,
       modify_date,endpoint_url,read_only_routing_url,avalibility_mode,
       failover_mode,session_timeout,backup_priority
from master.sys.avalibility_replicas

image

 

Get Availability group databases

T-SQL :

select database_name,is_failover_ready,is_pending_secondary_suspend,is_database_joined
from master.sys.dm_hadr_database_replica_cluster_states

select * from sys.dm_hadr_database_replica_states

 

image

 

Get Availability group listeners

T-SQL:

select * from sys.availability_group_listeners

image

 

Join all DMV’s for AlwaysOn configuration

T-SQL :

select ag.name,replicas.replica_server_name,replicas.availability_mode_desc,
         replicas.failover_mode_desc,replicas.session_timeout,
         agd.database_name,agd.is_database_joined,drs.is_local,
         drs.is_primary_replica,drs.synchronization_state_desc,
         drs.synchronization_health_desc,drs.database_state_desc,agl.dns_name,
         agl.port,agl.ip_configuration_string_from_cluster
            from       master.sys.availability_replicas replicas 
            inner join master.sys.availability_groups ag on replicas.group_id=ag.group_id
            inner join master.sys.dm_hadr_database_replica_cluster_states agd on agd.replica_id=replicas.replica_id 
            inner join master.sys.dm_hadr_database_replica_states drs on drs.replica_id=replicas.replica_id
            inner join master.sys.availability_group_listeners agl on agl.group_id=ag.group_id

 

image

Categories: AlwaysOn 2012

Encrypt SQL Server database backup using Microsoft Azure®Tool

February 18, 2015 Leave a comment

 

Download Microsoft® SQL Server® Backup to Microsoft Azure®Tool and install it on the server where you want to encrypt database back.

 

Download link : http://www.microsoft.com/en-sg/download/details.aspx?id=40740

 

After the installation is successfully navigate to services.msc (Microsoft SQL Server backup to Windows Azure Tool) Service.The service installs with a default login , change it to the correct user and start the service.

 

image

 

Browse the tool from Start (Programs)

image

 

Lets Configure the Tool

 

image

 

Click Add

 

Specify the backup location or all paths.Lets specify a path for demo

image

 

Click Next and choose option local storage

image

 

Click Next  (Currently AES-256 Encryption is available with/without Compression) and click Finish.

 

image

 

To test , lets run the backup from SQL Server Management Studio, You don’t need to do any configuration changes in SQL Server Configuration.

If the backup is in single file , single encrypted file will be generated, if backup is done in multiple stripes , encryption will happen for each file as backup progresses.

 

image

 

image

Import SQL Server Policies

February 15, 2015 Leave a comment

A PowerShell script to easily and quickly import policies into SLQ Server.

 

param ([string] $Sqlserver) Import-Module sqlps -DisableNameChecking $Connectionstring = "server='"+$Sqlserver+"';Trusted_Connection=true" $Conn = New-Object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection($Connectionstring) $PolicyStore = New-Object Microsoft.SqlServer.Management.DMF.PolicyStore($Conn) $PolicyFiles=Get-ChildItem -Path 'C:\Program Files\Microsoft SQL Server\110\_MarkitBuild\Policies\*.*' -Include *.xml foreach ($File in $PolicyFiles.FullName) { $PolicyXmlPath =$File #"C:\Delete_Data_R2\MultipleFiles\Markit-Check-SQLServer2008R2-Build.XML" $xml = [System.Xml.XmlReader]::Create($PolicyXmlPath) $PolicyStore.ImportPolicy($xml,` [Microsoft.SqlServer.Management.Dmf.ImportPolicyEnabledState]::Unchanged, $true, $true)` |Select-Object Name,Enabled }

image

image

 

image

 

Invoking PowerShell script from T-SQL

 

declare @servername varchar(500) declare @vsql nvarchar(4000) declare @versionfolder varchar(4) set @servername=@@servername select @versionfolder=max(compatibility_level) from sys.databases set @vsql='"powershell.exe C:\PowershellScript\Apply_Policy.ps1 -Sqlserver ' +@servername +' -version '+@versionfolder+'"' exec master..xp_cmdshell @vsql

Categories: PowerShell

Security Part : (How to transfer logins and passwords between instances of SQL Server)

February 12, 2015 Leave a comment

This article describes how to transfer the logins and the passwords between instances of Microsoft SQL Server 2005,of Microsoft SQL Server 2008, and of Microsoft SQL Server 2012 on different servers.

Microsoft SQL Server provided sp_help_revlogin stored procedure which will ease this

use master go exec sp_help_revlogin

image

Categories: SQL Server Security

Difference between KILL and KILL WITH STATUSONLY – Estimate Rollback completion time

February 11, 2015 Leave a comment
Categories: T-SQL-Scripts