Feeds:
Posts
Comments

Posts Tagged ‘Sql Server 2016’

Quick one today:

A few days ago, as part of installing Sql Server 2016, we needed to install R Service on a lab machine. During installation, we learnt that the Sql Server DVD/media does not have necessary CAB files to perform installation. We ran into this situation:

Setup was unable to contact the download server. 
Provide the location of the Microsoft R Open and Microsoft R Server 
installation files and click 'Next'. 
The installation files can be downloaded from:

https://go.microsoft.com/fwlink/?LinkId=824879&lcid=1033
 https://go.microsoft.com/fwlink/?LinkId=824881&lcid=1033

Sql2016_R_Install

Resolution:

Since our lab machines do not have internet access, these CAB files could not be downloaded by the install wizard. Using the URL’s, we could download the necessary install CAB files (2 files) to a local laptop and then copy the files over to the lab server.

Then point the install wizard to this location (local to the server) where both the CAB files are present. See image below:

Sql2016_R_Install_with_CAB_Files

 

Once the CAB files path is identified, rest of the installation progresses smoothly. See image below with R Service installed.

Sql2016_R_Install_Successful

Hope this helps,
_Sqltimes
Advertisements

Read Full Post »

Quick one today:

Earlier today, when installing Sql Server 2016, ran into this interesting error:

Oracle JRE 7 Update 51 (64-bit) or higher is required for Polybase

oraclejre_error

Looks like JRE (Java Runtime Engine) is needed for Polybase application to run. So, lets get it from Oracle website here. Download from the JRE section (see image below).

oraclejre_error2

In the new page, “Accept License Agreement” & download the version pertinent to your environment.

oraclejre_error3

After installing JRE, the checks ran successfully for install.

Hope this helps,
_Sqltimes

Read Full Post »

Recently, we discussed how to call procedures with OUTPUT parameter. Now, we’ll cover a nuance in the same path. Default parameter values in Stored Procedures.

 

When we call a procedure that has DEFAULT values defined for its parameters, the way we call the procedure could change what values goes go into the execution of the procedure.

 

Variation 1: Purpose of DEFAULT values

DEFAULT values are defined in Store Procedures definition, so when no value is provided in procedure call, default values could be used (during execution).

Sample:

Consider the below sample procedure definition code below:

CREATE PROCEDURE dbo.abc
	  @Param1 INT = 10
...

Parameter @Param1 is defined with 10 as the DEFAULT value. So, when I call the procedure as:

EXEC dbo.abc
GO

The procedure takes the DEFAULT value 10 in its execution.

 

Variation 2 : Values in procedure call

When we actually provide any values in procedure call, the new value provided in the call is used in procedure execution, rather than the default value

DECLARE @P1 INT = 15
EXEC dbo.abc @P1

Now, in procedure execution, the new value (15) is used, and not the default value (10). This makes sense. Every time we need to run the procedure with different values, we just provide them in the parameter and it takes into effect; Where no value is provided, default values kick-in.

Variation 3 : NULL Value in procedure call

When we call the procedure with parameters, but do not provide any value, the default NULL value, assigned during variable declarations, will go in as the value.

DECLARE @P1 INT
EXEC dbo.abc @P1
GO

In this case, when the variable @P1 is declared, by default, NULL value is assigned. Since we do not have a subsequent step to assign value, the NULL value will go into the procedure call. This NULL value will overwrite the default value (10) mentioned in procedure definition.

So, if you want to run with default values defined in procedure definition, just run it like this:

EXEC dbo.abc
GO

 

Complete Code

For more testing, use the below complete code :

--
-- Sample procedure to check the default values
--
CREATE PROCEDURE dbo.abc
	  @Param1 INT = 10
	, @Param2 INT OUTPUT
AS
	SET @Param2 = @Param1

	SELECT @Param1, @Param2
GO

--
-- Variation 1: Purpose of DEFAULT values
--
EXEC dbo.abc
GO

--
-- Variation 2 : Values in procedure call
--
DECLARE @P1 INT = 1
      , @P2 INT
EXEC dbo.abc @Param1 = @P1, @Param2 = @P2 OUTPUT
GO

--
-- Variation 3 : NULL Value in procedure call
--
DECLARE @P1 INT
      , @P2 INT
EXEC dbo.abc @Param1 = @P1, @Param2 = @P2 OUTPUT
GO

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Once in a while we need a way to quick check how the database files are growing. This below script allows us to query the last few “AutoGrowth” events on database files.

Every time, autogrowth happens, it is logged in the trace file. Since Sql Server has a default trace file running all the time, we query it for autogrowth events.

