Tagged: Mircosoft sql server 2012

How to bypass restart computer error message while installing SQL Patch

If you ever encounter restart computer error message while performing SQL Server patching and you don’t have opportunity\permission to restart your machine.

You can bypass this situation by deleting value from PendingFileRenameOperations key.

Here are the steps which you need to follow –>

  1. To Open Registry Editor. Click Start  ( win + R ) –> Run, input “regedit” and press Enter.
  2. Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager, and delete any value in “PendingFileRenameOperations” key.

restart

regedit

rerun.PNG

Advertisements

Replication – Snapshot is not getting generated

Replication issues and resolutions

Today I have encountered very strange issue –

Scenario – Snapshot is not getting generated for newly added articles

or

Error when setting up Transactional Replication via SQL scripts.

Msg  14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 533

Specify all articles when subscribing to a publication using concurrent snapshot processing.

Step performed to resolved the issue-

  1. Re snap with reinitialize all subscription option no luck.
  2. Removed the newly added articles and add back and ran the snapshot but NO Luck.
  3. Drop the subscriber and add back and generate the snapshot but no luck ( Most of time it resolve the issue )
  4. Script out publication and drop the publication and when I try to add

While adding Publication i am getting error message –

Error Message –

Msg 14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 533

Specify all articles when subscribing to a publication using concurrent snapshot processing.

Resolution —

There are 2 unofficial workarounds:

(a) You can circumvent the check by specifying @reserve = ‘internal’ when you add the subscription for the new article and the snapshot agent should generate snapshot for the new article

(b) You could change the immediate_sync property in syspublications to 0 (see sp_changepublication).

Other more official workarounds —

Including changing the sync_method from ‘concurrent’ to either ‘database snapshot’ (enterprise edition only) and ‘native’ (which locks table during snapshot generation). Change the sync_method will force a reinitialization of all your subscriptions at this point. Alternatively you could create another publication and use this instead.

Before —

use [Publisher db]

exec sp_addpublication @publication = N’Publicatoin name’,

@description = N’Transactional publication of database ”dirtrbuter name” from Publisher ”publisher name”.’,

@sync_method = N’concurrent’,

@retention = 0,

@allow_push = N’true’,

 

After —

use [Publisher db]

exec sp_addpublication @publication = N’Publicatoin name’,

@description = N’Transactional publication of database ”dirtrbuter name” from Publisher ”publisher name”.’,

@sync_method = N’native’,

@retention = 0,

@allow_push = N’true’,

 

  1. I have change the @sync_method = N’concurrent’ to @sync_method = N’native’ and ran the script to create publication.
  1. Ran the snapshot but snapshot not getting generated for newly added articles

7. Remove all article (from console) and added again … and ran the snapshot resolve my issue

Microsoft Best Practice Analyzer

Microsoft Best Practice Analyzer –

Best Practices Analyzer (BPA) is a server management tool that is available in Windows Server® 2008 R2. BPA can help administrators reduce best practice violations by scanning one or more roles that are installed on Windows Server 2008 R2, and reporting best practice violations to the administrator. Administrators can filter or exclude results from BPA reports that they do not have to see. Administrators can also perform BPA tasks by using either the Server Manager GUI, or Windows PowerShell cmdlets.

You will find more details in –
https://technet.microsoft.com/en-us/library/dd759260(v=ws.11).aspx

Installation of BPA – SQL 2012

Prerequisite
• PowerShell V2.0
• Microsoft Baseline Configuration Analyzer V2.0
• User should have admin access on server and SQL Server
o Note – You need to add your individual id or else you will get login error after running BCA.

Steps by Step Installation –
Step 1 – Download and Install the Microsoft Baseline Configuration Analyzer v2 (BCA) (follow default download instructions) – This will install to C:\Program Files\Microsoft Baseline Configuration Analyzer
Download link – https://www.microsoft.com/en-us/download/details.aspx?id=16475
Step 2- Install the Microsoft SQL Server 2012 Best Practices Analyzer (SQL BPA) (follow default download instructions) – This will install to C:\Windows\System32\BestPractices\v1.0\Models\SQL2012BPA\
https://www.microsoft.com/en-us/download/details.aspx?id=29302
Note – You will not able to see any icon for (SQL BPA) as it’s only install executables.

