Advertisements

Archive

Archive for the ‘SQL Server 2017’ Category

SQL Server on Linux – Best practices post installation

August 30, 2018 1 comment

 

Setup SQL Server on Linux:

1. Spinning up a new Linux VM on Microsoft Azure

2. Install and Configure SQL Server 2017 on Linux Azure VM

3. Connect SQL Server on Linux with SSMS from a Windows machine
 

Best Practices:

Here are some of the best practices post installing SQL Server on Linux that can help you maximize database performance:

1. To maintain efficient Linux and SQL Scheduling behavior, it’s recommended to use the ALTER SERVER CONFIGURATION command to set PROCESS AFFINITY for all the NUMANODEs and/or CPUs. [Setting Process Affinity]

2. To reduce the risk of tempdb concurrency slowdowns in high performance environments, configure multiple tempdb files by adding additional tempdb files by using the ADD FILE command. [tempdb Contention]

3. Use mssql-conf to configure the memory limit and ensure there’s enough free physical memory for the Linux operating system.

sudo /opt/mssql/bin/mssql-conf set memory.memorylimitmb 1024
sudo systemctl restart mssql-server

4. On multi-node Non-Uniform Memory Access (NUMA) installations, auto NUMA balancing needs to be disabled to allow SQL Server to operate at maximum efficiency on a NUMA system.

sysctl -w kernel.numa_balancing=0

5. You can also change the kernel settings value for virtual address space to 256K, as the default value of 65K may be insufficient for a SQL Server installation.

sysctl -w vm.max_map_count=262144

6. Use the noatime attribute to disable last accessed timestamps with any file system that is used to store SQL Server data and log files.

7. For the most consistent performance experience, you must leave the Transparent Huge Pages (THP) option enabled.

8. Virtual machine (VM) features like Hyper-V Dynamic Memory shouldn’t be used with SQL Server installations. When using VMs, be sure to assign sufficient fixed-memory sizes.

9. Make sure you have a properly configured swapfile to avoid any out of memory issues.


Advertisements

Get started with Python on SQL Server – Run Python with T-SQL on SSMS

August 10, 2018 1 comment

 
With SQL Server 2016 Microsoft added Machine Learning support with R Language in SQL engine itself and called it SQL Server R Services.

Going ahead with the new SQL Server 2017 version Microsoft added Python too as part of Machine Learning with existing R Language, and thus renamed it to SQL Server Machine Learning Services.
 

Installation/Setup

Here are few steps to get you started with Python programming in SQL Server, so that you can run Python scripts with T-SQL scripts within SSMS:
 

1. Feature Selection: While installing SQL Server 2017 make sure you’ve selected below highlighted services


 

2. Configure “external scripts enabled”: Post installation run below SQL statements to enable this option

sp_configure 'external scripts enabled'
GO
sp_configure 'external scripts enabled', 1;
GO
RECONFIGURE; 
GO
sp_configure 'external scripts enabled'
GO


 

3. Restart SQL Server service: by “services.msc” program from command prompt, and run below SQL statement, this should show run _value = 1

sp_configure 'external scripts enabled'
GO


 

If still you don’t see run _value = 1, then try restarting the Launchpad service mentioned below in Step #4.

4. Launchpad Service: Make sure this service is running, in “services.msc” program from command prompt. Restart the service, and it should be in Running state.

Its a service to launch Advanced Analytics Extensions Launchpad process that enables integration with Microsoft R Open using standard T-SQL statements. Disabling this service will make Advanced Analytics features of SQL Server unavailable.

Post restarting this service, it should return run _value = 1 on running the query mentioned at Step #3
 

Run Python from SSMS

So as you’ve installed SQL Server with ML services with Python & R, and enabled the components, now you can try running simple “Hello World!” program to test it:

EXEC sp_execute_external_script 
	@language = N'Python', 
	@script = N'print(''Hello Python !!! from T-SQL'')'


 

Let’s do simple math here:

EXEC sp_execute_external_script 
@language = N'Python', 
@script = N'
x = 5
y = 6
a = x * y
b = x + y
print(a)
print(b)'

 

