Changes to MCSA SQL Server 2016

These are not the only changes Microsoft have made with SQL Server 2016. For those of you looking to upskill and get to grips with SQL Server 2016 - seeking training and certification - things are now a little different.

The previous certification pathway for SQL Server 2012 and 2014 required you to attain an MCSA in the basics of SQL Server before moving on to the role-based certifications of MCSE: Business Intelligence and MCSE: Data Platform. No more, Microsoft have reverted to the days of SQL Server 2008, with the reintroduction of role based certifications at the MCSA level. 

You now have the option of three new MCSA certification pathways. They focus on the roles of Database Administration, Database Development or Business Intelligence. Let’s take a closer look at those certifications.

MCSA: SQL Server 2016 - Database Development

This course will allow you to learn abilities needed to use the Transact-SQL language, critical to all SQL Server disciplines. This language is not only important to database development, but also to database administration and business intelligence.

You will learn database development abilities such as advanced table designs, column store indexes and using in-memory table. These are all great skills that grow your SQL competencies.

This course is targeted towards experts that work closely with SQL Server 2016, who are creating and managing databases and want to advance and improve their database skills.

Two exams are required to be able to obtain the MCSA: SQL Server 2016 - Database Development certification:

• Exam 70-761 - Querying Data with Transact-SQL
• Exam 70-762 - Developing SQL Databases

If you have already achieved MCSA: SQL Server 2012/14, you can upgrade to MCSA: SQL Server 2016 - Database Development by passing exam 70-762.

MCSA: SQL Server 2016 - Database Administration

This certification course teaches to administer, develop and manage SQL Server 2016 databases. You’ll learn about handling and approving user access, assigning and creating different types of database roles while also learning to automate SQL Server management. You will also master how to install, configure and manage SQL Server 2016.

This particular course is targeted toward database administrators, precisely those that use SQL Server 2016 on a regular basis. Two exams are required to be able to obtain the MCSA: SQL Server 2016 Database Administration certification: 

• Exam 70-764 - Administering a SQL Database Infrastructure
• Exam 70-765 - Provisioning SQL Databases

If you have already achieved MCSA: SQL Server 2012/14, you can upgrade to MCSA: SQL Server 2016 - Database Administration by passing exam 70-765.

MCSA: SQL Server 2016 - Business Intelligence Development

This certification course teaches you the fundamentals of Business Intelligence with SQL Server 2016. You will learn how to implement a SQL Server 2016 data warehouse solution to support a business intelligence. You will also learn to navigate your way through all the core features of the SQL server 2016, able to produce business solutions.

You will develop the expertise to question, process, analyse and report on a large quantity of raw business data. Other abilities learnt throughout the duration of the course will consist of creating multidimensional databases and cubes, using MDX, DAX and data mining.

This course is targeted towards database experts who work with the SQL server 2016 and who lead business intelligence solutions. 

To attain the MCSA in Business Intelligence Development, you must pass two exams:

• Exam 70-767 - Implementing a SQL Data Warehouse
• Exam 70-768 - Developing SQL Data Models

If you have already achieved MCSA: SQL Server 2012/14, you can upgrade to MCSA: SQL Server 2016 - Business Intelligence Development by passing exam 70-768

Oracle - Removing/Cleansing duplicate table rows

Removing duplicate table rows, the columns needed in the join are those that uniquely identify a specific row

delete from
   table_name a
where
   a.rowid >
   any (select b.rowid
   from
      table_name b
   where
      a.col1 = b.col1
   and
      a.col2 = b.col2
   );

Fragmentation in all indexes in all tables in a database

This script will create a table called Fragstats and load it with fragmentation information for all tables in the database.

SET NOCOUNT ON

DECLARE @tablename VARCHAR (128)

DECLARE @execstr VARCHAR (255)

DECLARE @objectid INT

DECLARE @indexid INT

DECLARE @frag DECIMAL

DECLARE @maxfrag DECIMAL

DECLARE @dbname VARCHAR (40)

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FragStats]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

CREATE TABLE [dbo].[FragStats] (

[ObjectName] [char] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ObjectId] [int] NULL ,

[IndexName] [char] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[IndexId] [int] NULL ,

[Lvl] [int] NULL ,

[CountPages] [int] NULL ,

[CountRows] [int] NULL ,

[MinRecSize] [int] NULL ,

[MaxRecSize] [int] NULL ,

[AvgRecSize] [int] NULL ,

[ForRecCount] [int] NULL ,

[Extents] [int] NULL ,

[ExtentSwitches] [int] NULL ,

[AvgFreeBytes] [int] NULL ,

[AvgPageDensity] [int] NULL ,

[ScanDensity] [decimal](18, 0) NULL ,