p1

Working –
Step 1 – Open Microsoft Baseline Configuration

p2

Note – If you wanted to connect remote computer, click on Connect to Another Computer.

p3

Step 2 – Select “SQL Server 2012 BPA” from drop down.
Step 3 – Go to Enter Parameters and select parameters you wants to select.

p4

Note – If it’s remote server provide server name in Alternate_Server_to_scan (Only server name not instance name), provide instance name in SQL_Server_Instanace_name fields.
Step 4 – Click on “Start Scan”
Step 5 – Analyze your result.

p6

Issues –
I was configuring BPA 2012 in windows 2012, after installing both BCA and SQL BPA, I was not able to see “SQL Server 2012 BPA” in drop down and to resolve the issue I have performed below steps –

p7

1. Navigate to the directory C:\Windows\System32\BestPractices\v1.0\Models and right-click the SQL2012BPA folder and make a copy.
2. Navigate to C:\ProgramData\Microsoft\Microsoft Baseline Configuration Analyzer 2\Models\ and paste the SQL2012BPA folder in this new location.
(Note – ProgramData is hidden folder which you need to see after checking “Hidden Items” in view of your file explorer)
3. Start the BCA with Run As Administrator and you will now see the SQL Server 2012 BPA listed in the “Select a product” dropdown.

Create Blocking Alert – SQL 2005 , SQL 2008 R2 and SQL 2012

We often need to create blocking alert in our Production server to capture existing blocking on server and keep record for future analysis.

Here are steps which help us to create blocking alerts-

Either you can receive blocking details through e-mail or you can log event id in application log and configure your monitoring tool to capture from application log.

STEP 1 –  Create SP to capture blocking details and generate e-mail\ event id in application log.

Note –  and datediff(minute, last_batch, getdate()) > 15, through which you can define blocking duration to generate e-mail or event, I am using 15 min

USE [db]

go

