SQL Tips


 

Auto Generate a security report on SQL Server instance – Security script

— Srored Procedure to generate Security Audit report in HTML format

CREATE PROC spAuditUsersPermissions

AS

SET NOCOUNT ON

DECLARE @sql VARCHAR(MAX)

DECLARE @strHTML VARCHAR(MAX)

DECLARE @i INT

DECLARE @rc INT

DECLARE @dbname VARCHAR(400)

/* Print header of the report */

SELECT @strHTML = ‘<HTML><HEAD><TITLE> Security list for the auditor </TITLE><STYLE>TD.Sub{FONT-WEIGHT:bold;BORDER-BOTTOM: 0pt solid #000000;BORDER-LEFT: 1pt solid #000000;BORDER-RIGHT: 0pt solid #000000;BORDER-TOP: 0pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} BODY{FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} TABLE{BORDER-BOTTOM: 1pt solid #000000;BORDER-LEFT: 0pt solid #000000;BORDER-RIGHT: 1pt solid #000000;BORDER-TOP: 0pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} TD{BORDER-BOTTOM: 0pt solid #000000;BORDER-LEFT: 1pt solid #000000;BORDER-RIGHT: 0pt solid #000000;BORDER-TOP: 1pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} TD.Title{FONT-WEIGHT:bold;BORDER-BOTTOM: 0pt solid #000000;BORDER-LEFT: 1pt solid #000000;BORDER-RIGHT: 0pt solid #000000;BORDER-TOP: 1pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 12pt} A.Index{FONT-WEIGHT:bold;FONT-SIZE:8pt;COLOR:#000099;FONT-FAMILY:Tahoma;TEXT-DECORATION:none} A.Index:HOVER{FONT-WEIGHT:bold;FONT-SIZE:8pt;COLOR:#990000;FONT-FAMILY:Tahoma;TEXT-DECORATION:none}</STYLE></HEAD><BODY><A NAME=”_top”></A><BR>’

PRINT @strHTML

/* Login information */

SELECT ROW_NUMBER () OVER (ORDER BY name) AS RowNumber,

name, dbname,language,

CONVERT(CHAR(10),CASE denylogin WHEN 1 THEN ‘X’ ELSE ‘–‘ END) AS IsDenied,

CONVERT(CHAR(10),CASE isntname WHEN 1 THEN ‘X’ ELSE ‘–‘ END) AS IsWinAuTHENtication,

CONVERT(CHAR(10),CASE isntgroup WHEN 1 THEN ‘X’ ELSE ‘–‘ END) AS IsWinGroup,

createdate,UPDATEdate,

CONVERT(VARCHAR(2000),

CASE sysadmin WHEN 1 THEN ‘sysadmin,’ ELSE ” END +

CASE securityadmin WHEN 1 THEN ‘securityadmin,’ ELSE ” END +

CASE serveradmin WHEN 1 THEN ‘serveradmin,’ ELSE ” END +

CASE setupadmin WHEN 1 THEN ‘setupadmin,’ ELSE ” END +

CASE processadmin WHEN 1 THEN ‘processadmin,’ ELSE ” END +

CASE diskadmin WHEN 1 THEN ‘diskadmin,’ ELSE ” END +

CASE dbcreator WHEN 1 THEN ‘dbcreator,’ ELSE ” END +

CASE bulkadmin WHEN 1 THEN ‘bulkadmin’ ELSE ” END ) AS ServerRoles

INTO #syslogins

FROM master..syslogins WITH (nolock)

ORDER BY name

SET @rc = @@rowcount

SELECT @strHTML = ‘<BR><CENTER><FONT SIZE=”5?><B> Server ‘ + @@servername + ‘</B></FONT></CENTER><BR>’

PRINT @strHTML

PRINT ‘

’— Query the data only if there are rows:IF @rc = 0BEGINPRINT ‘’PRINT ‘’ENDELSEBEGINUPDATE #sysloginsSET ServerRoles = SUBSTRING(ServerRoles,1,LEN(ServerRoles)-1)WHERE SUBSTRING(ServerRoles,LEN(ServerRoles),1) = ‘,’UPDATE #syslogins SET ServerRoles = ‘–‘WHERE LTRIM(RTRIM(ServerRoles)) = ”PRINT ‘

Logins information There are no logins on this server

 

 

’PRINT ‘’PRINT ‘’SET @i = 1WHILE @i’ + CONVERT(VARCHAR(50),name) + ‘’ +‘’ + CONVERT(VARCHAR(50),CASE ISNULL(dbname,’–‘) WHEN ” THEN ‘–‘ ELSEISNULL(dbname,’–‘) END) + ‘’ +‘’ + CONVERT(VARCHAR(50),ISNULL(language,’–‘)) + ‘’ +‘’ + CONVERT(VARCHAR(10),ISNULL(IsDenied,’–‘)) + ‘’ +‘’ + CONVERT(VARCHAR(10),ISNULL(IsWinAuTHENtication,’–‘)) + ‘’ +‘’ + CONVERT(VARCHAR(10),ISNULL(IsWinGroup,’–‘)) + ‘’ +‘’ + CONVERT(VARCHAR(30),ISNULL(createdate,’–‘)) + ‘’ +‘’ + CONVERT(VARCHAR(30),ISNULL(UPDATEdate,’–‘)) + ‘’ +‘’ + CONVERT(VARCHAR(100),ISNULL(ServerRoles,’–‘)) + ‘’ +‘’FROM #sysloginsWHERE RowNumber = @iPRINT @strHTMLSET @i = @i + 1ENDPRINT ‘

Logins information

Login Name     Default DB                Language               Denied acess?            Windows Auth?              Window group?              Date created            Date Update             Server roles

 

 

<BR><A CLASS=”Index” HREF=”#_top”>Back To Top ^</A><BR><BR>’PRINT'<BR><CENTER></CENTER><BR>’ENDDROP TABLE #syslogins/* Fetch data per database */CREATE TABLE #LoginMap (LoginName VARCHAR(200), UserName VARCHAR(200) NULL)CREATE TABLE #RoleUser (RoLEName VARCHAR(200), UserName VARCHAR(200) NULL)CREATE TABLE #ObjectPerms (RowNumber INT IDENTITY, UserName VARCHAR(50), PerTypeVARCHAR(10),PermName VARCHAR(30), SchemaName VARCHAR(50),ObjectName VARCHAR(100), ObjectType VARCHAR(20), ColName VARCHAR(50), IsGrantOption VARCHAR(10))CREATE TABLE #DatabasePerms (RowNumber INT IDENTITY,UserName VARCHAR(50),PermTypeVARCHAR(20),PermName VARCHAR(50),IsGrantOption VARCHAR(5))DECLARE dbs CURSOR FOR SELECT name FROM master..sysdatabases ORDER BY nameOPEN dbsFETCH NEXT FROM dbs INTO @dbnameWHILE @@FETCH_STATUS = 0BEGINTRUNCATE TABLE #LoginMapTRUNCATE TABLE #RoleUserTRUNCATE TABLE #ObjectPermsTRUNCATE TABLE #DatabasePermsSELECT @strHTML = ‘<BR><CENTER><FONT SIZE=”5?><B> Database ‘ + @dbname + ‘</B></FONT></CENTER><BR>’PRINT @strHTML/* Mapping of logins to users */EXEC(‘INSERT INTO #LoginMapSELECT login.loginname,users.nameFROM [‘+ @dbname+’].dbo.sysusers usersINNER JOIN [master].[dbo].[syslogins] loginON users.[sid] = login.[sid]WHERE users.uid < 16382and users.name not in (”public”,”dbo”,”guest”)‘)SET @strHTML = ”PRINT ‘’–Query the data only if there are rowsIF NOT EXISTS (SELECT 1 FROM #LoginMap)BEGINPRINT ‘’PRINT ‘’ENDELSEBEGINPRINT ‘’PRINT ‘’SELECT @strHTML = @strHTML +‘’ + CHAR(10)FROM #LoginMapORDER BY LoginNamePRINT @strHTMLENDPRINT ‘

Mapping of logins to users
There are no mappings in this database
Mapping of logins to users
Login Name User Name
’ + CONVERT(VARCHAR(50),LoginName) + ‘ ’ +CONVERT(VARCHAR(50),ISNULL(UserName,”)) + ‘

<BR><A CLASS=”Index” HREF=”#_top”>Back To Top ^</A><BR><BR>’/* SQL roles per user */EXEC (‘INSERT INTO #RoleUserSELECT b.name AS Role_name, a.name AS User_name ‘ +‘FROM [‘+ @dbname+’]..sysusers a ‘ +‘INNER JOIN [‘+ @dbname+ ‘]..sysmembers c on a.uid = c.memberuid ‘ +‘INNER JOIN [‘+ @dbname+ ‘]..sysusers b ON c.groupuid = b.uid ‘ +‘WHERE a.name <> ”dbo”’)SET @strHTML = ”PRINT ‘’— Query the data only if there are rows:IF NOT EXISTS(SELECT 1 FROM #RoleUser)BEGINPRINT ‘’PRINT ‘’ENDELSEBEGINPRINT ‘’PRINT ‘’SELECT @strHTML = @strHTML +‘’ + CHAR(10)FROM #RoleUserORDER BY RoLENamePRINT @strHTMLENDPRINT ‘

Roles per user
There are no users mapped to roles in this database
Roles per user
Role Name User Name
’ + CONVERT(VARCHAR(50),RoLEName) + ‘ ’ +CONVERT(VARCHAR(50),ISNULL(UserName,”)) + ‘

<BR><A CLASS=”Index” HREF=”#_top”>Back To Top ^</A><BR><BR>’/* Database level Permissions */EXEC (‘INSERT INTO #DatabasePerms(UserName,PermType,PermName,IsGrantOption)SELECT usr.name,CASE WHEN perm.state <> ”W” THEN perm.state_desc ELSE ”GRANT” END,perm.permission_name,CASE WHEN perm.state != ”W” THEN ”–” ELSE ”X” END AS IsGrantOptionFROM [‘+@dbname+’].sys.database_permissions AS permINNER JOIN[‘+@dbname+’].sys.database_principals AS usrON perm.grantee_principal_id = usr.principal_idWHERE perm.major_id = 0ORDER BY usr.name, perm.permission_name ASC, perm.state_desc ASC’)SET @rc = @@rowcountPRINT ‘’— Query the data only if there are rows:IF NOT EXISTS(SELECT 1 FROM #DatabasePerms)BEGINPRINT ‘’PRINT ‘’ENDELSEBEGINPRINT ‘’PRINT ‘’SET @i = 1WHILE @i’ + CONVERT(VARCHAR(50),UserName) + ‘’ +‘’ + CONVERT(VARCHAR(50),ISNULL(PermType,’–‘)) + ‘’ +‘’ + CONVERT(VARCHAR(50),ISNULL(PermName,’–‘)) + ‘’ +‘’ + CONVERT(VARCHAR(5),ISNULL(IsGrantOption,’–‘)) + ‘’+‘’FROM #DatabasePermsWHERE Rownumber = @iPRINT @strHTMLSET @i = @i + 1ENDENDPRINT ‘

Database level permissions
There are no specific permissions on the database level
Database level permissions
User Name Permission type Permission Name Grant option

<BR><A CLASS=”Index” HREF=”#_top”>Back To Top ^</A><BR><BR>’/* Database object Permissions */EXEC (‘INSERT INTO #ObjectPerms(UserName,PerType,PermName,SchemaName,ObjectName,ObjectType,ColName,IsGrantOption)SELECT usr.name AS UserName,CASE WHEN perm.state <> ”W” THEN perm.state_desc ELSE ”GRANT” END AS PerType,perm.permission_name,USER_NAME(obj.schema_id) AS SchemaName, obj.name AS ObjectName,CASE obj.TypeWHEN ”U” THEN ”Table”WHEN ”V” THEN ”View”WHEN ”P” THEN ”Stored Proc”WHEN ”FN” THEN ”Function”ELSE obj.Type END AS ObjectType,CASE WHEN cl.column_id IS NULL THEN ”–” ELSE cl.name END AS ColName,CASE WHEN perm.state = ”W” THEN ”X” ELSE ”–” END AS IsGrantOptionFROM [‘+@dbname+’].sys.database_permissions AS permINNER JOIN[‘+@dbname+’].sys.objects AS objON perm.major_id = obj.[object_id]INNER JOIN[‘+@dbname+’].sys.database_principals AS usrON perm.grantee_principal_id = usr.principal_idLEFT JOIN[‘+@dbname+’].sys.columns AS clON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_idWHERE obj.Type <> ”S”ORDER BY usr.name, perm.state_desc ASC, perm.permission_name ASC’)SET @rc = @@rowcountPRINT ‘’– Query the data only if there are rows:IF NOT EXISTS(SELECT 1 FROM #ObjectPerms)BEGINPRINT ‘’PRINT ‘’ENDELSEBEGINPRINT ‘’PRINT ‘’SET @i = 1WHILE @i’ + CONVERT(VARCHAR(50),UserName) + ‘’ +‘’ + CONVERT(VARCHAR(50),ISNULL(PerType,’–‘)) + ‘’ +‘’ + CONVERT(VARCHAR(50),ISNULL(PermName,’–‘)) + ‘’ +‘’ + CONVERT(VARCHAR(50),ISNULL(SchemaName,’–‘)) + ‘’ +‘’ + CONVERT(VARCHAR(50),ISNULL(ObjectName,’–‘)) + ‘’ +‘’ + CONVERT(VARCHAR(30),ISNULL(ObjectType,’–‘)) + ‘’ +‘’ + CONVERT(VARCHAR(50),ISNULL(ColName,’–‘)) + ‘’ +‘’ + CONVERT(VARCHAR(5),ISNULL(IsGrantOption,’–‘)) + ‘’FROM #ObjectPermsWHERE Rownumber = @iPRINT @strHTMLSET @i = @i + 1ENDENDPRINT ‘

Object permissions
There are no specific permissions to objects in this database
Object permissions
User Name Permission type Permission Name Schema Name Object Name Object type type Column Name Grant option

<BR><A CLASS=”Index” HREF=”#_top”>Back To Top ^</A><BR><BR>’FETCH NEXT FROM dbs INTO @dbnameEND/* Close cursor and drop all temporary objects */CLOSE dbsDEALLOCATE dbsDROP TABLE #LoginMapDROP TABLE #RoleUserDROP TABLE #ObjectPermsDROP TABLE #DatabasePermsPRINT ‘</BODY></HTML>’GO/* For the result- Execute the stored procedure */USE [Retail Assist 1]GODECLARE @return_value intEXEC @return_value = [dbo].[spAuditUsersPermissions]SELECT ‘Return Value’ = @return_value

GO

 

Read carefuly and i’m not ‘responsible’ for any damage or any whatsoever.

 

Mehboob
— Microsoft Certified Solutions Associate (MCSA)

 

 

To transfer logins and passwords between different versions of SQL Server and then assign logins to roles, follow these steps:

1. Run the following script on the source SQL Server.

USE master
GOIF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = ‘0x’
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = ‘0123456789ABCDEF’
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint – (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GOIF OBJECT_ID (‘sp_help_revlogin_2000_to_2005’) IS NOT NULL
DROP PROCEDURE sp_help_revlogin_2000_to_2005
GO
CREATE PROCEDURE sp_help_revlogin_2000_to_2005@login_name sysname = NULL,
@include_db bit = 0,
@include_role bit = 0AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @dfltdb varchar (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)IF (@login_name IS NULL)
DECLARE login_curs CURSOR STATIC FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), ‘master’)
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] <> ‘sa’
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), ‘master’)
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] = @login_nameOPEN login_cursFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdbIF (@@fetch_status = -1)
BEGIN
PRINT ‘No login(s) found.’
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
ENDSET @tmpstr = ‘/* sp_help_revlogin script ‘
PRINT @tmpstr
SET @tmpstr = ‘** Generated ‘
+ CONVERT (varchar, GETDATE()) + ‘ on ‘ + @@SERVERNAME + ‘ */’
PRINT @tmpstr
PRINT ”
PRINT ”
PRINT ”
PRINT ‘/***** CREATE LOGINS *****/’WHILE @@fetch_status = 0
BEGIN
PRINT ”
SET @tmpstr = ‘– Login: ‘ + @name
PRINT @tmpstrIF (@xstatus & 4) = 4
BEGIN — NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN — NT login is denied access
SET @tmpstr = ” –‘EXEC master..sp_denylogin ”’ + @name + ””
PRINT @tmpstr
END
ELSE
BEGIN — NT login has access
SET @tmpstr = ‘IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ”’ + @name + ”’)’
PRINT @tmpstr
SET @tmpstr = CHAR(9) + ‘CREATE LOGIN [‘ + @name + ‘] FROM WINDOWS’
PRINT @tmpstr
END
END
ELSE
BEGIN — SQL Server authentication
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUTIF (@binpwd IS NOT NULL)
BEGIN — Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
SET @tmpstr = ‘CREATE LOGIN [‘ + @name + ‘] WITH PASSWORD=’ + @txtpwd + ‘ HASHED’
END
ELSE
BEGIN — Null password
SET @tmpstr = ‘CREATE LOGIN [‘ + @name + ‘] WITH PASSWORD=””’
ENDSET @tmpstr = @tmpstr + ‘, CHECK_POLICY=OFF, SID=’ + @SID_string
PRINT @tmpstr
ENDFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
ENDIF @include_db = 1
BEGIN
PRINT ”
PRINT ”
PRINT ”
PRINT ‘/***** SET DEFAULT DATABASES *****/’FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdbWHILE @@fetch_status = 0
BEGIN
PRINT ”
SET @tmpstr = ‘– Login: ‘ + @name
PRINT @tmpstrSET @tmpstr = ‘ALTER LOGIN [‘ + @name + ‘] WITH DEFAULT_DATABASE=[‘ + @dfltdb + ‘]’
PRINT @tmpstrFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
ENDIF @include_role = 1
BEGIN
PRINT ”
PRINT ”
PRINT ”
PRINT ‘/***** SET SERVER ROLES *****/’FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdbWHILE @@fetch_status = 0
BEGIN
PRINT ”
SET @tmpstr = ‘– Login: ‘ + @name
PRINT @tmpstrIF @xstatus &16 = 16 — sysadmin
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”sysadmin”’
PRINT @tmpstr
ENDIF @xstatus &32 = 32 — securityadmin
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”securityadmin”’
PRINT @tmpstr
ENDIF @xstatus &64 = 64 — serveradmin
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”serveradmin”’
PRINT @tmpstr
ENDIF @xstatus &128 = 128 — setupadmin
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”setupadmin”’
PRINT @tmpstr
ENDIF @xstatus &256 = 256 –processadmin
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”processadmin”’
PRINT @tmpstr
ENDIF @xstatus &512 = 512 — diskadmin
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”diskadmin”’
PRINT @tmpstr
ENDIF @xstatus &1024 = 1024 — dbcreator
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”dbcreator”’
PRINT @tmpstr
ENDIF @xstatus &4096 = 4096 — bulkadmin
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”bulkadmin”’
PRINT @tmpstr
ENDFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
ENDCLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GOexec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
GO2.

Save the output, and then paste and run the output in SQL Server Management Studio on the destination SQL Server 2005.

Note If the source SQL Server contains a login that has a blank password, the output contains a statement that resembles the following.

CREATE LOGIN LoginName WITH PASSWORD = ”, CHECK_POLICY = OFF, SID = MySID

 

News Worldwide e-Books

Microsoft System Center Deploying Hyper-V with Software-Defined Storage & Networking
Microsoft TechNet and the Cloud Platform Team; Series Editor: Mitch Tulloch
March 2015
236 pages
This ebook covers a variety of aspects that make up the foundation of the software-defined datacenter: virtualization, storage, and networking. By the end, you will have a fully operational, small-scale configuration that will enable you to proceed with evaluation of your own key workloads, experiment with additional features and capabilities, and continue to build your knowledge.
Microsoft System Center Software Update Management Field Experience
Microsoft System Center Software Update Management Field Experience
Andre Della Monica, Chris Shilt, Russ Rimmerman, and Rushi Faldu; Series Editor: Mitch Tulloch
March 2015
160 pages
This ebook addresses some of the gaps and pain points you might encounter when implementing, administering, and troubleshooting Software Updates using Configuration Manager 2012 R2. The advice is based on the authors’ experiences working as Premier Field Engineers and Microsoft Consultants in customer environments on a daily basis. Enjoy this book and the shared experiences from the field.

Windows Server ebooks

Introducing Windows Server 2012 R2
Mitch Tulloch
November 2013
242 pages
Get a head start evaluating Windows Server 2012 R2—with technical insights from a Microsoft MVP and members of the Windows Server product team. Based on final Windows Server 2012 R2 release-to-manufacturing (RTM) software, this guide introduces new features and capabilities, with scenario-based advice on how the platform can meet the needs of your business. Get the high-level overview you need to begin preparing your deployment now.

Microsoft Azure ebooks