[BestCount] [int] NULL ,

[ActualCount] [int] NULL ,

[LogicalFrag] [decimal](18, 0) NULL ,

[ExtentFrag] [decimal](18, 0) NULL ,

[TStamp] [datetime] NULL ,

[DBName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

)

-- Declare cursor

DECLARE tables CURSOR FOR

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table

CREATE TABLE #fraglist (

ObjectName CHAR (255),

ObjectId INT,

IndexName CHAR (255),

IndexId INT,

Lvl INT,

CountPages INT,

CountRows INT,

MinRecSize INT,

MaxRecSize INT,

AvgRecSize INT,

ForRecCount INT,

Extents INT,

ExtentSwitches INT,

AvgFreeBytes INT,

AvgPageDensity INT,

ScanDensity DECIMAL,

BestCount INT,

ActualCount INT,

LogicalFrag DECIMAL,

ExtentFrag DECIMAL)

-- Open the cursor

OPEN tables

-- Loop through all the tables in the database

FETCH NEXT

FROM tables

INTO @tablename

WHILE @@FETCH_STATUS = 0

BEGIN

-- Do the showcontig of all indexes of the table

INSERT INTO #fraglist

EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')

WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')

FETCH NEXT

FROM tables

INTO @tablename

END

-- Close and deallocate the cursor

CLOSE tables

DEALLOCATE tables

insert into [FragStats] select *,getdate(),@dbname from #Fraglist

drop table #fraglist

go

Real-Time Blocker and Waiter Query

This lists whats blocked and why. The unique feature of this script is that it shows both the SQL statement that is blocked and the SQL statement waiting for the block.

select t1.resource_type

,db_name(resource_database_id) as [database]

,t1.resource_associated_entity_id as [blk object]

,t1.request_mode

,t1.request_session_id -- spid of waiter

,(select text from sys.dm_exec_requests as r --- get sql for waiter

cross apply sys.dm_exec_sql_text(r.sql_handle) where r.session_id = t1.request_session_id) as waiter_text

,t2.blocking_session_id -- spid of blocker

,(select text from sys.sysprocesses as p --- get sql for blocker

cross apply sys.dm_exec_sql_text(p.sql_handle) where p.spid = t2.blocking_session_id) as blocker_text

from

sys.dm_tran_locks as t1,

sys.dm_os_waiting_tasks as t2

where

t1.lock_owner_address = t2.resource_address

go

Longest running query - sys.dm_exec_query_stats




This script produces a list of queries, the max and average execution time plus the number of executions since the last DBCC call.
 

SELECT DISTINCT TOP 10
t.TEXT AS QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC

GO

RDP.exe Location in Windows

Windows RDP or "Remote Desktop Connection" functionality/shortcut is normally located in the Windows Start Menu in Accessories. On Some CSS virtual machines for some reason this is not the case which makes it more annoying to connect to client machines.

Having to browse to c:windows/system32/mstc.exe every time you use css software is an unnecessary evil. Normally you could load RDP by opening a run box or command line and typing 'mstsc' but some windows 7 clients are restricted to not display a run shortcut in windows or cmd shortcut from the accessories category in the windows menu.

I'm not sure if you are aware of this windows keyboard shortcut but if you press the (windows key + R) and then mstsc it will load the RDP program. Happy Days.

Batch file to move files from one directory to another

SET src_folder=C:\Users\shane.ryan\Desktop\88.29.66.131
SET tar_folder=C:\Users\shane.ryan\Desktop\88.29.66.131\Archive

for /f %%a IN ('dir "%src_folder%" /b') do move %src_folder%\%%a %tar_folder%

pause

How to call an SSIS package from the command line

 Alternatively save the script below as a batch file

echo "Starting Windows Task Scheduler - Batch File"
dtexec.exe /DTS "\"\MSDB\Load_FBDFiles_v01_13072016\"" /SERVER "\".\"" /CHECKPOINTING OFF  /REPORTING V
echo "Completing Windows Task Scheduler - Batch File"

The batch file can be called from an automated windows task

Here’s how to get there:

•       Open the Control Panel.
•        Open the Administrative Tools window.
•        In Windows 7, look under System and Security(Windows Vista, under System and Maintenance)
•        Open the Task Scheduler icon.
•        If prompted, type the administrator’s password or click Continue.
•        The Task Scheduler window appears.
•        Create new Task & Schedule ‘Load FBD Files Monthly’ accordingly, point task towards the following script “Load_FDB_Batch_WinTScheduler.bat” Ensure the task is set to run with the highest privileges. Set the schedule to desired frequency. The task should also be set to “Run whether user is logged in or not” and “Do not store password” should be checked.

