Preventing a Database Breach

Photo by Sora Shimazaki on Pexels.com

One of the hardest things to do is prevent something from happening when you don’t know when it might happen or who will try to make it happen. As a Database Administrator, you have to be aware that a data breach might happen and take all reasonable precautions to prevent it from happening. According to the 2016 study by IBM, 60% of database attacks are insiders (people using approved network credentials) looking to access or steal corporate data.

There are some basic steps you should execute to help prevent unauthorized access to your database environment.

  1. Enforce Privileges – As an employee starts their tenure at a company, they are usually given the exact correct privileges for their position. The longer the employee is with a company, the correct privileges start to vary from the effective privileges, until eventually the employee has the wrong access privileges.  You need to make sure those initial access rights are correct from day one, and that you periodically review the access rights for every employee. If there is any question about the correct privileges, you should contact their supervisor and document the correct level of access.
  2. Database Discovery – People are busy, and don’t always pay attention when new database instances are created. The people who manage the databases are often not the people who install the software, so this can lead to an environment where there are unauthorized or poorly configured database instances. Database discovery is a crucial first step for avoiding security issues, so you should scan your environment for new database instances as often as possible. The amount of change in your environment will dictate how often you should search for new database instances, but the minimum is annually.
  3. Connection EncryptionEncrypting the connection between the user and the database can help prevent man-in-the-middle attacks.
  4. Strong Password – You should expect the same password strength for your databases as you expect on the network. If possible, use Windows Authentication instead of SQL Server Authentication. This will help enforce the same password strength as your network password, and you must verify that the network settings are using best practice strength requirements.
  5. Detect Compromised Credentials – It is estimated that 60% of companies cannot detect compromised credentials, based on a study by solution vendor Rapid7. Since authorized individuals use databases in a predictable way, abnormal or unauthorized access will be detected and you can be alerted.  There are security appliances that can catch unusual or unwanted user access based solely on algorithm analysis, preventing a possible data breach.

Free Download: SQL Server Management Studio 19.2


SQL Server

SQL Server Management Studio (SSMS) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SSMS combines a broad group of graphical tools with a number of rich script editors to provide developers and administrators of all skill levels access to SQL Server.

Microsoft has announced the latest release of SQL Server Management Studio (SSMS) in November as a free download. SSMS 19.2 is now available.

Get it here:

Download – The version number for the latest release is 19.2.56.2

New in this release

New Item Details
Azure Data Studio installation integration The installation of SSMS installs Azure Data Studio 1.47.0.
Always Encrypted Added support for secure enclaves with Azure SQL Database in the New Database dialog, Database Properties dialog, and Always Encrypted Wizard.
Always Encrypted Improved performance for the Always Encrypted Wizard.
Azure SQL Managed Instance Added the Page Verify database option on the Options page within Database Properties.
Client Drivers Updated SSMS to use the latest driver versions for MSODBCSQL.MSI (17.10.5.1) and MSOLEDBSQL.MSI (18.6.7). The inclusion of these new versions could require users who also have older versions of the drivers to reboot after installing SSMS 19.2.
Connection References to Azure Active Directory (Azure AD) updated to Microsoft Entra.
Connection Updated F1 links for the Always Encrypted and Additional Connection Parameters pages in the Connection dialog.
Extended Events Added support for Watch Live Data for event sessions created in Azure SQL Database and Azure SQL Managed Instance. For Azure SQL Database, you must specify the database name in the Connect to database field in the Connection Properties tab of the Connection dialog. The ability to Watch Live Data is currently in preview.
Extended Events Introduced ability to use the XEvent Profiler for Azure SQL Database. For Azure SQL Database, you must specify the database name in the Connect to database field in the Connection Properties tab of the Connection dialog. The ability to use XEvent Profiler is currently in preview.
Extended Events Exposed the histogram target for event sessions in Azure SQL Database.
Import Flat File Updated Import Flat File wizard to improve file encoding detection.
General Introduced on-demand logging of Azure API calls from SSMS enabling customer-facing monitoring and troubleshooting for Azure-connected features, which can be accessed within Tools -> Options -> Output Window.
General Updated Help -> Technical Support and Help -> Send Feedback to direct to appropriate links.
Ledger Added support for creating a Ledger database in Azure SQL Managed Instance.
Link feature for Azure SQL Managed Instance Improved wizard for performing failover on Managed Instance link. Supports unidirectional failover to Azure and bi-directional failover between SQL Server 2022 and Azure SQL Managed Instance.
Link feature for Azure SQL Managed Instance Improved wizard for creating the link between SQL Server and Azure SQL Managed Instance. Supports link creation from SQL Server to Azure SQL Managed Instance and from Azure SQL Managed Instance to SQL Server 2022.
Link feature for Azure SQL Managed Instance Improved wizard for testing connectivity between SQL Server and Azure SQL Managed Instance. Creates a temporary testing endpoint if none exists and can be invoked from a database replica on either SQL Server or Azure SQL Managed Instance.
Link feature for Azure SQL Managed Instance Always On High Availability menu is now available in Object Explorer for Azure SQL Managed Instance and lists established Managed Instance links.
Linked servers Introduced Azure SQL resources browser in linked servers wizard facilitating linked servers setup for Azure SQL Managed Instance.
Object Explorer Reduced load time for the New Database dialog in Azure SQL Database.
Object Explorer Added support for the External File Format node under External Resources node for Azure SQL Database.
Query Editor Introduced connection pooling for Intellisense to reduce the number of new connections made and keep connections open between refreshes.
SSIS The IS Deployment Wizard now supports the Microsoft Entra Interactive Authentication Login method for Project Deployment.