Microsoft Azure Essentials: Fundamentals of Azure
Microsoft Azure Essentials: Fundamentals of Azure
Michael S. Collier and Robin E. Shahan
February 2015
246 pages
The “Microsoft Azure Essentials” series helps you advance your technical skills with Microsoft Azure. “Microsoft Azure Essentials: Fundamentals of Azure” introduces developers and IT professionals to the wide range of capabilities in Azure. The authors—both Microsoft MVPs in Azure—present conceptual and how-to content for seven key areas and describe management tools and business cases.
Building Cloud Apps with Microsoft Azure
Building Cloud Apps with Microsoft Azure
Scott Guthrie, Mark Simms, Tom Dykstra, Rick Anderson, and Mike Wasson
July 2014
198 pages
This ebook walks you through a patterns-based approach to building real-world cloud solutions. The patterns apply to the development process as well as to architecture and coding practices. The content is based on a presentation developed by Scott Guthrie and delivered by him at the Norwegian Developers Conference (NDC) in June of 2013 (part 1, part 2), and at Microsoft Tech Ed Australia in September 2013 (part 1, part 2). Many others updated and augmented the content while transitioning it from video to written form.
Introducing Microsoft Azure HDInsight
Introducing Microsoft Azure HDInsight
Avkash Chauhan, Valentine Fontama, Michele Hart, Wee Hyong Tok, and Buck Woody
May 2014
130 pages
In Introducing Microsoft Azure HDInsight, we cover what big data really means, how you can use it to your advantage in your company or organization, and one of the services you can use to do that quickly—specifically, Microsoft’s HDInsight service. We start with an overview of big data and Hadoop, but we don’t emphasize only concepts in this book—we want you to jump in and get your hands dirty working with HDInsight in a practical way.
Introducing Windows Azure for IT Professionals
Introducing Windows Azure for IT Professionals
Mitch Tulloch with the Windows Azure Team
October 2013
142 pages
Get a high-level overview of Windows Azure—and get a running start with this flexible cloud platform. Designed for IT professionals, this guide introduces core features and functionality, with technical insights and advice from a Microsoft MVP and members of the Windows Azure team. Topics include Windows Azure solutions, Compute services, Network services, Data services, and App services.
Rethinking Enterprise Storage: A Hybrid Cloud Model
Rethinking Enterprise Storage: A Hybrid Cloud Model
Marc Farley
July 2013
120 pages
Discover how a hybrid cloud model—combining cloud storage services with traditional on-premise storage—can solve multiple storage challenges cost-effectively, all under a single data management system. Focusing on the Microsoft hybrid cloud storage solution, this book offers concise insights on technical, strategic, and practical considerations—so you can begin evaluating potential benefits to your organization.
Windows ebooks
Introducing-Windows-8.1-for-IT-Professionals
Introducing Windows 8.1 for IT Professionals
Ed Bott
October 2013
140 pages
Get a head start evaluating Windows 8.1—with early technical insights from award-winning journalist and Windows expert Ed Bott. This guide introduces new features and capabilities, providing a practical, high-level overview for IT professionals ready to begin deployment planning now.
App Development ebooks
Creating Mobile Apps with Xamarin.Forms, Preview Edition
Creating Mobile Apps with Xamarin.Forms, Preview Edition
Charles Petzold
October 2014
272 pages
This Preview Edition ebook is about writing applications for Xamarin.Forms, the new mobile development platform for iOS, Android, and Windows Phone unveiled by Xamarin in May 2014. Xamarin.Forms lets you write shared user-interface code in C# and XAML that maps to native controls on these three platforms.
Programming Windows Store Apps with HTML, CSS and JavaScript
Programming Windows Store Apps with HTML, CSS and JavaScript
Kraig Brockschmidt
April 2014
1,311 pages
Microsoft Press is pleased to offer the second edition of Kraig Brockschmidt’s in-depth ebook on writing Windows Store apps using HTML, CSS3, and JavaScript on the Windows 8.1 platform. The ebook includes 20 chapters and 4 appendices. We’re delighted to welcome you into a world of Windows Reimagined!
.NET Technology Guide for Business Applications
.NET Technology Guide for Business Applications
Cesar de la Torre and David Carmona
November 2013
This guide will help you select the right Microsoft development technologies and approaches for your custom .NET application development, depending on the priorities you have for your application and business domain. It is targeted to decision makers, software architects, development leads, and developers who are involved in selecting which technologies to use for their applications and projects, and specifically covers custom enterprise application development, although independent software vendors (ISVs) might also find the information and recommendations useful.
Microsoft System Center ebooks
Microsoft System Center Deploying Hyper-V with Software-Defined Storage and Networking
Microsoft System Center Deploying Hyper-V with Software-Defined Storage & Networking
Microsoft TechNet and the Cloud Platform Team; Series Editor: Mitch Tulloch
March 2015
236 pages
This ebook covers a variety of aspects that make up the foundation of the software-defined datacenter: virtualization, storage, and networking. By the end, you will have a fully operational, small-scale configuration that will enable you to proceed with evaluation of your own key workloads, experiment with additional features and capabilities, and continue to build your knowledge.
Microsoft System Center Software Update Management Field Experience
Microsoft System Center Software Update Management Field Experience
Andre Della Monica, Chris Shilt, Russ Rimmerman, and Rushi Faldu; Series Editor: Mitch Tulloch
March 2015
160 pages
This ebook addresses some of the gaps and pain points you might encounter when implementing, administering, and troubleshooting Software Updates using Configuration Manager 2012 R2. The advice is based on the authors’ experiences working as Premier Field Engineers and Microsoft Consultants in customer environments on a daily basis. Enjoy this book and the shared experiences from the field.
Microsoft System Center Introduction to Microsoft Automation Solutions
Microsoft System Center Introduction to Microsoft Automation Solutions
Rob Costello, Richard Maunsell; Mitch Tulloch, Series Editor
December 2014
112 pages
Get advice from experts in the field on how to use Microsoft automation solutions! This free ebook introduces you to two Microsoft Automation solutions: Azure Automation and Service Management Automation. It explores both of these tools and how they can be used to meet the automation needs of your Microsoft Azure cloud solutions or your enterprise datacenter environments.
Microsoft System Center Extending Operations Manager Reporting
Microsoft System Center Extending Operations Manager Reporting
George Wallace, Chris Jones, Bill May, and Fred Lee; Series editor: Mitch Tulloch
November 2014
112 pages
This free ebook dives into how to extend the reporting capabilities within System Center Operations Manager. By using this information, Operations Manager administrations will have a more comprehensive approach to providing custom reports tailored to their environments. Learn how to create these custom reports and package them up in management packs for deployment.
Microsoft System Center: Integrated Cloud Platform
Microsoft System Center: Integrated Cloud Platform
David Ziembicki and Mitch Tulloch, Series Editor
April 2014
80 pages
This book is organized by cloud type and begins with a short overview of the Cloud OS strategy from Microsoft and a high-level hybrid cloud architecture. It also covers the design and deployment of private cloud solutions using Windows and System Center to deliver the software-defined datacenter where storage, network, compute, and management are all virtualized and delivered by the Microsoft platform.
Microsoft System Center: Network Virtualization and Cloud Computing
Microsoft System Center: Network Virtualization and Cloud Computing
Mitch Tulloch with Nader Benmessaoud, CJ Williams, Uma Mahesh Mudigonda
March 2014
94 pages
This brief book identifies some key usage and deployment scenarios for cloud computing to provide some deep technical background on the Microsoft SDN solution, enabling IT professionals to quickly learn the internals of HNV, how it works from end to end, and where and how it should be used.
Microsoft System Center: Building a Virtualized Network Solution
Microsoft System Center: Building a Virtualized Network Solution
Mitch Tulloch with Nigel Cain, Alvin Morales, Michel Luescher, Damian Flynn
February 2014
136 pages
This book is geared to private and hybrid cloud architects preparing to design and build a virtualized network solution based on Windows Server 2012 and System Center 2012 SP1, or later. Written by experts on the Microsoft System Center team and with Microsoft MVP Mitch Tulloch as series editor, this title focuses on architecture and design.
Introducing Microsoft System Center 2012 R2
Introducing Microsoft System Center 2012 R2
Mitch Tulloch with Symon Perriman and the System Center team
January 2014
180 pages
Get a high-level overview of System Center 2012 R2—and get a running start transforming your datacenter and cloud-optimizing your business. This guide introduces core features and functionality, with technical advice and under-the-hood insights from a Microsoft MVP and members of the System Center team at Microsoft.
Microsoft System Center: Designing Orchestrator RunbooksMicrosoft System Center: Designing Orchestrator Runbooks
David Ziembicki, Aaron Cushner, Andreas Rynes and series editor Mitch Tulloch
September 2013
186 pages
Part of a series of specialized guides on System Center—this book delivers a focused drilldown on designing Orchestrator runbooks for workflow management solutions. Written by experts on the System Center team and with Microsoft MVP Mitch Tulloch as series editor, these titles provide concise technical guidance as they step you through key design concepts, criteria, and tasks.
Microsoft System Center: Configuration Manager Field Experience
Microsoft System Center: Configuration Manager Field Experience
Rushi Faldu, Manish Raval, Brandon Linton, Kaushal Pandey and series editor Mitch Tulloch
September 2013
92 pages
Part of a series of specialized guides on System Center—this book delivers a focused drilldown on using Configuration Manager for queries and custom reporting, with scenario-based guidance for deployment success. Written by experts on the Microsoft System Center team and with Microsoft MVP Mitch Tulloch as series editor, this title provides concise, from-the-field guidance as you step through key concepts and tasks.
Microsoft System Center: Cloud Management with App Controller
Microsoft System Center: Cloud Management with App Controller
Yung Chou, Keith Mayer, and series editor Mitch Tulloch
November 2013
118 pages
Part of a series of specialized guides on System Center—this book focuses on core tasks for App Controller to implement and manage hybrid cloud solutions. Written by experts on the Microsoft System Center team and with Microsoft MVP Mitch Tulloch as series editor, this title delivers concise technical guidance as you step through key concepts, processes, and tasks.
Microsoft System Center: Troubleshooting Configuration Manager
Microsoft System Center: Troubleshooting Configuration Manager
Rushi Faldu, Manoj Kumar Pal, Andre Della Monica, Kaushal Pandey, and series editor Mitch Tulloch
November 2013
108 pages
Part of a series of specialized guides on System Center—this book addresses the most common pain points for Configuration Manager administrators, providing insider and from-the-field insights to help you succeed. Written by experts on the Microsoft System Center team and with Microsoft MVP Mitch Tulloch as series editor, this title delivers concise technical guidance as you step through key concepts and tasks.
Microsoft System Center: Optimizing Service Manager
Microsoft System Center: Optimizing Service Manager
Thomas Ellermann, Kathleen Wilson, Karsten Nielsen, John Clark, and series editor Mitch Tulloch
December 2013
96 pages
Part of a series of specialized guides on System Center—this book provides a framework for planning and delivering a successful Service Manager project. Written by experts on the Microsoft System Center team and with Microsoft MVP Mitch Tulloch as series editor, this title delivers concise guidance, from-the-field insights, and best practices for optimizing and maintaining your Service Manager environment.

 Back to topSQL Server 2014Windows Server 2012 R2System Center 2012 R2 Configuration ManagerWindows Server 2012 R2 Essentials

Microsoft Azure one-month trial

Windows 8.1 EnterpriseSystem Center 2012 R2Hyper-V Server 2012 R2Microsoft Intune 30-day trial

Windows Azure Pack

Office 2016 for Mac Preview is now available: Watch a demo of the new features

Office 2016 for Mac Preview is now available. Check out this episode from Office Mechanics to get an overview of the new features. You’ll see power user features in Excel, threaded comments and formatting enhancements in Word, changes to Outlook and OneNote, and more.

How to install and configure Azure PowerShell

Technical Evangelist Yung Chou steps you through the installation and configuration of Azure PowerShell. Learn two ways to connect PowerShell with your Azure subscription: Using Azure Active Directory and using a publish-settings file. Start your free Azure trial and follow along.

Discover how Microsoft Azure can help simplify your approach to rapid disaster recovery

Planning a disaster recovery solution can be a daunting (and expensive) task. It often involves redundant datacenters, data replication, and complex orchestration. Azure Site Recovery, however, delivers an easier (and less costly) solution that works for organizations of all sizes.

Moving from IaaS to PaaS: An Interview with Todd Gardner (co-founder of Track:js)

On this episode of TechNet Radio, Matt Hester welcomes Todd Gardner to discuss how Track:js migrated from IaaS to PaaS. Gardner shares some of the benefits the company experienced as a result of this move. Learn more about Microsoft Azure with a 30-day free trial.

Office Mechanics: Get an overview of updates to the new Office 365 Admin app

Office Mechanics takes a look at the Office 365 Admin app – available for Android, iOS, and Windows Phone – and provides an overview of new features found in the latest update. See how you can reset passwords, add and delete users, view service health, track open service tickets, and more.

Step-by-step guide: Global delivery with Azure CDN

The Azure Content Delivery Network sends audio, video, applications, and other files faster and more reliably using servers that are closest to each user. Learn more about Azure CDN, see how it improves the user experience, and find out how to configure it for your environment.

Q&A: Top questions and answers from IT pros about Azure AD

The IT professionals who attended the recent Azure AD Core Skills Jump Start session asked some excellent – and common – questions. Check out this episode of the Edge Show for a roundup of the top questions and answers.

Enterprise mobility core skills – monthly training sessions available through June 2015

Windows 10 is approaching. Now is the time to learn everything you need to know to master and deploy enterprise mobility management. In each session, Brad Anderson and Simon May delve into Enterprise Mobility Suite to give you hands-on experience to help you build core skills.Register now.

Back to topMS SQL 2014 Trace Flag: Trace flagDescription260Prints versioning information about extended stored procedure dynamic-link libraries (DLLs). For more information about __GetXpVersion(), see Creating Extended Stored Procedures.634Disables the background columnstore compression task. SQL Server periodically runs a background task that compresses columnstore index rowgroups with uncompressed data, one such rowgroup at a time. Columnstore compression improves query performance but also consumes system resources. You can control the timing of columnstore compression manually, by disabling the background compression task with trace flag 634, and then explicitly invoking ALTER INDEX REORGANIZE or ALTER INDEX REBUILD at the time of your choice.1118Removes most single page allocations on the server, reducing contention on the SGAM page. When a new object is created, by default, the first eight pages are allocated from different extents (mixed extents). Afterwards, when more pages are needed, those are allocated from that same extent (uniform extent). The SGAM page is used to track these mixed extents, so can quickly become a bottleneck when numerous mixed page allocations are occurring. This trace flag allocates all eight pages from the same extent when creating new objects, minimizing the need to scan the SGAM page.1204Returns the resources and types of locks participating in a deadlock and also the current command affected.  1211Disables lock escalation based on memory pressure, or based on number of locks. The SQL Server Database Engine will not escalate row or page locks to table locks.Using this trace flag can generate excessive numbers of locks. This can slow the performance of the Database Engine, or cause 1204 errors (unable to allocate lock resource) because of insufficient memoryIf both trace flag 1211 and 1224 are set, 1211 takes precedence over 1224. However, because trace flag 1211 prevents escalation in every case, even under memory pressure, we recommend that you use 1224. This helps avoid “out-of-locks” errors when many locks are being used.1222Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.1224Disables lock escalation based on the number of locks. However, memory pressure can still activate lock escalation. The Database Engine escalates row or page locks to table (or partition) locks if the amount of memory used by lock objects exceeds one of the following conditions:

  • Forty percent of the memory that is used by Database Engine. This is applicable only when the locks parameter of sp_configure is set to 0.
  • Forty percent of the lock memory that is configured by using the locks parameter ofsp_configure. More information, Server Configuration Options (SQL Server).

If both trace flag 1211 and 1224 are set, 1211 takes precedence over 1224. However, because trace flag 1211 prevents escalation in every case, even under memory pressure, we recommend that you use 1224. This helps avoid “out-of-locks” errors when many locks are being used.NoteLock escalation to the table- or HoBT-level granularity can also be controlled by using the LOCK_ESCALATION option of the ALTER TABLE statement.1448Enables the replication log reader to move forward even if the async secondaries have not acknowledged the reception of a change. Even with this trace flag enabled the log reader always waits for the sync secondaries. The log reader will not go beyond the min ack of the sync secondaries. This trace flag applies to the instance of SQL Server, not just an availability group, an availability database, or a log reader instance. Takes effect immediately without a restart. This trace flag can be activated ahead of time or when an async secondary fails.2528Disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE.By default, the degree of parallelism is automatically determined by the query processor. The maximum degree of parallelism is configured just like that of parallel queries. For more information, see Configure the max degree of parallelism Server Configuration Option.Parallel DBCC should typically be left enabled. For DBCC CHECKDB, the query processor reevaluates and automatically adjusts parallelism with each table or batch of tables checked. Sometimes, checking may start when the server is almost idle. An administrator who knows that the load will increase before checking is complete may want to manually decrease or disable parallelism.Disabling parallel checking of DBCC can cause DBCC to take much longer to complete and if DBCC is run with the TABLOCK feature enabled and parallelism set off, tables may be locked for longer periods of time.3042Bypasses the default backup compression pre-allocation algorithm to allow the backup file to grow only as needed to reach its final size. This trace flag is useful if you need to save on space by allocating only the actual size required for the compressed backup. Using this trace flag might cause a slight performance penalty (a possible increase in the duration of the backup operation).For more information about the pre-allocation algorithm, see Backup Compression (SQL Server).3205By default, if a tape drive supports hardware compression, either the DUMP or BACKUP statement uses it. With this trace flag, you can disable hardware compression for tape drivers. This is useful when you want to exchange tapes with other sites or tape drives that do not support compression.3226By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you create very frequent log backups, these success messages accumulate quickly, resulting in huge error logs in which finding other messages is problematic.With this trace flag, you can suppress these log entries. This is useful if you are running frequent log backups and if none of your scripts depend on those entries.3608Prevents SQL Server from automatically starting and recovering any database except the masterdatabase. If activities that require tempdb are initiated, then model is recovered and tempdb is created. Other databases will be started and recovered when accessed. Some features, such as snapshot isolation and read committed snapshot, might not work. Use for Move System Databasesand Move User Databases. Do not use during normal operation.3625Limits the amount of information returned to users who are not members of the sysadmin fixed server role, by masking the parameters of some error messages using ‘******’. This can help prevent disclosure of sensitive information.4199Controls multiple query optimizer changes previously made under multiple trace flags. More information, this Microsoft Support article4616Makes server-level metadata visible to application roles. In SQL Server, an application role cannot access metadata outside its own database because application roles are not associated with a server-level principal. This is a change of behavior from earlier versions of SQL Server. Setting this global flag disables the new restrictions, and allows for application roles to access server-level metadata.6527Disables generation of a memory dump on the first occurrence of an out-of-memory exception in CLR integration. By default, SQL Server generates a small memory dump on the first occurrence of an out-of-memory exception in the CLR. The behavior of the trace flag is as follows:

  • If this is used as a startup trace flag, a memory dump is never generated. However, a memory dump may be generated if other trace flags are used.
  • If this trace flag is enabled on a running server, a memory dump will not be automatically generated from that point on. However, if a memory dump has already been generated due to an out-of-memory exception in the CLR, this trace flag will have no effect.

7806Enables a dedicated administrator connection (DAC) on SQL Server Express. By default, no DAC resources are reserved on SQL Server Express. For more information, see Diagnostic Connection for Database Administrators.Scope: global only   8032Reverts the cache limit parameters to the SQL Server 2005 RTM setting which in general allows caches to be larger. Use this setting when frequently reused cache entries do not fit into the cache and when the optimize for ad hoc workloads Server Configuration Option has failed to resolve the problem with plan cache. Trace flag 8032 can cause poor performance if large caches make less memory available for other memory consumers, such as the buffer pool.8207Enables singleton updates for Transactional Replication. Updates to subscribers can be replicated as a DELETE and INSERT pair. This might not meet business rules, such as firing an UPDATE trigger. With trace flag 8207 an update to a unique column that affects only one row (a singleton update) is replicated as an UPDATE and not as a DELETE or INSERT pair. If the update affects a column on which has a unique constraint or if the update affects multiple rows, the update is still replicated as a DELETE or INSERT pair.9485Disables SELECT permission for DBCC SHOW_STATISTICS.Back to topTrace Flag 834*Trace flag 834 allows SQL Server 2005 to use large-page allocations for the memory that is allocated for the buffer pool.*May prevent the server from starting if memory is fragmented and if large pages cannot be allocated*Best suited for servers that are dedicated to SQL Server 2005*Page size varies depending on the hardware platform*Page size varies from 2 MB to 16 MB.*Improves performance by increasing the efficiency of the translation look-aside buffer (TLB) in the CPU*Only applies to 64-bit architecture*Startup*Documented: KB920093*Now automatic:*Enterprise / Developer Edition*“Lock Pages in Memory” privilege*>= 8GB RAMTrace Flag 835*Trace flag 835 enables “Lock Pages in Memory” support for SQL Server Standard Edition*Enables SQL Server to use AWE APIs for buffer pool allocation*Avoids potential performance issues due to trimming working set* More info visit*Introduced in:*SQL Server 2005 Service pack 3 Cumulative Update 4*SQL Server 2008 Service Pack 1 Cumulative Update 2*Only applies to 64-bit architecture*Startup*Documented: KB970070Trace Flag 3226*Trace flag 3226 prevents successful back operations from being logged*By default SQL Server logs every successful backup operation to the ERRORLOG andthe System event log*Frequent backup operations can cause log files to grow and make finding othermessages harderDocumented: BOLTrace Flag 806*Trace Flag 806 enables DBCC audit checks to be performed on pages to test for logical consistency problems.*These checks try to detect when a read operation from a disk does not experience any errors but the read operation returns data that is not valid.*Pages will be audited every time that they are read from disk*Page auditing can affect performance and should only be used in systems where data Stability is in question.Documented: KB841776“SQL Server I/O Basics, ” white paper
Trace Flag 818*“Trace flag 818 enables an in-memory ring buffer that is used for tracking the last 2,048successful write operations that are performed by the computer running SQL Server, notincluding sort and workfile I/Os”*Use to further diagnose operating system, driver, or hardware problems causing lost writeconditions or stale read conditions*May see data integrity-related error messages such as errors 605, 823, 3448.*Documented: KB826433Trace Flag 3422*Trace Flag 3422 enables log record auditing*“Troubleshooting a system that is experiencing problems with log file corruption may be easier using the additional log record audits this trace flag provides”*“Use this trace flag with caution as it introduces overhead to each transaction log record”*Similarly to trace flag 806, you would only usethis to troubleshoot corruption problemsDocumented:“SQL Server I/O Basics, ” white paper
Trace Flag 2528*Trace flag 2528 disables parallel checking ofobjects during DBCC CHECKDB, DBCCCHECKFILEGROUP and DBCC CHECKTABLE.*Scope: Global | Local*Documented: BOL*Typically leave parallel DBCC checks enabled*DBCC operations can dynamically change theirdegree of parallelism*Alternatives:*MAXDOP option*Resource GovernorTrace Flag 1224*Trace flag 1224 disables lock escalation based on the number of locks*Memory pressure can still trigger lock escalation*Database engine will escalate row or page locks to table locks*40% of memory available for locking*sp_configure ‘locks’*Non-AWE memory*Scope: Global | Session*Documented: BOLTrace Flag 1211*Trace flag 1211 disables lock escalation based on memory pressure or number of locks*Database engine will not escalate row or page locks to table locks*Scope: Global | Session*Documented: BOL*Trace flag 1211 takes precedence over 1224*Microsoft recommends using 1224*Trace flag 1211 prevents escalation in every case, even undermemory pressure*Helps avoid “out-of-locks” errors when many locks are being used.*Can generate excessive number of locks”” Can slow performance “”*Cause 1204 errorsTrace Flag 1118*Trace flag 1118 directs SQL Server to allocate full extents to each tempdb objects (instead of mixedextents)*Less contention on internal structures such as SGAM pages*Story has improved in subsequent releases of SQLServer*So represents a “edge case”Scope: GlobalDocumented: KB328551, KB936185Back to topIndexes and their usage detailsIt’s very important for a DBA to know how the indexes of tables are doing, the below script will let you know the usage of indexes. Based on the column “total_scan” I’ll be able to tell which indexes are mostly used and what is the associated table. This information makes a DBA to keep an eye on those indexes because fragmentations of them may create a performance bottleneck. Here is syntax, use at your risk, sqlboob didn’t take any responsibilty.SELECTo.name [Object_Name],SCHEMA_NAME(o.[schema_id]) [Schema_name],DB_NAME(s.database_id) Database_Name,i.name Index_name,i.Type_Desc,s.user_seeks,s.user_scans,s.user_lookups,(s.user_seeks + s.user_scans) AS total_scan,s.user_updates,s.system_seeks,s.system_scans,s.system_lookups,getdate() Stat_DateFROMsys.objects AS o INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id]INNER JOIN sys.dm_db_index_usage_stats AS s ON i.[object_id] = s.[object_id]AND i.index_id = s.index_id AND DB_ID() = s.database_idWHEREo.type = ‘u’AND i.type IN (1, 2)AND (s.user_seeks > 0 OR s.user_scans > 0 OR s.user_lookups > 0OR s.system_seeks > 0 OR s.system_scans > 0 OR s.system_lookups > 0)ORDER BY (s.user_seeks + s.user_scans) DESC;Missing IndexesThe below script will let you know the missing indexes in the selected database, this script is also an important for DBA to know time to time that what are the missing indexes are there because the information of below script will be generated by the actual usage of buffer pool.SELECTS.AVG_TOTAL_USER_COST * (S.AVG_USER_IMPACT / 100.0) * (S.USER_SEEKS + S.USER_SCANS) AS EST_IMPROVE,S.AVG_USER_IMPACT, LEFT (PARSENAME(D.STATEMENT, 1), 32) TABLE_NAME,ISNULL (D.EQUALITY_COLUMNS,”) + CASE WHEN D.EQUALITY_COLUMNS IS NOT NULL AND D.INEQUALITY_COLUMNS IS NOT NULL THEN ‘,’ELSE ” END + ISNULL (D.INEQUALITY_COLUMNS, ”) COLUMN_NAMES, ISNULL (D.INCLUDED_COLUMNS , ”) AS INCLUDE_COLUMNS,S.UNIQUE_COMPILES, S.USER_SEEKS, S.USER_SCANS, S.LAST_USER_SEEK, S.LAST_USER_SCAN, S.AVG_TOTAL_USER_COST,S.SYSTEM_SEEKS, S.SYSTEM_SCANS, S.LAST_SYSTEM_SEEK, S.LAST_SYSTEM_SCAN, S.AVG_TOTAL_SYSTEM_COST, S.AVG_SYSTEM_IMPACTFROMSYS.DM_DB_MISSING_INDEX_GROUPS G INNER JOIN SYS.DM_DB_MISSING_INDEX_GROUP_STATS S ON S.GROUP_HANDLE = G.INDEX_GROUP_HANDLEINNER JOIN SYS.DM_DB_MISSING_INDEX_DETAILS D ON G.INDEX_HANDLE = D.INDEX_HANDLEORDER BY EST_IMPROVE DESC ;Index Property table wiseThe below script will let you know the count of clustered, non-clustered indexes on a table and index size.SELECTsys.tables.name AS [TABLE],sys.tables.create_date AS CREATE_DATE,sys.tables.modify_date AS MODIFY_DATE,CASE WHEN sys.database_principals.name IS NULL THEN SCHEMA_NAME(sys.tables.schema_id) ELSEsys.database_principals.name END AS OWNER,SUM(ISNULL(CASE INDEXES.TYPE WHEN 0 THEN COUNT_TYPE END, 0)) AS COUNT_HEAP_INDEX,SUM(ISNULL(CASE INDEXES.TYPE WHEN 1 THEN COUNT_TYPE END, 0)) AS COUNT_CLUSTERED_INDEX,SUM(ISNULL(CASE INDEXES.TYPE WHEN 2 THEN COUNT_TYPE END, 0)) AS COUNT_NONCLUSTERED_INDEX,SUM(ISNULL(CASE INDEXES.TYPE WHEN 3 THEN COUNT_TYPE END, 0)) AS COUNT_XML_INDEX,SUM(ISNULL(CASE INDEXES.TYPE WHEN 4 THEN COUNT_TYPE END, 0)) AS COUNT_SPATIAL_INDEX,sys.tables.max_column_id_used AS COUNT_COLUMNS,sys.partitions.rows AS COUNT_ROWS,SUM(ISNULL(CASE WHEN sys.allocation_units.type <> 1 THEN USED_PAGES WHEN SYS.partitions.INDEX_ID < 2 THEN DATA_PAGES ELSE 0 END, 0)) *(SELECT low / 1024 AS VALUE FROM master.dbo.spt_values WHERE (number = 1) AND (type = N’E’)) AS SIZE_DATA_KB,SUM(ISNULL(sys.allocation_units.used_pages – CASE WHEN sys.allocation_units.type <> 1 THEN USED_PAGESWHEN SYS.partitions.INDEX_ID < 2 THEN DATA_PAGES ELSE 0 END, 0)) * (SELECT low / 1024 AS VALUEFROMmaster.dbo.spt_values AS spt_values_2WHERE (number = 1)AND (type = N’E’)) AS SIZE_INDEX_KBFROMsys.allocation_units INNER JOIN sys.partitions ON sys.allocation_units.container_id = sys.partitions.partition_idINNER JOIN(SELECT TOP (100) PERCENT object_id, index_id, type AS TYPE, COUNT(*) AS COUNT_TYPEFROM sys.indexes AS indexes_1 GROUP BY object_id, type, index_id ORDER BY object_id) AS INDEXES ONsys.partitions.object_id = INDEXES.object_idAND sys.partitions.index_id = INDEXES.index_idRIGHT OUTER JOIN sys.database_principalsRIGHT OUTER JOIN sys.tables ON sys.database_principals.principal_id = sys.tables.principal_idON INDEXES.object_id = sys.tables.object_idGROUP BY sys.tables.name, sys.tables.create_date, sys.tables.modify_date,CASE WHEN sys.database_principals.name IS NULL THEN SCHEMA_NAME(sys.tables.schema_id) ELSEsys.database_principals.name END,sys.tables.max_column_id_used, sys.partitions.rowsORDER BY COUNT_ROWS DESC;Find Longest Running Query:
It was interesting and I have given the longest running query to concern person but at the same time I thought that how many ways do I have to get it. I have found three different ways to get the same result. And they are:1:DBCC FREEPROCCACHE — DBCC command will clean the buffer.SELECT DISTINCT TOP 1
est.[text] AS SQLStatement,
eqs.execution_count AS SQLExecutionCount,
eqs.max_elapsed_time AS SQLMaximumElapsedTime,
ISNULL(eqs.total_elapsed_time / eqs.execution_count, 0) AS SQLAverageElapsedTime
FROM sys.dm_exec_query_stats eqs CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle ) est
ORDER BY eqs.max_elapsed_time DESC
GO2: SELECT * FROM master..sysprocesses WHERE status = ‘runnable’ ORDER BY cpu desc;DBCC INPUTBUFFER (spid) — SPID from the above query result output3: DECLARE @p_handle binary(20)SELECT @p_handle = sql_handle FROM master..sysprocesses
where spid = (select top 1 spid from sys.sysprocesses ORDER BY cpu desc);SELECT [text] FROM ::fn_get_sql(@p_handle);Indexing best practices:1) Periodically identify potentially missing indexes.2) Drop indexes that are never used.3) Don’t create redundant indexes.4) Generally every table should have at least a clustered index, but not always. The clustered index should be on a column that monotonically increases and value is unique.5) You can only create one clustered index per table, think carefully how it will be used.6) When you create a composite index, make the most selective column the first column of the index.7) Keep the “width” of your indexes as narrow as possible. It’ll reduce the size of the index and reduces the number of disk I/O to improve the performance.8) Avoid adding a clustered index on VARCHAR & NVARCHAR column with large width, which makes the index larger, it’ll increase I/O reads, and can hurt performance.9) Indexes should be considered on those columns which are frequently accessed by the JOIN, WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.10) Add indexes if you know that they will be used by queries run against the table.11) When creating indexes, try to make them unique indexes if possible. SQL Server can often search through a unique index faster than a non-unique index.12) Joins between two or more tables in your queries, performance will be optimized if each of the joined columns has proper indexes.13) A high FILL FACTOR is good for seldom changed data, so consider carefully index FILL FACTOR.14) Don’t over index your OLTP tables.Have a good day.Back to top
SQL Server 2014 – DBA Daily task …DBA on daily basis.1) Check System Event Logs and SQL Server Error Logs for unusual events.2) Verify that all scheduled jobs have run successfully.3) Confirm that backups have been made and successfully and moved to the backup storage or secure location.4) Monitor disk space to make sure SQL Servers will not run out of disk space.5) Periodically monitor performance of database and system using both System Monitor and SQL Server Profiler.6) Monitor and identify blocking issues.7) Keep a log of any changes you make to servers, instance setting, database setting and monitoring script.8) Create SQL Server alerts to notify you of problems, and e-mailed to you. Take action as needed.9) Regularly restore backups to a test server in order to verify that you can restore them.Indexing best practices1) Periodically identify potentially missing indexes.2) Drop indexes that are never used.3) Don’t create redundant indexes.4) Generally every table should have at least a clustered index, but not always. The clustered index should be on a column that monotonically increases and value is unique.5) You can only create one clustered index per table, think carefully how it will be used.6) When you create a composite index, make the most selective column the first column of the index.7) Keep the “width” of your indexes as narrow as possible. It’ll reduce the size of the index and reduces the number of disk I/O to improve the performance.8) Avoid adding a clustered index on VARCHAR & NVARCHAR column with large width, which makes the index larger, it’ll increase I/O reads, and can hurt performance.9) Indexes should be considered on those columns which are frequently accessed by the JOIN, WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.10) Add indexes if you know that they will be used by queries run against the table.11) When creating indexes, try to make them unique indexes if possible. SQL Server can often search through a unique index faster than a non-unique index.12) Joins between two or more tables in your queries, performance will be optimized if each of the joined columns has proper indexes.13) A high FILL FACTOR is good for seldom changed data, so consider carefully index FILL FACTOR.14) Don’t over index your OLTP tables.Indexes and their usage detailsIndex Usage

