PERSIST_SAMPLE_PERCENT

ON

The statistics will retain the set sampling percentage for subsequent updates that do not explicitly specify a sampling percentage.

OFF

The statistics sampling percentage will get reset to default sampling in subsequent updates that do not explicitly specify a sampling percentage.

Source: UPDATE STATISTICS (Transact-SQL) | Microsoft Docs

This keyword is added to T-SQL from SQL Server 2016 SP1 CU4.

Perdro Lopez (Microsoft Team) explained it deeply here .

Advertisements

All important commands you need to configure a Windows Server Core and install SQL Server on it

If you have to perform a core mode SQL Server installation, don’t be afraid, just know these commands:

  • create share folder :
net share "share folder"=c:\share /grant:everyone,full
  • restart server :
shutdown -r -t 0
  • configure server 2012+ :
sconfig.cmd
  • rename computer :
netdom renamecomputer %computername% /newname:Svr-1
  • set static ip address :
netsh interface ipv4 set address "Local Area Connection" static 10.10.10.10
  • join to domain :
netdom join Svr-1 /domain:YourDomain.com
  • configure time zone :
Control timedate.cpl
  • activate windows :
slmgr –ato
  • set the password to never expire (lab only) :
wmic useraccount where "Name='Administrator'" set PasswordExpires=false
  • system preparation tool :
%windir%\system32\sysprep\sysprep.exe /oobe /generalize /shutdown
  • enable remote desktop (%windir%\System32>) :
cscript scregedit.wsf /ar 0
  • disable remote desktop (%windir%\System32>) :
cscript scregedit.wsf /ar 1
  • enable the firewall to allow the remote desktop (%windir%\System32>) :
netsh advfirewall firewall set rule group="remote desktop" new enable=Yes
  • enable .net 3.5 on server 2012+ (powershell) :
Install-WindowsFeature NET-Framework-Core –Source D:\Sources\SxS\
  • install SQL Server Database Engine only :
Setup.exe /qs /ACTION=Install /FEATURES=SQLEngine /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="<DomainName\UserName>" /SQLSVCPASSWORD="<StrongPassword>" /SQLSYSADMINACCOUNTS="<DomainName\UserName>" /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /IACCEPTSQLSERVERLICENSETERMS

Configure Windows Server Core with Sconfig.cmd

If you are going to install SQL Server on Windows Server 2012+ Core edition, you can use sconfig.cmd to configure Windows server core easily.

Starting with Windows Server 2012, you can use the Server Configuration tool (Sconfig.cmd) to configure and manage several common aspects of Server Core installations. You must be a member of the Administrators group to use the tool.Sconfig.cmd is available in the Minimal Server Interface and in Server with a GUI mode.

To start the Server Configuration ToolChange to the system drive.

Type Sconfig.cmd, and then press ENTER.

The Server Configuration tool interface opens:

Source: Configure a Server Core Server with Sconfig.cmd

Create a Standalone R Server from the Command Line 

Run the following command from an elevated command prompt to install only Microsoft Machine Learning Server (Standalone) and its prerequisites.

Setup.exe /q /ACTION=Install /FEATURES=SQL_SHARED_MR /IACCEPTROPENLICENSETERMS /IACCEPTSQLSERVERLICENSETERMS

Source: Install Microsoft R Server from the Command Line | Microsoft Docs

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

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

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.

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

 

[Fixed] SSMS – Create script duplicates the statements for Grant or Deny Column Permissions…

I was reported a bug in Microsoft SQL Server (all versions) that related to generate script for database permissions on August 20, 2013.

Steps to Reproduce

Open SSMS >> Connect to Server >> In Object Explorer expand a Sample Database >> expand Security >> right click on Users >> click on New User… >> Fill needed data in General section >> click on Securables >> click on Search >> select a table and add it… >>
In Permission window in Explicit >> click on Select or Update >> Then click on Column Permission >> select Grant to one column and click OK
>>>>>>
Now If click on Script to generate its script, you can see the statements to grant or deny are duplicated in produced script.

Expected Results

non- duplicate
I’m happy to see that Microsoft fixed it soon on October 17, 2013. And the change will be included in a future release or servicing release for SQL Server. For more info please visit this link:
So, may need to install latest update!