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.
Download SQL Server 2019
Are you looking for a link on how to download SQL Server 2019, well here it is
You can also download the whitepaper from here : https://info.microsoft.com/ww-landing-SQLDB-Microsoft-SQL-Server-WhitePaper.html
And watch a Webinar from here
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
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.
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.
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.
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]
CASE WHEN dmr.statement_end_offset = -1 THEN 1000
ELSE (dmr.statement_end_offset-dmr.statement_start_offset)/2 END)
FROM sys.dm_exec_requests dmr
where CONVERT(NUMERIC(6,2),dmr.percent_complete) > 0
Finding the status of your SQL Server Database with a script
I have been in a position where I wanted to know the status of my databases at regular intervals and I would want this information in a script. Well here is a script that I have used in the past that once placed in a job, I could monitor if the database changes its state then of this information, it could send me an email.
It is handy to track to see if your database goes into a funny state during the day.
select name as DBNAME,getdate() as RUNDATE, STATUS, STATUS2,
CASE WHEN (STATUS & 1) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [AUTOCLOSE],
CASE WHEN (STATUS & 4) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [SELECT_INTO_BULKCOPY],
CASE WHEN (STATUS & 8) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [TRUNC_LOG_ON_CHKPT],
CASE WHEN (STATUS & 16) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [TORN_PAGE_DETECTION],
CASE WHEN (STATUS & 32) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [LOADING],
CASE WHEN (STATUS & 64) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [PRE_RECOVERY],
CASE WHEN (STATUS & 128) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [RECOVERING],
CASE WHEN (STATUS & 256) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [NOT_RECOVERED],
CASE WHEN (STATUS & 512) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [OFFLINE],
CASE WHEN (STATUS & 1024) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [READ_ONLY],
CASE WHEN (STATUS & 2048) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [DBO_USE_ONLY],
CASE WHEN (STATUS & 4096) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [SINGLE_USER],
CASE WHEN (STATUS & 32768) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [EMERGENCY_MODE],
CASE WHEN (STATUS & 4194304) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [AUTOSHRINK],
CASE WHEN (STATUS &1073741824) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [CLEANLY_SHUTDOWN],
CASE WHEN (STATUS2 & 16384) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [ANSI_null_default], –This one
CASE WHEN (STATUS2 & 65536) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [concat_null_yields_null],
CASE WHEN (STATUS2 & 131072) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [recursive_triggers], — This one
CASE WHEN (STATUS2 & 1048576) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [default_to_local_cursor], –This one
CASE WHEN (STATUS2 & 8388608) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [quoted_identifier],
CASE WHEN (STATUS2 & 33554432) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [cursor_close_on_commit],
CASE WHEN (STATUS2 & 67108864) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [ANSI_nulls],
CASE WHEN (STATUS2 & 268435456) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [ANSI_warnings],
CASE WHEN (STATUS2 & 536870912) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [full_text_enabled]
If you found this script useful, just leave a comment below
What is a database?
I thought I would write this article for people who have no experience in SQL Server or know what a database is.
A database is a method of storing information where that information can be accessed to answer or to solve questions. There are many types of databases that you can use from many different companies like
• IBM DB2
With every database, you have to ability to insert, delete, update and search information that is stored within them.
Here are some examples that you would use a database
• Storing your music collection information
• Storing a sports team activity
• Storing pupils attendance at a school.
A vast amount of things that you use today will have some built-in database system for them to store data, i.e. your mobile phone.
Microsoft have a database software called SQL Server which they have been developing since 1989 and the current version of SQL Server is called SQL Server 2017.
There are many different versions of SQL Server 2017 that are designed for different people and different solutions. The main versions that people use are
• SQL Server Enterprise Edition
• SQL Server Standard Edition
• SQL Server Business Intelligence Edition
• SQL Server Developer Edition
• SQL Server Express
SQL Server Enterprise, SQL Server Standard and SQL Server Business Intelligence Edition all cost money to use in a production/business environment
SQL Server Developer Edition is free and can only be used on test systems and cannot be used in production/business environment
SQL Server Express can be used in a test environment and production environment for free.
SQL Server Enterprise and SQL Server Developer have all the features enabled and SQL Server Express has limited features enabled.
Within SQL Server, you can create a database and a database is a collection of tables that store the data. You access this data by using a special database language called T-SQL or you can use a frontend application as well.
If you found this useful, just leave a comment below
Performance Tuning with Query Store in SQL Server 2017
If you want to monitor the performance of your queries within SQL Server 2017, you can with a feature called Query Store. Query Store monitors changes to your queries to see if performance deteriorates or improves when the query plan changes.
When you create a new database, is disabled by default; you need to enable this feature.
To enable Query Store, do the following steps
1. In Object Explorer, right-click a database, and then click Properties.
2. In the Database Properties dialogue box, select the Query Store page.
3. In the Operation Mode (Requested) box, select On.
You would use Query Store when you want to
• Find queries that have become slow over time and fix them by forcing the queries to use a different query plan.
• Find out how many times a query was run in a set period.
• Find out how many times the query plan was changed.
• Analysis why your query plan changes.
The best way to learn about
is to play around with it so just enable it and refresh the database, so it looks like this
Then just double click on each item in the query store to see what they do. It is better to enable Query Store on a database that has many transactions so you will be able to see results appear fast. I would suggest using a development server to test this feature as turning on this feature can have a minor effect on performance on your server.
If you have any comments on this article, just leave a comment below.
SQL Server Information from a script.
SQL Server has a lot of useful information that you need to administrate or develop solutions. Here is a great SQL Script to find out information about your SQL Server Instance and operating system.
-- Get selected server properties SELECT SERVERPROPERTY('MachineName') AS [MachineName], SERVERPROPERTY('ServerName') AS [ServerName], SERVERPROPERTY('InstanceName') AS [Instance], SERVERPROPERTY('IsClustered') AS [IsClustered], SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS], SERVERPROPERTY('Edition') AS [Edition], SERVERPROPERTY('ProductLevel') AS [ProductLevel], SERVERPROPERTY('ProductVersion') AS [ProductVersion], SERVERPROPERTY('ProcessID') AS [ProcessID], SERVERPROPERTY('Collation') AS [Collation], SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled], SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly], SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled], SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus], SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported]; -- Windows information SELECT windows_release, windows_service_pack_level, windows_sku, os_language_version FROM sys.dm_os_windows_info WITH (NOLOCK) OPTION (RECOMPILE); -- Get configuration values for instance SELECT name, value, value_in_use, minimum, maximum, [description], is_dynamic, is_advanced FROM sys.configurations WITH (NOLOCK) ORDER BY name OPTION (RECOMPILE); -- SQL Server Services information SELECT servicename, process_id, startup_type_desc, status_desc, last_startup_time, service_account, is_clustered, cluster_nodename, [filename] FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE); -- Get processor description from Windows Registry EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', N'ProcessorNameString'; -- Volume info for all LUNS that have database files on the current instance SELECT DISTINCT vs.volume_mount_point, vs.file_system_type, vs.logical_volume_name, CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS [Total Size (GB)], CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0) AS [Available Size (GB)], CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %] FROM sys.master_files AS f WITH (NOLOCK) CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs OPTION (RECOMPILE); -- File names and paths for TempDB and all user databases in instance SELECT DB_NAME([database_id]) AS [Database Name], [file_id], name, physical_name, type_desc, state_desc, is_percent_growth, growth, CONVERT(bigint, growth/128.0) AS [Growth in MB], CONVERT(bigint, size/128.0) AS [Total Size in MB] FROM sys.master_files WITH (NOLOCK) WHERE [database_id] > 4 AND [database_id] <> 32767 OR [database_id] = 2 ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE)
Configuring SQL Server 2017
How that you have installed SQL Server 2017, it is time to get your hands dirty and start to create databases. But before we do that, it is a good idea to configure your SQL Server 2017 environment correctly.
To do this, just Right Click the server and click properties.
Once you have done that, the following screen should appear.
Here is where you can set some properties for your SQL Server 2017 environment that will help you control the performance, administration and security of your databases.
In the General tab, you can see it just shows you general information about your server that you may need to know from time-to-time.
The Memory tab controls how much of the operating system SQL Server will use. Most of the time, the only option to you need to change on this screen is the Maximum Server Memory option. There are many calculators on the internet on how to work out the correct value, but if you just want a good value, subtract 4-8 gig from the total amount of memory you have on your pc/laptop and put in the value i.e.
So If you have 14 gigabytes installed on your hardware, type in ‘10000’ and that will mean SQL Server will use 10 gigabytes and 4 gigabytes will be used for Windows. You might need to type in a less number if you have other memory intensive software on your hardware.
Processor tab, you don’t usually change anything in this tab so just leave things as default.
Security tab, again you don’t change anything in this tab unless you have a security issue that you need to resolve. In a working environment, you might need to audit more processes in your situation so changing some of the settings for security purposes might need to happen. In a testing environment, I usually leave these as default.
The Connection tab governs how many connections you want your SQL Server to handle and if you’re going to allow remote connections to your server.
The Database Settings tab is where you can set where decide files are stored on your hard drive. Databases have two files when they are created, an MDF file and an LDF File. It is good practice to place this files on different hard drives for performance. In this table, you can set the default location on where this files will be created.
For setting up the environment for testing, you don’t need to change the ‘Advanced’ and ‘Permissions’ tabs but future posts, I will go into more details.
This is just a simple guide on how to configure your SQL Server 2017 environment and will go into more details in future blogs. If there is anything you want me to cover in more detail, just leave a comment below.