It’s very important for a DBA to know how the indexes of tables are doing, the below script will let you know the usage of indexes. Based on the column “total_scan” I’ll be able to tell which indexes are mostly used and what is the associated table. This information makes a DBA to keep an eye on those indexes because fragmentations of them may create a performance bottleneck. Here is syntax, use on your risk, addarr not take any responsibilty …

SELECT

o.name [Object_Name],

SCHEMA_NAME(o.[schema_id]) [Schema_name],

DB_NAME(s.database_id) Database_Name,

i.name Index_name,

i.Type_Desc,

s.user_seeks,

s.user_scans,

s.user_lookups,

(s.user_seeks + s.user_scans) AS total_scan,

s.user_updates,

s.system_seeks,

s.system_scans,

s.system_lookups,

getdate() Stat_Date

FROM

sys.objects AS o INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id]

INNER JOIN sys.dm_db_index_usage_stats AS s ON i.[object_id] = s.[object_id]

AND i.index_id = s.index_id AND DB_ID() = s.database_id

WHERE

o.type = ‘u’

AND i.type IN (1, 2)

AND (s.user_seeks > 0 OR s.user_scans > 0 OR s.user_lookups > 0

OR s.system_seeks > 0 OR s.system_scans > 0 OR s.system_lookups > 0)

ORDER BY (s.user_seeks + s.user_scans) DESC;

Missing Indexes

The below script will let you know the missing indexes in the selected database, this script is also an important for DBA to know time to time that what are the missing indexes are there because the information of below script will be generated by the actual usage of buffer pool.

SELECT

S.AVG_TOTAL_USER_COST * (S.AVG_USER_IMPACT / 100.0) * (S.USER_SEEKS + S.USER_SCANS) AS EST_IMPROVE,

S.AVG_USER_IMPACT, LEFT (PARSENAME(D.STATEMENT, 1), 32) TABLE_NAME,

ISNULL (D.EQUALITY_COLUMNS,”) + CASE WHEN D.EQUALITY_COLUMNS IS NOT NULL AND D.INEQUALITY_COLUMNS IS NOT NULL THEN ‘,’

ELSE ” END + ISNULL (D.INEQUALITY_COLUMNS, ”) COLUMN_NAMES, ISNULL (D.INCLUDED_COLUMNS , ”) AS INCLUDE_COLUMNS,

S.UNIQUE_COMPILES, S.USER_SEEKS, S.USER_SCANS, S.LAST_USER_SEEK, S.LAST_USER_SCAN, S.AVG_TOTAL_USER_COST,

S.SYSTEM_SEEKS, S.SYSTEM_SCANS, S.LAST_SYSTEM_SEEK, S.LAST_SYSTEM_SCAN, S.AVG_TOTAL_SYSTEM_COST, S.AVG_SYSTEM_IMPACT

FROM

SYS.DM_DB_MISSING_INDEX_GROUPS G INNER JOIN SYS.DM_DB_MISSING_INDEX_GROUP_STATS S ON S.GROUP_HANDLE = G.INDEX_GROUP_HANDLE

INNER JOIN SYS.DM_DB_MISSING_INDEX_DETAILS D ON G.INDEX_HANDLE = D.INDEX_HANDLE

ORDER BY EST_IMPROVE DESC ;

Index Property table wise

The below script will let you know the count of clustered, non-clustered indexes on a table and index size.

SELECT

sys.tables.name AS [TABLE],

sys.tables.create_date AS CREATE_DATE,

sys.tables.modify_date AS MODIFY_DATE,

CASE WHEN sys.database_principals.name IS NULL THEN SCHEMA_NAME(sys.tables.schema_id) ELSE sys.database_principals.name END AS OWNER,

SUM(ISNULL(CASE INDEXES.TYPE WHEN 0 THEN COUNT_TYPE END, 0)) AS COUNT_HEAP_INDEX,

SUM(ISNULL(CASE INDEXES.TYPE WHEN 1 THEN COUNT_TYPE END, 0)) AS COUNT_CLUSTERED_INDEX,

SUM(ISNULL(CASE INDEXES.TYPE WHEN 2 THEN COUNT_TYPE END, 0)) AS COUNT_NONCLUSTERED_INDEX,

SUM(ISNULL(CASE INDEXES.TYPE WHEN 3 THEN COUNT_TYPE END, 0)) AS COUNT_XML_INDEX,

SUM(ISNULL(CASE INDEXES.TYPE WHEN 4 THEN COUNT_TYPE END, 0)) AS COUNT_SPATIAL_INDEX,

sys.tables.max_column_id_used AS COUNT_COLUMNS,

sys.partitions.rows AS COUNT_ROWS,

SUM(ISNULL(CASE WHEN sys.allocation_units.type <> 1 THEN USED_PAGES WHEN SYS.partitions.INDEX_ID < 2 THEN DATA_PAGES ELSE 0 END, 0)) *

(SELECT low / 1024 AS VALUE FROM master.dbo.spt_values WHERE (number = 1) AND (type = N’E’)) AS SIZE_DATA_KB,

SUM(ISNULL(sys.allocation_units.used_pages – CASE WHEN sys.allocation_units.type <> 1 THEN USED_PAGES

WHEN SYS.partitions.INDEX_ID < 2 THEN DATA_PAGES ELSE 0 END, 0)) * (SELECT low / 1024 AS VALUE

FROM

master.dbo.spt_values AS spt_values_2

WHERE (number = 1)

AND (type = N’E’)

) AS SIZE_INDEX_KB

FROM

sys.allocation_units INNER JOIN sys.partitions ON sys.allocation_units.container_id = sys.partitions.partition_id

INNER JOIN

(

SELECT TOP (100) PERCENT object_id, index_id, type AS TYPE, COUNT(*) AS COUNT_TYPE

FROM sys.indexes AS indexes_1 GROUP BY object_id, type, index_id ORDER BY object_id

) AS INDEXES ON

sys.partitions.object_id = INDEXES.object_id

AND sys.partitions.index_id = INDEXES.index_id

RIGHT OUTER JOIN sys.database_principals

RIGHT OUTER JOIN sys.tables ON sys.database_principals.principal_id = sys.tables.principal_id

ON INDEXES.object_id = sys.tables.object_id

GROUP BY sys.tables.name, sys.tables.create_date, sys.tables.modify_date,

CASE WHEN sys.database_principals.name IS NULL THEN SCHEMA_NAME(sys.tables.schema_id) ELSE sys.database_principals.name END,

sys.tables.max_column_id_used, sys.partitions.rows

ORDER BY COUNT_ROWS DESC;

Back to top

What is RDBMS?

Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.

What is normalization?

Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

What are different normalization forms?

1NF: Eliminate Repeating Groups

Make a separate table for each set of related attributes, and give each table a primary key. Each field

contains at most one value from its attribute domain.

2NF: Eliminate Redundant Data

If an attribute depends on only part of a multi-valued key, remove it to a separate table.

3NF: Eliminate Columns Not Dependent On Key

If attributes do not contribute to a description of the key, remove them to a separate table. All

attributes must be directly dependent on the primary key

BCNF: Boyce-Codd Normal Form

If there are non-trivial dependencies between candidate key attributes, separate them out into distinct

tables.

4NF: Isolate Independent Multiple Relationships

No table may contain two or more 1:n or n:m relationships that are not directly related.

5NF: Isolate Semantically Related Multiple Relationships

There may be practical constrains on information that justify separating logically related many-to-many relationships.

ONF: Optimal Normal Form

A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.

DKNF: Domain-Key Normal Form

A model free from all modification anomalies. Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

What is Stored Procedure?

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database. e.g. sp_help, sp_renamedb, sp_helptext etc.

What is Trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS.Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.

Nested Trigger:

A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

What is View?

A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

What is Index?

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.

Clustered indexesdefine the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index.

Non-clustered indexesare created outside of the database table and contain a sorted list of references to the table itself.

What are the difference between clustered and a non-clustered index?

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

What are the different index configurations a table can have?

A table can have one of the following index configurations:

No indexes

A clustered index

A clustered index and many nonclustered indexes

A nonclustered index

Many nonclustered indexes

What are the cursors?

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. In order to work with a cursor we need to perform some steps in the following order:

Declare cursor

Open cursor

Fetch row from the cursor

Process fetched row

Close cursor

Deallocate cursor

What is the use of DBCC commands?

DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks. E.g. DBCC CHECKDB – Ensures that tables in the db and the indexes are correctly linked. DBCC CHECKALLOC – To check that all pages in a db are correctly allocated. DBCC CHECKFILEGROUP – Checks all tables file group for any damage.

What is a Linked Server?

Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.

What is Collation?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

What are different types of Collation Sensitivity?

Case sensitivity

A and a, B and b, etc.

Accent sensitivity

a and á, o and ó, etc.

What’s the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.

One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.

Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

What is a NOLOCK?

Using the NOLOCK query optimizer hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data. The advantage to performance is that your reading of data will not block updates from taking place, and updates will not block your reading of data. SELECT statements take Shared (Read) locks. This means that multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data. The updates will queue until all the read have completed, and reads requested after the update will wait for the updates to complete. The result to your system is delay (blocking).

What is difference between DELETE & TRUNCATE commands?

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command. TRUNCATE is faster and uses fewer system and transaction log resources than DELETE. TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log. TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger. TRUNCATE cannot be Rolledback. TRUNCATE is DDL ommand.

TRUNCATE Resets identity of the table. DELETE removes rows one at a time and records an entry in the transaction log for each deleted row. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.

DELETE Can be used with or without a WHERE clause

DELETE Activates Triggers.

DELETE Can be Rolledback.

DELETE is DML Command.

DELETE does not reset identity of the table.

Difference between Function and Stored Procedure?

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as

Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDF’s can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.

When is the use of UPDATE_STATISTICS command?

This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

What types of Joins are possible with SQL Server?

Joins are used in queries to explain how different tables are related. Joins also let you select data from

a table depending upon data from another table. Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

What is sub-query? 

Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword. A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a result set. Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested. A subquery SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery can be used anywhere an expression can be used. Properties of Sub-Query A subquery must be enclosed in the parenthesis. A subquery must be put in the right hand of the comparison operator, and A subquery cannot contain a ORDER-BY clause. A query can contain more than one sub-queries.

What are types of sub-queries?

Single-row subquery, where the subquery returns only one row. Multiple-row subquery, where the subquery returns multiple rows, and Multiple column subquery, where the subquery returns multiple columns.

What is SQL Profiler?

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performance by executing too slowly. Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

What is User Defined Functions?

User-Defined Functions allow to define its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.

What kind of User-Defined Functions can be created?

There are three types of User-Defined functions in SQL Server 2000 and they are Scalar, Inline Table-Valued and Multi-statement Table-valued. Scalar User-Defined Function, A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.

Inline Table-Value User-Defined Function, An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables. Multi-statement Table-Value User-Defined Function, A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a T-SQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, It can be used in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.

Which TCP/IP port does SQL Server run on? How can it be changed?

SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties –> Port number. Both on client and the server.

What are the authentication modes in SQL Server? How can it be changed?

Windows mode and mixed mode (SQL & Windows). To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the Security page.

Where are SQL server users names and passwords are stored in sql server?

They get stored in master db in the sysxlogins table.

Which command using Query Analyzer will give you the version of SQL server and operating system?

SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)

What is SQL server agent?

SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full-function scheduling engine, which allows you to schedule your own jobs and scripts.

Can a stored procedure call itself or recursive stored procedure? How many levels SP nesting possible?

Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.

What is @@ERROR?

The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a variable if it is needed to process it further after checking it.

What is Raiseerror?

Stored procedures report errors to client applications via the RAISERROR command. RAISERROR doesn’t change the flow of a procedure; it merely displays an error message, sets the @@ERROR automatic variable, and optionally writes the message to the SQL Server error log and the NT application event log.

What is log shipping?

Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db can be used this as the Disaster Recovery plan. The key feature of log shipping is that is will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.

What is the difference between a local and a globalvariable?

A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

What command do we use to rename a db?

sp_renamedb ‘oldname’ , ‘newname’ If someone is using db it will not accept sp_renmae db. In that case first bring db to single user using sp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi user mode.

What is sp_configure commands and set commands?

Use sp_configure to display or change server-level settings. To change database-level settings, use

ALTER DATABASE. To change settings that affect only the current user session, use the SET statement.

What are three SQL keywords used to change or set someone’s permissions?

GRANT, DENY, and REVOKE.

What does it mean to have quoted_identifier on? What are the implications of having it off?

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

What is the STUFF function and how does it differ from the REPLACE function?

STUFF function to overwrite existing characters. Using this syntax, STUFF(string_expression, start, length, replacement_characters), string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and

replacement_characters are the new characters interjected into the string.

REPLACE function to replace existing characters of all occurrences. Using this syntax REPLACE(string_expression, search_string, replaceme nt_string), where every incidence of search_string found in the string_expression will be replaced with replacement_string.

Using query analyzer, name 3 ways to get an accurate count of the number of records in a table?

SELECT * FROM table1

SELECT COUNT(*) FROM table1

SELECT rows FROM sysindexes WHERE id = OBJECT_ID(ta

ble1) AND indid < 2

What are the basic functions for master, msdb, model, tempdb databases?

The Master, database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.

The msdb , database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.

The tempdb, holds temporary objects such as global and local temporary tables and stored procedures.

The model, is essentially a template database used in the creation of any new user database created in the instance.

What are primary keys and foreign keys?

Primary keys, are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key.

Foreign keys, are both a method of ensuring data integrity and a manifestation of the relationship between tables.

What is data integrity? Explain constraints?

Data integrity is an important feature in SQL Server. When used properly, it ensures that data is accurate, correct, and valid. It also acts as a trap for otherwise undetectable bugs within applications.

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

What are the properties of the Relational tables?

Relational tables have six properties:

* Values are atomic.

* Column values are of the same kind.

* Each row is unique.

* The sequence of columns is insignificant.

* The sequence of rows is insignificant.

* Each column must have a unique name.

What is De-normalization?

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

How to get @@error and @@rowcount at the same time?

If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset. And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

What is Identity?

Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers,

the value of this cannot be controlled. Identity/GUID columns do not need to be indexed.

What is a Scheduled Jobs or What is a Scheduled Tasks?

Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity.

User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. Back up database, Update Stats of Tables. Job steps give user control over flow of execution.

If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.

What is a table called, if it does not have neither Cluster nor Non-cluster Index? What is it used for?

Unindexed table or Heap. Microsoft Press Books and Book On Line (BOL) refers it as Heap. A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together. Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from table and than do bulk of inserts and to restore those indexes after that.

What is BCP? When does it used?

BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination.

How do you load large data to the SQL server database?

BulkCopy is a tool used to copy huge amount of data from tables. BULK INSERT command helps to

Imports a data file into a database table or view in a user-specified format.

Can we rewrite subqueries into simple select statements or with joins?

Subqueries can often be re-written to use a standard outer join, resulting in faster performance. As we may know, an outer join uses the plus sign (+) operator to tell the database to return all non-matching rows with NULL values. Hence we combine the outer join with a NULL test in the WHERE clause to reproduce the result set without using a sub-query.

Can SQL Servers linked to other servers like Oracle?

SQL Server can be lined to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has a OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group.

How to know which index a table is using?

SELECT table_name,index_name FROM user_constraints

What is Self Join?

This is a particular case when one table joins to itself, with one or two aliases to avoid confusion.

A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company have a hierarchal reporting structure whereby one member of staff reports to another.

What is Cross Join?

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

Which virtual table does a trigger use?

Inserted and Deleted.

List few advantages of Stored Procedure.

* Stored procedure can reduced network traffic and latency, boosting application performance.

* Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing server overhead.

* Stored procedures help promote code reuse.

* Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.

* Stored procedures provide better security to your data.

 

Back to top

Good Luck 🙂

Free Microsoft eBooks and Resource Kits for you, including: SharePoint 2013, Office 2013, Office 365, Duet 2.0, Azure, Cloud, Windows Phone, Lync, Dynamics CRM, and more. Large Collection of Free Microsoft eBooks post (60+ eBooks) here on the blog and the response and feedback I am receiving about it is incredibly positive about how much you liked it. Because of this, I thought I would put up this follow-up post which includes even more free Microsoft eBooks available to you for download. Just like with the last list I published here for you, if you find this list helpful, please share it with your peers and colleagues so that they too can benefit from these resources.image
Developing an Advanced Windows Phone 7.5 App that Connects to the Cloud
image
Developing Applications for the Cloud, 2nd Edition
image
Building Hybrid Applications in the Cloud on Windows Azure
image
Building Elastic and Resilient Cloud Applications – Developer’s Guide to the Enterprise Library 5.0 Integration Pack for Windows Azure
PDF
EPUB
MOBI
image
Technical reference for Microsoft SharePoint Server 2010
image
Getting started with Microsoft SharePoint Foundation 2010
image
Deployment guide for SharePoint 2013 Preview
image
Deployment guide for Duet Enterprise for Microsoft SharePoint and SAP Server 2.0 Preview
image
Microsoft Dynamics GP 2010 Guides: Financials
image
Microsoft Dynamics CRM 2011 User’s Guide
image
Dynamics CRM 2011 Developer Training Kit
image
Microsoft Dynamics CRM 2011 Implementation Guide
image
Deployment guide for Office 2013 Preview
image
Office 2010 Developer Training Kit
image
Office 365 Developer Training Kit
image
Office 365 Guides for professionals and small businesses
image
Lync for Mac 2011 Deployment Guide
image
Microsoft Lync Server 2010 Resource Kit Tools
image
Microsoft Lync Server 2010 Resource Kit
image
Microsoft Lync Server 2010 Security Guide
image
Developing Applications for the Cloud – 2nd Edition
image
Visual Studio LightSwitch Training Kit
image
SQL Server 2012 Developer Training Kit
If you want to see the full list of almost 300 FREE Microsoft eBooks, click here for my original post; otherwise, I give you the Top 60 eBooks being downloaded so far (as of last night):  

