Skip to toolbar

Author Archive Gary Cowan

ByGary Cowan

Join our Affiliate Program

Can we send you a check every month?

Earn ongoing monthly income by joining the free affiliate program

We payout 35% commissions with a 90-day cookie for selling our courses. You could earn hundreds off a single email.

email: cloudwerhelp@gmail.com

and ask to join our affiliate program

 

ByGary Cowan

Power BI February 2020 Update

Here is what is in the Power BI February 2020 update

Dataset management

Incremental refresh is now generally available

Reporting

Hierarchical slicer (preview)
Updates to the new ribbon (preview)
Title bar updates
Keytips
Accessibility

Modeling

New DAX Functions
FirstNonBlankValue
LastNonBlankValue

Visualizations

Funnel and pyramid chart by xViz
OEE Dashboard by 3AG Systems
Organization chart by MAQ Software
PI VIZpack by PQ Systems
Histogram
Individuals control chart
Individuals and moving range control chart
Multi-group individuals chart
X-bar control chart
X-bar and range control chart
P-chart
U-chart
Updates to Zebra BI tables and charts

Template Apps

Microsoft 365 usage analytics
NFL analytics by P3
Acterys for Quickbooks, Xero & WorkflowMax

Data preparation

Non-admin support for query diagnostics

Data connectivity

MicroStrategy connector is now generally available
FHIR connector is now generally available
Factset connector
TIBCO connector
Jamf Pro connector
Asana connector

You can learn how to use Power BI in the SQL Server Bootcamp course

ByGary Cowan

Microsoft Ending SQL Server 2019 Certifications

From June 30 2020, you will not be able to take a certification in SQL Server 2019 according to the Microsoft blog post

This because the following courses are ending

  • Microsoft Certified Solutions Associate (MCSA)
  • Microsoft Certified Solutions Developer (MCSD)
  • Microsoft Certified Solutions Expert (MCSE)

There will be no further exams in SQL Server 2019.

If you want to learn how to use SQL Server, then enrol in this course: SQL Server Bootcamp

 

 

ByGary Cowan

SQL Server 2019 Developer Edition is FREE

SQLServerBootcamp

Use the discount code 30PEROFF until 15 March 2020 to get 30% off the yearly subscription.

Use the discount code M10PEROFF until 15 March 2020 to get 10% off the monthly subscription.

Join our Affiliate Program and earn 35% commission 

SQL Server 2019 Developer Edition is free!
Yes that is right, you can download SQL Server 2019 for free.

You can download SQL Server 2019 from here :- Download SQL Server 2019

The Developer Edition is the same as the Enterprise Edition, but the only difference is that you can’t use the Developer Edition in a production environment.

Downloading SQL Server 2019 Developer Edition is still great if you want to learn about all the new features that are now in SQL Server 2019.

If you don’t know what SQL Server is, well SQL Server from Microsoft it is a software solution to store and analysis data using a variety of tools.
You will mainly use
SQL Server Engine
SQL Server Integration Services
SQL Server Analysis Services

SQL Server Engine

This is where raw data is stored as information, and you can do a variety of tasks on this data to suit your needs.

SQL Server Integration Services

This tool is used to extract, transform and load information in a database or another storage locations

SQL Server Analysis Services

This is used for analysis information to try and find out patterns or summary information.

If you have never used SQL Server before, just download it and install it and try it out.

ByGary Cowan

Rebuilding Indexes Using A Script On SQL Server

There comes a time when you have to rebuild all the indexes in a database on SQL Server, and you have to figure out a quick way of doing this. You can set up different jobs and routines to schedule the rebuild, but it is handy to have a script.

You have to rebuild indexes when you are the administrator of a database and realised it is going very slow after you have done some investigation, and you have found out that databases have not been build for a very long time (or never).

If you want a quick way to rebuild your indexes on SQL Server using a script, then use the script below.

This script will find all the indexes within a database and rebuild them. You can change the script if you like to add ONLINE rebuilding if you want your tables to be accessible through the rebuild. I have used this index rebuild many times to speed up the process of fixing performance related problem within a database when you know that there has not been a proper maintenance plan set up for the database.

This script on SQL Server 2012, SQL Server 2016 and SQL Server 2017. I have tried this script on these editions and they work and probably will work on future editions.