Continue reading “Free Download: SQL Server Management Studio 19.2”

Free Download: SQL Server Management Studio 19.1


SQL Server

SQL Server Management Studio (SSMS) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SSMS combines a broad group of graphical tools with a number of rich script editors to provide developers and administrators of all skill levels access to SQL Server.

The SSMS 19.x installation doesn’t upgrade or replace SSMS versions 18.x or earlier. SSMS 18.x installs side by side with previous versions, so both versions are available for use. However, if you have a preview version of SSMS 19.x installed, you must uninstall it before installing SSMS 19.1. You can see if you have the preview version by going to the Help > About window.

If a computer contains side-by-side installations of SSMS, verify you start the correct version for your specific needs. The latest version is labeled Microsoft SQL Server Management Studio 19

What’s new in 19.1

New Item Details
Azure Data Studio installation integration The installation of SSMS installs Azure Data Studio 1.44.
Always Encrypted Added support for secure enclaves and in-place encryption in the Always Encrypted Wizard.
Azure SQL Managed Instance Introduced visibility to the status of the Distributed Transaction Coordinator (DTC) service for Azure SQL Managed Instance. Object Explorer can be used to determine if DTC is enabled on the Azure SQL Managed Instance (within the Management node).
Backup/Restore Added capability to restore backup files from S3-compatible storage to SQL Server 2022 and Azure SQL Managed Instance.
General SSMS Updated File Version for ssms.exe to align with product version.
General SSMS Removed deprecated hardware from the list of available service-level objects.
General SSMS Changed the system browser setting, within Tools > Options > Azure Services, to default to True. The external browser will be used, instead of the legacy embedded browser.
General SSMS Removed Vulnerability Assessment from SSMS.
Link feature for Azure SQL Managed Instance Added Network Checker wizard, providing the capability to test the network connection and ports prior to creating the link.
Link feature for Azure SQL Managed Instance Added an advanced network troubleshooting capability within the existing link creation wizard. This provides the capability to troubleshoot network connectivity issues while link creation is in progress.
Object Explorer Removed script header text when selecting the top 1000 rows.
PowerShell Added ability for users to choose the version of PowerShell to use when launched from SSMS.
PowerShell Introduced more PowerShell options within Tools > Options > SQL Server Object Explorer > Commands.

Beginning with SQL Server Management Studio (SSMS) 18.7, Azure Data Studio is automatically installed alongside SSMS. Users of SQL Server Management Studio are now able to benefit from the innovations and features in Azure Data Studio.

Continue reading “Free Download: SQL Server Management Studio 19.1”

TIOBE Index for May 2023 – Which Programming Language is Most Popular?

Programming - @SeniorDBA

Have you seen the latest TIOBE rankings report?

The TIOBE Programming Community index is an indicator of the popularity of programming languages. The index is updated once a month. The ratings are based on the number of skilled engineers world-wide, courses and third-party vendors. Popular search engines such as Google, Bing, Yahoo!, Wikipedia, Amazon, YouTube and Baidu are used to calculate the ratings. Observe that the TIOBE index is not about the best programming language or the language in which most lines of code have been written.

It has been stated before, programming language popularity is rather stable. If we look at the first 10 programming languages in the TIOBE index, then C# is the youngest of them all. C# started in 2000. That is 23 years ago! Almost every day a new programming language is born, but hardly any of them enter the top 100. At least not in their first 10 years. The only languages younger than 10 years in the current top 100 are: Swift (#14), Rust (#17), Crystal (#48), Solidity (#59), Pony (#71), Raku (#72), Zig (#88) and Hack (#92). None of them are less than 5 years old. In other words, it is almost impossible to hit the charts as a newbie. On the contrary, we see that golden oldies revive. Take for instance Fortran, which is back in the top 20 thanks to the growing demand for numerical computational power. So, if you have just invented a brand new language, please have some patience! — Paul Jansen CEO TIOBE Software

You can read the details of how and why languages are popular at the TIOBE website. If you are a developer, you will find this information interesting.

Continue reading “TIOBE Index for May 2023 – Which Programming Language is Most Popular?”

TIOBE Index for January 2023 – Which Language is Most Popular?

Programming - @SeniorDBA

Have you seen the latest TIOBE rankings report?

The TIOBE Programming Community index is an indicator of the popularity of programming languages. The index is updated once a month. The ratings are based on the number of skilled engineers world-wide, courses and third-party vendors. Popular search engines such as Google, Bing, Yahoo!, Wikipedia, Amazon, YouTube and Baidu are used to calculate the ratings. Observe that the TIOBE index is not about the best programming language or the language in which most lines of code have been written.

Scripting language Lua is back in the top 20 of the TIOBE index. In its heyday in 2011, Lua briefly touched a top 10 position. Whether this is going to happen again is unknown. But it is clear that Lua is catching up in the game development market: easy to learn, fast to execute, and simple to interface with C. This makes Lua a perfect candidate for this job. One of the drivers behind the recent success of Lua is the very popular gaming platform Roblox, which uses Lua as its main programming language. –Paul Jansen CEO TIOBE Software

TIOBE also announced that C++ is the programming language of 2022. You can read the details of how and why at the TIOBE website, as well as see the runners up (C and Python). If you are a developer, you will find this information interesting.

Continue reading “TIOBE Index for January 2023 – Which Language is Most Popular?”

Free Download: SQL Server Management Studio 18.12.1


SQL Server

SQL Server Management Studio (SSMS) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SSMS combines a broad group of graphical tools with a number of rich script editors to provide developers and administrators of all skill levels access to SQL Server.