image
Introducing Windows 8.1 for IT Professionals
PDF
EPUB
MOBI
image
Windows 7 Keyboard Shortcuts
PDF
image
Word Online Keyboard Shortcuts
PDF
image
Windows PowerShell 4.0 Examples
PDF
image
Windows 8 Keyboard Shortcuts
PDF
image
How To Recover That Un-Saved Office Document
PDF
image
Discover SharePoint
PDF
image
Windows PowerShell 4.0 Language Quick Reference
PDF
image
Excel 2013 Keyboard Shortcuts
PDF
image
Windows PowerShell Integrated Scripting Environment 4.0
PDF
image
Introducing Windows 8: An Overview for IT Professionals
PDF
EPUB
MOBI
image
Work Smart: Windows 8 Shortcut Keys
DOCX
image
Word 2013 Keyboard Shortcuts
PDF
image
Getting To Know Office 365
PDF
imageSecrets of PowerShell Remoting
DOCX
Save As PDF Avail
image
Microsoft System Center: Troubleshooting Configuration Manager
PDF
EPUB
MOBI
W7PUG Cover-free
Windows 7 Power Users Guide
PDF
image
Windows PowerShell Desired State Configuration Overview
PDF
image
Windows 8.1 Deployment Planning – A Guide for Education
PDF
image
Windows 8.1 Deployment to PCs – A Guide for Education
PDF
image
Windows PowerShell Web Access Quick Reference
PDF
image
Explore SharePoint 2013
DOC
PDF
EPUB
MOBI
image
Windows 8.1 Update Power User Guide for Business
PDF
image
Deployment guide for SharePoint 2013
DOC
PDF
EPUB
MOBI
image
Deployment Guide for Office 2013
DOC
PDF
EPUB
image
OneNote 2013 Keyboard Shortcuts
PDF
image
The Wiki Ninjas Guide to SharePoint 2013
PDF
image
Windows 8.1 Update Quick Guide for Business
PDF
image
Explore Windows 8.1 Update
DOCX
image
Microsoft Excel 2013 Quick Start Guide
image
Windows 8.1 Product Guide
PDF
image
Windows 8 End User Training Brochure
PDF

Introducing Windows Server 2012 – RTM Edition!
PDF
EPUB
MOBI
image
SharePoint Adoption Guide
PDF
image
The Big Book of PowerShell Gotchas
DOCX
Save As PDF Avail
image
Introducing Microsoft SQL Server 2014
PDF
EPUB
MOBI
image
Introducing Windows Server 2012 R2
PDF
EPUB
MOBI
imageWindows PowerShell Networking Guide
DOCX
Save As PDF Avail
image
Outlook 2013 Keyboard Shortcuts
PDF
image
Windows To Go – A Guide for Education
PDF
image
Windows Store Apps – A Deployment Guide for Education
PDF
imageThe Community Book of PowerShell Practices
DOCX
Save As PDF Avail
image
Programming Windows 8 Apps with HTML, CSS, and JavaScript
EPUB
MOBI
PDF
image
Access 2013 Keyboard Shortcuts
PDF
image
Office 2010 User Resources
ZIP
image
Creating HTML Reports in PowerShell
DOCX
Save As PDF Avail
imageThe Big Book of PowerShell Error Handling
DOCX
Save As PDF Avail
image
Install Microsoft Exchange server 2013 step by step with DAG
PDF
imageMaking Historical and Trend Reports in PowerShell
DOCX
Save As PDF Avail
image
Windows 8 Product Guide for Business
PDF
image
Programming Windows Store Apps with HTML, CSS and JavaScript
PDF
EPUB
MOBI
Companion Files
image
Start working in CRM
PDF
image
The Wiki Ninjas Guide to SharePoint 2013 – Part II
PDF
image
Introducing Microsoft SQL Server 2012
PDF
EPUB
MOBI
image
Microsoft Word 2013 Quick Start Guide
image
.NET Technology Guide for Business Applications
PDF
image
Programming Windows Store Apps with HTML, CSS, and JavaScript, Second Edition (Second Preview)
PDF
Companion Files
image
Visio 2013 Keyboard Shortcuts
PDF
imageIntroducing Microsoft System Center 2012 R2
PDF
EPUB
MOBI
image
TCP/IP Fundamentals for Microsoft Windows
PDF

Reference: Microsoft TechNetWhy should i buy when we have F R E E  to download SQL Server, SharePoint, Windows, ASP, e-books ….E-Books in English
Office Deployment guide for Office 2013Office and SharePoint content publishing team
Guide
Learn how to install and configure the new Office in your organization. Topics include volume activation, Group Policy, and customization.Applies to: Office 2013 (with Office 365)
Source: TechNet library
E-Book publication date: October 2013
281 pages
EPUB MOBI PDF | Source content  First Look: Microsoft Office 2010
Katherine Murray
Overview
Welcome to Office 2010. Whether you work primarily in the office or on the go, you’ll find smart tools in this release that enable you to get your work done easier, faster, and more professionally than ever. All the freedom to multitask built into Office 2010 has an upside you might not expect: being able to work anywhere, anytime means more flexibility, which translates to higher efficiency and effectiveness. And when your work is done quickly and well, you have more time left over for the people, places, and possibilities that intrigue you.
Applies to: Office 2010
Publisher: Microsoft Press
E-book publication date: January 2010
202 pages

XPS | PDF | Source content
 Microsoft Office 365: Connect and Collaborate Virtually Anywhere, Anytime
Katherine Murray
Step-by-Step
This book shows you how you can use cloud computing—and specifically,
Office 365—to get more done, collaborate more easily, and work more
flexibly than you ever have before.
Applies to: Office 365
Publisher: Microsoft Press
E-book publication date: August 2011
337 pages

EPUB | MOBI | PDF | Source content
 Microsoft Office 365 for professionals and small businesses: Help and How To
Office 365 User Assistance Team
Step-by-Step 
Your organization uses Microsoft Office 365 for professionals and small businesses to communicate and collaborate. Office 365 includes email, document sharing, Microsoft Office Web Apps and more. There are important tasks that you need to do to use all of the features of Office 365. This guide leads you through those steps.Applies to: Office 365 for professionals and small businesses
Source: Microsoft Online Help
E-book publication date: June 2012
197 pages
EPUB MOBI PDF Source content  Security and Privacy for Microsoft Office 2010 Users
Mitch Tulloch
Guide
Take control—and put the built-in security and privacy features in Microsoft Office to work! Whether downloading documents, publishing a presentation, or collaborating online—this guide offers concise, how-to guidance and best practices to help protect your documents and your ideas.
Applies to: Office 2010
Publisher: Microsoft Press
E-book publication date: February 2012
98 pages
EPUB | MOBI | PDF | Source content
SharePoint Explore SharePoint 2013
Office and SharePoint content publishing team
Guide
Learns what’s new in SharePoint Server 2013 and SharePoint Foundation 2013Applies to: SharePoint 2013
Source: TechNet Library
E-book publication date: October 2012
100 pages
EPUB MOBI PDF | Source content  Deployment guide for SharePoint 2013
Office and SharePoint content publishing team
Guide
Get a head start on installing and configuring SharePoint Server 2013 or SharePoint Foundation 2013 with this deployment guide. Applies to: SharePoint 2013
Source: TechNet Library
E-book publication date: October 2012
1031 pages
EP UB MOBI PDF | Source content  Test Lab Guide: eBook for SharePoint Server 2013 Intranet and Team Sites
Office and SharePoint content publishing team
Guide
Learn how to create a test lab containing intranet and team sites in a SharePoint Server 2013 three-tier farm. Applies to: SharePoint 2013
Source: TechNet Library
E-book publication date: May 2013
44 pages
EPUB  | MOBI PDF DOC |Source content  Create a Balanced Scorecard
SharePoint Business Intelligence Content Team 
Quick Step-by-Step 
Learn how to create scorecards and strategy maps across various measurements and display them in one dashboard by using PerformancePoint Services in Microsoft SharePoint Server 2010. Applies to: SharePoint Server 2010
Source: TechNet Library
E-book publication date: June 2012
32 pages

EPUB MOBI PDF Source content
Configure Kerberos Authentication for SharePoint 2010 Products
Tom Wisnowski
Guide
This document covers the concepts of identity in SharePoint 2010 products, how Kerberos authentication plays a critical role in authentication and delegation in business intelligence scenarios, and the situations where Kerberos authentication should be leveraged or may be required in solution designs. It also covers how to configure Kerberos authentication end-to-end within your environment, including scenarios which use various service applications in SharePoint Server. Additional tools and resources are described to help you test and validate Kerberos configuration.Applies to: SharePoint 2010
Source: White paper
E-book publication date: May 2012
220 pages
EPUB MOBI PDF | Source content  SharePoint Server for Business Intelligence
SharePoint Business Intelligence Content Team
Step-by-Step 
 
Step-by-step, learn how to install and configure SharePoint Server 2010 and SQL Server 2008 to create a robust infrastructure to support business intelligence.Applies to: SharePoint Server 2010, SQL Server 2008, SQL Server 2008 R2
Source: TechNet Library
E-book publication date: June 2012
117 pages

EPUB | MOBI | PDF | Source content  Business continuity management for SharePoint Server 2010
Office and SharePoint content publishing team
Guide
Provides information about the business decisions, processes, and tools you put in place in advance to handle crises, such as backing up and restoring data. Information includes features of Microsoft SharePoint Server 2010 that are likely to be part of your business continuity management strategy.Applies to: SharePoint Server 2010
Source: TechNet library
E-book publication date: October 2011
942
EPUB | MOBI | PDF | Source content  Deployment guide for SharePoint Server 2010
Office and SharePoint content publishing team
Guide
This book includes information deployment scenarios, step-by-step installation instructions, and post-installation configuration steps for deploying Microsoft SharePoint Server 2010.Applies to: SharePoint Server 2010
Source: TechNet library
E-book publication date: October 2011
936
EPUB | MOBI | PDF | Source content  Get started with SharePoint Server 2010
Office and SharePoint content publishing team
Guide
This book provides basic information about the capabilities of and requirements for Microsoft SharePoint Server 2010. The audiences for this book include application specialists, line-of-business application specialists, information architects, IT generalists, program managers, and infrastructure specialists who are just starting to learn about SharePoint Server 2010 and want a quick introduction plus installation steps.Applies to: SharePoint Server 2010
Source: TechNet library
E-book publication date: October 2011
50
EPUB MOBI PDF | Source content  Governance guide for Microsoft SharePoint Server 2010
Office and SharePoint content publishing team
Guide
This book provides guidance to help you determine the aspects of a Microsoft SharePoint Server 2010 deployment to govern and the governance techniques to use. Governance is the set of policies, roles, responsibilities, and processes that you establish in your enterprise to guide, direct, and control how it uses technologies to accomplish business goals. To strike the right balance between the needs of the users of your SharePoint Server 2010 deployment and the IT professionals who deploy and operate SharePoint Server 2010, we recommend that you form a governance body that includes representatives of all stakeholders in the SharePoint Server 2010 deployment. This body can then create and enforce rules that govern the use of SharePoint Server 2010.Applies to: SharePoint Server 2010
Source: TechNet library
E-book publication date: October 2011
128
EPUB | MOBI | PDF | Source content  Profile synchronization guide for SharePoint Server 2010
Office and SharePoint content publishing team
Guide
This book describes how to plan and configure profile synchronization in Microsoft SharePoint Server 2010. Also included is technical reference information about profile properties, data types, and permissions. Applies to: SharePoint Server 2010
Source: TechNet library
E-book publication date: October 2011
263
EPUB MOBI PDF | Source content  Remote BLOB storage for Microsoft SharePoint Server 2010

Guide
How to use Microsoft SharePoint Server 2010 together with Remote BLOB Storage (RBS) and Microsoft SQL Server 2008 Express and Microsoft SQL Server 2008 R2 Express to optimize database storage resources.Applies to: SharePoint Server 2010
Source: TechNet library
E-book publication date: October 2011
63
EPUB MOBI PDF | Source content  Technical reference for Microsoft SharePoint Server 2010
Office and SharePoint content publishing team
Guide
This book includes technical information about the Microsoft SharePoint Server 2010 provider for Windows PowerShell and other helpful reference information about general settings, security, and tools.Applies to: SharePoint Server 2010
Source: TechNet library
E-book publication date: October 2011
1557
EPUB | MOBI | PDF | Source content  Upgrading to SharePoint Server 2010
Office and SharePoint content publishing team
Guide
This book is designed to guide administrators and IT professionals through the process of upgrading to Microsoft SharePoint Server 2010.Applies to: SharePoint Server 2010
Source: TechNet library
E-book publication date: October 2011
605
EPUB | MOBI | PDF | Source content SQL Server 5 Tips for a Smooth SSIS Upgrade to SQL Server 2012
Runying Mao, Carla Sabotta
Quick Guide 
Microsoft SQL Server 2012 Integration Services (SSIS) provides significant improvements in both the developer and administration experience. This article provides tips that can help to make the upgrade to Microsoft SQL Server 2012 Integration Services successful. The tips address editing package configurations and specifically connection strings, converting configurations to parameters, converting packages to the project deployment model, updating Execute Package tasks to use project references and parameterizing the PackageName property.Applies to: SQL Server 2012
Source: White Paper
E-book publication date: November 2012
11 pages
EPUB MOBI PDF | Source content
 A Hitchhiker’s Guide to Microsoft StreamInsight Queries
Ramkumar (Ram) Krishnan, Jonathan Goldstein, Alex Raizman
Step-by-Step
This paper is a developer’s introduction to the Microsoft StreamInsight Queries. The paper has two goals: (1) To help you think through stream processing in simple, layered levels of understanding, complementing the product documentation. (2) To reinforce this learning through examples of various use cases, so that you can design the query plan for a particular problem and compose the LINQ query. This ability for top-down composition of a LINQ query, combined with bottom-up understanding of the query model, will help you build rich and powerful streaming applications. The more advanced sections of this paper provide an overview of a query plan, the operators that constitute the query, and, where appropriate, the foundational query algebra itself. The paper is not a feature reference or a substitute for MSDN documentation.Applies to: StreamInsight
Source: MSDN Library
E-book publication date: November 2012
54 pages
EPUB | MOBI | PDF | Source content
 Backup and Restore of SQL Server Databases
SQL Server 2012 Books Online 
Reference 
This book describes the benefits of backing up SQL Server databases, basic backup and restore terms, and introduces backup and restore strategies for SQL Server and security considerations for SQL Server backup and restore.Applies to: SQL Server 2012
Source: SQL Server 2012 Books Online
E-book publication date: June 2012
288 pages
EPUB MOBI PDF Source content  Data Analysis Expressions (DAX) Reference
SQL Server 2012 Books Online
Reference
The Data Analysis Expressions (DAX) language is a library of functions and operators that can be combined to build formulas and expressions.Applies to: SQL Server 2012
Source: SQL Server 2012 Books Online
E-book publication date: June 2012
316 pages

EPUB MOBI PDF Source content  Data Mining Extensions (DMX) Reference
SQL Server 2012 Books Online
Reference
Data Mining Extensions (DMX) is a language that you can use to create and work with data mining models in Microsoft SQL Server Analysis Services. You can use DMX to create the structure of new data mining models, to train these models, and to browse, manage, and predict against them. DMX is composed of data definition language (DDL) statements, data manipulation language (DML) statements, and functions and operators.
Applies to: SQL Server 2012Source: Microsoft SQL Server 2012 Books Online
E-book publication date: June 2012
167
pages
EPUB | MOBI PDF | Source content
 Data Quality Services
SQL Server 2012 Books Online
Reference 
SQL Server Data Quality Services (DQS) is a knowledge-driven data quality product. DQS enables you to build a knowledge base and use it to perform a variety of critical data quality tasks, including correction, enrichment, standardization, and de-duplication of your data. DQS enables you to perform data cleansing by using cloud-based reference data services provided by reference data providers. DQS also provides you with profiling that is integrated into its data-quality tasks, enabling you to analyze the integrity of your data.Applies to: SQL Server 2012
Source: SQL Server 2012 Books Online
E-book publication date: June 2012
193 pages

EPUB MOBI PDF Source content  Extracting and Loading SharePoint Data in SQL Server Integration Services
Kevin Idzi
Quick Step-by-Step
This article introduces the SharePoint List Source and Destination adapters,
demonstrates their use in an SSIS package, and documents their features and options.
Applies to: SQL Server 2005, 2008, 2008 R2, and 2012
Source: White Paper
E-book publication date: November 2012
25 pages
EPUB | MOBI | PDF | Source content
 High Availability Solutions
SQL Server 2012 Books Online
Reference
This book introduces two SQL Server high-availability solutions that improve the availability of servers or databases: AlwaysOn Failover Cluster Instances and AlwaysOn Availability Groups. A high-availability solution masks the effects of a hardware or software failure and maintains the availability of applications so that the perceived downtime for users is minimized.
Applies to: SQL Server 2012Source: Microsoft SQL Server 2012 Books Online
E-book publication date: June 2012
468 pages
EPUB MOBI PDF Source content
 Integration Services: Extending Packages with Scripting
SQL Server 2012 Books Online
Reference
You can extend the power of Integration Services (SSIS) by adding code within the wrappers provided by the Script task and the Script component. This section of the Developer Reference provides instructions and examples for extending the control flow and data flow of an SSIS package using the Script task and the Script component.Applies to: SQL Server 2012
Source: SQL Server 2012 Books Online
E-book publication date: January 2013
188 pages
EPUB MOBI PDF Source content
 Master Data Services
SQL Server 2012 Books Online
Reference 
Master Data Services (MDS) is the SQL Server solution for master data management. Master data management (MDM) describes the efforts made by an organization to discover and define non-transactional lists of data, with the goal of compiling maintainable master lists. An MDM project generally includes an evaluation and restructuring of internal business processes along with the implementation of MDM technology. The result of a successful MDM solution is reliable, centralized data that can be analyzed, resulting in better business decisions.Applies to: SQL Server 2012
SourceSQL Server 2012 Books Online
E-book publication date: June 2012
270 pages

EPUB MOBI PDF Source content  Master Data Services (MDS) Operations Guide
Karan Gulati
GuideThis article provides guidance about how to install, configure and manage SQL Server 2012 Master Data Services (MDS). You also learn how to deploy a Master Data Services model and create a SQL Server Integration Services (SSIS) solution to monitor MDS.Applies to: SQL Server Master Data Services (MDS), SQL Server Integration Services (SSIS)
Source: White paperE-book publication date: October 2013
49 pages
PDF | Source content  Monitor and Tune for Performance
SQL Server 2012 Books Online
Quick Reference
The goal of monitoring databases is to assess how a server is performing. Effective monitoring involves taking periodic snapshots of current performance to isolate processes that are causing problems, and gathering data continuously over time to track performance trends.   Ongoing evaluation of the database performance helps you minimize response times and maximize throughput, yielding optimal performance.Applies to: SQL Server 2012
Source: SQL Server 2012 Books Online
E-book publication date: June 2012
30 pages

EPUB MOBI PDF Source content  Multidimensional Expressions (MDX) Reference
SQL Server 2012 Books Online
Reference
Multidimensional Expressions (MDX) is the query language that you use to work with and retrieve multidimensional data in Microsoft Analysis Services. MDX is based on the XML for Analysis (XMLA) specification, with specific extensions for SQL Server Analysis Services. MDX utilizes expressions composed of identifiers, values, statements, functions, and operators that Analysis Services can evaluate to retrieve an object (for example a set or a member), or a scalar value (for example, a string or a number).Applies to: SQL Server 2012
Source: SQL Server 2012 Books Online
E-book publication date: June 2012
361 pages

EPUB MOBI PDF Source content  Multidimensional Model Programming
SQL Server 2012 Books Online
Reference
Analysis Services provides several APIs that you can use to program against an Analysis Services instance and the multidimensional databases that it makes available. This section describes the approaches available to developers who want to create custom applications using Analysis Services multidimensional solutions. You can use this information to choose the programming interface that best meets the requirements of a particular project. Analysis Services development projects can be based on managed or non-managed code that runs on a Windows platform, or other platforms that support HTTP access.
Applies to: SQL Server 2012
Source: SQL Server 2012 Books Online
E-book publication date:
317 pages
EPUB MOBI PDF | Source content
 Optimized Bulk Loading of Data into Oracle
Carla Sabotta, Debarchan Sarkar
Quick Step-by-Step
SQL Server 2008 and SQL Server 2008 R2 (Enterprise and Developer editions) support bulk loading Oracle data using Integration Services packages with the Microsoft Connector for Oracle by Attunity. For SQL Server 2005 and the non-Enterprise and non-Developer editions of SQL Server 2008 and 2008 R2, there are alternatives for achieving optimal performance when loading Oracle data. This paper discusses these alternatives.Applies to: SQL Server 2005 (all editions), SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012 (non-Enterprise and non-Developer editions)
Source: White Paper
E-book publication date: November 2012
14 pages
EPUB | MOBI | PDF | Source content
 Planning Disaster Recovery for Microsoft SQL Server Reporting Services in SharePoint Integrated Mode
Jaime Tarquino
Quick Guide
This white paper discusses disaster recovery options for Microsoft SQL Server Reporting Services solutions configured to use SharePoint integrated mode. This paper extends best practices for Microsoft SharePoint solutions that include both SQL Server Reporting Services and SharePoint Products. This paper also contains procedures, examples, and scripts that you can use to apply these practices to your organization.Applies to: SQL Server 2012 Reporting Services in SharePoint 2010 (with SP1) and SharePoint 2013, SQL Server 2012 Reporting Services in SharePoint 2013, SQL Server 2008 R2 Reporting Services in SharePoint 2010 (with SP1)
Source: White Paper
E-book publication date: November 2012
36 pages
EPUB | MOBI | PDF | Source content
 SQLCAT’s Guide to BI and Analytics
Microsoft SQLCAT Team
Guide and Reference
This e-book is a collection of some of the more popular technical content that was available on the old SQLCAT.COM site. It covers SQL Server technology ranging from SQL Server 2005 to SQL Server 2012. However, this is not all the content that was available on SQLCAT.COM. To see additional content from that site you can follow the SQLCAT blog which will point to additional content. For more comprehensive content on SQL Server, see the MSDN library .Applies to: SQL Server Business Intelligence and Analytics
Source: SQLCAT Blog
E-book publication date: September 2013
202 pages
PDF  | Source content
 SQLCAT’s Guide to High Availability Disaster Recovery
Microsoft SQLCAT Team
Guide and Reference
This e-book is a collection of some of the more popular technical content that was available on the old SQLCAT.COM site. It covers SQL Server technology ranging from SQL Server 2005 to SQL Server 2012. However, this is not all the content that was available on SQLCAT.COM. To see additional content from that site you can follow the SQLCAT blog  which will point to additional content. For more comprehensive content on SQL Server, see the MSDN library .Applies to: SQL Server High Availability and Disaster Recovery
Source: SQLCAT Blog
E-book publication date: September 2013
37 pages
PDF  | Source content
 SQLCAT’s Guide to Relational Engine
Microsoft SQLCAT Team
Guide and Reference
This e-book is a collection of some of the more popular technical content that was available on the old SQLCAT.COM site. It covers SQL Server technology ranging from SQL Server 2005 to SQL Server 2012.Applies to: SQL Server 2005 to 2012
Source: SQLCAT Blog
E-book publication date: September 2013
238 pages
PDF  | Source content
 SQL Server Community FAQs Manual
SQL Server Forum Support Team
Reference
The SQL Server Forum Support Team authored, collected and consolidated commonly asked questions in SQL Server MSDN and TechNet forums into this book so as to provide an offline reading and learning experience for IT professionals and people who are interested in SQL Server. The book includes about 101 items related to common problems with database administration, SSAS, SSIS and SSRS. (The book was originally published in June 2011.)Applies to: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2
Source: MSDN Blogs
E-book publication date: January 2013
182 pages
EPUB | MOBI | PDF | Source content
 SQL Server Distributed Replay
SQL Server 2012 Books Online
Quick Reference 
The Microsoft SQL Server Distributed Replay feature helps you assess the impact of future SQL Server upgrades. You can also use it to help assess the impact of hardware and operating system upgrades, and SQL Server tuning.Applies to: SQL Server 2012
Source: SQL Server 2012 Books Online
E-book publication date: June 2012
42 pages

EPUB MOBI PDF Source content  Transact-SQL Data Definition Language (DDL) Reference
SQL Server 2012 Books Online
Reference
Data Definition Language (DDL) is a vocabulary used to define data
structures in SQL Server 2012. Use these statements to create, alter, or drop data structures in an instance of SQL Server.
Applies to: SQL Server 2012Source: Microsoft SQL Server 2012 Books Online
E-book publication date: June 2012
772 pages
EPUB | MOBI PDF Source content
Transact-SQL Data Manipulation Language (DML) Reference
SQL Server 2012 Books Online
Reference
Data Manipulation Language (DML) is a vocabulary used to retrieve and work with data in SQL Server 2012. Use these statements to add, modify, query, or remove data from a SQL Server database.Applies to: SQL Server 2012
Source: Microsoft SQL Server 2012 Books Online
E-book publication date: May 2012
263 pages
E
PUB | MOBI PDF | Source content  XQuery Language Reference
SQL Server 2012 Books Online
Reference
XQuery is a language that can query structured or semi-structured XML data. With the xml data type support provided in the Database Engine, documents can be stored in a database and then queried by using XQuery. XQuery is based on the existing XPath query language, with support added for better iteration, better sorting results, and the ability to construct the necessary XML.
Applies to: SQL Server 2012Source: Microsoft SQL Server 2012 Books Online
E-book publication date: June 2012
234 pages
EPUB MOBI PDF Source content
 Data Access for Highly-Scalable Solutions: Using SQL, NoSQL, and Polyglot Persistence
