Enable AlwaysOn Availability Groups on a Windows Server Core 

Using SQL Server Configuration Manager RemotelyThese steps are meant to be performed on a PC running the client edition of Windows, or Windows Server that has the Server Graphical Shell installed.Open Computer Management. To open Computer Management, click Start, type compmgmt.msc, and then click OK.In the console tree, right-click Computer Management, and then click Connect to another computer….In the Select Computer dialog box, type the name of the Server Core machine that you want to manage, or click Browse to find it, and then click OK.In the console tree, under Computer Management of the Server Core machine, click Services and Applications.Double click SQL Server Configuration Manager.In SQL Server Configuration Manager, click SQL Server Services, right-click SQL Server (), where is the name of a local server instance for which you want to enable Always On Availability Groups, and click Properties.Select the AlwaysOn High Availability tab.Verify that Windows failover cluster name field contains the name of the local failover cluster node. 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 WSFC cluster has been shut down, or this edition of SQL Server 2017 that does not support AlwaysOn Availability Groups.Select the Enable AlwaysOn Availability Groups check box, and click OK.SQL Server Configuration Manager saves your change. Then, you must manually restart the SQL Server service. This enables you to choose a restart time that is best for your business requirements. When the SQL Server service restarts, AlwaysOn will be enabled, and the IsHadrEnabled server property will be set to 1.

Source: Configure SQL Server on a Server Core Installation | Microsoft Docs

Advertisements

Configure remote access of SQL Server on Windows Server Core

To enable remote connections, use SQLCMD.exe locally and execute the following statements against the Server Core instance:


EXEC sys.sp_configure N'remote access', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

Source: Install SQL Server 2016 on Server Core | Microsoft Docs

Hello, World Again!

 

#include <stdio.h>
main( )
{
    printf("Hello, World Again!\n");
}

 

I was absent for about two years. My son was born two years ago, and I had to spend more time with my family.
Now, he is a two year old and I can spend more time on my own room every night without any distractions! ūüėČ
So, I will resume all my activities that I was stopped for two years. I resume to get more socialized!(Twitter, Facebook, LinkedIn, …).
I also started to setup a new website: saeidhasani.com which still is down, but It will become online in next days.
However, I will write my blog posts here, so, I do not shut down this blog. I have many ideas for writing new articles which I collected them in this two years. Now, they are just titles, but I will resume to publish them soon.
Thanks for reading!
Saeid Hasani

Upgrade SQL Server 2016 to Service Pack 1 faced with Exit code (Decimal): -2068380094

 

 

1

I wanted to upgrade the SQL Server 2016 installed on my laptop to service pack 1 last night. But the setup failed with this error:

  Final result:                  The patch installer has failed to update the shared features. To determine the reason for failure, review the log files.

Exit code (Decimal):           -2068380094

What I noticed in the process was that the setup process tried to stop the MSSQLSERVER service, but after about one minute of trying without any success, the setup process finished with the above error code.

2

So, I start analyzing the detail log file. Then, I found the following error code:

(01) 2017-01-20 19:56:47 Slp: Error: Failed to run patch request for instance: MSSQLSERVER (exit code: -2068709375)

There was nothing more than that to identify what was happened in the upgrade process. So, I start checking the sql server configuration manager and faced with two interesting things:

  1. I could not manually stop the MSSQLSERVER service
  2. Polybase Services stuck in Change Pending state

3

So, I had an idea for more investigations! Finally, I found the Greg Low’s following post

http://sqlblog.com/blogs/greg_low/archive/2016/07/06/sql-server-2016-polybase-services-stuck-in-change-pending-state.aspx

He was told that ‚Äúenabling TCP and restarting made all good‚ÄĚ

I check it and he was completely right!

5

I enabled the TCP and restarted and tried setup again with a success!

8

 

Event ID 129 Reset to device, \Device\RaidPort0, was issued on Database Server

Two months ago, one of my clients called me that his DB Server is not responsive.