The SSMS 18.x installation doesn’t upgrade or replace SSMS versions 17.x or earlier. SSMS 18.x installs side by side with previous versions, so both versions are available for use. However, if you have a preview version of SSMS 18.x installed, you must uninstall it before installing SSMS 18.12. You can see if you have the preview version by going to the Help > About window.

If a computer contains side-by-side installations of SSMS, verify you start the correct version for your specific needs. The latest version is labeled Microsoft SQL Server Management Studio 18.

Beginning with SQL Server Management Studio (SSMS) 18.7, Azure Data Studio is automatically installed alongside SSMS. Users of SQL Server Management Studio are now able to benefit from the innovations and features in Azure Data Studio.

Continue reading “Free Download: SQL Server Management Studio 18.12.1”

Understanding VSS and SQL Server

SQL Server - @SeniorDBA

In the early days of SQL Server and Windows, backups weren’t always easy to create. Sometimes you had to completely stop the SQL Server services to get full backups. It could be difficult to backup everything without a pre-backup script to stop required services and post-backup scripts to get everything running again.  In the last 10 years, however, a lot has changed. Microsoft has included a few technologies in Windows Server that facilitate the conversation between these different components to allow them to work better together. The main component behind all this success is the Volume Shadow Copy Service (VSS) introduced in Windows Server 2003. The idea is actually quite simple: create a Windows service that is able to coordinate the actions required to create a consistent shadow copy (also known as a snapshot or a point-in-time copy) of the data you want to backup. VSS operates at the block level of the file system. You can then use those shadow copies as your backup or you can take them to another disk or to tape as required, without affecting the running application at that point.

Continue reading “Understanding VSS and SQL Server”

SQL Server Configuration Recommendations

SQL Server

SQL Server Recommendations

The following is the list of general recommendations that are made for your installation of SQL Server. Each instance of SQL Server is unique, so you may have specific configuration requirements that are outside of these recommendations. You will often hear “it depends…” when discussing specific recommendations. People often ask about general recommendations, which is really just a place to start when you are beginning to configure your new server.

  • Always use Page Checksum to audit data integrity.
  • Consider using compression for read-only filegroups for higher storage efficiency.
  • Use NTFS for security and availability.
  • Use instant file initialization for performance optimization.
  • Use manual file growth database options.
  • Use partitioning (available in Enterprise Edition) for better database manageability.
  • Storage-align indexes with their respective base tables for easier and faster maintenance.
  • Storage-align commonly joined tables for faster joins and better maintenance.
  • Choose your RAID level carefully. For excellent performance and high reliability of both read and write data patterns, use RAID10. For read-only data patterns, use RAID5. Compared to RAID0, all other RAID levels have lower write performance, all else being equal, because RAID0 does not have redundancy. You do not want to use RAID0.
  • For optimized I/O parallelism, use 64 KB or 256 KB stripe size.
  • Although disk performance is commonly attributed to the disk seek time and rotational speed, the amount of cache also plays a role. For servers that frequently perform sequential workloads, like SQL Server, having a large disk cache is often times more important than seek time. Important – Make sure that the cache is backed up by battery.
  • For future scalability and ease of maintenance, use volume mount points.
  • To increase bus bandwidth reliability, use multipathing software.
  • For small servers with less than three disks performing mostly sequential I/O, or servers with approximately eight disks performing random I/O, PCI is sufficient. However, PCI-X is recommended and can service a wider range of servers with varying workload size.
  • Directly attached I/O is recommended for small- to medium-sized servers.
  • SAN systems are recommended for larger servers.
  • NAS systems are not recommended. Use iSCSI instead.
  • For better recoverability, use a SCSI interface instead of SATA and IDE.
  • For larger server loads, use SCSI or SATA with TCQ support.
  • Store transaction logs separate from data files. Do not stripe on the same disk as the data files.
  • For large bandwidth demands on the I/O bus, use a different bus for the transaction log files.
  • The number of data files within a single filegroup should equal to the number of CPU cores. This includes the TempDB and user database files.
  • Don’t assume the person configuring your server knows anything about SQL Server performance versus Windows server performance.

Who is Connected using SSMS to your SQL Server Database

Which users are connected to your database using SQL Server Management Studio? This may be helpful in quickly determining which users are directly connected vs. those connected via an application.

tips-tricks

SELECT CO.client_net_address, 
       SE.host_name, 
       SE.login_name, 
       ST.text
FROM   sys.dm_exec_sessions SE
	   INNER JOIN sys.dm_exec_connections CO
           ON SE.session_id = CO.session_id
CROSS APPLY sys.dm_exec_sql_text(CO.most_recent_sql_handle) ST
WHERE  SE.program_name LIKE 'Microsoft SQL Server Management Studio%'
ORDER BY SE.program_name, 
         CO.client_net_address;

This should work in SQL Server 2005+

History of SQL Server

Have you seen the video on the history of SQL Server?

History of SQL Server Video

Microsoft released its first version of SQL Server in 1988. It was designed for the OS/2 platform and was jointly developed by Microsoft and Sybase. During the early 1990s, Microsoft began to develop a new version of SQL Server for the NT platform.

This post has really useful information on the subject of SQL Server history, written by Euan Garden.

The SAF (SQL Admin Facility) interface from SQL Server 1.1:

SAF Interface from SQL Server 1.1

This article lists some early notes about the development:

“While it was under development, Microsoft decided that SQL Server should be tightly coupled with the NT operating system. In 1992, Microsoft assumed core responsibility for the future of SQL Server for NT. In 1993, Windows NT 3.1 and SQL Server 4.2 for NT were released. Microsoft’s philosophy of combining a high-performance database with an easy-to-use interface proved to be very successful. Microsoft quickly became the second most popular vendor of high-end relational database software. In 1994, Microsoft and Sybase formally ended their partnership. In 1995, Microsoft released version 6.0 of SQL Server. This release was a major rewrite of SQL Server’s core technology. Version 6.0 substantially improved performance, provided built-in replication, and delivered centralized administration. In 1996, Microsoft released version 6.5 of SQL Server. This version brought significant enhancements to the existing technology and provided several new features. In 1997, Microsoft released version 6.5 Enterprise Edition. In 1998, Microsoft released version 7.0 of SQL Server, which was a complete rewrite of the database engine. In 2000, Microsoft released SQL Server 2000. SQL Server version 2000 is Microsoft’s most significant release of SQL Server to date. This version further builds upon the SQL Server 7.0 framework. According to the SQL Server development team, the changes to the database engine are designed to provide an architecture that will last for the next 10 years.”

If you are just interested in the sequence of events the following timeline by Raksh Mishra summarizes the development history of SQL Server:

  • 1987 Sybase releases SQL Server for UNIX
  • 1988 Microsoft, Sybase, and Aston-Tate port SQL Server to OS/2
  • 1989 Microsoft, Sybase, and Aston-Tate release SQL Server 1.0 for OS/2
  • 1990 SQL Server 1.1 is released with support for Windows 3.0 clients. Aston-Tate drops out of SQL Server development
  • 1991 Microsoft and IBM end joint development of OS/2
  • 1992 Microsoft SQL Server 4.2 for 16-bit OS/2 1.3 is released
  • 1992 Microsoft and Sybase port SQL Server to Windows NT
  • 1993 Windows NT 3.1 is released
  • 1993 Microsoft and Sybase release version 4.2 of SQL Server for Windows NT
  • 1994 Microsoft and Sybase co-development of SQL Server officially ends
  • Microsoft continues to develop the Windows version of SQL Server
  • Sybase continues to develop the UNIX version of SQL Server
  • 1995 Microsoft releases version 6.0 of SQL Server
  • 1996 Microsoft releases version 6.5 of SQL Server
  • 1998 Microsoft releases version 7.0 of SQL Server
  • 2000 Microsoft releases SQL Server 2000
  • SQL Server 2000 Service Pack 1 – Release date: June 12, 2001
  • SQL Server 2000 Service Pack 2 – Release date: November 30, 2001
  • SQL Server 2000 Service Pack 3 – Release date: January 17, 2003
  • SQL Server 2000 Service Pack 3a – Release date: May 19, 2003
  • SQL Server 2000 Service Pack 4 – Release date: May 6, 2005
  • 2005 Microsoft releases SQL Server 2005 on November 7th, 2005
  • SQL Server 2005 Service Pack 1 – Release date: March 18, 2006
  • SQL Server 2005 Service Pack 2 – Release date: March 5, 2007
  • SQL Server 2005 Service Pack 3 – Release date: December 15, 2008
  • 2008 Microsoft releases SQL Server 2008 RTM on August 2008
  • SQL Server 2008 Service Pack 1 – Release date: August 27, 2009
  • SQL Azure
  • Microsoft released SQL Server 2008 R2 RTM on April 21, 2010
  • SQL Server 2008 Service Pack 2 – Release date: September 29, 2010
  • SQL Server 2011, Code name Denali CTP1 Release date: November 8, 2010
  • SQL Server 2005 Service Pack 4 – Release date: December 17, 2010

These are also some (humorous) details from Kevin Kline at this site.

Best Practice: Stored Procedure Optimization Tips

Best Practice - SeniorDBA

SQL Server performance isn’t a simple checkbox you check that improves database performance. You have to look at many different factors to gain incremental improvements. Each change might give you a 5-10% improvement, but 10 changes might lead to a 50-80% improvement to the speed of your stored procedure.

In this article by Pinal Dave, we see some of the most common tweaks to your stored procedures that should be addressed as a best practice in your environment.

  • Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.
CREATE PROC dbo.ProcName
 AS
 SET NOCOUNT ON;
 --Procedure code here
 SELECT column1 FROM dbo.TblTable1
 -- Reset SET NOCOUNT to OFF
 SET NOCOUNT OFF;
 GO
  • Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like
SELECT * FROM dbo.MyTable -- Preferred method
 -- Instead of
 SELECT * FROM MyTable -- Avoid this method
 --And finally call the stored procedure with qualified name like:
 EXEC dbo.MyProc -- Preferred method
 --Instead of
 EXEC MyProc -- Avoid this method
  • Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.
  • Use IF EXISTS (SELECT 1) instead of (SELECT *)
  • Use the sp_executesql stored procedure instead of the EXECUTE statement

You can read the entire article here.

Common Database Design Mistakes

Project Management

When creating a new database instance, people will often make mistakes. While I can’t list all the mistakes that people can or will make, I hope this brief list will help you know what mistakes are possible, and help guide you to not making as many mistakes. Sometimes we attack a design problem with the idea that we will just get the work done, but most times it is better to take the extra time to do it right.

I’m not perfect, and I have made these (and many other) mistakes in database design. I’m not trying to tell you what to do or even how to do it. I’m just trying to take my lessons learned and provide a simple list so that you might not make the same mistakes. I also want to point out that no list will ever be the only way to do anything. With database design questions, the best answer is usually “it depends”. When considering the many variables that make up your environment, you will need to make many decisions that help your database instance work best in your unique environment. You have to take into account the personnel you are working with, the limits of your hardware, company policies, etc.