SSIS Dynamic FTP task & Unzip & Load Process

 I've developed an automated SSIS solution to download zip files from an FTP Server every month. Each month the code gets the latest version of a specific file i.e. filename_YYYYMM_full.zip, downloads it and unzips it and finally loads them into the target database

Open SQL Data tools  (Visual Studio 2010 Shell)
Create an SSIS project
Its possible to set the FTP parameters using both package variables and local user variables as per the screenshot below

Its possible to set package and local user variables to use expressions to make them dynamic e.g. varRemotePath1 is defined as @[$Package::FTPFileName2] + (DT_STR,4,1252)YEAR( getdate() )  + Right( "0" + (DT_WSTR, 2 ) MONTH(Getdate()),2)+ "_FULL.ZIP"

I installed 7zip and its possible to call this dynamically to pass in the dynamic filename and destination path e.g. "x "+ @[User::VarZipFilePath]+  (DT_STR,4,1252)YEAR( getdate() )  + Right( "0" + (DT_WSTR, 2 ) MONTH(Getdate()),2)+ "_FULL.ZIP" +  " -o"+ @[User::VarUnzipFolderPath] +" -aoa"
(This will overwrite existing files if they exist)

If DBmail is configured its possible to send email notifications with a dynamic subject line & Content from an Execute T-SQL task e.g.

-- send email confirming Schema number
DECLARE @DBServerName VARCHAR(20),
                @DBSubject VARCHAR(200)

set @DBServerName  = @@SERVERNAME
set @DBSubject = @DBServerName  + ': Load Report - Completed - Confirm Edition below'

-- send email confirming Schema number
EXEC msdb..sp_send_dbmail
        @profile_name = 'DBATEAM',
        @recipients = 'john.doe@mail.co.uk',
        @subject = @DBSubject,
        @query = 'SELECT [sBulletinText] as Edition FROM [statsdb].[dbo].[fdb_Bulletin] where iLineNumber = 2',
        @query_result_header=0
 commented out email notification */

The actual load process as below calls another couple of scripts which I will upload in another post

USE master
GO

DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id('[MyDB')

EXEC(@kill);
GO

--Delete the Database
Drop Database MyDB
GO

-- Recreate the Database
Create Database MyDB
GO

-- enable XP_CmdShell
Exec sp_configure 'Show Advanced Options', 1
Reconfigure
Exec sp_configure 'XP_CmdShell', 1
Reconfigure
GO

--copy scripts to directory
EXECUTE xp_cmdshell 'copy C:\etl\scripts\ C:\temp\Uncompressed\'
GO

--load the schema into the MyDB database
USE [MyDB]
DECLARE @DBServerName VARCHAR(20),
                @DBName VARCHAR(20),
    @FilePathName VARCHAR(100),
    @sql VArchar(200)
set @DBServerName  = @@SERVERNAME
set @DBName = 'MyDB'
set @FilePathName = 'C:\temp\Uncompressed\Create_Schema_SQL_SERVER2008.sql'
set @sql = 'sqlcmd -S ' + @DBServerName + ' -d  ' + @DBName + ' -i ' + @FilePathName
EXEC xp_cmdshell  @sql
GO

--Import Files into the MyDB database
USE [MyDB]
DECLARE @DBServerName VARCHAR(20),
                @DBName VARCHAR(20),
    @FilePathName VARCHAR(100),
    @sql VArchar(200)
set @DBServerName  = @@SERVERNAME
set @DBName = 'MyDB'
set @FilePathName = 'C:\temp\Uncompressed\ImportMyFiles.sql'
set @sql = 'sqlcmd -S ' + @DBServerName + ' -d  ' + @DBName + ' -i ' + @FilePathName
EXEC xp_cmdshell  @sql
GO

-- delete downloaded file example
EXECUTE xp_cmdshell 'del /Q C:\temp\Uncompressed\*.*'
GO

-- delete downloaded file example
EXECUTE xp_cmdshell 'del /Q C:\etl\downloads\*.*'
GO

-- disable XP_CmdShell
Exec sp_configure 'Show Advanced Options', 1
Reconfigure
Exec sp_configure 'XP_CmdShell', 0
Reconfigure
GO

Add more tempdb files

USE [master];

GO

ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev2', FILENAME = N'T:\MSSQL\DATA\tempdev2.ndf' , SIZE = 8GB , FILEGROWTH = 0);

ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev3', FILENAME = N'T:\MSSQL\DATA\tempdev3.ndf' , SIZE = 8GB , FILEGROWTH = 0);

ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev4', FILENAME = N'T:\MSSQL\DATA\tempdev4.ndf' , SIZE = 8GB , FILEGROWTH = 0);

GO