patterns & practices
Guide
The business requirements of many modern business applications often involve processing large amounts of data. Additionally, the performance requirements typically make it imperative that the application uses the most efficient means to store and retrieve this data. In the past, most application designers would have simply chosen to save this data in a SQL database, but the recent advances in NoSQL technology have brought about highly-scalable, non-relational databases that can handle vast amounts of information very efficiently. Each type of NoSQL database is often optimized for a particular pattern of data access, and application designers are now increasingly looking to integrate multiple NoSQL and SQL databases into their solutions.
This guide describes how to design and build applications and services that can take best advantage of SQL and NoSQL databases by combining them into a polyglot solution. It provides an end to end walkthrough of a business application that uses SQL Server in conjunction with a variety of NoSQL databases, showing how the designers selected the databases to closely match the various business requirements.
Applies to: SQL Server 2012Source: MSDN Library (patterns & practices)
E-book publication date: September 2013
273 pages

PDF | EPUB Source content
 Introducing Microsoft SQL Server 2008 R2
Ross Mistry and Stacia Misner
Overview
Our purpose in this book is to point out both the new and the improved in SQL Server 2008 R2. There are a lot of exciting enhancements and new capabilities engineered into SQL Server 2008 R2 that will have a positive impact on your applications, ranging from improvements in operation to those in management. Applies to: SQL Server 2008 R2
Publisher: Microsoft Press
E-book publication date: April 2010
216 pages

XPS | PDF | Source content
 Introducing Microsoft SQL Server 2012
Ross Mistry and Stacia Misner
Overview
Microsoft SQL Server 2012 is Microsoft’s first cloud-ready information platform. It gives organizations effective tools to protect, unlock, and scale the power of their data, and it works across a variety of devices and data sources, from desktops, phones, and tablets, to datacenters and both private and public clouds. Our purpose in this book is to point out both the new and the improved capabilities as they apply to achieving mission-critical confidence, breakthrough insight, and using a cloud on your terms.
Applies to: SQL Server 2012
Publisher: Microsoft Press
E-book publication date: March 2012
288 pages

EPUB
| MOBI | PDF | Source content
 Master Data Services Capacity Guidelines
Yair Helman
Quick Guide
This document provides capacity planning guidelines for Microsoft SQL Server 2012 Master Data Services (MDS).
Applies to: SQL Server 2012
Source: TechNet Wik
i

E-book publication date
: May 2012
6 pages

EPUB | MOBI PDF  | Source content  Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery
LeRoy Tuttle, Jr.
Quick Guide
This white paper discusses how to reduce planned and unplanned downtime, maximize application availability, and provide data protection using SQL Server 2012 AlwaysOn high availability and disaster recovery solutions.A key goal of this paper is to establish a common context for related discussions between business stakeholders, technical decision makers, system architects, infrastructure engineers, and database administrators. Applies to: SQL Server 2012
Source: White paper
Ebook publication date : May 2012
32 pages

EPUB MOBI PDF | DOC | Source content  Microsoft SQL Server Analysis Services Multidimensional Performance and Operations Guide
Thomas Kejser and Denny Lee
Guide
Download this book to learn about Analysis Services Multidimensional performance tuning from an operational and development perspective. This book consolidates the previously published SQL Server 2008 R2 Analysis Services Operations Guide and SQL Server 2008 R2 Analysis Services Performance Guide into a single publication that you can view on portable devices.Applies to: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012
Source: White paper
E-book publication date: May 2012
200 pages

EPUB MOBI PDF  | Source content 1 Source content 2
QuickStart: Learn DAX Basics in 30 Minutes
Owen Duncan
Quick Step-by-Step
This QuickStart is for users new to PowerPivot or tabular model projects authored in SQL Server Data Tools. It is meant to give you a quick and easy introduction on how you can use Data Analysis Expressions (DAX) to solve a number of basic data modeling and analytical problems. Applies to: PowerPivot for Excel (all versions), SQL Server 2008 R2, SQL Server 2012
Source: TechNet Wiki
E-book publication date: May 2012
13 pages

EPUB MOBI PDF  | Source content
 SQL Server 2012 Tutorials: Analysis Services – Data Mining
SQL Server 2012 Books Online
Step-by-Step
Microsoft SQL Server Analysis Services makes it easy to create sophisticated data mining solutions. The step-by-step tutorials in the following list will help you learn how to get the most out of Analysis Services, so that you can perform advanced analysis to solve business problems that are beyond the reach of traditional business intelligence methods.Applies to: SQL Server 2012
Source: SQL Server 2012 Books Online
E-book publication date: June 2012
213 pages
EPUB MOBI PDF Source content  SQL Server 2012 Tutorials: Analysis Services – Multidimensional Modeling
SQL Server 2012 Books Online
Step-by-Step
This tutorial describes how to use SQL Server Data Tools to develop and deploy an Analysis Services project, using the fictitious company Adventure Works Cycles for all examples. Applies to: SQL Server 2012
Source: SQL Server 2012 Books Online
E-book publication date: June 2012
168 pages
EPUB MOBI PDF Source content  SQL Server 2012 Tutorials: Analysis Services – Tabular Modeling
SQL Server 2012 Books Online
Quick Step-by-Step 
This tutorial provides lessons on how to create a SQL Server 2012 Analysis Services tabular model running in In-Memory mode by using SQL Server Data Tools (SSDT).Applies to: SQL Server 2012
Source: SQL Server 2012 Books Online
E-book publication date: June 2012
57 pages
EPUB MOBI PDF Source content  SQL Server 2012 Tutorials: Reporting Services
SQL Server 2012 Books Online 
Quick Step-by-Step 
This book contains tutorials for SQL Server 2012 Reporting Services: Create a Data-Driven Subscription, Create a Basic Table Report,  Create a Sample Report in Power View, and Optimize a Sample PowerPivot Model for Power View Reports.Applies to: SQL Server 2012
Source: SQL Server 2012 Books Online
E-book publication date: June 2012
61 pages
EPUB MOBI PDF Source content  SQL Server 2012 Tutorials: Writing Transact-SQL Statements
SQL Server 2012 Books Online
Quick Step-by-Step 
This tutorial is intended for users who are new to writing SQL statements. It will help new users get started by reviewing some basic statements for creating tables and inserting data. This tutorial uses Transact-SQL, the Microsoft implementation of the SQL standard. This tutorial is intended as a brief introduction to the Transact-SQL language and not as a replacement for a Transact-SQL class. The statements in this tutorial are intentionally simple, and are not meant to represent the complexity found in a typical production database.Applies to: SQL Server 2012
Source: SQL Server 2012 Books Online
E-book publication date: June 2012
21 pages
EPUB MOBI PDF Source content  Troubleshooting SQL Server AlwaysOn
Vijay Rodrigues
Quick Reference 
SQL Server AlwaysOn is the latest High Availability (HADR) offering in Microsoft SQL Server. SQL Server AlwaysOn has been introduced in SQL 2012. This document is meant as a quick reference. This document has common troubleshooting information that may be have been encountered either by me or by my colleagues, with troubleshooting steps/commands that are publicly available in SQL Server Books Online (BOL) on MSDN. Rather than having this information in multiple blogs posts (there are already quite a few on the internet), I just felt a combined document may make this information more readable to a user, as a quick reference guide. Applies toSQL Server 2012, SQL Server 2014
E-book publication date: February 2014
44 pages
PDF  System Center Cmdlet Reference for App Controller in System Center 2012 SP1
Microsoft
Reference
The Windows PowerShell module for App Controller includes cmdlets that are designed specifically for App Controller. This Cmdlet Reference contains the help files for these cmdlets. The topics in this guide include information about the cmdlets and their associated parameters, and provide examples about how to use the cmdlets.Applies to: System Center 2012 Service Pack 1 (SP1) – App Controller
Source: TechNet Library
E-book publication date: April 2013
76 pages
EPUB MOBI PDF Source content
 Technical Documentation for System Center 2012 – Virtual Machine Manager
VMM Information Experience Team
GuideVirtual Machine Manager (VMM) is a management solution for the virtualized datacenter, enabling you to configure and manage your virtualization host, networking, and storage resources in order to create and deploy virtual machines and services to private clouds that you have created.Applies to: System Center 2012 – Virtual Machine Manager (VMM) and Virtual Machine Manager in System Center 2012 Service Pack
Source: TechNet LibraryE-book publication date: April 2013
549 pages
EPUB MOBI PDF Source content Visual Studio Better Unit Testing with Microsoft Fakes
Visual Studio ALM Rangers
Guide 
This book contains practical guidance for migrating to and unit testing with Microsoft Fakes. Practical walk-throughs allow you to navigate basic and advanced concepts, giving you a comfortable and confident start in implementing Microsoft Fakes as a mocking solution.Applies to: Visual Studio 2012
E-book publication date: March 2013
97 pages
EPUB MOBI |
PDF  Building a Release Pipeline with Team Foundation Server 2012
patterns & practices
Guide 
Leaders in the world of online businesses have shrunk the timeline for software delivery from months to days or even hours. No matter what size business you have, customers now expect features such as real-time customer service and frequent releases of services.
The goal of this guidance is to put you on the road toward continuous delivery. By continuous delivery, we mean that through techniques such as versioning, continuous integration, automation, and environment management, you will be able to decrease the time between when you first have an idea and when that idea is realized as software that’s in production. Any software that has successfully gone through your release process will be software that is production ready, and you can give it to customers whenever your business demands dictate. We also hope to show that there are practical business reasons that justify every improvement you want to make.             
.Applies toVisual Studio 2012
Source: MSDN Library (patterns & practices)
E-book publication date: September 2013
161 pages
PDF | EPUB Source content
 Developer’s Guide to Dependency Injection Using Unity
patterns & practices
Guide 
This guide is one of the resources available with the Unity v3 release to help you to learn about Unity, learn about some of the problems and issues that Unity can help you to address, and get started using Unity in your applications. Unity is primarily a dependency injection container and so the guide also contains an introduction to dependency injection that you can read in isolation even if you don’t plan to use Unity, although we hope you will.
This book is intended for any architect, developer, or information technology (IT) professional who designs, builds, or operates applications and services and who wants to learn how to realize the benefits of using the Unity dependency injection container in his or her applications. You should be familiar with the Microsoft .NET Framework, and Microsoft Visual Studio to derive full benefit from reading this guide.
Applies toVisual Studio 2012Source: MSDN Library (patterns & practices)
E-book publication date: August 2013
142 pages
PDF | EPUB Source content
 Developer’s Guide to Microsoft Enterprise Library, 2nd Edition
patterns & practices
Guide 
Microsoft Enterprise Library is a collection of application blocks designed to assist developers with common enterprise development challenges. Application blocks are a type of guidance, provided as source code that can be used “as is,” extended, or modified by developers for use in their development projects.Applies to:Visual Studio 2012
Source: MSDN Library (patterns & practices)
E-book publication date: November 2013
245 pages
PDF | EPUB Source content
 Moving to Microsoft Visual Studio 2010
Patrice Pelland, Pascal Paré, and Ken Haines
Guide
This book is for professional developers who are working with previous versions of Visual Studio and are looking to make the move to Visual Studio 2010 Professional.
Applies to: Visual Studio 2010
Publisher: Microsoft Press
E-book publication date: September 2010
36 pages

XPS | PDF | Source content
 Testing for Continuous Delivery with Visual Studio 2012
patterns & practices
GuideTesting is potentially an obstacle to agile software deployment. The smallest change in your code could require the whole application to be retested. This book shows you how to minimize these costs by using the testing infrastructure of Visual Studio 2012. The code samples gives you a starting point to test your infrastructure.Applies to: Visual Studio 2012
Source: MSDN Library (patterns & practices)E-book publication date: December 2012
248 pages
EPUB | MOBI | PDF | Source content  Upgrade Team Foundation Server 2012: the ultimate upgrade guide
Tim Elhajj
Step-by-Step
This is the same guidance that Microsoft offers its upgrade customers, now in an e-book format for your convenience. Step-by-step guidance that covers the most popular upgrade tasks, including in-place upgrades and upgrades that involve moving to new hardware.
Applies to: Visual Studio 2012, Team Foundation Server 2012
Source: MSDN Library
E-book publication date: July 2012
61 pages

EPUB | MOBI | PDF | Source content
Web Development
 ASP.NET Multi-Tier Windows Azure Application Using Storage Tables, Queues, and Blobs
Rick Anderson   Tom Dykstra
Step-by-Step
This tutorial series shows how to create a multi-tier ASP.NET MVC 4 web application that uses Windows Azure Storage tables, queues, and blobs, and how to deploy the application to a Windows Azure Cloud Service. The tutorials assume that you have no prior experience using Windows Azure. On completing the series, you’ll know how to build a resilient and scalable data-driven web application and deploy it to the cloud.Applies to: Windows Azure, ASP.NET MVC, Windows Azure Storage Queues, Windows Azure Storage Tables, Windows Azure Storage Blobs
Source: WindowsAzure.com
E-book publication date: February 2013
207 pages
EPUB MOBI PDF Source content
 ASP.NET Web Deployment using Visual Studio
Tom Dykstra
Step-by-Step
This tutorial series shows you how to deploy (publish) an ASP.NET web application to a Windows Azure Web Site or a third-party hosting provider, by using Visual Studio 2012 or Visual Studio 2010. You develop a web application in order to make it available to people over the Internet. But web programming tutorials typically stop right after they’ve shown you how to get something working on your development computer. This tutorial begins where the others leave off: you’ve built a web site, tested it, and it’s ready to go. What’s next? This tutorial shows you how to deploy first to IIS on your local development computer for testing, and then to Windows Azure or a thirdparty hosting provider for staging and production. The sample application that you’ll deploy is a web application project that uses the Entity Framework, SQL Server, and the ASP.NET membership system. Entity Framework Code First Migrations is used to deploy the application database. The sample application uses ASP.NET Web Forms, but the procedures shown apply also to ASP.NET MVC and Web API.Applies to: ASP.NET, Visual Studio 2010, Visual Studio 2012, Windows Azure
Source: ASP .NET Site
E-book publication date: April 2013
155 pages
EPUB MOBI PDF Source content
 ASP.NET Web Deployment with SQL Server Compact using Visual Studio 2010
Tom Dykstra
Step-by-Step
This tutorial series shows how to make an ASP.NET web application that uses SQL Server Compact available over the internet by deploying it to a third-party hosting provider. Uses Visual Studio 2010 without the Web Publish Update. Does not use Entity Framework Code First Migrations for deployment. For more up-to-date information about deployment features, or for information about how to deploy SQL Server editions other than SQL Server Compact, see ASP.NET Web Deployment using Visual Studio.Applies to: ASP.NET 4.0 and earlier, Visual Studio 2010
Source: ASP.NET site
E-book publication date: May 2012
162 pages
EPUB  | MOBI  | PDF Source content    Developing Modern Mobile Web Apps
patterns & practices 
Guide
This project provides guidance on building mobile web experiences using HTML5, CSS3, and JavaScript. Developing web apps for mobile browsers can be less forgiving than developing for desktop browsers. There are issues of screen size, the availability of specific feature support, and other differences between mobile browsers that will impact how you develop your apps. In addition, there are various levels of support for the emerging standards of HTML5 and CSS3, and standards for some features, such as touch, are just beginning to take shape. All of these factors suggest that it is best to keep your HTML, CSS, and JavaScript as simple as you can in order to ensure compatibility with as many devices as possible. This project illustrates how to do this, as well as how to add more advanced functionality where supported.Applies to: ASP.NET MVC 4, HTML5, CSS3, and JavaScript
Source: MSDN Library (patterns & practices)
E-book publication date: June 2012
111 pages

EPUB MOBI PDF Source content  The Entity Framework 4.0 and ASP.NET Web Forms – Getting Started
Tom Dykstra 
Step-by-Step 
In this book, you’ll learn the basics of using Entity Framework Database First to display and edit data in an ASP.NET Web Forms application.Applies to: ASP.NET 4.0, ASP.NET Web Forms, Entity Framework 4.0, Visual Studio 2010
Source: ASP.NET site
E-book publication date: June 2012
257 pages

EPUB MOBI PDF Source content  Getting Started with ASP.NET 4.5 Web Forms and Visual Studio 2013
Erik Reitan 

Step-by-Step
This series of tutorials guides you through the steps required to create an
ASP.NET Web Forms application using Visual Studio Express 2013 for Web and ASP.NET 4.5.
Applies to: ASP.NET Web Forms

Source
: ASP.NET site
E-book publication date: January, 2014

200 pages

PDF  | Source content  Getting Started with the Entity Framework 4.1 using ASP.NET MVC
Tom Dykstra

Step-by-Step
In this book, you’ll learn the basics of using Entity Framework Code First to
display and edit data in an ASP.NET MVC application.
Applies to: ASP.NET 4.0, MVC 3, Entity Framework 4.1, Visual Studio 2010

Source
: ASP.NET site
E-book publication date: June 2012

59 pages

EPUB  | MOBI PDF | Source content  Intro to ASP.NET MVC 4 with Visual Studio (Beta) Rick Anderson and Scott Hanselman
Step-by-StepThis tutorial will teach you the basics of building an ASP.NET MVC Web application using Microsoft Visual Studio 11 Express Beta for Web, which is a free version of Microsoft Visual Studio.Applies to: ASP.NET MVC 4 Beta, Visual Studio 11 Beta, ASP.NET MVC 4 RC, Visual Studio 2012 RC
Source: ASP.NET site
E-book publication date: May 2012
115 pages
EPUB  | MOBI PDF  | Source content  Introducing ASP.NET Web Pages 2
Mike Pope
Step-by-Step 
This set of tutorials gives you an overview of ASP.NET Web Pages (version 2) and Razor syntax, a lightweight framework for creating dynamic websites. It also introduces WebMatrix, a tool for creating pages and sites. The tutorials take you from novice programmer through seeing your site live on the Internet. Topics include how to install Microsoft WebMatrix (a set of tools for creating sites); how to work with forms; how to display, add, update, and delete data; how to create a consistent site layout; and how to publish to the Web.Applies to: ASP.NET Web Pages 2 RC, Visual Studio 2012 RC
Source: ASP.NET site
E-book publication date: June 2012
147 pages

EPUB MOBI PDF Source content  Project Silk: Client-Side Web Development for Modern Browsers
patterns & practices
Guide
Project Silk provides guidance for building maintainable cross-browser web applications that are characterized by rich graphics, interactivity, and a responsive user interface, resulting in an immersive and engaging user experience. Project Silk also illustrates how you can take advantage of the fast JavaScript engines of the modern, standards-based web browsers to achieve a user experience and execution speeds that rivals desktop applications.
Applies to: SQL Server 2012Source: Microsoft SQL Server 2012 Books Online
E-book publication date: September 2011
319
pages
PDF | Source content
Windows
 Administrator’s Guide for Microsoft Application Virtualization (App-V) 5.0
MDOP Information Experience Team
Guide
Microsoft Application Virtualization (App-V) 5.0 helps businesses provide their end users with access to virtually any application, anywhere without installing applications directly on their computers.Applies to: App-V 5.0
Source: TechNet Library
E-book publication date: February 2013
168 pages
EPUB MOBI PDF | Source content
 Administrator’s Guide for Microsoft BitLocker Administration and Monitoring 1.0
MDOP Information Experience Team
Guide
This guide provides background information about MBAM and describes how to install and use the product. The intended audience for the guide is MBAM administrators and IT personnel.Applies to: MBAM 1.0
Source: TechNet Library
E-book publication date: February 2013
110 pages
EPUB MOBI PDF Source content
 Administrator’s Guide for Microsoft Diagnostics and Recovery Toolset (DaRT) 7
MDOP Information Experience Team
Guide
Microsoft Diagnostics and Recovery Toolset (DaRT) 7 lets you diagnose and repair a computer that cannot be started or that has problems starting as expected. By using DaRT 7, you can recover end-user computers that have become unusable, diagnose probable causes of issues, and quickly repair unbootable or locked-out computers. When it is necessary, you can also quickly restore important lost files and detect and remove malware, even when the computer is not online.Applies to: DaRT 7.0
Source: TechNet Library
E-book publication date: February 2013
42 pages
EPUB MOBI PDF Source content
 Administrator’s Guide for Microsoft Diagnostics and Recovery Toolset (DaRT) 8.0
MDOP Information Experience Team
Guide
Microsoft Diagnostics and Recovery Toolset (DaRT) 8.0 lets you diagnose and repair a computer that cannot be started or that has problems starting as expected. By using DaRT 8.0, you can recover end-user computers that have become unusable, diagnose probable causes of issues, and quickly repair unbootable or locked-out computers. When it is necessary, you can also quickly restore important lost files and detect and remove malware, even when the computer is not online.Applies to: DaRT 8.0
Source: TechNet Library
E-book publication date: February 2013
49 pages
EPUB MOBI PDF Source content
 Administrator’s Guide for Microsoft User Experience Virtualization (UE-V) 1.0
MDOP Information Experience Team
Guide
Microsoft User Experience Virtualization (UE-V) captures and centralizes application settings and Windows operating system settings for the user. These stored settings are then applied to the different computers that are accessed by the user, including desktops, notebooks, and virtual desktop infrastructure (VDI) sessions.
Applies to: UE-V 1.0
Source: TechNet Library
E-book publication date: February 2013
64 pages
EPUB MOBI PDF Source content
 Deploying Windows 7: Essential Guidance
Windows 7 Resource Kit and TechNet Magazine
Guide
Looking for guidance specific to Windows 7 deployment? Check out what the industry’s leading experts have to say in this free Microsoft Press eBook with selected chapters from the Windows 7 Resource Kit and TechNet Magazine.
Applies to: Windows 7
Publisher: Microsoft Press
E-book publication date: October 2009
332 pages

PDF | Source content
 Developing an end-to-end Windows Store app using C++ and XAML: Hilo
patterns & practices
GuideThe Hilo end-to-end photo sample provides guidance to C++ developers that want to create a Windows 8 app using modern C++, XAML, the Windows Runtime, and recommended development patterns. Hilo comes with source code and documentation.Applies to: Windows 8
Source: MSDN Library (patterns & practices)E-book publication date: November 2012
219 pages
PDF | Source content  Developing an end-to-end Windows Store app using JavaScript: Hilo
patterns & practices
GuideThe JavaScript version of the Hilo photo sample provides guidance to JavaScript developers who want to create a Windows 8 app using HTML, CSS, JavaScript, the Windows Runtime, and modern development patterns. Hilo comes with source code and documentation.Applies to: Windows 8
Source: MSDN Library (patterns & practices)E-book publication date: December 2012
125 pages
PDF | Source content  Prism for the Windows Runtime: Developing a Windows Store business app using C#, XAML, and Prism
patterns & practices
Guide
This book provides guidance to developers who want to create a Windows Store business app using C#, Extensible Application Markup Language (XAML), the Windows Runtime, and modern development practices. The guide comes with source code for Prism for the Windows Runtime, source code for the AdventureWorks Shopper product catalog and shopping cart reference implementation, and documentation. The PDF provides guidance on how to implement MVVM with navigation and app lifecycle management, validation, manage application data, implement controls, accessible and localizable pages, touch, search, tiles, and tile notifications. It also provides guidance on testing your app and tuning its performance. Applies to: Windows 8
Publisher: patterns & practices
E-book publication date: May 2013
222 pages

PDF | Source content  | Sample code


Programming Windows 8 Apps with HTML, CSS, and JavaScript
Kraig Brockschmidt
Guide
This book is about writing Windows 8 apps using HTML5, CSS3, and JavaScript. Our primary focus will be on applying these web technologies within the Windows 8 platform, where there are unique considerations, and not on exploring the details of those web technologies themselves.
Applies to: Windows 8
Publisher: Microsoft Press
E-book publication date: October 2012
833 pages

PDF | EPUB | MOBI Sample code | Source content

