BLOGS

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

 

 

ByDr. SubraMANI Paramasivam

Linked reports in SQL Server Reporting Services

Linked reports is a great feature and helps in easy maintenance. You can easily avoid any duplicity of the report just for the sake of security. For example if a report has got 5 regions like Europ, NA, Asia, China, SA. You don’t need 5 different versions of report and instead you can have one single report with report parameters pointing to region and easily grant access to relevant AD groups to access their own regional reports. Any single change made in the main RDL file will immediately reflect in other regions as well and no need to make the same change in each regions. Imagine if you have 50 departments and if you are in this situation, linked reports concept a real handy one.

A quick demo is available here.

LinkedReports

ByDr. SubraMANI Paramasivam

SSRS – Report Builder a closer look

Report builder is a browser based report authoring tool to enable any end users to start building / modifying existing reports (if they have enough permissions) without the need of any installation of BIDS or SSDT. This tool is available via local installation of Report manager. This is a light weight tool that will enable you to definitely build a decent report and you can deploy it directly to the report manager. You can start building this report against SQL Server database, SQL Azure, Oracle and others. Below image also explains more on Report builder tool and also try watching a quick video on this here.

 

ReportBuilder