Database design and implementation is the cornerstone of any database related project and should be treated will the importance that deserves. If you do your job really well, people will tend to minimize how important your job is in getting their projects completed. Like a police department that does a good job catching and locking up criminals, people start wondering why they need so many policemen when the crime rate goes down. People might start asking why they need your help in getting good database design, but it will only take a few failed projects for them to come back to you for your professional help.

Continue reading “Common Database Design Mistakes”

Top 50 Tables with Stale Statistics in SQL Server

Statistics - @SeniorDBA

SQL Server uses statistics in the Query Optimizer (QO) to create query plans that have the best possible query performance. The Query Optimizer already generates the necessary statistics for a high-quality query plan for most queries. Statistics for query optimization are binary large objects (BLOBs) that contain statistical information about the distribution of values in one or more columns of a table or indexed view. The Query Optimizer uses these statistics to estimate the cardinality, or number of rows, in the query result. These cardinality estimates enable the Query Optimizer to create a high-quality query plan. These statistics can become stale after certain INSERT, UPDATE and DELETE operations which involve a large number of rows.

A histogram measures the frequency of occurrence for each distinct value in a data set. The query optimizer computes a histogram on the column values in the first key column of the statistics object, selecting the column values by statistically sampling the rows or by performing a full scan of all rows in the table or view. If the histogram is created from a sampled set of rows, the stored totals for number of rows and number of distinct values are estimates and do not need to be whole integers.

Continue reading “Top 50 Tables with Stale Statistics in SQL Server”

Hard Drive RAID Levels Explained

RAID Levels - @SeniorDBA

What is RAID?

RAID stands for Redundant Array of Inexpensive Disks. It is a technology used to distribute data across multiple hard drives in one of several ways called “RAID levels”, depending on what level of redundancy and performance is required.

Wikipedia defines RAID as “a data storage virtualization technology that combines multiple physical disk drive components into one or more logical units for the purposes of data redundancy, performance improvement, or both. Data is distributed across the drives in one of several ways, referred to as RAID levels, depending on the required level of redundancy and performance. The different schemes, or data distribution layouts, are named by the word “RAID” followed by a number, for example RAID 0 or RAID 1. Each schema, or RAID level, provides a different balance among the key goals: reliability, availability, performance, and capacity. RAID levels greater than RAID 0 provide protection against unrecoverable sector read errors, as well as against failures of whole physical drives.”

In environments where speed and redundancy are required, you need to select the proper RAID level that matches your requirements and budget. In general, a RAID-enabled system uses two or more hard disks to improve the performance or provide some level of fault tolerance for a NAS or server.

There are several RAID concepts that you must also understand:

Continue reading “Hard Drive RAID Levels Explained”

TIOBE Index for March 2022 – Which Language is Most Popular?

Programming - @SeniorDBA

Have you seen the latest TIOBE rankings report?

The TIOBE Programming Community index is an indicator of the popularity of programming languages. The index is updated once a month. The ratings are based on the number of skilled engineers world-wide, courses and third-party vendors. Popular search engines such as Google, Bing, Yahoo!, Wikipedia, Amazon, YouTube and Baidu are used to calculate the ratings. Observe that the TIOBE index is not about the best programming language or the language in which most lines of code have been written.

Scripting language Lua is back in the top 20 of the TIOBE index. In its heyday in 2011, Lua briefly touched a top 10 position. Whether this is going to happen again is unknown. But it is clear that Lua is catching up in the game development market: easy to learn, fast to execute, and simple to interface with C. This makes Lua a perfect candidate for this job. One of the drivers behind the recent success of Lua is the very popular gaming platform Roblox, which uses Lua as its main programming language. –Paul Jansen CEO TIOBE Software

You’ll also notice Python has moved to the top, and Java has lost some popularity and is down to 3th.

Continue reading “TIOBE Index for March 2022 – Which Language is Most Popular?”

Free Download: SQL Server Management Studio 18.11.1


SQL Server

SQL Server Management Studio (SSMS) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SSMS combines a broad group of graphical tools with a number of rich script editors to provide developers and administrators of all skill levels access to SQL Server.

The SSMS 18.x installation doesn’t upgrade or replace SSMS versions 17.x or earlier. SSMS 18.x installs side by side with previous versions so both versions are available for use. If you have a previous GA version of SSMS 18 installed, installing SSMS 18.10 upgrades it to 18.10.

If a computer contains side-by-side installations of SSMS, verify you start the correct version for your specific needs. The latest version is labeled Microsoft SQL Server Management Studio 18.

Beginning with SQL Server Management Studio (SSMS) 18.7, Azure Data Studio is automatically installed alongside SSMS. Users of SQL Server Management Studio are now able to benefit from the innovations and features in Azure Data Studio.

Continue reading “Free Download: SQL Server Management Studio 18.11.1”

Preventing a Database Breach

Photo by Sora Shimazaki on Pexels.com

One of the hardest things to do it prevent something from happening when you don’t know when it might happen or who will try to make it happen. As a Database Administrator, you have to be aware that data breaching might happen and take reasonable precautions to prevent them. According to the 2016 study by IBM, 60% of database attacks are insiders (people using approved network credentials) looking to access or steal corporate data.

