26 Feb

ITPro Track – Demystifying SQL Server Backups | 19-March-2014

Microsoft MVP community India along with Microsoft is hosting an online tech conference called “Microsoft Virtual Tech Conference” for both IT Pro and Developer audience.  This is scheduled on 19th March 2014.

I’ll be presenting a Session for ITPro Track: Demystifying SQL Server Backups. In this Demo heavy session SQL Server MVP Sarabpreet Singh Anand will explain you the untold tips and tricks. Sarabpreet will Demo how to do Point-in-time recovery, what are the key things to keep in mind while planning the backup strategy of your mission critical DB. We’ll corrupt a DB live and then show you how to recover it.

I’ll also try to cover the new features of SQL Server 2014 related to Backups.

So Stay tuned and Register for this session: https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032577204&culture=en-IN

 

VTCC2014 - Sarabpreet Singh Anand - Demystifying SQL Server backups


3 Jun

MS Webcast | Lunch Time – How to Monitor SQL Servers using Powershell – Part 1 | 26-June-2013

I’ll be delivering another webcast in Lunch Time series and this time its on How to Monitor SQL Servers using Powershell – Part 1. Join this webcast to learn how to create your own monitoring solution for SQL Servers using Powershell.

About the Session: Monitoring and Alerting is a vital process of every IT Pro and if you are managing more than just a couple of SQL Servers it becomes indispensable. This webcast will guide you and explain the ted-bits about creating your own Monitoring Solution. This will work as a Ice-Breaker session for those who don’t know much about PowerShell and how to use PowerShell to create your own customized monitoring\alerting solution.

To Register visit: https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032555479&Culture=en-IN&community=0

How_To_Monitor_SQLServer_using_Powershell_Part1_Webcast


1 May

MS Webcast | Lunch Time – Implementing SQL Server AlwaysOn | 15-May-2013

I’ll be delivering a Webcast on AlwaysON in the new Lunch Time series.

Lunch Time is a new talk series scheduled for every alternate Wednesday exactly during the Lunch Time. And the presenters of this talk series are the Most Valuable Professionals. This would give the audience a good platform to learn from these exceptional technical leaders and give product feedback to Microsoft through them.

Hurry up Register Now!

To Register: https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032545159&culture=en-IN

LunchHour-ALWAYSON Webcast


12 Nov

SQL Server Resource Governor Webcast – Demo Scripts

Hi Friends,

Last week I took a webcast “Tap is the only Solution Ver.2 (SQL Server Resource Governor)“. The webcast was very well received and there were a lot of questions regarding the Demo and the scripts. So in this blog post I’ll provide all the scripts to implement, test, tweak & remove Resource Governor. You can also use these scripts to do Proof of Concept (POC) on Resource Governor.

Hope this will help you.

Happy Learning\Testing – DO leave a comment to let us know how we are doing.

Implement\ configure Resource Governor

 

Create database simple

go

—Login creation for all users, I disabled strong password checking for demo

 

–purposes only but this is against best practices

USE [master]

GO

CREATE LOGIN [Sales_app] WITH PASSWORD=N’sales_app’, DEFAULT_DATABASE=[simple], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE LOGIN [Reporting_app] WITH PASSWORD=N’reporting_app’, DEFAULT_DATABASE=[simple], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE LOGIN [Fin_App] WITH PASSWORD=N’fin_app’, DEFAULT_DATABASE=[simple],CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Go

use simple

Go

CREATE USER [Sales_app] FOR LOGIN [Sales_app]

CREATE USER [Reporting_app] FOR LOGIN [Reporting_app]

CREATE USER [Fin_App] FOR LOGIN [Fin_App]

GO

–limiting resources to test Resource Governor because I am using a laptop with 8 proc and 8GB ram

 

–which will be too much and complex to do POC

sp_configure ‘show ‘,1

go

reconfigure

go

sp_configure ‘min server’, 2048;

go

sp_configure ‘max server’, 2048;

RECONFIGURE

GO

— create user pools

— note that we are using all default parameters

CREATE RESOURCE POOL Pool_Sales_fin_app

CREATE RESOURCE POOL Pool_reporting

— create user groups also note that all groups created with default parameters only pointing to the

 

–corresponding pools (and not ‘default’ pool)

CREATE WORKLOAD GROUP Sales_Group

USING Pool_Sales_fin_app

CREATE WORKLOAD GROUP Reporting_Group

USING Pool_reporting

CREATE WORKLOAD GROUP Fin_Group

USING Pool_Sales_fin_app

GO

— now create the classifier function

Use master

go

IF OBJECT_ID(‘DBO.CLASSIFIER_1′,’FN’) IS NOT NULL

DROP FUNCTION DBO.CLASSIFIER_1

GO

— note that this is just a regular (UDF) User Defined Function

CREATE FUNCTION DBO.CLASSIFIER_1()

RETURNS SYSNAME WITH SCHEMABINDING

