Archive for the ‘Configuration’ Category

It’s been a long time since I’ve posted on here, mainly because I no longer work primarily as a DBA but more as a SQL Developer, I was also looking after thousands of instances so came across a lot of issues. However, this one had me stuck for a few days. We have a developer who wanted to run ‘R’ and a 2016 instance which should have let him, but no dice.

The Problem

When running the following ‘R’ test script:

EXEC sp_execute_external_script
@language =N’R’,
@script=N’OutputDataSet<-InputDataSet',
@input_data_1 =N’SELECT 1 AS hello'<
WITH RESULT SETS (([hello world] int not null));
GO

I got the following error:
Msg 39021, Level 16, State 1, Line 1 Unable to launch runtime for ‘R’ script.
Please check the configuration of the ‘R’ runtime. Msg 39019, Level 16, State 1, Line 1 An external script error occurred: Unable to launch the runtime. ErrorCode 0x80070490: 1168(Element not found.).

The Solution

I was running on SQL Server 2016 (13.0.4001.0) with no previous ‘R’ installations or CTP instalattions.
The solution was to uninstall and install the dll.
In my case the path to it was C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\library\RevoScaleR\rxLibs\x64\RegisterRExt

So, first I opened up dos with admin rights and ran:
“C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\library\RevoScaleR\rxLibs\x64\RegisterRExt” /uninstall

After that I ran
“C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\library\RevoScaleR\rxLibs\x64\RegisterRExt” /install

Note: Each time you run the uninstall or install script it will stop and start your SQL instance

This was for a default instance. I think, if you are using a named instance you need to add: /instance:InstanceName after the /install flag

 

Advertisements

The Problem

When I tried to deploy our SQL Maintenance plans to a server nothing happened.
I then checked the server and go the error: The affinity mask specified does not match the CPU mask on this system.

I ran sp_configure and saw that advanced options were not turned on. I wanted to turn them on so I could set the affinity mask properly. When I tried to do this I got the error: The affinity mask specified does not match the CPU mask on this system. I ran sp_configure again and I saw that the configured value was set to 1 but the run value was set to 0. This happened even I fI used Reconfigure with Override.
I restarted the instance to see if that would help. It didn’t.
I tried to change the affinity mask through Properties on the Instance in SSMS. It was all greyed out so I was unable to change anything.

Solution

I won’t detail all the things I tried which didn’t work, but there were many.
I changed the server start-up parameters to include –f. This starts the server in single user mode with limited configuration.
I also stopped the SQL Agent Servers as I didn’t want it taking my single connection.
There was an application which connects as soon as SQL starts. This was local so I couldn’t disable TCP/ip or use ipSec to stop it getting in. The solution was simpler – I disabled the account as it was using a SQL Login.
I then restarted SQL.
In SSMS I created a DAC connection to the box by going to File > New Query > then connected as
ADMIN:Servername
I ran sp_configure and could see that affinity mask had a configured value of 2 but a run value of 0 – due to the limited configuration start up. I ran sp_confiure and set it to 0. I did the same with the I/O affinity mask.
I then removed the –f start up parameter and restarted SQL. I re-enabled the SQL Login I’d disabled and started up the SQL Agent.
All was now fine.

Why did it happen?

This was a VM with only one CPU. I assume the affinity settings were put in place on a physical box. The machine was then virtualised with one CPU and the problems started.

To amend the default path for the SSIS package store amend this file on the server:

C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml

<Folder xsi:type=”FileSystemFolder”>

<Name>File System</Name>

<StorePath>E:\SSIS</StorePath>

</Folder>

Nearly everything in the package can be configured.

The main items you will want to configure are the passwords in server connections along with the name of the server and possible the initial catalog (database).
To set up a configuration option right click the package main window and select package configurations.
The options available are to use an XML file, Registry Entry, Parent Package Variable or a SQL database.
In this example I have a database called dataimports which I am going to use. In dataimports I have created a schema for each batch of work I use. This means I can separate out all the SSIS logging and configuration and I can give users access through the schemas.

Once you select the SQL server option you will need to select a database connection to use and then a table. If you already have config tables the drop down box will list them – else you can create a new one. You will need to type in a configuration filter. This is a key word that will match up the configuration settings in the table with this package. The name should be easily identifiably like >> DataDownload.
When you click next you will see a list of configurable items. If this is a new package there will not be very many.

You should try to restrict the configured values to the ones really needed else you will make reading the configuration table difficult which can lead to errors.

A config file should be used to store secure data – like passwords. It should also be used to store values which will or could change. For example a package which emails a user upon completion could have the To field of the email task configured. This way if you need to change the recipient you can change the config table. If a server name is going to change as the package is deployed then store the server name in a config file. You shouldn’t store static or trivial data like the protection level of a database connection – this will probably never change; or the connection string to a database and then all the individual elements of the connection. If you are using Windows Authentication you may not need to store any connection data.

What it looks like in the table:

I have created a primary key on ConfigurationFeature and PackagePath – the table is created without any primary key.

Note for passwords the value stored in the config file will be *****. This is a place holder. You will need to change this value to the actual password and set security on the table to stop people reading it.