There are some basic steps you should execute to help prevent unauthorized access to your database environment.

  1. Enforce Privileges – As an employee starts their tenure at a company, they are usually given the exact correct privileges for their position. The longer the employee is with a company, the correct privileges start to vary from the effective privileges, until eventually the employee has the wrong access privileges.  You need to make sure those initial access rights are correct from day one, and that you periodically review the access rights for every employee. If there is any question about the correct privileges, you should contact their supervisor and document the correct level of access.
  2. Database Discovery – People are busy, and don’t always pay attention when new database instances are created. The people who manage the databases are often times not the people who install the software, so this can lead to an environment where there are unauthorized or poorly configured database instances. Database discovery is a crucial first step for avoiding security issues, so you should scan your environment for new database instances as often as possible. The amount of change in your environment will dictate how often you should search for new database instances, but the minimum is annually.
  3. Connection EncryptionEncrypting the connection between the user and the database can help prevent man-in-the-middle attacks.
  4. Strong Password – You should expect the same password strength for your databases as you expect on the network. If possible, use Windows Authentication instead of SQL Server Authentication. This will help enforce the same password strength as your network password, and you must verify that the network settings are using best practice strength requirements.
  5. Detect Compromised Credentials – It is estimated that 60% of companies cannot detect compromised credentials, based on a study by solution vendor Rapid7. Since authorized individuals use databases in a predictable way, abnormal or unauthorized access will be detected and you can be alerted.  There are security appliances that can catch unusual or unwanted user access based solely on algorithm analysis, preventing a possible data breach.

Scripts for listing all SQL Server Databases and Objects using PowerShell

PowerShell and SQL Server - SeniorDBA

This powerful script lists all objects in an instance and scripts them into a network folder, by date and instance, so you can keep a record of the objects.

Installing PowerShell the SqlServer module:

Install-Module -Name SqlServer

If there are previous versions of the SqlServer module on the computer, you may be able to use Update-Module, or provide the -AllowClobber parameter:

Install-Module -Name SqlServer -AllowClobber

This article by Angel Gomez gives you the script and some information on how to use it.

Continue reading “Scripts for listing all SQL Server Databases and Objects using PowerShell”

TIOBE Index for January 2022 – Which Language is Most Popular?

Programming - @SeniorDBA

Have you seen the latest TIOBE rankings report?

The TIOBE Programming Community index is an indicator of the popularity of programming languages. The index is updated once a month. The ratings are based on the number of skilled engineers world-wide, courses and third-party vendors. Popular search engines such as Google, Bing, Yahoo!, Wikipedia, Amazon, YouTube and Baidu are used to calculate the ratings. Observe that the TIOBE index is not about the best programming language or the language in which most lines of code have been written.

Python started at position #3 of the TIOBE index at the beginning of 2021 and left both Java and C behind to become the number one of the TIOBE index. But Python’s popularity didn’t stop there. It is currently more than 1 percent ahead of the rest. Java’s all-time record of 26.49% ratings in 2001 is still far away, but Python has it all to become the de facto standard programming language for many domains. There are no signs that Python’s triumphal march will stop soon.– Paul Jansen CEO TIOBE Software

Continue reading “TIOBE Index for January 2022 – Which Language is Most Popular?”

10 Ways to make the Wrong Impression on Your First Day

Congratulations, you finally landed a new job as technology, like database administrator, cybersecurity analyst, etc. It can be easy to make the wrong impression on your first day. Now all you have to do it survive your first day at work, without doing just about everything the wrong way. If this is your first real job, or your first new job in a long time, you might need a few pointers to prevent your first day from being a disaster.

Meeting

By avoiding these 10 annoying behaviors, you get start your new job without sticking out or making enemies.

1. Know It All – You might be the smartest person you know, but you probably don’t know everything about your new company on the first day. There might be plenty of things you will see and hear that sound like they are doing it wrong, but they have been doing it that way before you got there. The key is to absorb the information and take plenty of notes. Once you have been there for a while, you can start making recommendations on process improvements. Telling people that they are doing something wrong on your first day is not the best way to make friends or impress your co-workers.

2. Fatigue – Make sure you are fully rested and on time for your first day. First impressions are important, so it is better to be early than late. You want to be fully rested and ready to spend the entire day working hard and learning everything you can about how your company does what it does. Don’t expect a long lunch and don’t even think about leaving early.

3. Dress Code – One of the things you want to get straight before you arrive on your first day is what you are expected to wear. As you attend your interviews or if you make visits to the office, note the what everyone else is wearing. If you are given conflicting messages or aren’t sure what to wear, over dress. It is better to be known as the guy who showed up on their first day in a suit when everyone else is wearing jeans, than the guy who showed up in shorts and a tee shirt when everyone else is wearing a suit. You can be prepared to change your clothes if you need to dress down a little as the day goes on. You can always remove a jacket and tie, pull on a sweater, or even change shoes, if required. People judge books by their covers and you by your first-day attire.

4. TMI – Too Much Information (TMI) is a nail in the coffin of a first-day employee. People are going to want to get to know you a little so they will ask you questions about your personal life or previous employers. Keep the stories short and without very much depth. People don’t want to hear all about your romantic relationships and previous bosses. Once you get to know your co-workers a little better, you will also get to know more about who you can trust or who is the office gossip.

5. Romance – I don’t care how interesting or attractive a co-worker is, you must keep your distance on the first day. Be polite and warm, but avoid any appearance of flirting or romantic attraction to anyone and everyone. While office romances are a bad idea in general, you definitely don’t want to be doing anything that looks like an office romance on our first day. You want the first-day reputation as that smart new person, not the jerk who kept flirting with Pat in accounting.

6. Complaints – There will be issues the first day. You might be given assignments that seem too simple for your level of training or experience. You might have trouble getting your lunch break or finding the bathrooms. Keep everything negative to yourself. Don’t be seen as someone who complains about anything. Anything. You are the person that solves problems and never complains about anything. You will gain the immediate respect of your coworkers and supervisors. Later, after they get to know you little, you can start complaining and standing your ground.