If you still face issues you can go through addition configuration steps mentioned in [MSDN Docs link].

 


SQL Server 2017 Setup error – VS Shell installation has failed with exit code 1638

May 14, 2018 Leave a comment

 
Today while installing SQL Server 2017 on my PC I got this popup on the Installation Progress tab. Earlier I had installed SQL Server 2017 but had to uninstall it for some reason. Now today while re-installing it I was getting this error.

TITLE: Microsoft SQL Server 2017 Setup
——————————
The following error has occurred:

VS Shell installation has failed with exit code 1638.

——————————
BUTTONS: OK
——————————

 

… and once you click OK, the installation resumes, but ends in error again, with following error details:

Error details:
§ Error installing Microsoft Visual C++ 2015 Redistributable
VS Shell installation has failed with exit code 1638.
Error code: 1638
Log file: C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log\20180824_225215\VCRuntime140_x64_Cpu64_1.log
Visit https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000 &ProdVer=14.0.1000.169&EvtType=VCRuntime140_x64%40Install%400×1638 to get help on troubleshooting.

 

Resolution:

– As per the second error, the SQL Server setup tries to install Microsoft Visual C++ 2015 Redistributable, but as I already have Visual Studio 2017 installed, thus it is failing to install a previous version of VC++, so you need to:
   1. Uninstall Visual Studio 2017.
   2. Install SQL Server 2017
   3. Install Visual Studio 2017 back again.

– Or, rather than uninstalling whole VS 2017, you can simply:
   1. Uninstall the VC++ 2017 Redistributable
   2. Install SQL Server 2017
   3. Install the VC++ 2017 Redistributable (download: x86, x64)


 


Download SQL Server 2017 for free (with full MSBI stack)

March 1, 2018 Leave a comment

 
With SQL Server 2014 Microsoft made its SQL Server Developer Edition free for Development and Test database in a non-production environment. This edition is not meant for Production environments or for use with production data.

SQL Server 2014 Dev Ed free

With SQL Server 2017 Developer edition developers can build any kind of application on top of SQL Server. It includes all the functionality of Enterprise edition, but is licensed for use as a development and test system, not as a production server.

So, with this free edition you get the Database Engine as well as full MSBI stack with DW/BI capabilities ( i.e. SSIS /AS /RS) for free 🙂
 

Downloads here:

SQL Server 2017 Developer Edition

SQL Server Management Studio (SSMS, latest version)

– Sample databases for SQL Server [AdventureWorks] [Wide World Importers]

SQL Operations Studio


Seeking response on: LDAP Authentication with SQL Server 2017 on Linux

January 8, 2018 1 comment

 
Few days back I got an email from one of the reader of this blog “Amit Bhatt”. As I haven’t worked much with the AD/LDAP stuff, thus I thought to throw this question to you guys thru this blog post. I also feel this may also help other Developers/DBAs hunting for similar stuff.
 

Here he goes:

We have installed SQL Server 2017 on Linux server. I am able to connect SQL Server locally as well remotely but with local user access.

I have my AD account created and have AD server information. Can you please help me how can I connect using my AD account to SQL Server 2017 on Linux remotely?

I heard something about LDAP Authentication. Is it possible to use this concept without adding Linux server to AD domain? Our security team does not allow to add Linux server in Active Directory group.

I am stucked here since last many days, requesting assistance on urgent basis.

 

Please provide your suggestion on the comment section below.
 

–> Responses:

1. To support AD Authentication, SQL depends on SSSD, SPN and a keytab file which have the required tokens to talk to AD. Without these in place SQL cannot talk to AD which is necessary for AD authentication.
Ref: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication

2. If you are looking to login to SQL Server on linux with Windows authentication the linux server should be joined to the domain. If the server cannot be added to domain then SQL authentication is the way to go. The below link has more details to configure windows authentication, https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication


 

–> Videos on Linux:

1. Create a Linux VM on Azure

2. Install SQL Server on Linux Azure VM

3. Connect SQL Server on Linux VM from SSMS