I would try this script out in a development environment first just to make sure you are happy with the performance because it will have a performance hit on your server when this is running. It may also take a while to run the script as this is dependant on how big your tables are and how many indexes you have.

SQL Server Rebuilding Indexes Code

DECLARE @Database VARCHAR(255) 
DECLARE @Table VARCHAR(255) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fillfactor INT

SET @fillfactor = 100

DECLARE DatabaseCursor CURSOR FOR 
SELECT name 
FROM sys.databases

ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database 
WHILE @@FETCH_STATUS = 0 
BEGIN

SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
 table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES
 WHERE table_type = ''BASE TABLE'''

-- create table cursor 
 EXEC (@cmd) 
 OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Table 
 WHILE @@FETCH_STATUS = 0 
 BEGIN

IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
 BEGIN
 -- SQL 2005 or higher command
 SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD '
 
 --PRINT @cmd
 EXEC (@cmd)
 END
 ELSE
 BEGIN
 -- SQL 2000 command
 --DBCC DBREINDEX(@Table,' ',@fillfactor) 
 PRINT 1
 END

FETCH NEXT FROM TableCursor INTO @Table 
 END

CLOSE TableCursor 
 DEALLOCATE TableCursor

FETCH NEXT FROM DatabaseCursor INTO @Database 
END 
CLOSE DatabaseCursor 
DEALLOCATE DatabaseCursor

Why not try it and give it a go to see if it works for you?

More tools like this are included in the SQL Server Bootcamp course

Please leave a comment below if this script to rebuild your indexes work or what improvements you could do to it.

ByGary Cowan

SQL Server Management Studio (SSMS) 18.0

Database

You can now download

SQL Server Management Studio (SSMS) 18.0

Just follow the link below

SQL Server Management Studio (SSMS) 18.0 - Download

ByGary Cowan

Creating a SQL Server RDS Instance on AWS

Here is a quick video on creating a SQL Server RDS Instance on AWS. You will need an AWS account to do this quick tutorial.

ByGary Cowan

Find out which SQL Agent Jobs that are currently running

Find out which SQL Agent Jobs that are currently running

If you ever want to find out which SQL Server Agent jobs that are currently running on your server without using Management Studio, just run the code below.

SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,
ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
Js.step_name
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
ON ja.job_id = js.job_id
AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE
ja.session_id = (
SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC
)
AND start_execution_date is not null
AND stop_execution_date is null
order by 2 desc
ByGary Cowan

Securing AWS – IAM (Identity Access Management)

To secure AWS properly, you need to do some initial steps within IAM (Identity Access Management).  These steps are used to make it harder for hackers to take control of your AWS Cloud System.

You need to make sure that when you go into IAM, you go through all the steps and make sure that your IAM screen has all the green ticks like the following image.

IAM (Identity and Access Management)

These are just some basic things you need to do before you start to implement any design you think about doing within AWS.

Once you have set up individual user accounts, make sure you stop using the root account and start to use an IAM User. This is because if somebody manages to gain access to your root account, then they will be able to destroy your entire cloud environment. Make sure that your root account password with multifactor authentication is kept secure with limited access to others.

 

 

ByGary Cowan

Has it finished?

Has it finished? is a question you will ask yourself many times when you are running something within SQL Server. If you are backing up a database, restoring a database or you just want to find out how long a task on your server will take to complete, then just run the code below to return information about each request that is executing within SQL Server.

SELECT
dmr.session_id,
dmr.command,
CONVERT(NUMERIC(6,2),dmr.percent_complete)AS [Percent Complete],
CONVERT(VARCHAR(20),DATEADD(ms,dmr.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),dmr.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),dmr.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),dmr.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours]
,CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,dmr.statement_start_offset/2,
CASE WHEN dmr.statement_end_offset = -1 THEN 1000
ELSE (dmr.statement_end_offset-dmr.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)
)
) [sqltxt]
FROM sys.dm_exec_requests dmr
where CONVERT(NUMERIC(6,2),dmr.percent_complete) > 0

If you want to learn how to become a DBA, then do this course.

The Complete SQL Server DBA Bootcamp : Beginner To Expert

If you want to learn how to write TSQL, then this course if for you.

The Complete SQL Bootcamp In a SQL Server Database

If you want to learn how to use SQL Server on Amazon Web Service, the join this course

The Complete AWS SQL Server RDS Bootcamp : Beginner To Expert