One place for hosting & domains

      SQL

      A Primer on SQL Transactions


      What are SQL Transactions?

      A SQL transaction is a grouping of one or more SQL statements that interact with a database. A transaction in its entirety can commit to a database as a single logical unit or rollback (become undone) as a single logical unit. In SQL, transactions are essential for maintaining database integrity. They are used to preserve integrity when multiple related operations are executed concurrently, or when multiple users interact with a database concurrently.

      In this Guide

      This guide demonstrates:

      Why are SQL Transactions Necessary?

      A database application has to account for every possible failure scenario while writing and reading from a database. Without SQL transactions, application code that protects database integrity would be complex and expensive to develop and maintain. With SQL transactions, application code and database maintenance can be simplified.

      Understanding SQL Transactions with an Example

      Consider a financial brokerage that maintains a database for its clients. The brokerage is required to generate a quarterly financial statement for each of its clients. The financial statement reports the statement balance, current values of holdings, and any transactions that occurred during the quarter. To generate the statement, you may need to process the following steps:

      1. Loop through each client’s transaction history account to ascertain and calculate each of the transactions that occurred during the quarter (both purchases and sales).
      2. Calculate each client portfolio’s total return for the quarter, along with year-to-date returns.
      3. Calculate each client’s taxable and non-taxable returns for the quarter.
      4. Insert a record into a statements table to record the prior three steps.
      5. Update the current portfolio holdings’ values and investment totals for the quarter in a quarterly_values table.
      6. Update the statement balance in an accounts table.

      Many read and write operations are required for the above steps. There are a number of scenarios where data integrity could be violated, including:

      • A transaction that falls within a reported quarter is backed out or changed after calculations have already been made

      • One of the updates noted above fails after we have already inserted a record into the statements table

      • The total statement balance cannot be updated

      Without SQL transactions, application code would need to account for every possible permutation of errors that could occur in the database. With SQL transactions, all of the above statements can be contained in a transaction block that either succeeds or fails.

      SQL Transaction Delimiter Syntax

      Note

      The implementation of transactions is very similar in concept across most database implementations, but the syntax can vary slightly.

      Typically, the beginning of a transaction in a SQL Server command line is defined using the BEGIN TRANSACTION statement:

      BEGIN TRANSACTION NameOfTransaction;
      

      In MySQL, the syntax is slightly different, but has the same meaning:

      START TRANSACTION;
      

      Commit Transaction Syntax

      If a database application determines that all of the changes for a transaction have succeeded, the application can use the COMMIT TRANSACTION statement. This commits those changes to the database, and it is placed at the end of a block of statements. In SQL Server, the following command is used to commit the transaction:

      COMMIT TRANSACTION;
      

      Alternatively, you can also use the below command. The following command can also be used in MySQL.

      COMMIT;
      

      Roll Back Transaction Syntax

      If a database application determines that a change in a transaction has failed, the application can use the ROLLBACK statement. This statement can effectively de-commit any statements that have already been executed since the beginning of the transaction. In SQL Server and MySQL, the following command is used to roll back a transaction:

      ROLLBACK;
      

      Database Transaction Examples

      To demonstrate the mechanism behind transaction processing, consider the example of a simple database named School. One of the tables in the database is named Course and is defined as follows:

      The Course table is created using the below SQL command:

      CREATE TABLE Course(
        CourseId   SMALLINT    NOT NULL;
        CourseName VARCHAR(40) NOT NULL
      );
      

      The following examples demonstrate different ways to use SQL transactions from the command line.

      Example 1: Commit a Transaction

      The example below obtains the largest CourseId value from table Course and adds 1 to it. It then inserts a row into the Course table and commits the transaction. Before committing, if any part of the CourseAdd transaction fails to execute, then none of the transactions can be processed. That means if the Select or Insert statement fails in some capacity, the entire transaction is null and void.

      BEGIN TRANSACTION CourseAdd;
      
      DECLARE @CrsId SMALLINT;
      
      SELECT @CrsId = MAX(CourseId) + 1
      FROM Course;
      
      INSERT Course VALUES (@CrsId, 'Biology 101');
      
      COMMIT TRANSACTION;
      

      Example 2: Roll Back a Transaction

      In the example below, although rows are manually inserted into the Course table, all the Insert statements are wrapped into a transaction. That way, if a transaction fails to execute, you can roll back the entire transaction using the following MySQL syntax:

      START TRANSACTION;
      INSERT Course VALUES (1, 'Biology 101');
      INSERT Course VALUES (2, 'Computer Science 101');
      ROLLBACK;
      

      From the above MySQL syntax, you have ensured that the Insert statements have not committed (inserted) the data into the Course table until a Commit command is received. By issuing a Rollback statement, you have effectively undone the two prior Insert statements, and not committed either of these two rows to the database.

      Example 3: Combining Commit and Rollback in a Transaction

      The example below combines the ability to both commit and rollback transactions in the same transaction code block.

      BEGIN TRANSACTION InsertCourse;
      
      DECLARE @CrsId SMALLINT;
      
      SELECT @CrsId = MAX(CourseId) + 1
      FROM Course;
      
      INSERT Course VALUES (@CrsId, 'Biology 101');
      
      IF (SELECT COUNT(CourseName)
         FROM Course
         WHERE CourseName="Biology 101") > 1
         ROLLBACK;
      END IF;
      
      COMMIT TRANSACTION;
      

      The MySQL code above inserts a row in the Course table with the next highest CourseId. Before committing the transaction, the code checks if there are more than one rows where the CourseName is Biology 101. If true, the transaction is not committed to the database. At this point, the transaction rolls back and the code segment aborts from further processing. Otherwise, if the new row is the first instance of a CourseName of Biology 101, then the transaction proceeds and is committed to the database.

      More Benefits of Using Transactions

      When should you use transactions? Should you always use transactions?

      The simple answer is yes. This is especially true when you are dealing with multiple groups of statements. In a transaction, all of the statements in a sequence of statements must succeed for the associated data to be committed to the database. A failure of a component within the transaction necessitates a rollback.

      The use of transactions is also beneficial to protect against database failure conditions, including power failures, server crashes, disk drive failure, and database software crashes. In the event of one of these failures, if there are transactions that have not yet been committed, database integrity is maintained. Without transactions, any atomic statements that are applied to the database remain intact, regardless of whether associated statements have been executed. This may result in a data integrity issue.

      Conclusion

      SQL transaction logic is a fundamental mechanism for ensuring database integrity and minimizing error handling logic that is required in a multi-user database application. The use of transactions in SQL environments guarantees accuracy and completeness.



      Source link

      SQL Server Security Best Practices, Part 2


      This guide is the second in a series of articles that covers SQL Server security best practices.
      Part 1 of this series discussed a SQL Server installation’s physical security, operating system security, and application maintenance. Additionally, the previous guide outlined how to disable unnecessary features, enable encryption, and implement data masking.

      The second part of this series describes how and why you should:

      SQL Server Authentication

      Protection of data stored with SQL Server depends upon the ability to authenticate access to specific sets of data. SQL Server provides two options for database authentication in a Windows or Linux environment:

      You are prompted to select one of these SQL Server authentication modes during SQL Server setup.

      Note

      You can change the SQL Server authentication mode even after the initial installation decision has been made.

      Windows or Linux Authentication Mode

      In this mode, an installer logs into SQL Server using their Windows or Linux account. SQL Server validates the account name and password via the Windows or Linux operating system. SQL Server does not prompt for a password and does not perform the validation.

      Windows or Linux authentication uses Active Directory (AD) accounts. As a result, you can have centralized policy control for authentication. Policies can govern password strength and complexity, password expiration, account lockout, and active directory groups in the active directory.

      Windows or Linux-based authentication is the default authentication mode and is much more secure than
      SQL Server Authentication (discussed in the next section). Windows or Linux Authentication uses the Kerberos security protocol to support the above-mentioned security features. A connection made using Windows or Linux Authentication is sometimes called a trusted connection because SQL Server trusts the credentials provided by the underlying Windows or Linux operating system.

      SQL Server and Windows/Linux Authentication Mode (Mixed-Mode)

      When using SQL Server Authentication, logins are created in SQL Server and are not based on Windows or Linux user accounts. Both the username and the password are created
      by SQL Server and are stored within SQL Server. Users connecting using SQL Server Authentication must provide their credentials (username and password) every time that they connect to SQL Server.

      This mode does not use the Windows or Linux Kerberos security protocol, and it is considered to be inferior to
      Windows or Linux Authentication mode.

      System Administrator (SA) Account

      If you are using
      SQL Server (mixed-mode) authentication, SQL Server automatically creates a System Administrator (SA) user login with sysadmin privileges and permissions. To increase the security of your SQL Server, you should perform the following:

      1. Rename the SA login account to a different, more obscure, name.
      2. Disable the account entirely, if you do not plan on using it.
      3. For the SA (or renamed) account, select a complex password, consisting of lower/upper case letters, numbers, and punctuation symbols.
      4. Do not allow applications to use the SA (or equivalently renamed) account in any of the application connection strings.

      Note

      Any other user-based (lower-privileged) SQL Server accounts should also use complex, unique passwords.

      High-Privileged Operating System Accounts

      SQL Server uses a Windows or Linux account to run its services. Typically one should not assign high-privileged, built-in accounts (or equivalents) such as Network Service or Local System to the various SQL Server services. This can increase the risk of nefarious database/server activity, should someone be able to log into these types of accounts.

      Only assign the appropriate level of security-required accounts to SQL Server services. If not needed, any high-privileged operating system accounts on the server housing the SQL Server should be disabled as appropriate.

      Restrict SQL Traffic

      Database servers typically have one or more servers connecting to them. Access to these servers must be allowed only to and from designated IP addresses. Doing this can potentially prevent a nefarious user from accessing the server. In certain cases, a user of SQL Server may need to connect directly to the database. Restricting those SQL connections to the specific IP addresses (or at least IP class block or segment) that require it should be implemented.

      These IP restrictions can be managed with different solutions on different platforms:

      SQL Server Patches (Service Packs)

      Microsoft regularly releases SQL Server service packs and/or cumulative packs for fixing known issues, bugs, and security issues. It is highly advisable to apply SQL Server patching on production instances of SQL Server. However, before applying a security patch to production systems, it is advisable to apply these patches in a test environment. This is done to validate the changes in the patch and ensure that your database operates as expected under the patch.

      Backups

      When dealing with production instances of SQL Server, it is important to regularly backup the server’s databases. A database backup creates a copy of the operational state, architecture, and stored data of a database. Backups help guard against potential database failures. These failures can happen because of corruption, disk array failure, power outages, disasters, and other scenarios.

      Backups can also assist with non-failure scenarios where a rollback of your database to a particular date may be necessary. Full database backups (on a regularly scheduled basis) and incremental backups (on a daily or running time basis) should be performed and maintained.

      Securing your backups is critical, and database professionals sometimes do not consider all of the requirements for securing database backups. This work includes:

      • Restriction of access to backup files. Do not provide all people in your organization the access rights (create, view, modify, and delete) to backup files.

      • Encrypting backup files properly.

      • Storing backups in an off-site facility. Depending on the organization and the critical nature of the database data, backups of a certain age should be preserved and archived.

      Auditing

      Auditing is another key component of SQL Server security. A designated database administrator or database security team should regularly review SQL Server auditing logs for failed logins.

      SQL Server provides a default login audit mechanism for reviewing all of the login accounts. These audit facilities record incoming requests by username and client IP address. Login failures can assist in discovering and eliminating suspicious database activity. The following types of activity can show up in the SQL Server audit logs:

      • Extended Events: Extended Events is a lightweight performance monitoring system that enables users to collect data needed to monitor and troubleshoot problems in SQL Server.

      • SQL Trace: SQL Trace is SQL Server’s built-in utility that monitors and records SQL Server database activity. This utility can display server activity, create filters that focus on the actions of users, applications, or workstations, and can filter at the SQL command level.

      • Change Data Capture: Change Data Capture (CDC) uses a SQL Server agent to record insert, update, and delete activity that applies to a specific table.

      • Triggers: Application-based SQL Server Triggers can be written specifically to populate a user-defined audit table to store changes to existing records in specific tables.

      • SQL Server-Level Audit Specifications: A Server Audit Specification defines which Audit Action Groups can be audited for the entire server (or instance). Some audit action groups consist of server-level actions such as the creation of a table or modification of a server role. These are only applicable to the server itself.

      Hardware and/or software firewall logs (that is, external to SQL Server) should be regularly examined to monitor and detect any nefarious attempts at server penetration.

      Conclusion

      In part two of this article series, you reviewed additional methods of enhancing the security of SQL Server databases. These included choosing an
      authentication mode, restricting the
      System Administrator account, assignment of
      security-friendly accounts to SQL Server,
      restricting SQL traffic, application of
      patch updates,
      backup strategies, and use of
      auditing. To review earlier security recommendations, revisit
      Part 1: SQL Server Security Best Practices.



      Source link

      How to Learn SQL… Fast


      Whether you want to become a back end developer or simply familiarize yourself with databases, you’ll need to learn SQL. However, programming languages can seem a little daunting for beginners.

      Fortunately, you can start learning SQL even without coding experience. Watching YouTube tutorials and taking online courses are some of the best ways to master SQL.

      In this article, we’ll introduce you to SQL and the benefits of learning it. Then, we’ll show you how to learn this language as quickly as possible. Let’s get started!

      An Introduction to SQL

      SQL is an abbreviation for Structured Query Language. Put simply, it is a programming language that you can use to manage relational databases.

      In a relational database, structured data is organized into tables with rows and columns. All Relational Database Management Systems (RDMS) like MySQL, PostgreSQL, Oracle DB, and SQL Server use SQL to communicate with this data.

      In WordPress, your site data is stored in a MySQL database. Using SQL queries, you can store, alter, or delete data from this database.

      For example, you can use a MySQL query to manage your spam comments. This will inform WordPress to locate comments marked as spam and delete them:

      DELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam';

      If you want to view your customer transactions, you can query the purchased items or customer ID. This enables you to quickly access information about your online store:

      SELECT *
      FROM users
      JOIN orders
       ON orders.user_id = users.id
      WHERE state="Washington";

      There are different types of SQL commands that you can use:

      • DDL (Data Definition Language): this creates and modifies database objects.
      • DML (Data Manipulation Language): it creates, modifies, or deletes data.
      • DCL (Data Control Language): this controls access to data in the database.
      • DQL (Data Query Language): this performs queries to find information.

      However, SQL can only be used in relational databases. Since non-relational (NoSQL) databases do not store data in tables, they require different query languages.

      Get Content Delivered Straight to Your Inbox

      Subscribe to our blog and receive great content just like this delivered straight to your inbox.

      Why You Might Want to Learn SQL

      After you master the fundamentals of SQL, you can use it to extract relevant data from a large database. Whether you’re a data analyst, business owner, or website administrator, you can benefit from learning SQL.

      Although SQL was first introduced in the 1970s, it remains one of the most popular programming languages. In fact, SQL ranks the third most frequently used coding language behind JavaScript, HTML, and CSS.

      Plus, SQL is a standard language that many businesses use to manage their data. Even social media platforms like Facebook use SQL to store and analyze information about their users. Since SQL is so common, learning this language can give you new job opportunities.

      As a data scientist or analyst, you’ll have to extract, analyze, and interpret data. Without needing to download data into a spreadsheet, you can use SQL to directly access and analyze the information in a database.

      Here are some additional jobs that may require SQL:

      • Back end development: create, update, and delete data on the back end of a website.
      • Digital marketing: use SQL queries to find actionable insights about business processes.
      • Accounting: retrieve and analyze a business’s financial data.
      • Database administration: manage databases with SQL and database software.

      Even though SQL can seem complicated at first, it is an easy programming language to learn. Its syntax is made up of common English words, so you can quickly understand the purpose of each query or command.

      Chances are you deal with data on a daily basis. Using SQL, you can access and interpret information on your website or business. Then, you can perform data analysis to solve problems and understand your audience.

      How to Learn SQL (3 Methods)

      There are plenty of online resources that you can use to learn SQL. Let’s look at some effective ways to master this language.

      1. Find a YouTube Tutorial

      If you’re looking to learn SQL as fast as possible, YouTube is a great place to start. Rather than completing a lengthy course, you can find a tutorial that simplifies the language into its core concepts.

      For example, the SQL for Beginners Tutorial will teach you how to retrieve data from a database:

      Learn SQL for beginners course on YouTube

      In this 45-minute video, you’ll learn the basics of SQL, databases, and Relational Database Management Systems. The creator will walk you through installing Microsoft SQL Server and SQL Server Management Studio. Using these free tools, you can familiarize yourself with tables, primary keys, data types, and more.

      You can also find free courses that dive into more complex SQL topics. If you want to learn about queries, Learnit Training provides a 3-hour video called SQL Querying for Beginners:

      Learnit Training SQL Querying Tutorial

      This video starts by explaining some basic SQL terminology and its main uses. It then shows you how to perform an SQL query. By the end of the video, you’ll be able to use criteria conditions, retrieve data from multiple tables, and organize and export the query results.

      Before watching a video, we recommend checking its publishing date and view count. You’ll want to make sure its information is still relevant. You can also review its comments to see if other users found the tutorial helpful.

      2. Take a Free Course

      Some people prefer structured courses. Fortunately, there are many e-learning materials that focus on the fundamentals. By completing untimed modules, you can become an SQL developer at your own pace.

      One of the best platforms to learn coding skills is Codecademy. After you create an account, you can look for beginner courses such as Learn SQL. This course will teach you how to use SQL to communicate with relational databases:

      Codecademy Learn SQL course

      In each module, you’ll be able to enter commands, run queries, and use functions to perform database operations:

      Codecademy course module

      If you like educational videos, Khan Academy’s Intro to SQL course may be the right course for you. It teaches you the basics of making queries and modifying databases with instructional videos:

      Khan Academy SQL course

      During the course, you can watch code being written in real-time. You’ll also be able to copy it from the video:

      Khan Academy course module

      For a more in-depth explanation of SQL, you can complete the SQL Tutorial on W3Schools. This shows you how to write SQL statements with proper syntax, as well as more complex tasks like creating and altering a database:

      W3Schools SQL tutorial

      Once you learn about these processes, you can test your new knowledge with practice exercises:

      W3Schools SQL exercise

      As you can see, there’s no shortage of online courses you can take to learn SQL. All you need to do is find the one that suits your needs.

      3. Earn a Certification

      In most cases, you won’t need a college degree to work with SQL. However, you might want to showcase your coding skills on your resume. When looking for a position that requires knowledge of SQL, having a certification can increase your chances of getting hired.

      Fortunately, you can easily gain a SQL certification online. Coursera’s Introduction to Structured Query Language teaches you how to create a MySQL database. Once you complete the course, you’ll receive a certificate:

      Coursera SQL course

      Keep in mind that this is the second level in the Web Applications for Everybody Specialization. To get up to speed, you’ll have to first take the Building Web Applications in PHP course. If you’re already familiar with PHP, HTML, and CSS, feel free to sign up for the SQL lessons.

      Another way to get certified in SQL is to purchase a course on Udemy. In the Complete SQL Bootcamp, you’ll learn about analyzing data, creating tables and databases, and more:

      Udemy SQL course

      This Udemy course teaches you how to set up a PostgreSQL database management system. Since this applies to other SQL databases, it can be beneficial to include this certification on your resume.

      Start Managing Your Database with SQL

      If you own a WordPress website, your data will be stored in a MySQL database. When you learn the SQL query language, you can instruct WordPress on how to deal with this data. Plus, SQL skills can also help you get high-paying jobs in data science, marketing, and development.

      To review, here are some simple ways that you can start learning SQL:

      1. Find a YouTube tutorial from creators like Kevin Stratvert or Learnit Training.
      2. Take a free course on Codecademy, Khan Academy, or W3Schools.
      3. Earn a certification from Coursera or Udemy.

      Once you learn the SQL basics, you’ll likely want to create a new MySQL database. With a DreamHost shared hosting plan, you can build unlimited databases and enjoy faster queries!

      Power Your Website with DreamHost

      We make sure your website is fast, secure and always up so your visitors trust you. Plans start at $1.99/mo.

      shared hosting



      Source link