BLOGS

ByDr. SubraMANI Paramasivam

Hyperscale technology lets a DBA sleep, +30 minutes a day!

Ease of access and easy life is now possible with On-premises and hybrid cloud technology with SQL Server 2016.

SQL Server 2016’s Hyperscale technology gives us a huge relief in Backups, Disaster Recovery solutions, access resources no matter whether they are in on-premises, private or public cloud. New tools within SQL Server 2016 and Microsoft Azure has made every single DBA/Developers life much easier to scale to the cloud.
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

Microsoft SQL Server 2016 Upgrade Advisor released

Microsoft have now released its latest SQL Server 2016 Upgrade Advisor Preview 1.3 and you can access it here.

This tool is not part of SQL Server 2016 CTP3.0 and have to be done additionally. This tool advises to upgrade below scenarios.

  1. Stretch Database
  2. Database upgrade analyzer
  3. Analysis Services Upgrade advisor
  4. Integration services upgrade advisor
  5. Reporting services upgrade advisor.
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