If you're sure the user in question has View Server State
(and it looks like in your screenshot he does).
Then there are a number of reasons previously put into an msdn blog. Ranging from:
- Performance Objects and counters set-up during the SQL Server installation failed.
- A mixture of 64 and 32 bit platforms.
- Registry permissions have been skewed
To resolve this we can use the same steps outlined in the guidelines for reinstalling the performance counters in a different stack-exchange post:
Using an elevated administrator command prompt perform the following steps.
- Change the path to the
BINN
directory of the SQL Server instance you desire to correct.
(Ex: C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn
)
- Execute
unlodctr <<REGISTERED SERVER NAME>>
For example: unlodctr MSSQL$SQL2008
or SQLAgent$SQL2008
...
- Execute
lodctr /T:<<perf-sql* matching the counters you desire to load>>
For example: perf-MSSQL$SQL2008sqlctr.ini
or perf-SQLAgent$SQL2008sqlagtctr.ini
for SQLAgent. The /T
is important to load the SQL Server performance counter provider as a trusted provider.
- Cycle the remote registry service:
net stop "Remote Registry"
then net start "Remote Registry"
- Force a WMI synchronization using
winmgmt /resyncperfctr "<<PID>>"
where PID is the process id of the WinPriv.exe
(you can get this from Task Manager)
The following may also be required:
I created a test DB and table, I adjusted the schema to make it a bit easier and also commented out the set in the proc.
USE [Test]
GO
/****** Object: Table [dbo].[news] Script Date: 2/4/2017 12:21:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[news](
[id_news] [int] NULL,
[news_owner_id] [int] NULL,
[news_owner_type] [bit] NULL,
[news_cat_id] [int] NULL,
[news_producer] [nvarchar](1000) NULL,
[news_title] [nvarchar](1000) NULL,
[news_sutitr] [nvarchar](1000) NULL,
[news_text] [nvarchar](1000) NULL,
[news_date] [nvarchar](128) NULL,
[news_time] [nvarchar](16) NULL,
[news_link] [nvarchar](512) NULL,
[img_uri_type] [tinyint] NULL,
[img_uri] [xml] NULL,
[newsContentID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I'm going to answer 2 first. I ran the proc once, it inserted one time. I looked through the execution plan and it shows a straight forward insert. You can see it here: Standard run. I then set your error handling to 0, just to see the plan with that being triggered. Also straight forward. Error handling triggered. I would run SQL Profiler when you see it insert two records or set up sp_whoisactive to catch the plan when it does multiple inserts. My guess is that it's being executed twice.
For question 1, it looks fine to me. You could put in more exact error handling, but overall I would not say it's bad. I really like this template by spaghetti dba.
Best Answer
You need Enterprise edition for fully functional Always ON AG and a minimum Standard edition to use the basic Always On AG. Express edition of SQL Server does not supports Availability Groups