7. Social Invitations – Your co-workers may ask you to go to lunch with them or ask if you want to stop by the local bar for a drink after work. If you say no you might be branded the person who doesn’t want to spend time with your co-workers. Do you think you are too good to spend time with them? Try to accept a reasonable invitation (safety first), but also control yourself. Even if you are buying your own lunch, keep the order to a reasonable quantity. If your boss or co-worker is paying, order something simple and inexpensive. Even if everyone else is drinking alcohol, you need to stick to soft drinks or water. You want to be seen as that great new employee, not that new drunk.

8. Comparisons – You might be willing to make comparisons to how you used to do things at you last job or what you were told in a college class. Unless you are asked, you keep that information to yourself. Things are done a certain way at your new job, and you just keep your mouth shut. You might have some great ideas on ways to do things better, faster, or easier but you just need to make good notes and keep your mouth shut. No one wants to hear the new guy tell them all the things they are doing the wrong way. All your ideas can come out after a day or two of gainful employment.

9. Excitement – You are going to be a little confused and lost your first day, but don’t forget this job is what you wanted. This should be a happy day, full of wonder and excitement. Don’t be afraid to let people know you are glad you took the job offer, how you are happy to be there, and how you are excited to begin this next step in your career. This will remind them that you are willing to learn anything they want to show you, you are capable of being a valuable member of the team, and that you have a contagious level of excitement. You co-workers may have forgotten why working for this great company is so great, and you are there to remind them how lucky they are for working there.

10. Thank You – Training a new employee takes a lot of time and effort. These people are taking time out of their normal assignments to train you on the mundane tasks that make up their daily activities. Even if you already know how to do the task or you only spent two minutes talking to them, always make sure they understand you appreciate their time and always say “Thank You”.

These behaviors are just recommendations for your first few days. These behaviors are not sound advice for normal corporate life, but are just applicable for your first day or two. Once you get to know everyone and understand what they expect from you, you can open up a little and be a little more casual.

TIOBE Index for November 2021

Programming - @SeniorDBA

Have you seen the latest TIOBE rankings report?

The TIOBE Programming Community index is an indicator of the popularity of programming languages. The index is updated once a month. The ratings are based on the number of skilled engineers world-wide, courses and third-party vendors. Popular search engines such as Google, Bing, Yahoo!, Wikipedia, Amazon, YouTube and Baidu are used to calculate the ratings. Observe that the TIOBE index is not about the best programming language or the language in which most lines of code have been written.

Since the start of the TIOBE index, more than 20 years ago, PHP has been a permanent top 10 player. Recently, we saw PHP struggling to stay in that top 10. PHP was once the master of web programming, but now it is facing a lot of competition in this field. This is not to say that PHP is dead. There are still a lot of small and medium enterprises relying on PHP. So I expect PHP to decline further but in a very slow pace. Two of PHP’s competitors, Ruby and Groovy, gain both 3 positions this month. Ruby from #16 to #13 and Groovy from #15 to #12. Other interesting moves this month are Lua (from #32 to #26), Dart (from #40 to #31), and Kotlin (from #38 to #33). — Paul Jansen CEO TIOBE Software

Continue reading “TIOBE Index for November 2021”

Free Download: SQL Server Management Studio 18.10


SQL Server

SQL Server Management Studio (SSMS) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SSMS combines a broad group of graphical tools with a number of rich script editors to provide developers and administrators of all skill levels access to SQL Server.

The SSMS 18.x installation doesn’t upgrade or replace SSMS versions 17.x or earlier. SSMS 18.x installs side by side with previous versions so both versions are available for use. If you have a previous GA version of SSMS 18 installed, installing SSMS 18.10 upgrades it to 18.10.

If a computer contains side-by-side installations of SSMS, verify you start the correct version for your specific needs. The latest version is labeled Microsoft SQL Server Management Studio 18.

Beginning with SQL Server Management Studio (SSMS) 18.7, Azure Data Studio is automatically installed alongside SSMS. Users of SQL Server Management Studio are now able to benefit from the innovations and features in Azure Data Studio.

Continue reading “Free Download: SQL Server Management Studio 18.10”

Top 10 Database Administrator (DBA) Mistakes

Everyone makes mistakes, but a Database Administrator (DBA) shouldn’t make these common mistakes more than once. Let’s investigate some common areas of improvement to see if you can be better at your job.

