MS SQL Server

ByDr. SubraMANI Paramasivam

Tired of connecting on-prem & cloud data? Take a DB stretch

Save your time and cost on storages and get extra time to relax with this brand new stretch database concept within SQL Server 2016.

You can stretch your legs and relax and get a quick session on delivering your analytics against hot, warm & cold data. Stretch Database is the new concept, which allows you to stretch from on-prem to cloud easily. You can easily enable / disable data stretch, accessing data using stretch database, setup remote data archiving, basic concepts on enabling database/table and backup & restore for the stretch enabled databases.

Stretch DB also covers the concepts of Shallow & Deep backups. However, Deep backups are not supported with SQL Server 2016 CTP2.

You can download the latest CTP 3.2 from this link.

ByDr. SubraMANI Paramasivam

How to verify and then create a specific SQL users?

It is always a best practise to validate whether the logins & user exists before creating a new one in SQL Server. You can verify this by using the system tables (syslogins & sysusers). I have given below T-SQL queries to validate first and then create. To access any specific database, a login creation is a mandatory. If the login already exists, you can still verify it with syslogins and then create the user for any new databases.

–Check the logins first for the SQL Server
USE [master]
GO
SELECT name FROM sys.syslogins;
GO
SELECT name FROM sys.sysusers;
GO

–Check the users for any specific databases.
USE [AdventureWorks2012]
GO
SELECT name FROM sys.sysusers
GO

After reviewing the available logins & users for the SQL Server and specific databases, and according to your company policies in naming convention, you then can create users accordingly. But before that need to drop the user if exists with the below queries.

–IF EXISTS DROP USER & LOGINS for a SQL user named ServiceAct_App1
USE [AdventureWorks2012]
GO
IF EXISTS (SELECT name FROM sys.sysusers WHERE name = N’ServiceAct_App1′)
DROP USER ServiceAct_App1
GO
USE [master]
GO
IF EXISTS (SELECT name FROM sys.sysusers WHERE name = N’ServiceAct_App1′)
DROP USER ServiceAct_App1
GO
IF EXISTS (SELECT name FROM sys.syslogins WHERE name = N’ServiceAct_App1′)
DROP LOGIN ServiceAct_App1
GO

In here, I am creating login & user after dropping it with if exists statement.

–CREATE LOGIN, CREATE USER, ASSIGN ROLES for Applications – Service Account
USE [master]
GO
CREATE LOGIN [ServiceAct_App1] WITH PASSWORD=N’YSM2015′, DEFAULT_DATABASE=[AdventureWorks2012], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks2012]
GO
CREATE USER [ServiceAct_App1] FOR LOGIN [ServiceAct_App1]
GO
ALTER ROLE [db_datareader] ADD MEMBER [ServiceAct_App1]
GO
USE [master]
GO
CREATE USER [ServiceAct_App1] FOR LOGIN [ServiceAct_App1]
GO
ALTER ROLE [db_datareader] ADD MEMBER [ServiceAct_App1]
GO

 

ByDr. SubraMANI Paramasivam

Resource Governor – Quick tips

Quick and useful tips on using Resource Governor in your SQL Server environment. I have selected randomly on some of the Resource Governor related T-SQL queries that might be useful in your work environment.
–to view the resource governor configuration
SELECT * FROM sys.dm_resource_governor_configuration;

GO

— to view the resource governor workload groups
SELECT * FROM sys.dm_resource_governor_workload_groups

GO

— to view the resource governor resource pools

SELECT * FROM sys.dm_resource_governor_resource_pools

GO

–to reset the accumulated statistics about the resource governor usage

ALTER RESOURCE GOVERNOR RESET STATISTICS;
GO

–if you have more than one resource governor classifier function, you can use below statement to apply another function to take effect

ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.AnotherClassifierFunction);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

–Code to Remove All Classifier Functions from the Resource Governor
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

ByDr. SubraMANI Paramasivam

SQL Server 2016 CTP 3.1 is out now

Microsoft keeps improving the SQL Server 2016 in every CTP version and the latest CTP 3.1 has been released and can be downloaded in this link.

SQL Server 2016 Technical documentation is available here.

As usual, the downloads are available both in ISO & CAB formats.

 

ByDr. SubraMANI Paramasivam

Enable, Disable Stretch Database

You can now easily enable & disable stretch database both in GUI via SSMS and also using T-SQL. Below screens provides you on each step involved in enabling the Stretch for the database.

Enable_StretchDB

Enable_StretchDB02 Enable_StretchDB03 Enable_StretchDB04 Enable_StretchDB05 Enable_StretchDB06 Enable_StretchDB07 Enable_StretchDB08 Enable_StretchDB09

You can easily disable stretched database using below T-SQL.

ALTER DATABASE DAGEOP_STRETCH2
SET REMOTE_DATA_ARCHIVE = OFF

ByDr. SubraMANI Paramasivam

Resource Governor – Default settings

How does the resource governor default settings look like? Below is a simple screenshots taken from SSMS and also from the resource governor properties.

RG_Default_01 RG_Default_02

How does the resource governor would look like after configuring a successful resource governor? This can be seen from the below screenshot, which shows four resource pools and workload groups configuration, which has dedicated lanes for Applications, Reporting systems, Developers and Business users.

RG_Default_03

 

 

ByDr. SubraMANI Paramasivam

Tiny bits about Resource Governor and of course my favorite

Resource Governor helps sorry saves DBA’s life in terms of performance improvement by deriving proper lanes on what applications or which service accounts should have high horse power when it comes to CPU & I/O usage. This feature was introduced in SQL Server 2008 and made available in Enterprise, Developer and Evaluation editions only. This has got the ability to classify connections and route the workload to specific already derived lanes by DBA as per the business requirements. This is limited to SQL Server Engine only and self contained and instant specific only. This has got 3 main below concepts.

  • Resource Pool
    • Represents physical resource
  • Workload Group
    • Categorizes the sessions and routes to relevant resource pool
  • Classification
    • A function to decide the sessions to allocate the relevant group

Below picture explains the available system views, DMVs, Performance counters that are specific to Resource Governor only.

ResourceGovernor

ByDr. SubraMANI Paramasivam

Running Stretch Database Advisor against AdventureworksDW2008R2 OLAP database

This article gives a quick glimpse on how to run SQL Server 2016 Upgrade Advisor Preview 1.2 against AdventureWorksDW2008R2 OLAP database. Once you shoot the Upgrade Advisor, go to Scenarios and then select the first option “Run the Stretch Database Advisor”. Following this, you will get below screen and then click on Select Databases to Analyze. This will then shoot the blade option to select the databases but requires you to connect to which instance the database is located. In the below example I am connecting to my YSMLab01\SQL2016 instance.

RunStretch_01

After choosing the instance, I am selecting AdventureWorksDW2008R2 database and clicking on Run button.

RunStretch_05

Following the analyzed database of AdventureWorksDW2008R2, it has identified the database size and total number of tables being analyzed. By clicking on this result set it further displays the list of tables with status on eligibility to cloud or not. If it has errors then it gives out the reasons on why it is not selected in blade format. From below screenshot, you can see that the first table has been identified to be ready for stretch with a green tick mark, where as the next table is not eligible.

RunStretch_06

 

 

You can see the same results when the stretch database advisor is run against AdventureWorks2014 OLTP database here.