Windows Azure Autoscaling Application Block and Transient Fault Handling Application Block Reference
patterns & practices 
Reference
The Autoscaling Application Block provides a mechanism for adding autoscaling behaviors to Windows Azure applications based on predictive usage patterns or reactive rules. The Transient Fault Handling Application Block provides a set of reusable and testable components for adding retry logic into your Windows Azure applications by using Windows Azure SQL Database, Windows Azure storage, Service Bus and Caching Service. This makes your Windows Azure application more reliable and resilient to transient faults (such as temporary network connectivity issues or temporary service unavailability). This also improves overall application stability. The blocks are part of the Microsoft Enterprise Library Integration Pack for Windows Azure.Applies to: Windows Azure SDK for .NET (includes the Visual Studio Tools for Windows Azure), Windows Azure SQL Database, Windows Azure Service Bus, Enterprise Library 5, Microsoft .NET Framework version 4.0, Microsoft Visual Studio 2010
Source: MSDN Library (patterns & practices)
E-book publication date: June 2012
159 pages

EPUB MOBI PDF Source content  Building Elastic and Resilient Cloud Applications – Developer’s Guide to the Enterprise Library 5.0 Integration Pack for Windows Azure
patterns & practices
Guide
The Microsoft Enterprise Library is a set of reusable application blocks that help developers meet common enterprise software development challenges. The Microsoft Enterprise Library Integration Pack for Windows Azure is an extension to the Microsoft Enterprise Library 5.0 that can be used with Windows Azure. It includes the Autoscaling Application Block, the Transient Fault Handling Application Block, a protected configuration provider and the Blob configuration source.
Applies to: Windows AzureSource: MSDN Library (patterns & practices)
E-book publication date: June 2012
220 pages
EPUB
MOBI PDF | Source content  Building Real World Cloud Apps With Windows Azure
Tom Dykstra, Rick Anderson, Mike Wasson

Guide
This e-book walks you through a patterns-based approach to building real-world cloud solutions. The patterns apply to the development process as well as to architecture and coding practices. The content is based on a presentation developed by Scott Guthrie and originally delivered at the Norwegian Developers Conference (NDC) in June of 2013. Many others updated and augmented the content while transitioning it from video to written form.Applies to: Windows Azure Web Sites, ASP.NET, Visual Studio, Visual Studio Online, Windows Azure Active Directory, Windows Azure SQL Database

Source
: ASP.NET site

E-book publication date
: January, 2014

227 pages

PDF  | Source content  Building Hybrid Applications in the Cloud on Windows Azure
patterns & practices
Guide
This guide focuses on the common challenges you will encounter when building applications that run partly in the cloud and partly on-premises, or when you decide to migrate some or all elements of an existing on-premises application to the cloud. It focuses on using Windows Azure as the host environment, and shows how you can take advantage of the many features of this platform, together with Windows Azure SQL Database, to simplify and speed the development of these kinds of applications.
Applies to: Windows Azure, Windows Azure SQL Database, SQL Server, Windows Identity Foundation, Windows Azure Service Bus, Enterprise Library, Windows Azure Diagnostics, Windows Azure Management Cmdlets
Source: MSDN Library (patterns & practices)
E-book publication date: July 2012
405 pages
PDF Source content  Create Your First Application: Node.js and Windows Azure
Windows Azure Developer Center
Quick Step-by-Step 
Create your first application using Node.js and Windows Azure. 

  • Implement a simple Hello World application in Node.js and deploy the application to a Windows Azure Web Site.
  • Learn how to use the Windows Azure PowerShell cmdlets to create a Node.js application, test it in the Windows Azure Emulator, and then deploy it as a Windows Azure Cloud Service.
  • Implement a Node.js application using WebMatrix, and then deploy it to a Windows Azure web site.
  • Implement a task list application using Node.js and MongoDB.

Applies to: Windows Azure, Node.js
Source: Windows Azure Developer Center
E-book publication date: June 2012
86 pages

EPUB MOBI PDF Source content  Developing Multi-tenant Applications for the Cloud on Windows Azure (3rd Edition)
patterns & practices
GuideThis guide is the third release of the second volume in a series about Windows Azure. It demonstrates how you can create from scratch a multitenant, Software as a Service (SaaS) application to run in the cloud by using the latest versions of the Windows Azure tools and the increasing range of capabilities of Windows Azure.
The guide is intended for any architect, developer, or information technology (IT) professional who designs, builds, or operates applications and services that are appropriate for the cloud. It is primarily written for people who work with Windows-based systems. You should be familiar with the Microsoft .NET Framework, Microsoft Visual Studio, ASP.NET, SQL Server and Microsoft Visual C#.Applies to: Windows Azure
Source: MSDN Library (patterns & practices)E-book publication date: December 2012
246 pages
PDF | Source content  Deploy SQL Server Business Intelligence in Windows Azure Virtual Machines
Chuck Heinzelman
Step-by-StepThis document describes and walks you through the creation of a multiserver deployment of SQL Server Business Intelligence features, in a Windows Azure Virtual Machines environment. The document focuses on the use of Windows PowerShell scripts for each step of the configuration and deployment process.Applies to: SQL Server 2012 SP1, Windows Azure
Source: MSDN LibraryE-book publication date: August 2013
90 pages
PDF Source content  Drupal on Windows Azure
Rama Ramani, Jason Roth, Brian Swan
Quick Guide
This e-book explains the migration, architecture patterns and management of your Drupal based website on Windows Azure.Applies to: Windows Azure
Source: MSDN blogs
E-book publication date: June 2012
20 pages

EPUB MOBI PDF Source content 1 Source content 2  Exploring CQRS and Event Sourcing: A journey into high scalability, availability, and maintainability with Windows Azure
patterns & practices
GuideThis guide is focused on building highly scalable, highly available, and maintainable applications with the Command & Query Responsibility Segregation and the Event Sourcing architectural patterns. It presents a learning journey, not definitive guidance. It describes the experiences of a development team with no prior CQRS proficiency in building, deploying (to Windows Azure), and maintaining a sample real-world, complex, enterprise system to showcase various CQRS and ES concepts, challenges, and techniques. The development team did not work in isolation; we actively sought input from industry experts and from a wide group of advisors to ensure that the guidance is both detailed and practical.Applies to: Windows Azure
Source: MSDN Library (patterns & practices)E-book publication date: July 2012
378 pages
PDF | Source content  Migrating Data-Centric Applications to Windows Azure
Kun Cheng, Selcin Turkarslan, Norberto Garcia, Steve Howard, Shaun Tinline-Jones, Sreedhar Pelluru, Silvano Coriani, Jaime Alva Bravo
Guide
This guide provides experienced developers and information technology (IT) professionals with detailed guidance on how to migrate their data-centric applications to Windows Azure Cloud Services, while also providing an introduction on how to migrate those same applications to Windows Azure Virtual Machines. By using this guide, you will have the planning process, migration considerations, and prescriptive how to’s needed for a positive migration experience. Capturing the best practices from the real-world engagements of CAT and the technical expertise of the SQL Database Content team, Migrating Data-Centric Applications to Windows Azure can help you simplify the migration process, provide guidance on the most appropriate migration tools, and drive a successful implementation of your migration plan.Applies to: Windows Azure
Source: MSDN Library
E-book publication date: June 2012
143 pages

EPUB MOBI PDF Source content  Moving Applications to the Cloud, 2nd Edition
patterns & practices
Guide
This book demonstrates how you can adapt an existing, on-premises ASP.NET application to one that operates in the cloud. The book is intended for any architect, developer, or information technology (IT) professional who designs, builds, or operates applications and services that are appropriate for the cloud. Although applications do not need to be based on the Microsoft Windows operating system to work in Windows Azure, this book is written for people who work with Windows-based systems. You should be familiar with the Microsoft .NET Framework, Microsoft Visual Studio, ASP.NET, and Microsoft Visual C#.Applies to: Windows Azure SDK for .NET (includes the Visual Studio Tools for Windows Azure), Windows Azure SQL Database, Microsoft SQL Server or SQL Server Express 2008, Windows Identity Foundation, Enterprise Library 5, WatiN 2.0, Microsoft Anti-Cross Site Scripting Library V4, Microsoft .NET Framework version 4.0, Microsoft Visual Studio 2010
SourceMSDN Library (patterns & practices)
E-book publication date: June 2012
128 pages
EPUB MOBI PDF Source content  Moving Applications to the Cloud on Windows Azure (3rd Edition)
patterns & practices
Guide
This guide is the third edition of the first volume in a series about Windows Azure. It demonstrates how you can adapt an existing on-premises ASP.NET application to one that operates in the cloud by introducing a fictitious company named Adatum that modifies its expense tracking and reimbursement system, aExpense, so that it can be deployed to Windows Azure. To illustrate the wide range of options and features in Windows Azure, this guide and the code examples available for it show a step-by-step migration process that includes using Windows Azure Web Sites, Virtual Machines, Cloud Services, and SQL Database. Together with useful information on developing, deploying, managing, and costing cloud-hosted applications, this guide provides you with a comprehensive resource for moving your applications to Window Azure.Applies to: Windows Azure
Source: MSDN Library (patterns & practices)
E-book publication date: April 2013
206 pages
PDF | Source content
 Using Windows Azure Mobile Services to Cloud-Enable your iOS Apps
Windows Azure Developer Center
Step-by-Step
This topic shows you how to use Windows Azure Mobile Services to leverage data in an iOS app. In this tutorial, you will download an app that stores data in memory, create a new mobile service, integrate the mobile service with the app, and then login to the Windows Azure Management Portal to view changes to data made when running the app.Applies to: Windows Azure Mobile Services
Source: Windows Azure Developer Center
E-book publication date: January 2013
11 pages
EPUB | MOBI | PDF | Source content
 Using Windows Azure Mobile Services to Cloud-Enable Your Windows Phone 8 Apps
Windows Azure Developer Center
Step-by-Step
This section shows you how to use Windows Azure Mobile Services to leverage data in a Windows Phone 8 app. In this tutorial, you will download an app that stores data in memory, create a new mobile service, integrate the mobile service with the app, and then login to the Windows Azure Management Portal to view changes to data made when running the app.Applies to: Windows Azure Mobile Services, Windows Phone 8
Source: Windows Azure Developer Center
E-book publication date: January 2013
84 pages
EPUB | MOBI | PDF | Source content
 Using Windows Azure Mobile Services to Cloud-Enable your Windows Store Apps in C#
Windows Azure Developer Center
Step-by-Step
This section shows you how to use Windows Azure Mobile Services and C# to leverage data in a Windows Store app. In this tutorial, you will download an app that stores data in memory, create a new mobile service, integrate the mobile service with the app, and then login to the Windows Azure Management Portal to view changes to data made when running the app.Applies to: Windows Azure Mobile Services
Source: Windows Azure Developer Center
E-book publication date: January 2013
86 pages
EPUB | MOBI | PDF | Source content
 Using Windows Azure Mobile Services to Cloud-Enable Your Windows Store Apps in JavaScript
Windows Azure Developer Center
Step-by-Step
This section shows you how to use Windows Azure Mobile Services and JavaScript to leverage data in a Windows Store app. In this tutorial, you will download an app that stores data in memory, create a new mobile service, integrate the mobile service with the app, and then login to the Windows Azure Management Portal to view changes to data made when running the app.Applies to: Windows Azure Mobile Sources
Source: Windows Azure Developer Center
E-book publication date: January 2013
87 pages
EPUB | MOBI | PDF | Source content
 Windows Azure and SQL Database Tutorials
Jonathan Gao
Step-by-Step
These Windows Azure and SQL Database (formerly SQL Azure) tutorials are designed for beginners who have some .NET development experience. Using a common scenario, each tutorial introduces one or two Windows Azure features or components. Even though each tutorial builds upon the previous ones, the tutorials are self-contained and can be used without completing the previous tutorials.Applies to: Windows Azure SQL Database
Source: TechNet Wiki
E-book publication date: November 2012
92 pages
EPUB | MOBI | PDF | Source content
 Windows Azure Prescriptive Guidance
Larry Franks, Sidney Higa, Suren Machiraju, Christian Martinez, Valery Mizonov, Walter Myers III, Rama Ramani, Jason Roth, Mark Simms, Paolo Salvatori, Adam Skewgar, Ralph Squillace, Patrick Wickline, Trace Young
Guide
Windows Azure Prescriptive Guidance provides you with some of the best practices and recommendations for working with the Windows Azure platform. Categories cover planning, designing, developing, and managing a variety of different types of Windows Azure applications, including mobile, enterprise, and consumer-based applications. It also provides guidance for developers using non-.NET applications, libraries, and stacks with Windows Azure.
Applies to: Windows Azure, Windows Azure SQL Database, Windows Azure Cloud Services, and Enterprise Integration Applications
Source: MSDN Library
E-book publication date: May 2012
422 pages
EPUB | MOBI PDF |
Source content  Windows Azure Service Bus Reference
Seth Manheim and Ralph Squillace
Reference
The Windows Azure Service Bus provides a hosted, secure, and widely available infrastructure for widespread communication, large-scale event distribution, naming, and service publishing. The Service Bus provides connectivity options for Windows Communication Foundation (WCF) and other service endpoints – including REST endpoints — that would otherwise be difficult or impossible to reach. Endpoints can be located behind network address translation (NAT) boundaries, or bound to frequently-changing, dynamically-assigned IP addresses, or both.Applies to: Windows Azure Service Bus
Source: MSDN Library
E-book publication date: May 2012
260 pages

EPUB | MOBI PDF | Source content Windows Phone Developing an Advanced Windows Phone 7.5 App that Connects to the Cloud patterns & practices
Guide
This guide provides an end-to-end walkthrough of how to create an advanced Windows Phone 7.5 app that integrates with remote Windows Azure-based services. The guide can help you understand how to implement individual features for Windows Phone 7.5 and how to use them together to build an app.
After reading this book, you will be familiar with how to design and implement advanced applications for Windows Phone that take advantage of remote services to obtain and up-load data while providing a great user experience on the device.
Applies to : Windows Phone 7.5, Windows Azure
Source: MSDN Library (patterns & practices)
E-book publication date: January 2012
208 pages
PDF | Source content
 Programming Windows Phone 7 Charles Petzold
Guide
Get started building applications for Windows Phone 7—expertly guided by award-winning author Charles Petzold. You’ll focus on the core concepts and techniques for creating apps with Microsoft® XNA and Microsoft Silverlight®, with coverage of Microsoft Visual Studio®, .NET Framework managed code sandbox, the phone emulator, sensors, and location. As always, Charles brings a unique combination of pragmatism and authority to his instruction—along with an eminently readable style and a wealth of hands-on examples.
Note: This book is not applicable to Windows Phone 7.5 or Windows Phone 8 development.
Applies to : Windows Phone 7
Publisher: Microsoft Press

E-book publication date: October 2010
960 pages
EPUB  | MOBI | PDF | Source content

Windows Server
 A Guide to Claims-Based Identity and Access Control, Second Edition
patterns & practices
Guide Imagine a world where you don’t have to worry about authentication. Imagine instead that all requests to your application already include the information you need to make access control decisions and to personalize the application for the user. In this world, your applications can trust another system component to securely provide user information, such as the user’s name or email address, a manager’s email address, or even a purchasing authorization limit. The user’s information always arrives in the same simple format, regardless of the authentication mechanism, whether it’s Microsoft® Windows® integrated authentication, forms-based authentication in a web browser, an X.509 client certificate, or something more exotic. Even if someone in charge of your company’s security policy changes how users authenticate, you still get the information, and it’s always in the same format. This is the utopia of claims-based identity that A Guide to Claims-Based Identity and Access Control describes. Applies to: Windows Identity Foundation (WIF), Microsoft Active Directory® Federation Services (ADFS) v2, and Windows Azure Access Control Service (ACS)Source: MSDN Library (patterns & practices)
E-book publication date: December 2011
411 pages
PDF  | Source content  Introducing Windows Server 2008 R2
Charlie Russel and Craig Zacker with the Windows Server Team at Microsoft
Overview
In this book, we focus on the new features and refinements in Windows Server 2008 R2. This book is targeted primarily at Windows server administrators who are responsible for hands-on deployment and day-to-day management of Windows-based servers for large organizations. Applies to: Windows Server 2008 R2
Publisher: Microsoft Press
E-book publication date: October 2009
200 pages

XPS | PDF | Source content

 Introducing Windows Server 2012
Mitch Tulloch with the Windows Server Team
Overview
This book represents a “first look” based on the RTM release of Windows Server 2012 and is intended to help IT professionals familiarize themselves with the capabilities of the new platform.
Applies to: Windows Server 2012
Publisher: Microsoft Press
E-book publication date: June 2012
256 pages

EPUB | MOBI | PDF | Source content

 Migrate Roles and Features to Windows Server 2012 R2 or Windows Server 2012Overview
This E-Book includes guidance to help you migrate server roles and features to Windows Server 2012 R2 or Windows Server 2012. Also included is an installation and operations guide for Windows Server Migration Tools, a set of five Windows PowerShell cmdlets that can be used to migrate some roles and features to Windows Server 2012 R2 or Windows Server 2012. This E-Book might not include the most up-to-date content about Windows Server migration, and is not guaranteed to be complete. To view the most up-to-date Windows Server migration content, see Migrate Roles and Features to Windows Server on the Microsoft TechNet website.
Applies to: Windows Server 2012 R2, Windows Server 2012
E-book publication date: January 2014
641 pages
PDF | Source content

 TCP/IP Fundamentals for Microsoft Windows 

Joe Davies, Author
Anne Taussig, Editor
Guide
This online book describes the fundamentals of TCP/IP in Windows Server 2008, Windows Vista, Windows XP, and Windows Server 2003, including IPv6.
Applies to: Windows Server 2008, Windows Vista, Windows XP, and Windows Server 2003
Publisher: Microsoft
E-book publication date: Jan 2012
560 pages
PDF | Source content
Learning & Career Own Your Future: Update Your Skills with Resources and Career Ideas from Microsoft
Katherine Murray
In this book, students will find a wealth of Microsoft resources they can 
use to identify the technology skills they need, and gather knowledge and 
experience to help them take charge of their careers. Here at Microsoft, we care about students’ career success and hope these resources will open doors to learning that will lead them to better opportunities and a deeper understanding  of the way technology continues to change and improve the ways people work—both here in the U.S. and around the world.
Applies to
Publisher: Microsoft Press 
E-book publication date: February 2010
126 pages
XPS  | PDF  Source content
Have a good day, now comments are welcome here.
25 DBA worst practices:While there may be some disagreement on best practices, there is usually no argument on worst practices, some of which are listed below here we go:1 Not considering service-level agreements (SLAs) when designing a database environment and/or not considering the need for scheduled downtime for various maintenance activities, such as the installation of service packs.2 Defining “disaster” too narrowly and not simulating/practicing a disaster recovery (DR) plan. Having a DR plan is fine, but how do you know it will work (and several people can follow it) when required?3 Designing a storage system from a capacity perspective alone.4 Assuming a storage area network (SAN) will meet/exceed performance requirements. Just because SANs are (typically) expensive, it does not mean the storage design process can be skipped.5 Failing to track-align disk partitions and/or formatting them with the default allocation unit size (4K).6 Using RAID 5 volumes for write-intensive applications.7 Failing to validate an I/O subsystem for performance and validity before production implementation.8 Virtualizing/consolidating SQL Server instances and databases without consideration of the scalability, licensing, support, administration, and performance profile implications.9 Installing service packs, cumulative updates, or hotfixes without reading the release notes and/or not installing them in a test environment first.10 Installing all SQL Server features on the off chance they may be needed at some point in the future. Doing so increases the attack surface area and results in running unnecessary services that may reduce performance.11 Installing multi-instance clusters without considering the resource implications of failover situations.12 Creating logins/jobs with elevated privileges. Implementing least privilege can be tough work, but it’s essential in locking down a system for maximum security.13 Changing configuration values from their default settings without adequate research and/or a detailed change log.14 Placing data and transaction logs on the same physical disk(s).15 Storing backups on the same disk as the database files.16 Relying on autogrow for file sizing, and leaving the tempdb database at its default size.17 Not making backups and/or not checking their validity and/or not practicing and documenting various recovery situations. All of these are equally bad.18 Leaving the database in the full recovery model without taking transaction log backups.19 Implementing database mirroring in high-safety (synchronous) mode without considering network latency and/or transaction log usage from index maintenance.20 Not running regular DBCC checks.21 Running REPAIR_ALLOW_DATA_LOSS as the primary/default recovery response, and not following up corruption events with a root-cause analysis.22 Not evaluating index usage and/or fragmentation levels as part of an indexmaintenance routine.23 Updating statistics using the default sampling rate after a full index rebuild.24 Using SQL Profiler in place of server-side traces, and using it as the primary performance analysis/tuning technique.25 Doing manual administration with SQL Server Management Studio. For maximum efficiency and minimal errors, tasks should be scripted and automated, and you should employ appropriate monitoring and alerting mechanisms such as MOM or SQL Agent operators and alerts. SQL Server 2014 CTP 2

Books Online for SQL Server 2014
Developer Reference for SQL Server 2014
Installation for SQL Server 2014
Setup and Servicing Installation
Upgrade Advisor
Tutorials for SQL Server 2014
Microsoft JDBC Driver 4.0 for SQL Server
Microsoft Drivers for PHP for SQL Server
Microsoft ODBC Driver for SQL Server
Microsoft OLE DB Provider for DB2 Version 4.0

Current Versions

Previous Versions

SQL Server 2014 CTP 2

SQL Server 2012

Windows Azure SQL Database

SQL Server 2008 R2

SQL Server 2008

SQL Server 2005

SQL Server 2000

Send feedbackdividerDate and support from MicorosoftThe information on this page is subject to the Microsoft Policy Disclaimer and Change Notice. Return to this site periodically to review any such changes.Export this table to a CSV fileProducts ReleasedLifecycle Start DateMainstream Support End DateExtended Support End DateService Pack Support End DateNotesSQL Server 2005 Compact Edition2/19/20074/12/20114/12/20167/10/2007 SQL Server 2005 Developer Edition1/14/20064/12/20114/12/20167/10/2007 SQL Server 2005 Enterprise Edition1/14/20064/12/20114/12/20167/10/2007 SQL Server 2005 Enterprise Edition for Itanium-based Systems1/14/20064/12/20114/12/20167/10/2007 SQL Server 2005 Enterprise X64 Edition1/14/20064/12/20114/12/20167/10/2007 SQL Server 2005 Express Edition6/1/20064/12/20114/12/20167/10/2007 SQL Server 2005 Express Edition with Advanced Services7/16/20064/12/20114/12/20167/10/2007 SQL Server 2005 Service Pack 14/18/2006Not ApplicableNot Applicable4/8/2008 SQL Server 2005 Service Pack 22/19/2007Not ApplicableNot Applicable1/12/2010 SQL Server 2005 Service Pack 312/15/2008Not ApplicableNot Applicable1/10/2012 SQL Server 2005 Service Pack 412/13/2010Review NoteReview Note Support ends 12 months after the next service pack releases or at the end of the product’s support lifecycle, whichever comes first. For more information, please see the service pack policy at http://support.microsoft.com/lifecycle/#ServicePackSupport.SQL Server 2005 Standard Edition1/14/20064/12/20114/12/20167/10/2007 SQL Server 2005 Standard Edition for Itanium-based Systems1/14/20064/12/20114/12/20167/10/2007 SQL Server 2005 Standard X64 Edition1/14/20064/12/20114/12/20167/10/2007 SQL Server 2005 Workgroup Edition1/14/20064/12/20114/12/20167/10/2007 SQL Server Compact 3.52/19/20084/9/20134/10/201810/13/2009 SQL Server Compact 3.5 Service Pack 1 for Windows Mobile8/11/2008Not ApplicableNot Applicable7/12/2011 SQL Server Compact 3.5 Service Pack 26/29/2010Review NoteReview Note Support ends 12 months after the next service pack releases or at the end of the product’s support lifecycle, whichever comes first. For more information, please see the service pack policy at http://support.microsoft.com/lifecycle/#ServicePackSupport.The information on this page is subject to the Microsoft Policy Disclaimer and Change Notice. Return to this site periodically to review any such changes. Visit here for more info from Microsoft.

Products Released Lifecycle Start Date Mainstream Support End Date Extended Support End Date Service Pack Support End Date Notes
SQL Server 2000 64-bit Edition 11/30/2000 4/8/2008 4/9/2013 7/11/2002
SQL Server 2000 Developer Edition 11/30/2000 4/8/2008 4/9/2013 7/11/2002
SQL Server 2000 Enterprise Edition 11/30/2000 4/8/2008 4/9/2013 7/11/2002
SQL Server 2000 Service Pack 1 6/12/2001 Not Applicable Not Applicable 2/28/2002
SQL Server 2000 Service Pack 2 11/30/2001 Not Applicable Not Applicable 4/7/2003
SQL Server 2000 Service Pack 3a 1/7/2003 Not Applicable Not Applicable 7/10/2007
SQL Server 2000 Service Pack 4 5/6/2005 Review Note Review Note Support ends 12 months after the next service pack releases or at the end of the product’s support lifecycle, whichever comes first. For more information, please see the service pack policy at http://support.microsoft.com/lifecycle/#ServicePackSupport.
SQL Server 2000 Standard Edition 11/30/2000 4/8/2008 4/9/2013 7/11/2002
SQL Server 2000 Windows CE Edition 2.0 12/16/2002 1/8/2008 1/8/2013
SQL Server 2000 Workgroup Edition 6/1/2005 4/8/2008 4/9/2013