Note: Data in trace file only stays there for a short period of time (depending on retention), so if this particular event did not occur in the recent past it may not be in the trace file anymore.

According to Event Class  List:

  1. 92 is for Data file auto growth
  2. 93 is for Log file auto growth
--
--	Get the trace file name
--
DECLARE @TraceFile NVARCHAR(1000);

SELECT @TraceFile = path
FROM sys.traces
WHERE is_default = 1;

--
--	Look for 'AutoGrowth' events in TraceFile
--
SELECT    TOP 10
		  DatabaseID				AS [Database_ID]
		, DB_NAME(DatabaseID)		AS [Database_Name]
		, FileName					AS [DatabaseFileName_Logical]
		, (Duration*1.0/1000000)	AS [TimeSpentOn_AutoGrowth]

FROM sys.fn_trace_gettable(@TraceFile, DEFAULT)

WHERE EventClass IN ( 92	--	DataFile Growth
					, 93)	--	LogFile Growth
AND DatabaseID = DB_ID()
GO
Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Microsoft has released a new free ebook along with the release of new version of Sql Server 2016. This is an exciting time !!

Sql Server 2016

Sql Server 2016

Hope you enjoy reading and learning more !! You can download it from here.

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Earlier today, we needed to add a new drive to existing Sql Server cluster. New LUN was created and added to the cluster, but when we tried to add a new data file, this error occured:

Msg 5184, Level 16, State 2, Line 1
Cannot use file 'I:\MSSQL\Data\SsampleDB2.ndf' for clustered server. 
Only formatted files on which the cluster resource of the server has a dependency can be used. 
Either the disk resource containing the file is not present in the cluster group or the 
cluster resource of the Sql Server does not have a dependency on it.

Msg 5009, Level 16, State 8, Line 1
One or more files listed in the statement could not be found or could not be initialized.

The error message is verbose and helpful. It points to the exact things we could check to resolve it.

  1. Either the disk resource containing the file is not present in the cluster group OR
  2. Cluster resource of the Sql Server does not have a dependency on it

The first one did not apply here as the drive was already added to the Cluster through Failover Cluster Manager. So, lets look at the second step.

Dependency chain is an important aspect of Sql Server Clustering. It tells Sql Server what to do in case of failure. A resource group has several resources in it. All the resources in a single resource group act as a single group. They failover as a one unit; Thus providing high availability on the available nodes.

Keep these questions in mind, when you think about different resources in a cluster.

  • What resources belong to a Resource Group?
  • What to do when each of them fail?
    • Do we try to re-start the resource (or NOT)?
    • How many attempts to bring the resource back online?
    • Do we wait a few minutes and then attempt restarting?
    • Does failure of one resource trigger failover of the entire Resource Group?
    • Do we just failover that single resource?  (for this to happen, that resource needs to be in its own Resource Group

All these points are answered during the configuration of resources to a cluster.

For this situation, we need to add this new drive to the dependency chain for Sql Server Resource Group.

Resolution Steps:

Step 1:

First, go to Failover Cluster Manager. Under the relevant cluster name, go to Roles; On the right-hand window, highlight the Resource Group that has Sql Server resources. In our situation, it is called: Sql Server (MSSQLSERVER). See below:

Step 1: Sql Cluster Add NewDrive

Step 1: Sql Cluster Add NewDrive

Step 2:

When Sql Server Resource Group is highlighted, go to the bottom window, where details are displayed. It has two tabs.

  1. Summary
  2. Resources

Go to Resources tab; Here you might see a few sections.

  1. Storage:  All the drives added to this cluster Resource Group are listed here. Make sure the new drive can be seen in the list.
  2. Server Name : These are the logical Cluster Name and IP addresses
  3. Other Resources : Depending on the resources added, we see Sql Server, Sql Server Agent, MSDTC, etc

Right click on Sql Server, and go to properties.

Step 2: Sql Cluster Add NewDrive

Step 2: Sql Cluster Add NewDrive

Step 3:

In Properties window, go to Dependencies tab. Here we configure the Dependency Chain for Sql Server Resource in the Resource Group called Sql Server(MSSQLSERVER). Each and every data drive is critical for Sql Server to function properly. So, we use AND condition to specify that each and every drive is needed.

Click on Insert button, add condition as AND & pick the new Cluster Disk in the drop down under the Resource side.

Sql Cluster Add NewDrive 3

Sql Cluster Add NewDrive 3

Click ok and Voila !! The new drive is added to the “Dependency Chain”. For more information about the Cluster response to failure, review the options under the Policies tab in the same Properties window.

Now we could add a new NDF file to the new drive location.

Hope this helps,
_Sqltimes

Read Full Post »