Their Server is a HP DL580 with this specs:

  1. CPU: 4 x 12 cores
  2. Memory: 256 GB
  3. Storage:  2 x Raid 10 (8 x 600 GB SAS 15K)
  4. OS: Windows Server 2012 r2
  5. DBMS: SQL Server 2014 sp2

The situation was strange:

  1. We had ping
  2. IIS , SSMS and ODBC Data Sources could not connect to the SQL Server
  3. RDP was not responsive

We decided to call the Data Center and ask them to restart the server by powering it off. After restarting server, I start investigating what, why and when the issue occurred by using Event Viewer ans SSMS log file viewer. I found that we had an event id 129 exactly before stopping the SQL Server services.

So, I start identifying that what it is and what we can do to resolve it.

I found this MSDN blog post:

https://blogs.technet.microsoft.com/kevinholman/2013/06/21/event-id-129-storachi-reset-to-device-deviceraidport0-was-issued/

I applied its solution, but we had the same problem after a one week.

Then, I found this one:

https://blogs.msdn.microsoft.com/ntdebugging/2011/05/06/understanding-storage-timeouts-and-event-129-errors/

This one helped me so much to learn what usually happen in Windows I/O layered structure. So, I found that the problem was from the raid controller. We asked Hardware Unit to replace it with new one. We didn’t have any issue after changing it.

based on this article: If you are seeing Event ID 129 errors in your event logs, then you should start investigating the storage and fiber network.

TNWikiSummit15 Presentation: Error Handling in SQL Server 2012 and 2014

 

The follow text is copied from this blog:
Official Blog of TechNet Wiki

 

Using Error Handling within the Transact SQL language needs a deep knowledge. It depends on the SQL Server version, there are various implementations for handling the errors in T-SQL. But if you use SQL Server 2012 or later versions, you can use the simplest and fully structured error handling.

Saeid Hasani showed how to implement such simple error handling in T-SQL. He showed this topic from a problem solving approach and particularly in SQL Server 2012  and the later versions. In this session, he covered some questions to providing a step by step tutorial for designing a structured error handling mechanism in SQL Server.

This session is available for download using the following links:

Video:

https://gallery.technet.microsoft.com/TN-Summit15-Structured-dfdcb9a5

PowerPoint:

https://gallery.technet.microsoft.com/TN-Summit15-Structured-118896bb

Original TN Wiki Article:

http://social.technet.microsoft.com/wiki/contents/articles/20002.structured-error-handling-mechanism-in-sql-server-2012.aspx

Judges’ comments for the article are from TechNet GURU Awards РSeptember 2013 :

Richard Mueller:

“Useful tutorial with lots of examples and very good links. Excellent comparison of THROW and RAISERROR.”

Jinchun Chen:

“Nice article. Thank you for the sharing.”

Ed Price:

“Incredibly well formatted with images, code, and explanations. And great interactions in the comments, making the improvements suggested by Naomi. ”

DB:

“Useful, clear and accurate guidance on error handling”

Samuel Lester:

“Great coverage of different techniques, challenges, pros/cons. Well done, very informative, and easy to read.”

 

TechNet Wiki International Summit 2015

You can register by filling out the TNWiki Summit15 form  and choosing your favorite Presentations.

Registration for TNWiki Summit15 are open  . REGISTER NOW   !!!

Here’s¬†the schedule:

¬†DAY 1 –¬†March, 17th
Time / GMT  DEV track WIKI track  INFRA track
GMT “-8” ¬†: ¬†01h00PM
GMT “-2” ¬†: ¬†07h00PM
GMT “0” ¬† : ¬†09h00PM
GMT “+2” : ¬†11h00PM
“TechNet Wiki
Social Synergy”
by Ed Price
GMT “-8” ¬†: ¬†01h45PM
GMT “-2” ¬†: ¬†07h45PM
GMT “0” ¬† : ¬†09h45PM
GMT “+2” : ¬†11h45PM
“SharePoint:
Adding client-side
controls to an AngularJS
app in Office 365”
by Matthew Yarlett
“Wiki Life (Turkish):
Best Practices”
by Hasan Dimdik,
Erdem SELÇUK,
Recep YUKSEL
and Ugur Demir
“SQL Server:
SQL Server Memory”
by Shashank Singh
aka Shanky
GMT “-8” ¬†: ¬†02h30PM
GMT “-2” ¬†: ¬†08h30PM
GMT “0” ¬† : ¬†10h30PM
GMT “+2” : ¬†12h30AM
“Wiki Life (Portuguese):
Best Practices”
by Alan Carlos,
Durval Ramos
and Luciano Lima
“Exchange Server
Kurulum SenaryolariKSEL”
by Recep YUKSEL
(only Turkish Language)
GMT “-8” ¬†: ¬†03h15PM
GMT “-2” ¬†: ¬†09h15PM
GMT “0” ¬† : ¬†11h15PM
GMT “+2” : ¬†01h15AM
“Small Basic”
by Ed Price
“Writing a Good Wiki Article”
by Matthew Yarlett
“Seguran√ßa em Profundidade
em Ambientes Microsoft”
by Luciano Lima
(only Portuguese Language)
¬†DAY 2 –¬†March, 18th
Time / GMT  DEV track WIKI track  INFRA track
GMT “-8” ¬†: ¬†09h00AM
GMT “-2” ¬†: ¬†03h00PM
GMT “0” ¬† : ¬†05h00PM
GMT “+2” : ¬†07h00PM
“Creating and Querying
Microsoft Azure
DocumentDB”
by Chervine Bhiwoo
“Visio with Office 365
SharePoint Online”
by Daniel Christian
aka Dan Christian
GMT “-8” ¬†: ¬†09h45AM
GMT “-2” ¬†: ¬†03h45PM
GMT “0” ¬† : ¬†05h45PM
GMT “+2” : ¬†07h45PM
“Visual C#:
Working with
WCF Data Services”
by Jaliya Udagedara
“Cross-Linking and Making
our¬†Articles Discoverable”
by Richard Mueller
“Transact-SQL:
The power of
INSTEAD OF Triggers”
by Ronen Ariely aka Pituach
GMT “-8” ¬†: ¬†10h30AM
GMT “-2” ¬†: ¬†04h30PM
GMT “0” ¬† : ¬†06h30PM
GMT “+2” : ¬†08h30PM
“Building a Business
Intelligence Solution with
Power BI Components”
by Paul Turley
“Wiki Ninjas
Social Media”
by Ronen Ariely aka Pituach,
Peter Geelen
and Sandro Pereira
“Windows Server:
Leading Spaces in
Active Directory Names”
by Richard Mueller
GMT “-8” ¬†: ¬†11h15AM
GMT “-2” ¬†: ¬†05h15PM
GMT “0” ¬† : ¬†07h15PM
GMT “+2” : ¬†09h15PM
“Small Basic and TechNet”
by Nonki Takahashi
“Exchange Server”
by Ugur Demir
(only Turkish Language)
GMT “-8” ¬†: ¬†01h45PM
GMT “-2” ¬†: ¬†07h45PM
GMT “0” ¬† : ¬†09h45PM
GMT “+2” : ¬†11h45PM
“How to Integrate Cortana
in a Windows Phone
application”
by Sara M.G.Silva
“TechNet Guru”
by Peter Laker
aka XAML guy
“BizTalk Server 2013 R2
JSON support and
integration¬†with Cloud API’s”
by Steef-Jan Wiggers
GMT “-8” ¬†: ¬†02h30PM
GMT “-2” ¬†: ¬†08h30PM
GMT “0” ¬† : ¬†10h30PM
GMT “+2” : ¬†12h30AM
“Wiki Ninja Belts”
by Margriet Bruggeman,
Peter Laker
aka XAML guy
and Ed Price
“DevOps¬†Desenvolvimento
e Opera√ß√Ķes Juntos!”
by Alan Carlos
(only Portuguese Language)
¬†DAY 3 –¬†March, 19th
Time / GMT  DEV track WIKI track  INFRA track
GMT “-8” ¬†: ¬†11h15AM
GMT “-2” ¬†: ¬†05h15PM
GMT “0” ¬† : ¬†07h15PM
GMT “+2” : ¬†09h15PM
“Implementing audio
media stream sources in
windows phone 8.1”
by Mihai Cosmin
aka MCosmin
GMT “-8” ¬†: ¬†12h00PM
GMT “-2” ¬†: ¬†06h00PM
GMT “0” ¬† : ¬†08h00PM
GMT “+2” : ¬†10h00PM
“Office 365
SharePoint Uygulamalari”
by Erdem SELÇUK
(only Turkish Language)
“Pr√©sentation de la recherche
dans SharePoint 2013”
by Benoit Jester
(only French Language)
GMT “-8” ¬†: ¬†01h00PM
GMT “-2” ¬†: ¬†07h00PM
GMT “0” ¬† : ¬†09h00PM
GMT “+2” : ¬†11h00PM
“Wiki Life (French):
Best Practices”
by Benoit Jester
and Gokan Ozcifci
“Windows Server:
Active Directory Migration”
by Hasan Dimdik
GMT “-8” ¬†: ¬†01h45PM
GMT “-2” ¬†: ¬†07h45PM
GMT “0” ¬† : ¬†09h45PM
GMT “+2” : ¬†11h45PM
“Structured Error
Handling Mechanism
in SQL Server 2012 & 2014”
by Saeid Hasani
“Wiki Life: Technet Wiki
Best Practices
(from administrators view)”
by Peter Geelen
“The good,the bad and¬†ugly
part for Office365
for large sized enterprises”
by Gokan Ozcifci
(only French Language)
GMT “-8” ¬†: ¬†02h30PM
GMT “-2” ¬†: ¬†08h30PM
GMT “0” ¬† : ¬†10h30PM
GMT “+2” : ¬†12h30AM
“Getting Started with
MongoDB on Azure”
by Jan Hentschel
aka Horizon_Net
“TechNet Wiki:
User Groups”
by Craig Lussier
GMT “-8” ¬†: ¬†03h15PM
GMT “-2” ¬†: ¬†09h15PM
GMT “0” ¬† : ¬†11h15PM
GMT “+2” : ¬†01h15AM
“From Forums to Wiki:
Templates”
by Sandro Pereira
“FIM 2010
Best Practices &¬†Technet Wiki”
by Peter Geelen
GMT “-8” ¬†: ¬†04h00PM
GMT “-2” ¬†: ¬†10h00PM
GMT “0” ¬† : ¬†12h00AM
GMT “+2” : ¬†02h00AM
“Evitando que minha
coluna IDENTITY quebre
a sequencia num√©rica”
by Durval Ramos
(only Portuguese Language)
“TechNet Wiki
International Communities”
by Ed Price
“The difference between
SharePoint On-Prem’ platforms
and Cloud/Hybrid”
by Gokan Ozcifci

 

Webinar: Error Handling in SQL Server

 

TN Summit 2015

 

March 19, 2015 9:45 PM GMT

Saeid Hasani

Structured Error Handling in SQL Server

The goal of this session is to provide a simple and easy to use error handling mechanism with minimum complexity. This session is compatible with MS SQL SERVER 2012 and 2014.

Biography

Saeid Hasani is a Senior Database Developer, SQL Server Consultant, freelance SQL author and T-SQL Trainer. His main expertise is T-SQL Programming and Query Tuning. As a database developer, he has several years of experience in developing software in various domains such as MIS, ERP and customized Enterprise applications. As a freelance SQL author, he has been writing about SQL since 2010. He has been one of the MSDN SQL Server Forums Moderators.

Webinar Link: https://www307.livemeeting.com/lrs/0000000379_116/Registration.aspx?pageName=gdx8jdv0r7rmftrk

T-SQL: Delete All Rows From a Table Except Top(N) Rows

I added the article which comes from a question that asked in MSDN forum. The problem is how to delete all rows from a table but remain the Top (N) rows, for example, remaining only the top (1000) rows. All Code samples in this article are downloadable from this   link . Please navigate to this link