Send feedbackdividerSQL Server BackupFully tested backup and restore plan in place for your SQL Server databases is one of, if not, THE most  important job that you have to ensure you get right as a DBA.Getting it wrong could mean huge financial costs to your company, potentially leaving your companies reputation in tatters and leaving you out of a job.The important thing to bear in mind is that your database backups are only good if you can restore them correctly. If you do not have a tested disaster recovery plan in place, don’t put it off any longer, get one setup and test it regularly.SQL Server Backup types:
There are a number of different ways to backup your database and I will expand on these in later posts. For now here is a summary:*** Full – a complete database backup which truncates the transaction log of inactive records*** Differential – a backup of all of the changed data pages since the last full backup. Usually smaller than a full backup, assuming that not all pages have changed*** Log- transaction log backup containing all transactions since the last transaction or full backup. Also truncates the log of all inactive log records*** File – a way to backup individual database files.*** Filegroup – a way to backup a group of files contained inside of a filegroup.*** Copy-Only – a backup which can be taken without disrupting the log chain. Great for taking a copy of a production database for development purposes*** Mirror – allows you to backup to more than once device simultaneously.*** Partial – similar to filegroup but will backup the primary, all read/write filegroups and optionally, read only filegroupsSQL Server Backup – which recovery model should you choose?
This is an important decision to make when setting up your databases. The recovery model determines whether you can perform point in time recovery or not. It also affects transaction log size.There are 3 types of recovery model:-*** Full
*** Simple
*** Bulk-Logged
*** Full recovery modelIf your database is enabled with a full recovery model, it means that the transaction log will only be truncated upon completion of a FULL backup or upon completion of a transaction log backup.This recovery model is necessary for log shipping, database mirroring and transaction log backups to provide point in time recovery.Point in time recovery is vital if you cannot tolerate much data loss. How often you backup your transaction log will determine how much data you can recover in the event that you have a disaster.Backing up the transaction log is far superior than setting up differential backups every 30 minutes as a differential will backup all the changed data pages taken since the last full backup. This could take some time to complete and consume lots of disk space.In comparison, a transaction log backup will simply backup the transactions written to the log since the last log backup – a much quicker and less disk space hungry operationIt is advised that if you implement the FULL recovery model, that you do also put in place a policy to backup your transaction logs regularly. Failing to do this on a busy system could mean that your transaction log disks run out of disk space very quickly!Simple recovery model:-
With simple recovery, the transaction log is truncated on each checkpoint.What this means is that if a transaction is committed to disk, the space used by that transaction inside of the transaction log, is emptied leaving space within the transaction log file.If you are not concerned about point in time recovery or any of the other processes associated with the full recovery model, then this is a handy option to enable and requires less management of the transaction logs because the log is always truncating.Bulk logged recovery model:-
Finally we have the bulk logged recovery model. For this option, it is still possible to backup the transaction log but it does not permit point in time recovery. The advantage with this recovery model is that it uses minimal logging in the transaction log.This is not only faster because less is being written to the log but it also means that the transaction log will not grow as large compared with the full recovery model.If the business requirements allow you to switch from full logging to minimal logging, you might choose to enable this during maintenance windows when index rebuilds are happening. This will help to reduce the transaction log growth and upon completion, switching back to the full recovery model for example.You can make this switch because the log chain is intact. The bulk logged recovery model will preserve the log chain and not require you do take another full backup. However as the transactions recorded during the time when the database was in bulk recovery are only minimally recorded, you cannot restore your database to a point in time when the bulk recovery model was enabled.Only a restore point before or after the recovery model was enabled will be possible for point in time recovery.As the transactions could suffer data loss under this recovery model, it is advisable to only make this switch during periods when the users are not updating the database or when you are able to recover the transactions which were otherwise lost by some other means. So for example, you might have an SSIS package importing a bunch of files which could easily be re-imported.Scheduling and retention of your SQL Server backup:-
It’s possible to implement scheduling of your backups using the SQL Server Agent. What you choose to implement will depend on the data retention requirements for your business. A very simple example of a schedule might look like this:*** Take a weekly FULL backup
*** Take daily DIFFERENTIALS
*** Take regular transaction log backups, for example every 30 minutes.This schedule might repeat each week to a point when the backups are a certain age, they would no longer be kept in storage and re-used. This will depend on your business requirements.In SQL Server, backup schedules can be put in place which are customized to your needs and available backup capacity. The available options provide great flexibility as everything can be done at the database level.You may have databases which are critical to the business which need frequent backups and you may also have databases which do not change, for example read-only databases. You may only back these up very rarely in this instance but retain those backups for long periods of time.Backup storage – what to use, i can think of some options:*** Backup to local disk
*** Backup to network share or SAN
*** Backup to tape
*** Combination of disk/network/SAN plus tape – you may keep aged backups on tape and recent backups on disk for example.
*** Backup to cloud – there are companies now which provide this service.
*** Backup to FTP locationEnsure that whatever option or options to decide upon that there is redundancy there to help protect your backups.A word on backing up to local disk:-
Where I work, we have a 3rd party solution which takes care of our backups. It authenticates to the SQL Servers and takes regular backups to a large SAN. Those backups are then copied to tape and the tapes are taken offsite.The key to this is that the backups are stored external to the SQL Servers and not locally. The issue with local backups is that if your server dies for any reason, you can potentially lose your backups and you are in trouble.You can backup locally and it could perform better than backing up over a network for example but you’ve got to make sure that you have copies of those backups made somewhere else to safeguard your data.A word on backing up to tape
Similar to local disk backups, these backups are only as good as the tapes and device you put them on. So ensure you have copies made of your backups. I personally would not put a backup solution in place which consisted purely of tape backups.A word on database snapshots
It’s possible to take a snapshot of a database in SQL Server. This is a great way of taking a cut of the database which you might choose to do some reporting on for example. It’s also possible to revert a database back to a snapshot however do not rely on snapshots for your database backups.Snapshots exist on the same database server as the source database. If you lose your drives, you lose your source database and database snapshots! You may as well then go clear our your desk and hand in your security pass.SQL Server backup software
Now it would be foolish of Microsoft not to supply you with the tools to do this and thankfully, it has long been possible to completely manage your backup and restores using SQL Server Management Studio and the SQL Agent for scheduling.The tools which are available to you do a pretty good job but as your backup requirements grow, you may decide it is time to invest in some backup software to help manage your SQL Server backups.For more information you can visit this linkSend feedbackdividerFree Microsoft Training Delivered by Experts

Courses by Topics:

Courses by Products

divider

SQL Server live on 3rd and 4th December 2013be sure to check out the SQL Server 2014 CTP2?  Essentially feature complete, this second public community technical preview enables you to try and test all of the capabilities of the full SQL Server 2014 release. To help get you trained on the new capabilities, Microsoft Virtual Academy (MVA) has two lively, demo filled events planned. First on December 3rd, Mission Critical Performance with SQL Server 2014 will show how SQL Server 2014 will help you improve performance speed by 10-30 times, using your current hardware. And you’ll improve reliability at the same time. Then on December 4th, we’ll cover the benefits of moving your organization to the cloud, and how that can provide cost benefits at the same time as increasing scale and flexibility. Microsoft experts will demonstrate how you can make that move one step at a time, using SQL Server 2014 to create a hybrid environment.Mission Critical Performance with SQL Server 2014Date:  December 3, 2013
Time: 9:00am – 5:00pm
Where: Live, online virtual classroom
Cost: Free!Register nowPlatform for Hybrid Cloud with SQL Server 2014Date:  December 4, 2013
Time: 9:00am – 5:00pm
Where: Live, online virtual classroom
Cost: Free!Register nowSend feedbackdivider Configuring a 2-node multi-site cluster on Windows Server 2008 R2Creating your cluster and configuring the quorum: Node and File Share Majority.I would want to implement one. Microsoft has a great web page and white paper that you will want to download to get you all of the details.  But basically a multi-site cluster is a disaster recovery solution and a high availability solution all rolled into one. A multi-site cluster gives you the highest recovery point objective (RTO) and recovery time objective (RTO) available for your critical applications. With the introduction of Windows Server 2008 failover clustering a multi-site cluster has become much more feasible with the introduction of cross subnet failover and support for high latency network communications.I mentioned “cross-subnet failover” as a great new feature of Windows Server 2008 Failover Clustering, and it is a great new feature. However, SQL Server has not yet embraced this functionality, which means you will still be required to span your subnet across sites in a SQL Server multi-site cluster. As of Tech-Ed 2009, the SQL Server team reported that they plan on supporting this feature, but they say it will come sometime after SQL Server 2008 R2 is released. For the foreseeable future you will be stuck with spanning your subnet across sites in a SQL Server multi-site cluster. There are a few other network related issues that you need to consider as well, such as redundant communication paths, bandwidth and file share witness placement.Network ConsiderationsAll Microsoft failover clusters must have redundant network communication paths. This ensures that a failure of any one communication path will not result in a false failover and ensures that your cluster remains highly available. A multi-site cluster has this requirement as well, so you will want to plan your network with that in mind. There are generally two things that will have to travel between nodes: replication traffic and cluster heartbeats. In addition to that, you will also need to consider client connectivity and cluster management activity. You will want to be sure that whatever networks you have in place, you are not overwhelming the network or you will have unreliable behavior. Your replication traffic will most likely require the greatest amount of bandwidth; you will need to work with your replication vendor to determine how much bandwidth is required.With your redundant communication paths in place, the last thing you need to consider is your quorum model. For a 2-node multi-site cluster configuration, the Microsoft recommended configuration is a Node and File Share Majority quorum.For a detailed description of the quorum types, have a look at this article.The most common cause of confusion with the Node and File Share Majority quorum is the placement of the File Share Witness. Where should I put the server that is hosting the file share? Let’s look at the options.Option 1 – place the file share in the primary site.This is certainly a valid option for disaster recovery, but not so much for high availability. If the entire site fails (including the Primary node and the file share witness) the Secondary node in the secondary site will not come into service automatically, you will need to force the quorum online manually. This is because it will be the only remaining vote in the cluster. One out of three does not make a majority! Now if you can live with a manual step being involved for recovery in the event of a disaster, then this configuration may be OK for you.Option 2 – place the file share in the secondary site.This is not such a good idea. Although it solves the problem of automatic recovery in the event of a complete site loss, it exposes you to the risk of a false failover. Consider this…what happens if your secondary site goes down? In this case, your primary server (Node1) will go also go offline as it is now only a single node in the primary site and will no longer have a node majority. I can see no good reason to implement this configuration as there is too much risk involved.Option 3 – place the file share witness in a 3rd geographic locationThis is the preferred configuration as it allows for automatic failover in the event of a complete site loss and eliminates any the possibility of a failure of the secondary site causing the primary node to go offline. By having a 3rd site host the file share witness you have eliminated any one site as a single point of failure, so now the cluster will act as you expect and automatic failover in the event of a site loss is possible. In fact, you may consider the cloud itself as your secondary data center and just failover to the cloud in the event of a disaster. I think the possibilities of cloud based computing and disaster recovery configurations are extremely enticing and in fact I plan on doing a whole blog post on a just that in the near future.Configure the ClusterNow that we have the basics in place, let’s get started with the actual configuration of the cluster. You will want to add the Failover Clustering feature to both nodes of your cluster. For simplicity sake, I’ve called my nodes PRIMARY and SECONDARY (Father or child). This is accomplished very easily through the Add Features Wizard as shown below.1 – Add the Failover Clustering Role
Next you will want to have a look at your network connections. It is best if you rename the connections on each of your servers to reflect the network that they represent. This will make things easier to remember later.2- Change the names of your network connections
You will also want to go into the Advanced Settings of your Network Connections of each server and make sure the Public network is first in the list.3- Make sure your public network is first
Your private network should only contain an IP address and Subnet mask. No Default Gateway or DNS servers should be defined. Your nodes need to be able to communicate across this network, so make sure the servers can communicate across this network; add static routes if necessary.4 – Private network settings
Once you have your network configured, you are ready to build your cluster. The first step is to “Validate a Configuration”. Open up the Failover Cluster Manager and click on Validate a Configuration.5 – Validate a Configuration
The Validation Wizard launches and presents you the first screen as shown below. Add the two servers in your cluster and click Next to continue.6 – Add the cluster nodes
A multi-site cluster does not need to pass the storage validation, see more information from Microsoft site it self (see Microsoft article). To skip the storage validation process,click on “Run only the tests I select” and click Continue.7 – Select “Run only tests I select”
In the test selection screen, unselect Storage and click Next8 – Unselect the Storage test
You will be presented with the following confirmation screen. Click Next to continue.9 – Confirm your selection
If you have done everything right, you should see a summary page that looks like the following. Notice that the yellow exclamation point indicates that not all of the tests were run. This is to be expected in a multi-site cluster because the storage tests are skipped. As long as everything else checks out OK, you can proceed. If the report indicates any other errors, fix the problem, re-run the tests, and continue.10 – View the validation report
You are now ready to create your cluster. In the Failover Cluster Manager, click on Create a Cluster.11 – Create your cluster
The next step asks whether or not you want to validate your cluster. Since you have already done this you can skip this step. Note this will pose a little bit of a problem later on if installing SQL as it will require that the cluster has passed validation before proceeding. When we get to that point I will show you how to by-pass this check via a command line option in the SQL Server setup. For now, choose No and Next.12 – Skip the validation test
The next step is that you must create a name for this cluster and IP for administering this cluster. This will be the name that you will use to administer the cluster, not the name of the SQL cluster resource which you will create later. Enter a unique name and IP address and click Next.Note: This is also the computer name that will need permission to the File Share Witness as described later in this document.13 – Choose a unique name and IP address
Confirm your choices and click Next.14 – Confirm your choices
Congratulation, if you have done everything right you will see the following Summary page. Notice the yellow exclamation point; obviously something is not perfect. Click on View Report to find out what the problem may be.15 – View the report to find out what the warning is all about
If you view the report, you should see a few lines that look like this.16 – Error report
Don’t fret; this is to be expected in a multi-site cluster. Remember we said earlier that we will be implementing a Node and File Share Majority quorum. We will change the quorum type from the current Node Majority Cluster (not a good idea in a two node cluster) to a Node and File Share Majority quorum.Implementing a Node and File Share Majority quorumFirst, we need to identify the server that will hold our File Share witness. Remember, as we discussed earlier, this File Share witness should be located in a 3rd location, accessible by both nodes of the cluster. Once you have identified the server, share a folder as you normally would share a folder. In my case, I create a share called MYCLUSTER on a server named DEMODC.The key thing to remember about this share is that you must give the cluster computer name read/write permissions to the share at both the Share level and NTFS level permissions. If you recall back at 13, I created my cluster and gave it the name “MYCLUSTER” what ever you like, use std name. You will need to make sure you give the cluster computer account read/write permissions as shown in the following screen shots.17 – Make sure you search for Computers
18 – Give the cluster computer account NTFS permissions
19 – Give the cluster computer account share level permissions
Now with the shared folder in place and the appropriate permissions assigned, you are ready to change your quorum type. From Failover Cluster Manager, right-click on your cluster, choose More Actions and Configure Cluster Quorum Settings.20 – Change your quorum type
On the next screen choose Node and File Share Majority and click Next.21 – Choose Node and File Share Majority
In this screen, enter the path to the file share you previously created and click Next.22 – Choose your file share witness
Confirm that the information is correct and click Next.23 – Click Next to confirm your quorum change to Node and File Share Majority
Assuming you did everything right, you should see the following Summary page.24 – A successful quorum change
Now when you view your cluster, the Quorum Configuration should say “Node and File Share Majority” as shown below.25 – You now have a Node and File Share Majority quorum
The steps I have outlined up until this point apply to any multi-site cluster, whether it is a SQL, Exchange, File Server or other type of failover cluster. The next step in creating a multi-site cluster involves integrating your storage and replication solution into the failover cluster. This step will vary from depending upon your replication solution, so you really need to be in close contact with your replication vendor to get it right.dividerIT cloud skills gapWhen we asked global market intelligence firm IDC to investigate the state of IT jobs in cloud computing, even those of us close to industry trends were surprised by the findings. 1.7 million unfilled cloud-related jobs in 2012. 7 million open cloud positions within the next three years. A near-crisis in IT hiring circles to find qualified candidates with essential experience, training and certification.As illustrated in the infographic below, it all adds up to a world of opportunity. For companies struggling to fill cloud-related jobs, the next few years will be challenging; however for future and current IT professionals, career prospects couldn’t be brighter.That’s why we produced this month’s live event, Microsoft Certified Career Day 2013, where IT and cloud professionals gathered to discuss how the cloud is redefining IT jobs; and launched 90 Days to MCSA, a free program with the roadmap, tools and community support to get you cloud-certified in just three months. It all adds up to a clear view of your future: now’s the right time to close the “IT Cloud skills gap” and get trained, get certified, and get hired.Click the image below for a full-sized version or download the PDF.
Want to embed this infographic on your website? Grab the embed code below! 
 Explore new statistics on the growth of cloud computing and opportunities for cloud-skilled IT professionals   The 6th annual Global Knowledge and Windows IT Pro IT Skills & Salary Report is now available—and this year’s report is well worth your attention. More than 12,000 North American IT Pros responded—the largest survey of its kind in the industry—revealing the impact that training and certification has on one’s career path.dividerFree ebook: Introducing Windows Server 2012 (RTM Edition)Mitch Tulloch has updated his very popular free ebook on Windows Server 2012 based on the RTM version of the software.A key feature of this book is the inclusion of sidebars written by members of the Windows Server team, Microsoft Support engineers, Microsoft Consulting Services staff, and others who work at Microsoft. These sidebars provide an insider’s perspective that includes both “under-the-hood” information concerning how features work, and strategies, tips, and best practices from experts who have been working with the platform during product development.Please see the links below to download one or all of the available formats.PDFIntroducing Windows Server 2012 RTM Edition – PDF ebook EPUB –Introducing Windows Server 2012 RTM Edition – ePub format MOBI – Introducing Windows Server 2012 RTM Edition – MOBI format (for Kindle) If you prefer a hard copy of the book, you can order it here for $14.99 from our official distributor, O’Reilly Media. divider Self-paced eLearning VMware Traininghttp://vmwarelearning.com/Video List Instructional VideosSite Pairing VMware TrainingBuild knowledge and expertise through VMware Training offerings, much more, just visit and have a good time there too.Free ebooks that won’t cost you a pennyMany of free ebooks are offered in three formats – PDF, Mobi (Kindle) and epub.imagecover for Introducing Windows Server 2012 RTM Editioncover for Introducing Microsoft SQL Server 2012cover for Programming Windows 8 Apps with HTML, CSS, and JavaScriptcover for Office 365cover for Security and Privacy for Microsoft Office 2010 Userscover for Introducing Microsoft SQL Server 2008 R2cover for Introducing Windows Server 2008 R2cover for Understanding Microsoft Virtualization Solutions, 2nd EdCover for First Look Microsoft Office 2010cover for Deploying Windows 7cover for Moving to Visual Studio 2010cover for Programming Windows Phone 7NETWORK CENTERS AND LIBRARYAll Developer CentersMSDN LibraryTUTORIALSVisual Studio Walkthroughs

Windows Azure Tutorials

Windows Phone Quickstarts

ASP.NET Web Forms Tutorials

ASP.NET MVC Tutorials

VIDEOVisual Studio 2010 “How Do I” videos

Windows videos

Windows Hardware videos

Windows Phone 7 “How Do I” videos

ASP.NET videos (including MVC)

Channel 9: Videos and Chats

SQL Server 2008 R2 Command Prompt Utilities