BEGIN

DECLARE @val varchar(32)

SET @val = ‘default’;

if ‘Sales_app’ = SUSER_SNAME()

SET @val = ‘Sales_Group’;

else if ‘Reporting_app’ = SUSER_SNAME()

SET @val = ‘Reporting_Group’;

else if ‘Fin_App’ = SUSER_SNAME()

SET @val = ‘Fin_Group’;

return @val;

END

GO

— make function known to the Resource Governor

ALTER RESOURCE GOVERNOR

WITH (CLASSIFIER_FUNCTION = DBO.CLASSIFIER_1)

GO

–Check if Reconfigure is pending for any setting of Resource Governor

SELECT * FROM sys.dm_resource_governor_configuration

— make the changes effective

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

Workload – by different Sessions

 

declare @i int

declare @s varchar(100)

set @i = 10

while @i > 0

begin

select @s = @@version;

end

Using Cap_CPU_Percent Parameter – New to SQL 2012 to configure Hard Cap on CPU

CPU_CAP_PERCENT & AFFINITY SCHEDULER settings are available only through Scripts as of now- Planned to come in GUI from SP1

 

ALTER RESOURCE POOL [Pool_Sales_fin_app]

WITH (CAP_CPU_PERCENT=30)

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

ALTER RESOURCE POOL [Pool_Reporting]

WITH (CAP_CPU_PERCENT=40)

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

Using Affinity Parameter to dedicate a scheduler to a single pool

 

ALTER RESOURCE POOL Pool_Sales_fin_app

WITH (AFFINITY SCHEDULER = (0))

GO

ALTER RESOURCE POOL Pool_reporting

WITH (AFFINITY SCHEDULER = (1))

GO

ALTER RESOURCE GOVERNOR RECONFIGURE

Go

Internal Tables\DMVs for Resource Governor

–Metadata Tables

 

SELECT * FROM sys.resource_governor_workload_groups

SELECT * FROM sys.resource_governor_resource_pools

SELECT * FROM sys.resource_governor_configuration

DMV’s

 

SELECT * FROM sys.dm_resource_governor_workload_groups

SELECT * FROM sys.dm_resource_governor_resource_pools

SELECT * FROM sys.dm_resource_governor_configuration

Select * from sys.dm_resource_governor_resource_pool_affinity — – New DMV in SQL 2012 for Affinity setting & Schedulers mask

Script to check which scheduler is being used by which Resource Group

select

r.session_id,

CONVERT(NCHAR(20), wg.name) as group_name,

t.scheduler_id,

r.status

from sys.dm_exec_requests r

join sys.dm_os_tasks t on r.task_address = t.task_address

join sys.dm_resource_governor_workload_groups wg on r.group_id = wg.group_id

where

r.session_id > 50

Clean-Up

—Clean Up Script

ALTER RESOURCE GOVERNOR disable;

–Drop Logins and associated Users

USE [simple]

GO

DROP USER [Sales_app]

DROP USER [Reporting_app]

DROP USER [Fin_App]

GO

USE [master]

GO

DROP LOGIN [Sales_app]

DROP LOGIN [Reporting_app]

DROP LOGIN [Fin_App]

GO

–drop workload

USE [master]

GO

DROP WORKLOAD GROUP [Reporting_Group]

DROP WORKLOAD GROUP [Sales_Group]

DROP WORKLOAD GROUP [Fin_Group]

go

–drop pools

USE [master]

GO

DROP RESOURCE POOL [Pool_Sales_fin_app]

DROP RESOURCE POOL [Pool_Reporting]

GO

—drop classifier function

ALTER RESOURCE GOVERNOR

WITH (CLASSIFIER_FUNCTION = Null)

GO

DROP FUNCTION DBO.CLASSIFIER_1

 

Reference: Boris Baryshnikov’s blog post & Whitepapers for Resource Governor 2008 & 2012.

Regards

Sarabpreet Anand


4 Nov

Free SQL Server Webcast | Tap is the only Solution Ver.2 | 08-Nov-2012

Tap is the only solution – Ver. 2

Scarcity is the fundamental economic problem of having humans who have unlimited wants and needs in a world of limited resources. Alternatively, scarcity implies that not all of society’s goals can be pursued at the same time.
Just like Water the Server Resources are also getting scarce and we should find a way to put a Tap on these resources to optimally utilize them as and when needed. In this session we’ll see a way to control & manage server resources to get desired performance and targets.(Level: 100 – 300)

Register: http://ssgwebcast08nov2012.eventbrite.com/

Note: You will get the LiveMeeting details a few hours before the event. Please make sure that your laptop/PC is equipped with LiveMeeting client.

image


1 May

SQL Server Webcast on Log Shipping

16-may-2012 log shipping-2


22 Feb

SQL Server Webcast on Bulletproof Backup & Restore Strategy

First Webcast by Sarabpreet Singh on Bulletproof Backup & Restore Strategy