/****** Object: StoredProcedure [dbo].[usp_checkForLongRunningBlockers_log] Script Date: 5/18/2015 9:22:50 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[usp_checkForLongRunningBlockers_log]

as

declare @@leadblocker_spid smallint

declare @@dbname nvarchar(128)

declare @@login_time datetime

declare @@last_batch datetime

declare @@open_tran smallint

declare @@status nchar(30)

declare @@hostname nchar(128)

declare @@program_name nchar(128)

declare @@cmd nchar(16)

declare @@loginame nchar(128)

declare @@net_address nchar(12)

declare @@net_library nchar(12)

declare @@string varchar(2000)

select

@@leadblocker_spid = sp.spid,

@@dbname = db_name(sp.dbid),

@@login_time = login_time,

@@last_batch = last_batch,

@@open_tran = open_tran,

@@status = sp.status,

@@hostname = hostname,

@@program_name = program_name,

@@cmd = cmd,

@@loginame = loginame,

@@net_address = net_address,

@@net_library = net_library

FROM master.dbo.sysprocesses sp

LEFT JOIN sys.dm_exec_requests er

ON er.session_id = sp.spid

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt

WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

AND blocked = 0

and datediff(minute, last_batch, getdate()) > 15

set @@string =

‘Blocking Details -Date: ‘ + convert(varchar(20), getdate(), 9) + ‘

@@leadblocker_spid = ‘ + convert(varchar(5),@@leadblocker_spid) + ‘

@@dbname = ‘ + @@dbname + ‘

@@login_time = ‘ + convert(varchar(20), @@login_time, 9) + ‘

@@last_batch = ‘ + convert(varchar(20), @@last_batch, 9) + ‘

@@open_tran = ‘ + convert(varchar(5), @@open_tran) + ‘

@@status = ‘ + @@status + ‘

@@hostname = ‘ + @@hostname + ‘

@@program_name = ‘ + @@program_name + ‘

@@cmd = ‘ + @@cmd + ‘

@@loginame = ‘ + @@loginame + ‘

@@net_address = ‘ + @@net_address + ‘

@@net_library = ‘ + @@net_library + ”

if @@leadblocker_spid is null

return

else

–exec dbo.sp_SQLSMTPMail_html

— @vcTo = ‘nshukla@xyz.com’,

— @vcBody = @@string,

— @vcSubject = ‘Long Running Blocking’,

— @vcAttachments = null,

— @vcQuery = null,

— @vcFrom = ‘noreply@xyz.com’,

— @vcCC = ”,

— @vcBCC = ”,

— @vcSMTPServer = ‘nav-mail2.xyz.com’, — put local network smtp server name here

— @cSendUsing = ‘2’,

— @vcPort = ’25’,

— @cAuthenticate = ‘0’,

— @vcDSNOptions = ‘0’,

— @vcTimeout = ’30’,

— @vcSenderName = null,

— @vcServerName = null

EXEC xp_logevent 60000, @@string, error

GO

STEP 2 – Create table to capture blocking history –

USE [db]

GO

/****** Object:  Table [dbo].[BLOCKING_HISTORY]    Script Date: 5/18/2015 9:26:40 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[BLOCKING_HISTORY](

[spid] [smallint] NULL,

[kpid] [smallint] NULL,

[blocked] [smallint] NULL,

[waittype] [varbinary](5000) NULL,

[waittime] [bigint] NULL,

[lastwaittype] [nvarchar](500) NULL,

[waitresource] [nvarchar](500) NULL,

[dbid] [smallint] NULL,

[uid] [smallint] NULL,

[cpu] [int] NULL,

[physical_io] [bigint] NULL,

[memusage] [int] NULL,

[login_time] [datetime] NULL,

[last_batch] [datetime] NULL,

[ecid] [smallint] NULL,

[open_tran] [smallint] NULL,

[status] [nvarchar](500) NULL,

[hostname] [nvarchar](500) NULL,

[program_name] [nvarchar](500) NULL,

[hostprocess] [nvarchar](500) NULL,

[cmd] [nvarchar](500) NULL,

[nt_domain] [nvarchar](500) NULL,

[nt_username] [nvarchar](500) NULL,

[net_address] [nvarchar](500) NULL,

[net_library] [nvarchar](500) NULL,

[loginame] [nvarchar](500) NULL,

[context_info] [varbinary](5000) NULL,

[sql_handle] [varbinary](5000) NULL,

[stmt_start] [int] NULL,

[stmt_end] [int] NULL,

[request_id] [int] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

STEP 3 – Create SP to INSERT  blocking details in Blocking History table.

USE [db]

GO

/****** Object:  StoredProcedure [dbo].[usp_blockingEntry]    Script Date: 5/18/2015 9:25:52 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE Procedure [dbo].[usp_blockingEntry]

as

begin

SET NOCOUNT ON

declare @blockingcount int  =   ( select count(1) from sys.sysprocesses where blocked 0 )

if @blockingcount > 0

INSERT INTO [dbo].[BLOCKING_HISTORY]

(      spid

,      kpid

,      blocked

,      waittype

,      waittime

,      lastwaittype

,      waitresource

,      dbid

,      uid

,      cpu

,      physical_io

,      memusage

,      login_time

,      last_batch

,      ecid

,      open_tran

,      status

–,    sid

,      hostname

,      program_name

,      hostprocess

,      cmd

,      nt_domain

,      nt_username

,      net_address

,      net_library

,      loginame

,      context_info

,      sql_handle

,      stmt_start

,      stmt_end

,      request_id

)

select

spid

,      kpid

,      blocked

,      waittype

,      waittime

,      lastwaittype

,      waitresource

,      dbid

,      uid

,      cpu

,      physical_io

,      memusage

,      login_time

,      last_batch

,      ecid

,      open_tran

,      status

–,    sid

,      hostname

,      program_name

,      hostprocess

,      cmd

,      nt_domain

,      nt_username

,      net_address

,      net_library

,      loginame

,      context_info

,      sql_handle

,      stmt_start

,      stmt_end

,      request_id

from sys.sysprocesses  where blocked 0

end

GO

STEP – 4 – Create SP to get lead blocker –

USE [db]
GO

/****** Object: StoredProcedure [dbo].[usp_leadblocker] Script Date: 5/18/2015 9:44:39 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[usp_leadblocker]
as

select *
from
(
select
‘Blocking’ as State,
hostname as host,
db_name(dbid) as dbname,
SPID,
blocked,
lastwaittype,
dbid,
login_time,
last_batch,
open_tran,
status,
hostname,
program_name,
cmd,
nt_domain,
nt_username

from sys.sysprocesses (nolock)
where
spid in (
select distinct blocked
from sys.sysprocesses (nolock)
where
blocked 0
and blocked spid
)
and blocked = 0
and blocked spid

union all

select
‘Blocked’ as State,
hostname as host,
db_name(dbid) as dbname,
SPID,
blocked,
lastwaittype,
dbid,
login_time,
last_batch,
open_tran,
status,
hostname,
program_name,
cmd,
nt_domain,
nt_username
from sys.sysprocesses (nolock)
where
blocked 0
and blocked spid
) dt
order by 1 desc

GO

STEP 5 – T- SQL to check SPID text.

DECLARE @sqltext VARBINARY(128)

SELECT @sqltext = sql_handle

FROM sys.sysprocesses

WHERE spid = 64

SELECT TEXT

FROM sys.dm_exec_sql_text(@sqltext)

GO

***************************************

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 64
SELECT TEXT
FROM ::fn_get_sql(@sqltext)
GO

*****************************

DBCC INPUTBUFFER(64)
GO

*************************

SP_WHO2 64

go

SQL Server 2012 Upgrade from Standard Edition to Enterprise edition

SQL Server 2012 Upgrade from Standard Edition to Enterprise edition

upgrade1

 

Step 1- Identify SQL executable file  , Right click and run as admin

upgrade2

 

Step 2 – click on Maintenance tab on left hand side.

upgrade3

Step 3- Click on Edition Upgrade

upgrade4

Step 4 – Click Okay

upgrade5

Step 5- Click Next

upgrade6

Step 6 – Click next on Production key

upgrade8

Step 7 – Click check on “I accept the License terms” and then click Next

upgrade7

Step 8 – Select Instance you want to upgrade form “Specify the Instance of SQL Server” and click next

upgrade9

 

Step 9 – Click next

upgrade10

Step 10 – Review upgrade and click Upgrade

upgrade11

 

Step 11 – Click Close

upgrade12

Step 11- Open SSMS and check version.

upgrade13

SSRS – How to convert in Day/Hour/Minute format in SSRS report

Scenario – In my SSRS report i have calculate Age field of Tickets like in 1 Day 2 Hours 20 min format and we are getting values in minute .

Solution –

I have created filed called Age(Minutes) in my SP or query

IN SQL –

datediff(minute,date_created, getdate()) as ‘Age (Minute)’

IN SSRS write expression

=  ( Fields!Age__Minute_.Value ) \ 1400 & ” days ” &  ((( Fields!Age__Minute_.Value ) MOD 1400) \ 60 ) & ” Hours ” & ((( Fields!Age__Minute_.Value ) MOD 1400) MOD 60 ) & ” Minutes “

SQL Server 2012 Always On Step by Step Configuration

SQL Server 2012 always on Step by step configuration –

Prerequisite to configure always on –

1. Domain accounts for SQL Server services should have local admin   access
2. Two IP addess
One is for Cluster
Other one is for always ON listener
Note – These IP address should be in customer facing sub-net
3. UNC share on any non-cluster server and non-domain controller.
Cluster computer account and each node computer account should have  R\W access to this share.

if you are not having domain controller access ask your Window Admin team

– Pre-staged cluster computer account in Active Directory. Account can be pre-staged according to the following article: http://technet.microsoft.com/en-us/library/cc731002.aspx#BKMK_steps_precreating

– Pre-staged AlwaysOn listener computer account in Active Directory. Account can be pre-staged according to the following article: http://technet.microsoft.com/en-us/library/cc731002(WS.10).aspx#BKMK_steps_precreating2

Windows cluster installation: 

1) Install Failover Cluster feature using Server Manager on both/all nodes.

2) Open Failover Cluster Manager and create a new cluster:

Cluster1.PNG
3) In “Select Servers” step add all your cluster nodes. Click Next.

4) Pass the validation, all test. Warnings regarding storage are OK – you’re setting up AlwaysOn, so you don’t have shared storage.

5) Specify cluster name and IP address on the next screen. If cluster account was pre-staged make sure to specify correct name (same as the pre-staged computer account name). Make sure to use IP, that was provided by customer and is 100% not in use.

6) Finish cluster installation.

7) Configure quorum or the newly created cluster:

How to configure Quorun –

a. Right Click on Cluster Name –> More action –>Configure Cluster Quorum setting

b) On the “Select Quorum Configuration Option” window select “Select the quorum witness”. Click “Next”.

c) On the “Select Quorum Witnes” window select “Configure a file share witness”. Click “Next”.

d) Specify a file share path, provided by the customer. Click “Next”

e) Click “Next”, then “Finish”

Installing SQL Server:

1) Install SQL Server as a stand-alone instance.

2) Following components are required and recommended for basing SQL Server installation:

SQLInstallation1.PNG

3) Complete the installation.

4) Make sure to add SQL Server service account to sysadmins at each instance. This is required.

Enabling AlwaysOn on instance level:

1) Open SQL Server configuration manager.

2) Select SQL Server Services in the left-side menu.

3) Right click SQL Server service and select “Properties”:

SQLConfiguration1.PNG
4) Go to “AlwaysOn High Availablity Tab” and check “Enable AlwaysOn Availability Groups”:

5) Click “OK”. Make sure to restart SQL Server service.

6) Repeat 1-5 on each node.

Configuring AlwaysOn high availability group (single interface option)

Please skip to the next part if you want to use separate NICs for mirroring traffic and user connections.

1) Open SQL Server Management studio and connect to the first node.

2) Open AlwaysOn High Availablity -> Availablity groups and select “New Availability Group” (make sure to use 2012 Management Studio):

3) In the new window enter availability group name (it may be any name you wish), click “Add” and specify secondary replica name:

alwayson_3

4) You can use default settings or customize them.

5) Click “OK”.

6) Go to SQL Server Management Studio, expand AlwaysOn High Availablity, expand your Availability Group, expand “Availability Replicas”, right click your second server and select “Join Availablity Group” (if it exists).
7) In new Window connect to it and click “Next” and “Finish”.

Configuring AlwaysOn availability group listener

1) Go to SQL Server Management Studio, expand AlwaysOn High Availablity, expand your Availability Group, created earlier, right click “Availabilty Group Listeners” and select “Add Listener”:

2) Specify Listener DNS Name (if AlwaysOn listener computer account was pre-staged, this should match the name of the computer), enter 1433 into “Port”, for “Network mode” select Static IP and click “Add” to add an IP address:
alwayson_1

3) In the “Add windows” select the correct subnet and enter an IP address provided by customer:

always_2

4) Click “OK” and “OK”.

Adding/Removing databases to/from AlwaysOn availability group.

1) To add a database to availability group you select “Add database” in “Availability databases” and follow the Wizard.

– You can use fully automated addition. In this case you will need network share, accessible from both servers and with RW rights for SQL Server service account.

SQL Server will perform backup and restore of the database automatically.

– You can use “Join only” addition. In this case you will need to perform full backup and t-log backup and restore (as when configuring Mirroring, WITH NORECOVERY) and then use the Wizard.

2)  To remove a database from availability group just right click the database and select “Remove database from availablity group…”:

Please note that before deleting database, that is part of AlwaysOn HA, you must delete it from Availability group.