dividerT-SQL Query to change the datatype of multiple columns of single or multiple tables:There might be a situation where a person designed a database with a particular datatype for many tables and now you want to change the datatype to a different one for all those columns in a particular table or multiple tables due to various reasons. Changing the datatype for a single table or five tables or 10 tables is a easy task, but when the tables list is in hundreds how easy is it do in the traditional way?Below stored procedure gives you the flexibility of changing the datatype of multiple columns in a single or multiple tables at one go. Things to note before running the scriptsA.Backup your databaseB.These scripts are provided as IS without warranty of any kind.Syntax:CREATE PROC usp_ChangeColumnDatatype (@currentDataType nvarchar(25),@DataTypeToSet nvarchar(50),@ScanTables nvarchar(100),@PrintCommandsOnly bit )   ASSET NOCOUNT ONDECLARE @ScanTables_Local nvarchar(100) SET @ScanTables_Local = ”” + REPLACE(REPLACE(@ScanTables,’,’,”’,”’),’ ‘,”) + ”” IF @ScanTables = ‘All’BEGINCREATE TABLE #Temp (CommandsToExecute nvarchar(max)) INSERT INTO #temp SELECT ‘ALTER TABLE ‘ + OBJECT_NAME(o.object_id) +      ‘ ALTER COLUMN ‘ + c.name + ‘ ‘ + @DataTypeToSet +     CASE WHEN c.is_nullable = 0 THEN ‘ NOT NULL’ ELSE ‘ NULL’ END AS CommandsToExecute FROM sys.objects o  INNER JOIN sys.columns c ON o.object_id=c.object_id INNER JOIN sys.types t ON c.system_type_id=t.system_type_id WHERE o.type=’u’and t.name = @currentDataType END  IF @ScanTables <> ‘All’BEGINCREATE TABLE #Temp_SpecificTables (CommandsToExecute nvarchar(max))   DECLARE @Cmd nvarchar(max) SET @Cmd = ‘INSERT INTO #Temp_SpecificTables  SELECT ”ALTER TABLE ” + OBJECT_NAME(o.object_id) +      ” ALTER COLUMN ” + c.name + ”’ + @DataTypeToSet + ”’ +     CASE WHEN c.is_nullable = 0 THEN ”NOT NULL” ELSE ”NULL” END AS CommandsToExecute FROM sys.objects o  INNER JOIN sys.columns c ON o.object_id=c.object_id INNER JOIN sys.types t ON c.system_type_id=t.system_type_id WHERE o.type=”u” and t.name = ”’+@currentDataType+”’ and OBJECT_NAME(o.object_id) in (‘+ @ScanTables_Local + ‘)’–PRINT @cmd EXECUTE (@cmd) ENDif @PrintCommandsOnly = ‘True’ and @ScanTables = ‘All’BEGINSELECT * FROM #TempDROP TABLE #TempEND  if @PrintCommandsOnly = ‘False’ and @ScanTables = ‘All’BEGIN–SELECT * FROM #Temp PRINT ‘Changing of the datatypes of table(s) ‘+ @ScanTables +’ from ‘ + @currentDataType + ‘ to ‘+ @DataTypeToSet + ‘ started at ‘ + CAST(GETDATE() AS varchar) WHILE (SELECT COUNT(*) FROM #Temp) <> 0 BEGINDECLARE @varTemp nvarchar(max) SELECT @varTemp = CommandsToExecute FROM #TempEXECUTE (@varTemp) DELETE FROM #temp WHERE CommandsToExecute = @varTemp ENDDROP TABLE #TempPRINT ‘Changing of the datatypes of table(s) ‘+ @ScanTables +’ from ‘ + @currentDataType + ‘ to ‘+ @DataTypeToSet + ‘ ended at ‘ + CAST(GETDATE() AS varchar) END  if @PrintCommandsOnly = ‘True’ and @ScanTables <> ‘All’BEGINSELECT * FROM #Temp_SpecificTables DROP TABLE #Temp_SpecificTables END  if @PrintCommandsOnly = ‘False’ and @ScanTables <> ‘All’BEGIN–SELECT * FROM #Temp_SpecificTables PRINT ‘Changing of the datatypes of table(s) ‘+ @ScanTables_Local +’ from ‘ + @currentDataType + ‘ to ‘+ @DataTypeToSet + ‘ started at ‘ + CAST(GETDATE() AS varchar) WHILE (SELECT COUNT(*) FROM #Temp_SpecificTables) <> 0 BEGINDECLARE @varTemp_SpecificTables nvarchar(max) SELECT @varTemp_SpecificTables = CommandsToExecute FROM #Temp_SpecificTables EXECUTE (@varTemp_SpecificTables) DELETE FROM #Temp_SpecificTables WHERE CommandsToExecute = @varTemp_SpecificTables ENDDROP TABLE #Temp_SpecificTables PRINT ‘Changing of the datatypes of table(s) ‘+ @ScanTables_Local +’ from ‘ + @currentDataType + ‘ to ‘+ @DataTypeToSet + ‘ ended at ‘ + CAST(GETDATE() AS varchar) ENDSET NOCOUNT OFFGOUsage:exec usp_ChangeColumnDatatype @currentDataType = ‘nvarchar’,           @DataTypeToSet = ‘varchar(50)’,           @ScanTables = ‘Table_1,Table_2’, –Table1, Table2,Table3 or ALL          @PrintCommandsOnly = ‘FALSE’ — TRUE – Will print the commands or FALSE – Will execute the commands.Granting Read-Only and administrative access to Central Management Server (CMS):About the Central Management Server and how to Register it. I will be telling how to give read-only access and a Administrative access to an existing CMS. Grating the Read-Only and Administrative access to CMS is very simply and the activity includes adding the right database users to right groups in msdb database. Grating the Read-Only access:USE [msdb]GOCREATE USER [DBUserName] FOR LOGIN [LoginName]GOEXEC sp_addrolemember N’ServerGroupReaderRole’, N’DBUserName’GOGranting Admin access: This access is usually give to the DBA’sUSE [msdb]GO CREATE USER [DBAUserName] FOR LOGIN [DBALoginName]GOEXEC sp_addrolemember N’ServerGroupAdministratorRole’, N’DBAUserName’GOT-SQL Query to find the list of Indexed Views in a Database   Here is the query which can be used for getting the list of Indexed views in a Database.USE <DatabaseName>GOdividerTempDB usage per active sessionTempDB usage per each active session.It helps identifying the sessions that use the tempdb heavily with internal objects.When the internal objects usage is high, the session is probably using big hash tables or spooling in worktables. It could be a symptom of an inefficient plan or a missing index.Shrinking a TempDB full of internal objects will probably have no effect, because the engine will not release the deallocated space. The only possible alternative to restarting the service or Kill the specified SPID after received the approval from business owner.It helps to identify the root cause of the problem why Tempdb is rapidly increased.Script: I already tested in our Citagus test environment and it work as expected.,;WITH task_space_usage AS (— SUM alloc/delloc pagesSELECT session_id,request_id,SUM(internal_objects_alloc_page_count) AS alloc_pages,SUM(internal_objects_dealloc_page_count) AS dealloc_pagesFROM sys.dm_db_task_space_usage WITH (NOLOCK)WHERE session_id <> @@SPIDGROUP BY session_id, request_id)SELECT TSU.session_id,TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],EST.text,— Extract statement from sql textISNULL(NULLIF(SUBSTRING(EST.text,ERQ.statement_start_offset / 2,CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset – ERQ.statement_start_offset ) / 2 END), ”), EST.text) AS [statement text],EQP.query_planFROM task_space_usage AS TSUINNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)ON TSU.session_id = ERQ.session_idAND TSU.request_id = ERQ.request_idOUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS ESTOUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQPWHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULLORDER BY 3 DESC, 5 DESCResolve the memory issue quickly at SQL server 2008:-Memory issue is very common stress for DBA but SQL server 2008 introduce new DMV which we can use to handle the issue quickly. Need to follow the below step to resolve the issue quickly.sys.dm_os_sys_memory This DMV was introduced in SQL Server 2008 with an intention of making life easier for all those systems where monitoring memory usage was a mandatory requirement due to the nature of deployment. The “system_memory_state_desc” column output of this DMV has three common states:•· Available physical memory is high
•· Available physical memory is low
•· Physical memory usage is steady
Step 1select * from sys.dm_os_sys_memory where system_memory_state_desc=’Available physical memory is low’. We can use the script to get the notification whenever memory use by SQL server high and take some proactive action before database stop response.Step 2: If we are getting alert like that SQL server Available physical memory is low then we can run the below script to know which database using most memory–Memory Occupied by each DatabaseSELECT(CASE WHEN ([is_modified] = 1) THEN ‘Dirty’ ELSE ‘Clean’ END) AS ‘Page State’,(CASE WHEN ([database_id] = 32767) THEN ‘Resource Database’ ELSE DB_NAME (database_id) END) AS ‘Database Name’,COUNT (*) AS ‘Page Count’FROM sys.dm_os_buffer_descriptorsGROUP BY [database_id], [is_modified]ORDER BY [database_id], [is_modified];GOStep 3: To freed up the memory we can use the below script together.DBCC FREESYSTEMCACHE (‘ALL’) WITH MARK_IN_USE_FOR_REMOVAL;DBCC FREESESSIONCACHE WITH NO_INFOMSGS;GODBCC DROPCLEANBUFFERSDBCC FREEPROCCACHE;Troubleshoot SP at SQL 2008:-Store procedure one of the object which are used in SQL server database very massively to line up with application. From DBA and Developer end it is tough deal to figure out the culprit store procedure which is consume high resource. We have observed the in production server more than 1000 user define store procedure created to run the application.By help of below code Developer and DBA can easily track which store procedure taking too much time and consume high CPU,IO and Memory.It help us our life to tune the program which required attention.– Top Cached SPs By Execution Count (SQL 2008)SELECT TOP(250) p.name AS [SP Name], qs.execution_count,ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.total_worker_time AS [TotalWorkerTime],qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],qs.cached_timeFROM sys.procedures AS p WITH (NOLOCK)INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)ON p.[object_id] = qs.[object_id]WHERE qs.database_id = DB_ID()ORDER BY qs.execution_count DESC OPTION (RECOMPILE);– Tells you which cached stored procedures are called the most often– This helps you characterize and baseline your workload– Top Cached SPs By Avg Elapsed Time (SQL 2008)SELECT TOP(25) p.name AS [SP Name], qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],qs.total_elapsed_time, qs.execution_count, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time,GETDATE()), 0) AS [Calls/Second], qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],qs.total_worker_time AS [TotalWorkerTime], qs.cached_timeFROM sys.procedures AS p WITH (NOLOCK)INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)ON p.[object_id] = qs.[object_id]WHERE qs.database_id = DB_ID()ORDER BY avg_elapsed_time DESC OPTION (RECOMPILE);– This helps you find long-running cached stored procedures that– may be easy to optimize with standard query tuning techniques– Top Cached SPs By Total Worker time (SQL 2008). Worker time relates to CPU costSELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime],qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count,ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_countAS [avg_elapsed_time], qs.cached_timeFROM sys.procedures AS p WITH (NOLOCK)INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)ON p.[object_id] = qs.[object_id]WHERE qs.database_id = DB_ID()ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);– This helps you find the most expensive cached stored procedures from a CPU perspective– You should look at this if you see signs of CPU pressure– Top Cached SPs By Total Logical Reads (SQL 2008). Logical reads relate to memory pressureSELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads],qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count,ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_countAS [avg_elapsed_time], qs.cached_timeFROM sys.procedures AS p WITH (NOLOCK)INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)ON p.[object_id] = qs.[object_id]WHERE qs.database_id = DB_ID()ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);– This helps you find the most expensive cached stored procedures from a memory perspective– You should look at this if you see signs of memory pressure– Top Cached SPs By Total Physical Reads (SQL 2008). Physical reads relate to disk I/O pressureSELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads],qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.execution_count,qs.total_logical_reads,qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_countAS [avg_elapsed_time], qs.cached_timeFROM sys.procedures AS p WITH (NOLOCK)INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)ON p.[object_id] = qs.[object_id]WHERE qs.database_id = DB_ID()AND qs.total_physical_reads > 0ORDER BY qs.total_physical_reads DESC, qs.total_logical_reads DESC OPTION (RECOMPILE);– This helps you find the most expensive cached stored procedures from a read I/O perspective– You should look at this if you see signs of I/O pressure or of memory pressure– Top Cached SPs By Total Logical Writes (SQL 2008).– Logical writes relate to both memory and disk I/O pressureSELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes AS [TotalLogicalWrites],qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites], qs.execution_count,ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],qs.cached_timeFROM sys.procedures AS p WITH (NOLOCK)INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)ON p.[object_id] = qs.[object_id]WHERE qs.database_id = DB_ID()ORDER BY qs.total_logical_writes DESC OPTION (RECOMPILE);– This helps you find the most expensive cached stored procedures from a write I/O perspective– You should look at this if you see signs of I/O pressure or of memory pressureWhat’s new in SQL Server 2012Enhanced Features:

Information on the WebSQL Server Resource Center

SQL Server BI Resource Center

SQLCat Web Site

SQL Server Release Notes

I’m NOT responsible for any damage that occurs doing so, USE AT YOUR OWN RISK! … If anything goes wrong. Read carefully, i am sure 99.99 % all syntax work.  @addarr.What does the sp_help_revlogin do?
The sp_help_revlogin SP is a Microsoft provided utility that generates a TSQL script to migrate logins from one server to another. This SP will not only copy the existing logins, but it will also copy the passwords and Security Identification Numbers (SID) associated with SQL Server Authenticated users.When you move a database from one server to another, the entire database and all the system tables associated with it are also moved. One of those system tables is the sysusers table. The sysusers table contains all the users, groups and roles that have access to the database. In order for a person to be able to access the database, they must have two things. The first thing they must have is a SQL Server login. The second thing they need is to be defined as a user in the database. Therefore, if you copy a database from one server to another and the users of the database don’t have SQL Server logins then these database users become orphan users. An orphan user is a user in a database with a SID that does not exist in the syslogins table in the master database. Also if the SID stored in the database sysusers table, differs from SID stored in the syslogin table for the matching database user, then the database user is also considered an orphan user. If you retain the original SID for logins, when a user database is migrated, you will not have a problem with orphan users./*Copy users in sql server 2005, 2008 & 2012.
This is code to copy sql and windows users from one sql 2005, 2008 & 2012 instance to another:*/USE master
GO
IF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = ‘0x’
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = ‘0123456789ABCDEF’
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint – (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
ENDSELECT @hexvalue = @charvalue
GOIF OBJECT_ID (‘sp_help_revlogin’) IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)DECLARE @defaultdb sysnameIF (@login_name IS NULL)
DECLARE login_curs CURSOR FORSELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name <> ‘sa’
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name = @login_name
OPEN login_cursFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT ‘No login(s) found.’
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = ‘/* sp_help_revlogin script ‘
PRINT @tmpstr
SET @tmpstr = ‘** Generated ‘ + CONVERT (varchar, GETDATE()) + ‘ on ‘ + @@SERVERNAME + ‘ */’
PRINT @tmpstr
PRINT ”
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ”
SET @tmpstr = ‘– Login: ‘ + @name
PRINT @tmpstr
IF (@type IN ( ‘G’, ‘U’))
BEGIN — NT authenticated account/groupSET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ FROM WINDOWS WITH DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’
END
ELSE BEGIN — SQL Server authentication
— obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, ‘PasswordHash’ ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT– obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @nameSET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ WITH PASSWORD = ‘ + @PWD_string + ‘ HASHED, SID = ‘ + @SID_string + ‘, DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ‘, CHECK_POLICY = ‘ + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ‘, CHECK_EXPIRATION = ‘ + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN — login is denied access
SET @tmpstr = @tmpstr + ‘; DENY CONNECT SQL TO ‘ + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN — login exists but does not have access
SET @tmpstr = @tmpstr + ‘; REVOKE CONNECT SQL TO ‘ + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN — login is disabled
SET @tmpstr = @tmpstr + ‘; ALTER LOGIN ‘ + QUOTENAME( @name ) + ‘ DISABLE’
END
PRINT @tmpstr
ENDFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
EXEC sp_help_revlogin
/* Remarks
Review the following information before you run the output script on the instance on server B:•
Review the output script carefully. If server A and server B are in different domains, you have to modify the output script.
Then, you have to replace the original domain name with the new domain name in the CREATE LOGIN statements. */Page split:When you think of page splits, you normally only think of clustered indexes. This is because clustered indexes enforce the physical order of the index, and page splitting can be a problem if the clustered index is based on a non-incrementing column. But what has this to do with non-clustered indexes?
While non-clustered indexes use a clustered index (assuming the table is not a heap) as their key, most people don’t realize that non-clustered indexes can suffer from page splitting, and because of this, need to have an appropriate fillfactor and pad_index set for them.Here’s an example of how non-clustered indexes can experience page splits. Let’s say you have a table that has a clustered index on it, such as customer number. Let’s also say that you have a non-clustered index on the zip code column. As you can quite well imagine, the data in the zip code column will have no relation to the customer number and will be more or less random, and data will have to be inserted into the zip code index randomly. Like clustered index pages, non-clustered index pages can experience page splitting.So just as with clustered indexes, non-clustered indexes need to have an appropriate fillfactor and pad_index, and also be rebuild on a periodic basis.Non-clustered indexes are best for queries:  That return few rows (including just one row) and where the index has good selectivity (generally above 95%). That retrieve small ranges of data (not large ranges). Clustered indexes perform better for large range queries.Where both the WHERE clause and the ORDER BY clause are both specified for the same column in a query. This way, the non-clustered index pulls double duty. It helps to speed up accessing the records, and it also speeds up the sorting of the records (because the returned data is already sorted).That use JOINs (although clustered indexes are better).When the column or columns to be indexed are very wide. While wide indexes are never a good thing, but if you have no choice, a non-clustered index will have overall less overhead than clustered index on a wide index.Maxdop:  On multiprocessor servers it is common with paralelism problems on large joins or heavy queries . It can give better perfomance to add maxdop. e.g.select * from databases option (maxdop 1)The number(1) states how many processors we want to be used as a maximum for this query. As a rule of thumb, never use more processors than you have physical processors in the server. Each query needs to be tested to find out how many processors to use for best performance. Max degree of paralelism can also be set for the whole server via sp_configure (max degree of parallelism ) This will affect all queries without the maxdop option.Re-start server remotely:  Very often RDP is failing on the nodes in our cluster when we are restarting the server or something else is happenign that requires an restart. To restart a server we can use one very simple command from any other server. The only requirement is that we have restart rights on the other server and that we are logged in to any server with that account. För example we are logged in to addarr-sec and need to restart addarr1-priOpen a commando prompt – cmd and type in:shutdown /iThis will start up the normal shutdown dialogue. Add the your servername you want to restart or shutdown and then do as usual.1.Code to start job
This code can start a job from any other sql server ( both 2000 and 2005) i have been using it from UTS to let the customer start their own SSIS package.In 2005 we can let a user have start rights on jobs without being sys user. Ny putting the job in an 2005 instance i cna let the customer starts job that runs agains a sql 2000 database(UTS). In this code i use a user and password but i can also let them use intergated security and that is an better solution for security reasons.declare @retcode int
declare @job_name varchar(300)
declare @server_name varchar(200)
declare @query varchar(8000)
declare @cmd varchar(8000)
set @job_name = ‘Copy UTS to Mindtree’set @server_name = ‘122.00.143.000,1304’
set @query = ‘exec msdb.dbo.sp_start_job @job_name = ”’ + @job_name + ””
set @cmd = ‘osql -U uts -Putsmind -S ‘ + @server_name + ‘ -Q “‘ + @query + ‘”‘
exec @retcode = master.dbo.xp_cmdshell @cmd
if @retcode <> 0 or @retcode is null
begin print ‘xp_cmdshell @retcode = ‘+isnull(convert(varchar(20),@retcode),’NULL @retcode’)
end2. This code can be used to identify all indexed that never have been used.
This lists all indexes that have not been used since the last start up of sql server 2005 instance. The index have bene updated (caused IO and CPU) but never referenced, used, scaned, seeked in etc. Note, before you delete any index, always run this for a long time. It might be that the code using the index just runs once every 3 month or so! This gives us a very good viwe of how the indexes is not used anyway.select object_name(i.object_id),
i.name,
s.user_updates,
s.user_seeks,
s.user_scans,
s.user_lookups
from sys.indexes i left join sys.dm_db_index_usage_stats s
on s.object_id = i.object_id and i.index_id = s.index_id and s.database_id = 5
where objectproperty(i.object_id, ‘IsIndexable’) = 1 and
— index_usage_stats has no reference to this index (not being used)
s.index_id is null or
— index is being updated, but not used by seeks/scans/lookups
(s.user_updates > 0 and s.user_seeks = 0
and s.user_scans = 0 and s.user_lookups = 0)
order by object_name(i.object_id) asc3. Code to list changes of tables, views, indexes etc in the last so many days  Change the number 20 to the days back in time you what to check. Works with sql server 2005 only.SELECT name AS object_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() – 20
ORDER BY modify_date;For sql server 2000:SELECT name AS object_name,type
,crdate as Create_date
,refdate as ChangeDate
FROM sysobjects
WHERE refdate > GETDATE() – 20
ORDER BY ChangeDate;List all tables without any sort of index at all
create table #Ttable (Table_name varchar(100) collate latin1_general_CI_AS not null)
insert into #Ttable
SELECT t.name AS table_name
FROM sys.tables AS t
WHERE object_id IN
(
SELECT object_id
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,’IsIndexed’) = 0
)
ORDER BY table_name;
select Table_name,i.rows
from sysindexes i, sysobjects o, #Ttable t
where i.id = o.id and t.table_name = o.name collate latin1_general_CI_AS
and i.indid <= 1
and o.type = ‘U’
order by i.rows desc
drop table #TtableCode to list all tables without primarykey and the number of rows in sql 2005
create table #Ttable (Table_name varchar(100) collate latin1_general_CI_AS not null)
insert into #Ttable
SELECT t.name AS table_name
FROM sys.tables AS t
WHERE object_id NOT IN
(
SELECT parent_object_id
FROM sys.key_constraints
WHERE type_desc = ‘PRIMARY_KEY_CONSTRAINT’ — or type = ‘PK’
)
ORDER BY table_name;
select Table_name,i.rows
from sysindexes i, sysobjects o, #Ttable t
where i.id = o.id and t.table_name = o.name collate latin1_general_CI_AS
and i.indid <= 1
and o.type = ‘U’
order by i.rows desc
drop table #Ttable4. Code to get tables without any Clustered indexes and the number of rows in the tables.  This code is for sql 2005:create table #Ttable (Table_name varchar(100) collate latin1_general_CI_AS not null)
insert into #Ttable
SELECT t.name AS table_name
FROM sys.tables AS t
WHERE NOT EXISTS
(
SELECT * FROM sys.indexes AS i
WHERE i.object_id = t.object_id
AND i.type = 1 — or type_desc = ‘CLUSTERED’
)
ORDER BY table_name;
select Table_name,i.rows
from sysindexes i, sysobjects o, #Ttable t
where i.id = o.id and t.table_name = o.name collate latin1_general_CI_AS
and i.indid <= 1
and o.type = ‘U’
order by i.rows desc
drop table #TtableCode to copy sql 2000 users to sql 2005This code  is for copying logins from sql 2000 and create them on an sql 2005 instance:USE master
GO
IF OBJECT_ID (’sp_hexadecimal’) IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = ‘0x’
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = ‘0123456789ABCDEF’
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint – (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GOIF OBJECT_ID (’sp_help_revlogin_2000_to_2005′) IS NOT NULL
DROP PROCEDURE sp_help_revlogin_2000_to_2005
GO
CREATE PROCEDURE sp_help_revlogin_2000_to_2005@login_name sysname = NULL,
@include_db bit = 0,
@include_role bit = 0AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @dfltdb varchar (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)IF (@login_name IS NULL)
DECLARE login_curs CURSOR STATIC FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), ‘master’)
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] ’sa’
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), ‘master’)
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] = @login_nameOPEN login_cursFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdbIF (@@fetch_status = -1)
BEGIN
PRINT ‘No login(s) found.’
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
ENDSET @tmpstr = ‘/* sp_help_revlogin script ‘
PRINT @tmpstr
SET @tmpstr = ‘** Generated ‘
+ CONVERT (varchar, GETDATE()) + ‘ on ‘ + @@SERVERNAME + ‘ */’
PRINT @tmpstr
PRINT ”
PRINT ”
PRINT ”
PRINT ‘/***** CREATE LOGINS *****/’WHILE @@fetch_status = 0
BEGIN
PRINT ”
SET @tmpstr = ‘– Login: ‘ + @name
PRINT @tmpstrIF (@xstatus & 4) = 4
BEGIN — NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN — NT login is denied access
SET @tmpstr = ” –’EXEC master..sp_denylogin ”’ + @name + ””
PRINT @tmpstr
END
ELSE
BEGIN — NT login has access
SET @tmpstr = ‘IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ”’ + @name + ”’)’
PRINT @tmpstr
SET @tmpstr = CHAR(9) + ‘CREATE LOGIN [‘ + @name + ‘] FROM WINDOWS’
PRINT @tmpstr
END
END
ELSE
BEGIN — SQL Server authentication
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUTIF (@binpwd IS NOT NULL)
BEGIN — Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
SET @tmpstr = ‘CREATE LOGIN [‘ + @name + ‘] WITH PASSWORD=’ + @txtpwd + ‘ HASHED’
END
ELSE
BEGIN — Null password
SET @tmpstr = ‘CREATE LOGIN [‘ + @name + ‘] WITH PASSWORD=””’
ENDSET @tmpstr = @tmpstr + ‘, CHECK_POLICY=OFF, SID=’ + @SID_string
PRINT @tmpstr
ENDFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
ENDIF @include_db = 1
BEGIN
PRINT ”
PRINT ”
PRINT ”
PRINT ‘/***** SET DEFAULT DATABASES *****/’FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdbWHILE @@fetch_status = 0
BEGIN
PRINT ”
SET @tmpstr = ‘– Login: ‘ + @name
PRINT @tmpstrSET @tmpstr = ‘ALTER LOGIN [‘ + @name + ‘] WITH DEFAULT_DATABASE=[‘ + @dfltdb + ‘]‘
PRINT @tmpstrFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
ENDIF @include_role = 1
BEGIN
PRINT ”
PRINT ”
PRINT ”
PRINT ‘/***** SET SERVER ROLES *****/’FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdbWHILE @@fetch_status = 0
BEGIN
PRINT ”
SET @tmpstr = ‘– Login: ‘ + @name
PRINT @tmpstrIF @xstatus &16 = 16 — sysadmin
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=’’sysadmin”’
PRINT @tmpstr
ENDIF @xstatus &32 = 32 — securityadmin
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=’’securityadmin”’
PRINT @tmpstr
ENDIF @xstatus &64 = 64 — serveradmin
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=’’serveradmin”’
PRINT @tmpstr
ENDIF @xstatus &128 = 128 — setupadmin
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=’’setupadmin”’
PRINT @tmpstr
ENDIF @xstatus &256 = 256 –processadmin
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”processadmin”’
PRINT @tmpstr
ENDIF @xstatus &512 = 512 — diskadmin
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”diskadmin”’
PRINT @tmpstr
ENDIF @xstatus &1024 = 1024 — dbcreator
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”dbcreator”’
PRINT @tmpstr
ENDIF @xstatus &4096 = 4096 — bulkadmin
BEGIN
SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”bulkadmin”’
PRINT @tmpstr
ENDFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
ENDCLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GOexec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
GOFix code for suspect database:  If a databse is in suspect code, this code can correct it. Note after you have run ALTER DATABASE LDS_CN002010 SET EMERGENCY, run each of the following rows one by one. If checkdb is reproitng errros you migh try with the the DBCC CheckDB (‘LDS_CN002010′, REPAIR_ALLOW_DATA_LOSS)Note!!! That will realy remove any pages, linsk etc that are corupted and remove that data that where there. This will for sure lead to missing data. Ins ome cases this is however the only choice. If there is an backup it is a better choice to use that in the first place.EXEC sp_resetstatus ‘LDS_CN002010′;
ALTER DATABASE LDS_CN002010 SET EMERGENCY
DBCC checkdb(‘LDS_CN002010′)
ALTER DATABASE LDS_CN002010 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (‘LDS_CN002010′, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE LDS_CN002010 SET MULTI_USER
ALTER DATABASE LDS_CN002010 SET onlineHow to turn of off all contraints in code :Very often when we migrate data, like with Big and the NY-USA incident, we need to be bale to turn off all check contraints to be able to truncate data in tables. To do this we can use the gui and that takes a lot of time. A better way is to use this code:– To turn off all check contrains(e.g forreign keys)
ALTER TABLE VDS_VehicleMaster NOCHECK CONSTRAINT ALL
– Enable all table constraints
ALTER TABLE VDS_VehicleMaster CHECK CONSTRAINT ALLNote, in many cases you also need to do the same on the other tables involved in the realtionship. This saved me hours when migrating the data.From Mehboob SQLDBA – MCITP  www.addarr.com

Advertisements