DBA Mistakes - @SeniorDBA

  1. Memory Management – You can run into serious trouble if you are not managing your MIN and MAX database settings. You might also have different configurations for different versions of SQL Server.
  2. Poor Database Disk Usage – You should not place your database files in the default location and hope for the best performance. Investigate your drive configurations and performance and relocate the files to drive locations that provide the best performance while reducing contention, hot spots, and throughput issues. Make sure the drives are formatted and configured properly, and the user databases, TempDB files, and all log files are on separate drives.
  3. Auto Shrink – If you have your database files set to Auto Shrink Enabled, you are not properly managing your database. This setting causes disk fragmentation and unwanted overhead that should be avoided in any production environment.
  4. Bulk Imports – Do not perform bulk inserts during normal production hours. If you have to import large amounts of data and you don’t have an available maintenance window, then determine the lowest usage times and perform the import during those times, if possible.
  5. No Backup Strategy – As a Database Administrator you are responsible for the data, yet many times you rely on someone else to make sure backups are properly scheduled and complete successfully. This is a mistake that will haunt you the first time you have a disaster and are asked to restore the databases but you find backups weren’t scheduled as you requested. Always verify the backup jobs are running and that they complete successfully. Practice your database restores at least one every calendar year.
  6. No Server Monitoring – You should measure database and server performance, even if everything is working great. If you are taking periodic measurements, you should be able to detect issues long before users start complaining about degraded performance. Don’t wait for users to start complaining before you look for issues.
  7. No Performance Tuning – At least 10% of your time should be spent looking at database and server performance, looking for ways to improve performance using existing hardware and software. If you can’t improve performance with improvements to stored procedures, moving of log and data files, or improved indexing then you should be looking at faster hard drives, more server memory, or faster CPUs.
  8. No Automation – This is a really simple idea. If you are doing anything manually more than once, you should be asking yourself if it can be automated. Spend some of your time looking at facts, analyzing data, improving services. Do not spend your limited time repeatedly figuring out how to gather facts, where is the data located, how to query performance statistics, etc. Use automation to improve your productivity. Look at scripts, scheduled jobs, etc.
  9. Using Poor SQL Code – Allowing developers to run poorly formatted or poorly designed Transact-SQL code to run on your production servers will cause serious performance issues. If you don’t have the resources to adequately check code before it goes live in production, at least monitor the performance on the production servers and provide immediate feedback to the development group.
  10. No Security – You should limit access to production data to just the specific users that require access. Each user should have their own account, and they should not be logging into the data with one single shared account for all users. If everyone uses the same account and it has elevated privileges – you’re asking for trouble and it is just a matter of days before disaster strikes.
  11. Bonus: Lack of Business Awareness – This bonus mistake is more career oriented than just focusing on the technical systems. You have to be aware of what is important to the business and understand when things change. This will help you focus on the critical processes and be more proactive in targeting those systems that are critical to the business.

Do you have additional mistakes that you think should be added to this short list?

Cycling SQL Server Logs

In the not too distant past, Windows servers were rebooted much too frequently for a truly stable environment, but we got used to the practice. With more recent versions of Windows, reboots have become less frequent and it isn’t uncommon to see a server that hasn’t been rebooted in more than 90 days. The issue is that SQL Server is using the same log file during that time, with thousands or even millions of rows that take forever to read and review.

Log Cycling Frequency

For most servers or workloads, you will typically want to keep only about one weeks of data per log. You might prefer to create a weekly job that cycles the SQL Server event log (i.e., terminates the current log and create a new one its place). This way, instead of having a single log in the screenshot below that stretches over the past 90 days, you’d end up having a new log for each week:

CyclingLogs1

View the SQL Server error log by using SQL Server Management Studio or any text editor. By default, the error log is located at Program FilesMicrosoft SQL ServerMSSQL.nMSSQLLOGERRORLOG and ERRORLOG.n files.

On some servers or with some workloads, however, cycling the logs on a nightly/monthly basis might make more sense. While there is no single ‘best’ approach to determining when to cycle the logs—if you’re troubleshooting a production problem, looking to audit access, or trying to use the logs for any reason, having to wait while records are loaded can be a problem. If you’re connecting to the server remotely (a best practice for production servers) if there are too many rows to load you’ll commonly end up with the “SSMS is busy” dialog:

CyclingLogs2

Cycling SQL Server Logs

Once you’ve determined an interval at which to regularly cycle your logs, cycling them is actually simple. Just set up a SQL Server Agent Job that  runs weekly, and runs this script to cycle the error log:

-- Error Log:
USE master;
GO
EXEC master.sys.sp_cycle_errorlog;
GO

You can also use this technique to cycle the SQL Server Agent Log as well:

-- SQL Server Agent Error Log:
USE msdb;
GO
EXEC dbo.sp_cycle_agent_errorlog;
GO

If you already have some jobs running to clean up backup history or other maintenance data, just add these steps.

Error Log Retention

For security purposes it’s a best practice to retain fairly large number of error logs on hand. Malicious users trying to cover their tracks will attempt to cycle these same logs. If they’re able to execute sp_cycle_errorlog they could (effectively) cycle the log enough to potentially cover when they had gained access to your system or done something evil. By default, a SQL Server instance will keep 6 error logs on hand—so if you’re dealing with highly sensitive information or an environment where auditing is very important you might want to push this number up. You can also purchase a log retention third-party product to push to logs from the production system to a system that just retains logs. Otherwise, most typically prefer to keep about 10 logs on hand in most environments (where security/auditing are not critical concerns).

To specify the number of log files retained (i.e., other than the default) you can either edit the registry or just use SQL Server Management Studio to edit the registry for you. To use SSMS, just right click on the SQL Server Logs node on the instance in question, and click on Configure:

CyclingLogs3

Then, you can set whatever options or choices you’d like from the ensuing dialog:

CyclingLogs4

You can use the Script button to extract these actions out for review and to use the script on another server.

Understanding SQL Server Databases

dba

To manage a database server, you need to understand the types of databases available and the location of those databases. There are two types of databases available in SQL Server:

  1. System Databases
  2. User Databases

System Databases

The system databases are default databases that are created when SQL Server is installed. These databases are used for various operational and management activities for SQL Server, and you have no control over the contents of those databases when they are originally created.

Types of System Databases

There are four system databases in SQL Server:

  • master
  • msdb
  • model
  • tempdb

There is also a fairly recent addition to the group called the resource database. It is very similar to the standard system databases but you need to know that it is hidden from your traditional view through the SQL Server Management Studio (SSMS) GUI and you have read-only access to the data it contains.

Continue reading “Understanding SQL Server Databases”