Read: Best Online Courses to Learn SQL

What Is Microsoft SQL Server Management Studio?

As mentioned in the intro, SSMS is a tool that helps you manage any SQL database infrastructure. This integrated environment can be used to access, administer, manage, develop, and configure all components of the following:

• • SQL Server
  • SQL Server on Azure VM
  • Azure SQL Database
  • Azure Synapse Analytics
  • Azure SQL Managed Instance

Thanks to its graphical tools, multiple rich script editors, and other out-of-the-box features, SSMS provides an all-in-one solution for database administrators, data analysts, security specialists, and database developers of all skill levels who need to access SQL Server.

Use SSMS to write complex SQL queries with ease and quickness, or use it to create databases and their elements. Whatever tasks you need to complete related to SQL Server, SSMS has you covered.

Microsoft SQL Server Management Studio Features

While Microsoft SQL Server Management Studio is not the only professional database management tool on the market of its kind, many find it to be in a class of its own due to its user-friendliness and the following features:

Object Explorer

Although Microsoft SQL Server Management Studio has many features, the Object Explorer may be the one you find yourself using the most, as it lets you view and manage all objects in one or more SQL Server instances.

Template Explorer

You can quickly create standard objects like databases, stored procedures, indexes, tables, functions, etc., and extended properties like logins, users, roles, and linked servers via templates. SQL Server Management Studio has tons of templates to achieve these goals, and the software also gives database administrators and developers the option to create custom code templates to fit your specific needs. By creating custom templates for your frequently-performed tasks via SSMS, you can save time for more important purposes.

Like Object Explorer, the Template Explorer’s user interface displays server objects in a hierarchical view. With this tool, database admins can run custom reports, view object properties (and edit them), search for specific objects, and more.

Activity Monitor

All of your current SQL Server processes and data can be found in the Activity Monitor. Use it to check connected users, see which queries are consuming resources, track server activities, view data input/output, and more.

Read: Best Certifications for Database Administrators

XEvent Profiler

Database administrators and developers can get an instant glimpse into their SQL Server’s diagnostic events with the XEvent Profiler. The tool lets you avoid performance degradation when debugging SQL Server events since it is less intrusive, and it even gives you the option to customize the live streaming view to your liking.

Query Editor

The SSMS Query Editor with IntelliSense support lets database programmers tackle essential tasks like writing, executing, and debugging code. Thanks to the IntelliSense technology that auto-completes code, you can save time writing code and enjoy higher accuracy. Specific features available with the query editor include drag-and-drop text, code formatting, bookmarks, selected code executing, and much more.

Database Designer

As you can tell from its name, this SSMS database tool helps you design and visualize databases and their components. The Database Designer allows you to tweak tables, columns, indexes, keys, constraints, and relationships when designing a database, plus offers the option to create a diagram to visualize a database by illustrating its tables, columns, relationships, keys, etc. There is no limit to how many database diagrams you can create for a database either, as it is entirely up to you.

Table Designer

With the table designer, database administrators get a visual tool to design and visualize database tables. When using it, you will have the power to create, edit, or delete the following:

• • • • Tables
      • Columns
      • Keys
      • Indexes
      • Constraints
      • Relationships

By performing these tasks visually, you can avoid having to type SQL code manually.

Read: Using the Command Line to Write SQL Server Data

Query And View Designer

Developing queries and views has never been easier. The Query and View Designer tool in SQL Server Management Studio eliminates the need to write code manually, as database programmers can create database queries using nothing more than your mouse. Another plus of this designer tool is that it lets you select a specific SQL query and reproduce it in the editor.

How Much Does Microsoft SQL Server Management Studio Cost?

There is not much to discuss when it comes to Microsoft SQL Server Management Studio’s pricing, as it is free to use. This is another reason why the software is so popular amongst database experts who recommend it as the top tool for managing any database SQL infrastructure.

How To Download Microsoft SQL Server Management Studio

If you feel that SSMS has all the features you need, you can get the software by visiting Microsoft’s official download page. There you will find instructions on how to download the latest version of SSMS, what the system requirements are, details on what is new in the latest release, and more. You can also find directions on how to download previous versions of SSMS, if necessary.

To access the latest version of SSMS, you will need supported hardware that includes a 1.8GHz or faster x86 processor. It can be AMD or Intel. For ideal performance, a dual-core or better processor is recommended. You will also need at least 2GB of RAM, with 4GB recommended for optimal performance. To install the software, you will need 2-10GB of hard disk space. Lastly, your system will need to be running on one of the following supported operating systems:

• • • • Windows Server 2022 (64-bit)
      • Windows Server 2019 (64-bit)
      • Windows Server 2016 (64-bit)
      • Windows Server 2012 R2 (64-bit)
      • Windows Server 2012 (64-bit)
      • Windows Server 2008 R2 (64-bit)
      • Windows 11 (64-bit)
      • Windows 10 (64-bit) version 1607 (10.0.14393) or later
      • Windows 8.1 (64-bit)

How To Install Microsoft SQL Server Management Studio

To install SSMS, do the following:

• Visit the official Microsoft download page.
• Download the setup file.
• Execute the setup file and begin the installation.
• Choose where you want to install the software by either picking the default location or clicking Change to switch to a different path.
• Click Install.
• Wait for the installation process to complete.

Once installation is finished, head to your Start menu to launch SQL Server Management Studio. The first time you launch SSMS, you will see a screen asking you to connect to Microsoft SQL Server. To do so, select the server type, server name, the type of authentication, and enter your username and password. You can select “Remember password” so you do not have to type in your credentials again. Lastly, click Connect to get started.

Read more database administration and database programming tutorials.

The post Microsoft SQL Server Management Studio Review appeared first on CodeGuru.

In most database-enabled scripting, unless something like Trusted Connections offered by SQL Express, or unix_socket authentication offered by MariaDB, is used, then it is required that the unencrypted credentials to access the database be embedded somewhere in the code that is accessible by that code. Some examples of this include, but are not limited to:

• Connection Strings or credentials in the code itself, or in a file that is read or included by such code.
• For web applications, a file that is read by the code, but is outside of the webroot directory that can be browsed publicly.

These concerns hold true no matter what scripting language is used. That being stated, the demonstrations below will include the username and password in the code directly. Properly securing these credentials, or using alternative means of authentication which do not require the use of unencrypted credentials, are beyond the scope of an introductory tutorial.

Read: Key Differences Between T-SQL and SQL

How to Install mysql.connector

Python uses a module named mysql.connector to enable communications to MySQL and MariaDB databases. This module can be added to Python via the pip3 command. However, the version of Ubuntu Server used for these articles does not have this utility installed by default. It will be necessary to install it. This can be done with the command:

$ sudo apt install python3-pip

As was the case with installing MariaDB, it may be necessary to use a different package manager depending on the Linux version being used.

Note: If multiple versions of Python are installed, such as both Python 2 and Python 3, then it is imperative to make sure that the correct pip command be used.

To install mysql.connector via pip3, invoke the command:

$ pip3 install mysql.connector

Something similar to the following output will appear upon successful completion of the mysql.connector module:

Figure 1 – Successful installation of mysql.connector

The code example below will test if it is possible to connect to the database. If the code fails, an unhandled exception will be thrown and the code will exit. Common causes of a connection failure include:

• Parse errors in the connection function.
• Typos in the credentials
• Failing to escape special characters in the credentials (e.g., \” instead of “)
• The MariaDB or MySQL Server rejects connections on localhost, but allows them on 127.0.0.1, or vice versa.
• Using valid credentials, but attempting to connect from a disallowed source, such as a remote server.

Exception handling is better presented in a more intermediate-level article. If the code is successful, there will be no notification that the database connection was made, and 15 randomly generated “bad” band names and some sample albums will be generated.

# bad-band-name-maker.py
import sys
import random
import mysql.connector

part1 = ["The", "Uncooked", "Appealing", "Larger than Life", "Drooping", "Unwell", "Atrocious", "Glossy", "Barrage", "Unlawful"]
part2 = ["Defeated", "Hi-Fi", "Extraterrestrial", "Adumbration", "Limpid", "Looptid", "Cromulent", "Unsettled", "Soot", "Twinkle"]
part3 = ["Brain", "Segment", "Audio", "Legitimate Business", "Mentality", "Sound", "Canticle", "Monsoon", "Preserves", "Hangout"]

part4 = ["Cougar", "Lion", "Lynx", "Ocelot", "Puma", "Jaguar", "Panther"]
part5 = ["Fodder", "Ersatz Goods", "Leftovers", "Infant Formula", "Mush", "Smoothie", "Milkshakes"]


def main(argv):
  # Connect to the RazorDemo database.
  conn = mysql.connector.connect(user='rd_user', password='myPW1234%', host='127.0.0.1', database='RazorDemo')
  # Generate 15 unique bad band names:
  previousNames = ""
  nameCount = 0
  while nameCount < 16:
    rand1 = random.randrange(0, 9)
    rand2 = random.randrange(0, 9)
    rand3 = random.randrange(0, 9)
    badName = part1[rand1] + ' ' + part2[rand2] + ' ' + part3[rand3]
    # A crude but effective way of ensuring uniqueness, although there is no unique constraint on the artist name in the 
    # database.
    # This prepends and appends bars to both the list of previously used names and the current name. If the current name is
    # new, it will not be in that string.
    if -1 == ("|" + previousNames + "|").find("|" + badName + "|"):   
      print ("Band name [" + str(1 + nameCount) + "] is [" + badName + "]")
      for y in range(1, 3):
        # There will be no check for duplicated album names. There are more than a few acts which have reused album 
        # names.
        rand4 = random.randrange(0, len(part4))
        rand5 = random.randrange(0, len(part5))
        albumName = part4[rand4] + " " + part5[rand5]
        print ("\tAlbum [" + albumName + "]")
      # Creates a bar-delimited list of previously used names.
      if "" == previousNames:
        previousNames = badName
      else:
        previousNames = previousNames + "|" + badName
      nameCount = 1 + nameCount  
  # Close the Connection
  conn.close()
  return 0

if __name__ == "__main__":
  main(sys.argv[1:])



Listing 2 - Testing database connectivity and basic functionality.

This code will give output similar to what is shown below:

Figure 2 – Output of basic connectivity application

Read: Using the Command Line to Write SQL Data

MariaDB Security Setup

All application development for SQL-oriented databases must take care to ensure that any SQL code that is generated within the application is properly secured. This often results in sacrificing efficiency for security. While this can be an annoying tradeoff, it contributes to a more secure application, and no developer wants to be in a position of having a compromised database (or worse) as a result of improperly secured application code.

Like any other SQL-oriented database connector, mysql.connector provides built-in security features, which must be used to properly sanitize anything that is executed by the database. The mysql.connector module provides a mechanism for creating parameterized statements, also referred to as prepared statements, for the purposes of securely executing SQL statements while ensuring that no unauthorized code is passed into the server. A parameterized statement is an SQL statement in a string that contains placeholders for input. The application code then replaces these placeholders with escaped versions of these strings, so that the statement can be executed safely.

While it is often tempting, especially as a beginning programmer, to simply create strings with SQL statements and pass these directly into the database, it is a very bad programming practice. If data is not sanitized, then it is possible for a malicious user to execute arbitrary SQL code against the application’s database, and if the MariaDB user account is not properly restricted, then other databases on the server may be compromised as well. This kind of attack on a database application is known as a SQL Injection Attack. More information on SQL Injection attacks can be read in ​​Python Database Programming with SQL Express for Beginners.

Note: never under any circumstances pass user-created inputs directly to a database. Always check the input to make sure it will not break database functionality or cause a security problem by way of a SQL Injection attack.

Properly INSERTing Data in MariaDB

Going back to the statements executed in the “Creating Tables” section of the previous part of this tutorial, it can be seen that any album record that is inserted will need to have the record identifier of its respective artist. This record ID isn’t just any random column in the table, it is the column identified by the primary key clause in the create table… statement. In the case of the Artists table, this is the rcdid column.

The mysql.connector module provides two mechanisms for executing parameterized SQL statements. The .execute() method allows for the execution of a single such statement, but the .executemany() method allows for multiple iterations of the same statement to be executed, albeit with different values for the parameters in the statement. The demonstration below will make use of both methods. The .execute() method will be used for the artist data because it enables the record identifiers for each artist to be recovered and then used to create the insert statement used for the albums. On the other hand, there is no need to recover the record identifiers for each of the albums, so the .executemany() method will work better for those.

The code below inserts each artist into the Artists table, retrieves the record ID of each newly created record, and then inserts that record ID into the insert statement for each album corresponding to the artist.

# bad-band-name-maker2.py
import sys
import random
import mysql.connector

part1 = ["The", "Uncooked", "Appealing", "Larger than Life", "Drooping", "Unwell", "Atrocious", "Glossy", "Barrage", "Unlawful"]
part2 = ["Defeated", "Hi-Fi", "Extraterrestrial", "Adumbration", "Limpid", "Looptid", "Cromulent", "Unsettled", "Soot", "Twinkle"]
part3 = ["Brain", "Segment", "Audio", "Legitimate Business", "Mentality", "Sound", "Canticle", "Monsoon", "Preserves", "Hangout"]

part4 = ["Cougar", "Lion", "Lynx", "Ocelot", "Puma", "Jaguar", "Panther"]
part5 = ["Fodder", "Ersatz Goods", "Leftovers", "Infant Formula", "Mush", "Smoothie", "Milkshakes"]


def main(argv):
  # Connect to the RazorDemo database.
  conn = mysql.connector.connect(user='rd_user', password='myPW1234%', host='127.0.0.1', database='RazorDemo')
  # Generate 15 unique bad band names:
  previousNames = ""
  nameCount = 0
  albumTuples = []
  cursor = conn.cursor()
  while nameCount < 16:
    rand1 = random.randrange(0, 9)
    rand2 = random.randrange(0, 9)
    rand3 = random.randrange(0, 9)
    badName = part1[rand1] + ' ' + part2[rand2] + ' ' + part3[rand3]
    # A crude but effective way of ensuring uniqueness, although there is no unique constraint on the artist name in the 
    # database.
    # This prepends and appends bars to both the list of previously used names and the current name. If the current name is
    # new, it will not be in that string.
    if -1 == ("|" + previousNames + "|").find("|" + badName + "|"):
      lastInsertID = -1
      print ("Band name [" + str(1 + nameCount) + "] is [" + badName + "]")
      sql1 = "insert into Artists (artist_name) values(%s)"
      values1 = [badName]
      cursor.execute(sql1, values1)
      conn.commit()
      lastInsertID = cursor.lastrowid
      for y in range(1, 3):
        # There will be no check for duplicated album names. There are more than a few acts which have reused album 
        # names. However, it is possible for the same "random" album name to be generated and used for multiple
        # artists.
        rand4 = random.randrange(0, len(part4))
        rand5 = random.randrange(0, len(part5))
        albumName = part4[rand4] + " " + part5[rand5]
        #print ("\tAlbum [" + albumName + "]")
        albumTuples.append((lastInsertID, albumName))
        # Since we do not care about the record IDs for rows in the Albums table, we can use .executemany()
      # Creates a bar-delimited list of previously used names.
      if "" == previousNames:
        previousNames = badName
      else:
        previousNames = previousNames + "|" + badName
      nameCount = 1 + nameCount  
  # Loop through the album tuples and insert these all at once.
  print (albumTuples)
  sql2 = "insert into Albums (artist_id, album_name) values(%s, %s)"
  cursor.executemany(sql2, albumTuples)
  conn.commit()
  cursor.close()
  # Close the Connection
  conn.close()
  return 0

if __name__ == "__main__":
  main(sys.argv[1:])

Listing 3 - Inserting randomly generated data

Before reviewing the output of this code, it is important to note a few practical problems that can arise in any coding for any SQL-oriented database application. Ideally, a good programming practice involving such code is to reduce, as much as possible, the number of interactions with the database, because each individual commit to the database incurs an overhead and that overhead can add up quickly if multiple nested SQL statements are executed in a loop. Using the .execute() method above to insert artist records into a database within a loop might be useful for an introductory demonstration, but in practical coding, it can significantly slow down application performance.

Using the .executemany() method with the album records, however, tells a different story. In this case, multiple tuples are passed into a single commit action. This incurs significantly less overhead than executing two insert statements within the for y… loop above. A tuple refers to a specific instance of data within a larger set of data. A tuple can have one or more individual data elements:

Figure 3 – The output of the above code, with tuples explained

If a significant number of statements need to be executed in a loop or similar construct, then consider using stored procedures or batches. However, these present their own coding challenges that are beyond the scope of an introductory MariaDB tutorial.

Note: table names in Linux versions of MariaDB are case-sensitive! This may be a shock to developers who are more familiar with SQL Server or SQL Express. But weirdly, column names are not case-sensitive. The best way to address this inconsistency is to assume that everything in MariaDB is case-sensitive.

The best way to evaluate the outcomes of this code is to look at the tables within the RazorDemo database in the mysql client directly:

Figure – Contents of Artists Table

Figure – Contents of Albums Table

Read: What Are Client Statistics in SQL Server?

SELECTing Data in MariaDB

The code below reads input from standard input and attempts to match it records in the database. Note the use of parameterized statements here for the purposes of preventing SQL Injection attacks:

# bad-band-name-maker3.py
import sys
import mysql.connector

def main(argv):
  searchValue = input("Enter something: ")
  # Cap the length at something reasonable. The first 20 characters.
  searchValue = searchValue[0:20]
  # Set the search value to lower case so we can perform case-insensitive matching:
  searchValue = searchValue.lower()

  # Connect to the RazorDemo database.
  conn = mysql.connector.connect(user='rd_user', password='myPW1234%', host='127.0.0.1', database='RazorDemo')
  
  sql1 = ("select a.artist_name, b.album_name from Artists a, Albums b where " +
    "b.artist_id=a.rcdid and ( lower(b.album_name) like (concat('%', %s, '%')) or " +   
    "lower(a.artist_name) like (concat('%', %s, '%')) ) order by a.artist_name, b.album_name;")

  values1 = [searchValue, searchValue]
  
  cursor = conn.cursor()
  cursor.execute(sql1, values1)
  
  rs1 = cursor.fetchall()
  oldAlbum = ""
  listings = ""
  for row in rs1:
    if oldAlbum != row[0]:
      listings = listings + row[0] + ":\r\n"
      oldAlbum = row[0]
    listings = listings + "\t" + row[1] + "\r\n"
  # Close the Connection
  conn.close()
  
  if "" == listings:
    print ("Nothing matched [" + searchValue + "]")
  else:
    print ("Matches for [" + searchValue + "]")
    print (listings)
  return 0

if __name__ == "__main__":
  main(sys.argv[1:])



Listing 4 - Querying the database

The query in the code above is slightly more complex as it matches on both the album or artist name, and it is important to note that MariaDB, while it still performs a fuzzy match on a string variable that contains a % sign, uses a different string concatenation operator than other databases.

The results below include an example of a crude SQL Injection attack attempt:

Figure 11 – Results of querying the data

Final Thoughts on Python and MariaDB

There is a common misconception out there that simply because a database server is zero-cost or open-source that it cannot deliver the kind of power and scalability needed for more robust software solutions. With regards to MariaDB this for the most part does not apply. It provides data storage for applications like MediaWiki, WordPress, and many other everyday software solutions and it can easily be integrated into any Python-driven application. This combination is well-suited for budget-oriented application developers for whom database servers like SQL Server or Oracle are prohibitively expensive. Hopefully, this article provided the reader with an easy introduction to getting started with both Python and MariaDB.

Read more database programming and database administration tutorials.

The post Python and MariaDB Database Programming: Part Two appeared first on CodeGuru.

The code examples presented in this tutorial use Python 3 running on a server named allium-unus (Latin for “another one”, at least if Google Translate got it right) running Ubuntu Server 22.04 LTS.

Read: Key Differences Between T-SQL and SQL

What is MariaDB/MySQL

MariaDB is a widely-used fork of the wildly popular free and open-source MySQL database. It uses its own variation of SQL. MariaDB uses both a username and password system, as well as a trusted user authentication system (known as unix_socket authentication) for access management. MariaDB runs on both Windows and Linux, as well as many other operating systems.

Python communicates with MariaDB using a module named mysql.connector. MariaDB and MySQL include a command-line tool that allows for management of the database; however, there are zero-cost graphical tools, such as HeidiSQL, which can also be used for database management.

How to Configure MariaDB for Python Development

Below, we will detail how to configure MariaDB for development environments on both the Linux and Windows platforms.

Configure MariaDB on Linux

The easiest way to install MariaDB in Linux is via Package Management. The preferred package manager for Ubuntu is the apt suite. However, MariaDB can be installed by whatever the preferred package manager is on any given Linux version:

$ sudo apt-get update && sudo apt-get upgrade                 # Update Linux and all installed software

$ sudo apt-get install mariadb-server                     # Install MariaDB Server

$ sudo systemctl start mariadb.service                    # Start the MariaDB Server

$ sudo systemctl enable mariadb.service                    # Enable on startup

$ sudo systemctl status mariadb.service                    # Gets the current status of the MariaDB Server

If the installation was successful, then the output of the last command above would look similar to this:

Figure 1 – Successful MariaDB Installation in Linux

Upon a successful installation, there are Linux-specific post-install configuration steps, which will need to occur. Note that for this demonstration, unix_socket authentication will not be used, because the programming examples will not work with Windows. This is not to say that unix_socket authentication is a bad thing, but rather, that the goal of this Python tutorial is to present a relatively platform-neutral introduction to MariaDB and Python. The security needs of a specific installation and use case are a different topic entirely.

The command to perform this post-install configuration is:

$ sudo mysql_secure_installation

The image below shows the answers to the security questions that will pertain to the coding demonstrations shown further below in this tutorial:

Figure 2 – Answering the MySQL/MariaDB initial security questions

Read: Using the Command Line to Write SQL Data

Configure MariaDB on Windows

The MariaDB Installation for Windows can be accessed via https://mariadb.com/kb/en/installing-mariadb-msi-packages-on-windows/. This site includes links to an installer, which will configure all of the options, but as it is Windows, unix_socket authentication will not be available. This installer also presents an option to install HeidiSQL, which will provide a graphical database management interface for MariaDB.

Please note that the links posted were active at the time this article was written. If the links are no longer active, this information can be accessed via one’s preferred search engine.

How to Create a Database in MariaDB

Now that MariaDB is installed and configured, the provided mysql client will allow for the creation of a basic database with appropriate access restrictions, as was the case with previous database programming tutorials in this series. You can read those in the links below:

• Python Database Programming with SQL Express for Beginners
• Getting Started With Razor Pages Using C# | CodeGuru.com

A database named RazorDemo, with tables for Artists and Albums/b> will be created in MariaDB, as it was in the above-mentioned tutorials. Additionally, a MariaDB user account will also be created so that the demonstration Python scripts can connect to this database and modify its contents.

The MariaDB root user is required in order to create a database and its associated user account and password. Once the database and its user account are created, the user account should be used to create the tables and any other database objects, such as views, stored procedures, and the like. To begin the creation process, it is necessary to use the mysql client, as shown below:

$ mysql --user=root --password

The –password parameter tells the mysql client to prompt for the password. Use the password entered in the initialization process above. The following commands can then be used to create the RazorDemo database and its associated user account. Note that these commands are executed within the mysql client after successful authentication. The > in the command listing below represents the prompt used by the mysql client. This may vary in appearance.

> create database RazorDemo;                   -- Creates the database
> create user 'rd_user'@'localhost' identified by 'myPW1234%';  -- Creates the user account with the password shown.
> use RazorDemo;                         -- Switches the client to use this database.
> grant all privileges on RazorDemo.* to 'rd_user'@'localhost'; -- Assigns all privileges for the database to the user account.
> flush privileges;                       -- Applies all security changes.

Note that the password as shown in the create user… command is the only instance in which the password will appear in clear text. The password is hashed and then stored internally. Its original value cannot be recovered and should it be forgotten, it will need to be reset.

The @’localhost’ syntax next to the create user… and grant all privileges… commands restricts the scope of the rd_user account to connections from localhost. This means that even if a remote user has the password for that account, no connections can be made to the database from any network location. Below is the typical output of the commands above:

Figure 3 – Creating the RazorDemo database and user account

Note: as is the case with any administrative or superuser account for any software application, the MariaDB root account should be used sparingly, and it should never be used in place of a proper regular database user account for either database management or from any database-enabled application.

The success of these commands can the be tested by connecting to the RazorDemo database directly using the rd_user/b> account:

Figure 4 – Connecting directly to the RazorDemo database

Read: What Are Client Statistics in SQL Server?

How to Create Tables in MariaDB

This time, the name RazorDemo is entered as the last parameter to the mysql command, telling the mysql client to automatically use the RazorDemo database. Upon successfully connecting, the tables can be created using the SQL code below:

create table Artists
(rcdid int not null auto_increment primary key,
artist_name varchar(255) not null default '');

create table Albums
(rcdid int not null auto_increment primary key,
artist_id int not null references Artists(rcdid) on delete cascade,
album_name varchar(255));

Listing 1 - MariaDB Table Creation SQL Code

The outputs of these commands will look similar to the image below:

Figure 5 – Successfully creating tables in MariaDB

Note: the –password parameter can also accept the actual password. However, there are two potential problems with this:

• Both Windows and Linux save their respective previously executed command histories, and the plaintext password will appear in these.
• Any characters in the password that are special to the operating system must be escaped.

As can be seen from the create table statements, the records in the Albums table must point to a record in the Artists table. The application code must respect this relationship as the database will be strictly enforcing it. MariaDB will reject any attempt to insert a record into the Albums table which does not correspond to a record in the Artists table. Furthermore, the on delete cascade clause in the artist_id column in the Albums table means that any deletion of an artist record will result in the automatic deletion of any Albums records associated with that artist. This is known as a cascading delete.

Final Thoughts on Python Programming with MySQL/MariaDB

Now that we have set up our test database, we can move on to the next part of this tutorial series, where we actually begin working with Python and MariaDB together. You can read that in our next tutorial, which you can find here: Database Programming with Python and MariaDB: Part Two.

The post Python Database Programming with MySQL/MariaDB for Beginners appeared first on CodeGuru.

In this database development tutorial, we will explain the step-by-step process used to create a dedicated SQL Pool in Microsoft’s Azure Synapse Analytics.

How to Create a Dedicated SQL Pool

To start, we want to create a dedicated SQL Pool in Azure. Log into the Azure Portal with your free or paid subscription. Then click on Create a Resource.

Next, in the Search Bar of Azure Portal, type Dedicated SQL Pool.

Choose Dedicated SQL Pool (Formerly SQL DW) and create a new Dedicated SQL Pool by clicking on the Create button.

Next, we need to either create a new Resource Group or select an existing one.

Enter the name of the new Resource Group and click Ok.

Now, fill in the basic details of the Dedicated SQL Pool.

Read: How to Migrate On-premise SQL Database to Azure

How to Create a New SQL Server in Azure

At this point, a new SQL Server needs to be created If you do not have a server setup already. If you do, you can skip this part. To create the new SQL Server, click on Create New and enter the Server Name and Location. If you already have an SQL Server, you can enter that information here.

Next, for now, you can simply use SQL Authentication. Enter in your Server Admin Login details and a secure password. Once finish, click OK.

Now, after you have entered the SQL Pool details with your SQL Pool Name and set the server, you also need to choose your performance level – choose wisely, as per your project needs.

Select the Performance Level and you will be prompted with a new Configuration option.

In the Performance Level screen, you can scale your system per your performance and storage scalability needs. Once set, click on Apply.

Next, click on the Networking tab. Select the appropriate options and then click on the Additional Settings tab.

Make sure you choose the Sample under the Use Existing Data section, which will create a sample database named AdventureWorksDW.

Then, click on Review + Create. Finally, click on Create to initiate the deployment process.

A notification will pop on the screen as the deployment is in progress; you can see the resources that will be created in real-time.

During deployment, you can also click on the Operation Details to learn about the operation which contains data from Operation ID, Timestamp, Duration, and more.

Once the deployment is complete, you will be provided with a Go to Resource link.

In the Resource Overview section, you can learn more about the Resource Sample Pool that was created. The server’s name, connection strings, and performance levels are all detailed here. Developers can also check out the metrics visualization of the DWU Usage that we just created.

Read more database programming and database administration tutorials.

The post Create a Dedicated SQL Pool in Azure appeared first on CodeGuru.

SQL is an ANSI/ISO standard language used for performing data operations on relational databases and relational database management systems (RDBMS). These international standards enable the user to be able to use similar queries across different database systems.

If you are new to database programming or database administration, you may have some confusion about how to choose the right language for learning how to develop database-driven apps. SQL and T-SQL (another popular database programming language) are often confused. To help clear this up, this database programming tutorial will discuss the subtle differences between SQL and T-SQL and shed some light on how to choose the right language for database development.

Read: Learn the Basics of Extracting Data in SQL

What is a Relational Database?

A relational database is used to work on databases whose tables are related to one another. In a relational database, each record is called a row, which we can identify through a unique key.

The ISO/ANSI standard has laid down some set of rules and conventions to work with relational databases. These rules are enclosed in a single language called Structured Query Language (SQL).

Some of the use cases for relational databases include use in e-commerce applications, inventory control systems, processing business transactions, holding data of entities such as customers, suppliers, and so forth.

Read: Best Relational Database Systems (RDBMS)

What is SQL?

SQL is a basic ANSI/ISO query language used for operating on data in a relational database. With SQL, not only you will be able to manipulate data in a relational database environment, but it also allows database programmers to join tables by using JOINS.

Learning SQL will build a solid foundation for programmers to learn other query languages because most of the non-standard extensions of SQL languages contain most of the features of SQL. For example, a LIKE clause works the same way in SQL and T-SQL.

Some common SQL-based languages include:

• DML (Data Manipulation Language) – Perform operations like insert, update and delete database records.
• DDL (Data Definition Language) – Perform operations like create, alter, truncate or drop table.
• DCL (Data Control Language) – Perform operations to control the database by granting and revoking the data.
• TCL (Transactional Control Language) – Perform operations like committing database or rolling back to a specific checkpoint.

By learning SQL, you will be able to work on most of the popular database systems.

Now that you know what SQL is, let’s get familiar with T-SQL.

What is T-SQL?

You may be wondering why we need T-SQL if we already have the standard-bearer SQL, which is used by the majority of relational database vendors. Is there any need to learn T-SQL if we already know SQL?

Even though most of the relational database vendors use standard SQL, there are many database systems that use a non-standard extension of SQL too. Microsoft SQL Server database system, for example, uses a specific implementation of SQL called Transactional SQL – or in short, T-SQL. T-SQL comprises all features and functions of SQL with add-on features including transactional control, exceptional handling, making use of variables, and record processing.

In some ways, you can say SQL is a subset of T-SQL. This means if you know T-SQL, you know SQL too. Note that, while working with the Microsoft SQL Server database system, the application interacting with the server uses T-SQL statements under the hood.

Example of SQL Server

Difference Between SQL and T-SQL

Below are the key differences between Standard SQL and T-SQL

• SQL is an open-source query language while T-SQL is owned and maintained by Microsoft
• SQL is used for DML and DDL commands. But in the case of T-SQL, you can use triggers, views, and functions which all are called T-SQL objects
• SQL is a data-oriented language while T-SQL is transactional oriented and with T-SQL you can add business logic to the application from the backend
• T-SQL comprises all features of SQL while the reverse is not possible
• T-SQL uses some keywords that are very slightly different than that of SQL. It also uses some commands or functions that are not present in standard SQL
• SQL follows a non-procedural approach while T-SQL follows a procedural approach. This means SQL statements are executed at one time while T-SQL statements are executed sequentially. The code is processed as blocks in T-SQL.

Below are some examples to help distinguish the differences between SQL and T-SQL.

To start, one of the differences between SQL and T-SQL is the TOP keyword. TOP is used to fetch a particular number of rows in a result set. TOP is used with the SELECT statement, along with the number of rows to return. The following code snippet illustrates this:

SELECT TOP 10 CustomerId, CustomerName, Address 
FROM Customers ORDER BY CustomerName;

You can find the above command in MS SQL Server only – it is not available in standard SQL.

Let’s take another case of the Substring function. The Substring function is used to cut a substring from a given string. The syntax of Substring is different in SQL and T-SQL as described below.

The function in standard SQL is defined as:

SUBSTRING(string FROM start [FOR length])

In MS SQL SERVER, the syntax is as follows:

SUBSTRING(string, start, length)

SQL versus T-SQL

A database programmer who has just started their journey in the database world may find it difficult to choose the language they should learn. If you want to learn a language that can give you the power to use the material that you have learned universally – and that can be used in almost every relational database management system – then you should go for SQL.

If, however, you already have a plan to work on a specific database system, such as Microsoft SQL Server, then learning T-SQL will be an asset.

Furthermore, you may also want to learn other languages. For example, PL/SQL, which is an Oracle extension to standard SQL, and PL/pgSQL, which is a PostgreSQL extension to SQL.

Final Thoughts on SQL and T-SQL

Deciding which database programming language you may want to learn becomes easier if you know exactly which database systems you are going to use. Most database beginners do not immediately understand the difference between SQL and T-SQL and might confuse them by treating them as entirely different languages. This database administration tutorial is written especially for beginner database programmers to help clear up the differences between the two options.

Read more database programming and database administration tutorials.

The post Key Differences Between SQL and T-SQL appeared first on CodeGuru.

If you missed the first part, you can read it here: Using the Command Line to Write SQL Server Data.

Using SQLCMD.EXE for SQL Code

At this point, we are ready to get data at the command line. To execute the code, we need to open a Windows Command Prompt and change to the folder where the full QUOTENAME code resides. If you do not know how to do this, follow the process below.

Finding the SQL Code File

To get this path, right-click on the filename heading in SSMS (the purple rectangle), and select Open Containing Folder (the red rectangle):

Finding the directory with the SQL code

A File Explorer window will appear. In this window, click in the address bar to convert the path to a format that will work with folder navigation in the Windows Command Prompt (for you history buffs, this involves what used to be DOS commands, and folders in Windows used to be called directories in DOS). In the example below, you would click where the red rectangle is, and the purple rectangle is the entry for the code file from SSMS:

The folder containing the code

Upon clicking the address bar, it will change into the path. Select the entire path (in the red rectangle) and right click on it to select Copy:

The actual full path

Copying the full path, after right-clicking

Read: Best Relational Database Software

Accessing the Command Prompt for SQL Code

With the full path copied, open a Windows Command Prompt by pressing the Start button and typing in the letters CMD, then pressing the Enter key.

Upon seeing the black Windows Command Prompt box, enter cd and a space and right-click the mouse. The full path should now be pasted. Press the Enter key. The Command Prompt window should look something like the following:

Command Prompt after switching to directory with code.

The folder you see will vary depending on where you saved the file. For this example, the filename of the code file is CSV Export Demo – select all employees quotename.sql.

The listing below contains the command line to SQLCMD.EXE, which will print the output to the Windows Command Prompt:

sqlcmd -S .\SQLEXPRESS -U exporter_readonly -P P@$$w0rd123 -s , -W -h -1 -i "CSV Export Demo - select all employees quotename.sql"

Before executing this, it is important to know what these parameters, or switches, do.

If you used characters in your password which need to be escaped in the Command Prompt window, you will need to do this before the command will work properly. For the purposes of this demonstration, no characters which needed escaping were used in the password.

Entering this command into the Command Prompt Window and pressing the Enter key gives us the following results:

Seeing the records in the command window

The above command uses the caret (^) convention to split the command between lines for better readability. The command could be a single line if you choose. If you see a line that says X records affected, it means you are missing set nocount on in your SQL Code. This may break CSV processing.

If you get an error indicating that the SQLCMD.EXE command cannot be found, then you will need to prefix the command with the full path to the file as determined at the top of this article. So instead of:

sqlcmd (rest of command)

Use:

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\sqlcmd.exe (rest of command)

Read: Learn the Basics of Extracting Data in SQL in Five Minutes

Saving SQL Data to a CSV File

Saving the data to a CSV file can be done by specifying the -o parameter and a filename:

sqlcmd -S .\SQLEXPRESS -U exporter_readonly -P P@$$w0rd123 -s , -W -h -1 -i "CSV Export Demo - select all employees quotename.sql" -o output-file.csv

Note that the listing above is intended to be a single line. The -o operator will overwrite any file specified if that file already exists. Redirect operators such as 1> or > will not work with SQLCMD.EXE

Note how the file output-file.csv appears in the File Explorer window now:

Newly created output file

Opening this file in a code editor like Notepad++ yields:

The final output

Conclusion to Command Line SQL Tutorial

This demonstration only scratches the surface of what SQLCMD.EXE can be used to do. As stated before, SQLCMD.EXE can execute any kind of SQL code, even code which writes to the database. There are times when this functionality can be more useful than using the SQL Server Agent depending on the business use case.

Beyond that, commands in the Windows Command prompt can be scripted by way of Windows Batch Files. Batch Files in Windows have a very rich programming syntax of their own, but they can provide very robust solutions in their own right. These Batch Files can then be scheduled via the Windows Task Manager so they can run unattended on a schedule. Any business process which requires data to be uploaded to an external source can make use of Batch Files which contain the call to SQLCMD.EXE and the creation of an output file, with an additional call to another software solution (e.g., WinSCP) to perform the upload.

Additionally, calls to SQLCMD.EXE can replace old SQL Server Integration Services (SSIS) Packages which may not be compatible with later versions of SQL Server. SQL Code files are also much easier to maintain as they do not require an IDE or a diagram to develop.

Read more database programming and database administration tutorials.

The post Using SQLCMD To Write SQL Server Data appeared first on CodeGuru.

Fortunately, Microsoft provides command line tools that allow for their output to be captured or written directly to files, all without making any security policy changes in SQL Server. The only “cost” of this feature is an SQL Server account with minimal read-only privileges for the database against which the SQL code is being run.

Now granted, there is always some hotshot who believes that changing the security settings in SQL Server to allow for the Special Stored Procedures that enable saving to files to run might be a good idea, and that would be the right way to perform such tasks, but that person won’t be laughing when an unintended side effect of those changes invites a remote compromise. And, realistically, we as database developers should work within the most restrictive security permission settings as possible, with the thought of altering those settings only being a last resort.

Read: Learn the Basics of Extracting Data in SQL in Five Minutes

Where Do I Find SQLCMD.EXE?

As most people who interact with SQL Server typically use the SQL Server Management Studio (SSMS), they may not be readily familiar with, or aware of the existence of, a command line utility. The command line utility in question is called SQLCMD.EXE and it is bundled with SQL Server when it is installed and it is usually available in your Windows System Path. However, it is best to confirm that this file does indeed exist on your system before proceeding. This can be done using the where command in the Windows Command Prompt:

Using the where command to confirm the location of SQLCMD.EXE

Even if SQLCMD.EXE is already in your system’s path, knowing the full path to the file can be useful if you wish to automate the execution of SQL code with its output being saved to a file. Say, for example, you need to create a list of authorized users every week and then upload that to a remote server that checks against that list. The scripting technology that you might use to execute that process may not have access to the same path, especially if it is run under a different Windows account than your own (such as the Network Service system account), so specifying the full path to point to SQLCMD.EXE would be the easiest way to make that script work.

Using SQL to Convert Data to CSV

SQLCMD.EXE will parse and execute any valid SQL code. It can even be used to run SQL code which writes data into, performs administrative tasks on, or does maintenance tasks for, a database if an SQL Server account with the correct privileges and credentials is provided. However, if the goal is to write data to a CSV file, then the output of the SELECT statement, which creates the output set, must itself be data which is easily converted into CSV data. SQLCMD.EXE can be configured to provide the comma delimiter for such files. Consider the following demonstration data set:

Demonstration Data Set

This, of course, is a very trivial SELECT statement. However, any T-SQL batch which outputs any record set could be outputted here. This could be the result of a very complex and tedious SQL batch and it would still apply. Of course, properly formatted CSV records have quotation marks around each value, and any quotation marks within the value are escaped as doubled quotation marks. The T-SQL command QUOTENAME solves this problem:

SQL Quotename

QUOTENAME escaped the quotation marks in the first entry and placed quotation marks around all the other text values. QUOTENAME has a maximum string length limit of 128 characters. Anything longer than this will need to be split up or truncated. Examples here are deliberately smaller than 128 characters.

Applying QUOTENAME to the first SELECT statement gets us the following:

SQL Quotename applied to entire table

Here is the SQL code:

# Very important, or else the rowcount will appear in SQLCMD.exe output.
set nocount on;

select 
	quotename(ltrim(rtrim(public_employee_id)), '"'),
	quotename(ltrim(rtrim(last_name)), '"'),
	quotename(ltrim(rtrim(middle_name)), '"'),
	quotename(ltrim(rtrim(first_name)), '"'),
	quotename(ltrim(rtrim(phone_number)), '"'),
	quotename(ltrim(rtrim(email)), '"')
from
	CSV_Export_Demo.dbo.employees;
                 

If you do not want to bother applying the quotename function to every column name, it is possible to query the information_schema.columns system view for a given table and dynamically build it, but for the purposes of this article it is not necessary, and practically speaking, unless you modify the export logic on a regular basis, that approach is overkill. At this point, make sure the SQL code is saved somewhere that is easy to access via the Windows Command Prompt.

Read: How to Find the Worst Performing T-SQL Code

How to Create a Read-Only SQL Login and User

Before we can write out any data, we need to be able to read it first. Under no circumstances should you use any SQL administrator account, such as sa, or your own Windows account should it have write access or administrator access at the command line. The main reason for this is not necessarily a security breach, but say the code file you load gets changed inadvertently to something destructive. There would be nothing stopping the execution of such code as an administrative user or as a user who has write access.

Treat any file you pass into SQLCMD.EXE like any other untrusted input in a traditional program, or like any SQL code you would build in any other program. Do not trust it by default and execute it using the least user privilege necessary.

SQL Server has two kinds of user access:

• Logins – which grant access to the SQL Server Instance itself.
• Users – which grant access to a particular database within a given SQL Server Instance.

In order for SQLCMD.EXE to access this data as a read-only user, a new login will need to be created if it does not exist already, and a new user will need to be mapped to that login and granted read-only access to the database. The T-SQL code below creates a user called exporter_readonly for this purpose:

use master;
–- Replace with a suitable password.
create login exporter_readonly with password='P@$$w0rd123';

-- Prevents the expiration of the password and prevents need to change password after first login.
-- This may not be the best option for your security preferences.
alter login exporter_readonly with check_policy=OFF;

-- Replace the database below with your database.
use CSV_Export_Demo;
create user exporter_readonly for login exporter_readonly;

exec sp_addrolemember 'db_datareader', 'exporter_readonly';
                      

T-SQL Code to create a new login and read-only user

Running this code combines all of these and will give the following output:

Read: Performing SQL Transactions with Visual Basic

Verifying User Access in SQL

Optionally, you may want to verify that the access for the exporter_readonly user is indeed properly set. To verify the Login, expand the highest-level security heading (the one under Databases) and expand the Logins heading below that. Right-click on the exporter_readonly user and click the Properties option. If you do not see exporter_readonly user, you may need to right-click on the Logins heading and click the Refresh option:

Finding the new login

Upon clicking the Properties option in the context menu, the following dialog appears. Ensure that the login name is exporter_readonly and that none of the checkboxes for Enforce password policy, Enforce password expiration or User must change password at next login are checked.

Note: These relaxed security settings are used because this is a tutorial demonstration. Once everything is working as expected, then these options can be set, but be prepared to follow through on making any password updates to any command line calls to SQLCMD.EXE with any new passwords.

New Login Properties

If everything is as it should be, click the Cancel button to make sure any changes aren’t inadvertently saved.

Conclusion to Part One of Command Line SQL

So far, we have learned how to find SQLCMD.EXE, convert data to a CSV file format, and create a read-only use for MSSQL Server. In the second part of this article, we will use SQLCMD.EXE to write and save data to an SQL Server database.

Read more database programming and database administration tutorials.

The post Using the Command Line to Write SQL Server Data appeared first on CodeGuru.

How to Create a Postgres Database in Azure Cloud

For the purpose of demonstrating PostgreSQL’s capabilities, we will create a Postgres server in Microsoft’s Azure Cloud. To begin, login to https://portal.azure.com and search for PostgreSQL. Select the Azure Database for PostgreSQL servers option, as shown here:

Azure Database for PostgreSQL Servers in Azure

Next, click Create Azure Database for PostgreSQL server:

Create New Database for PostgreSQL Servers

From the Select Azure database for PostgreSQL options, choose the Single Server option and click Create:

PostgreSQL Single Server Option

Next, select Basic from the Configuration page. By default, Compute Generation will be Gen 5. VCore 1 and 5 GB storage should be good enough. Keep Backup Retention options at their default setting:

PostgreSQL Basic Configuration Options

Next, we need to add a new Resource Group, as shown below:

PostgreSQL New Resource Group

Then, add a user and password under the Administrator account section:

PostgreSQL Administrative Account Details

Finally, review the Estimated Cost and click Create to create your new PostgreSQL server.

PostgreSQL Database: Review and Create

Read: Best Relational Database Software

C# Connect String to Connect to a PostgreSQL Database

Note the connection string in the code below, once the server instance is created. Connect the Azure PostgreSQL server from the Visual Studio SQL Server Object explorer and create the following employee database and table, using the C# code example here:

CREATE TABLE employee  
(  
employeeid   integer NOT NULL,  
employeename varchar(50),  
employeesalary numeric(6,2),  
CONSTRAINT emp_pkey PRIMARY KEY (empno)  
)  
INSERT INTO employee  (employeeid , employeename, employeesalary ) VALUES (1, 'Tapas', 3400.99);  
INSERT INTO employee  (employeeid , employeename, employeesalary) VALUES (2, 'Laltu', 2400.55);  
INSERT INTO employee  (employeeid , employeename, employeesalary) VALUES (3, 'Biltu', 1900.99);  
INSERT INTO employee  (employeeid , employeename, employeesalary) VALUES (4, 'Pintu', 1920.99);  

Creating a Console App in Visual Studio<//h3>

Open Visual Studio 2019 and Create a new project:

Visual Studio New Console Project

Select a Console App project, as depicted below:

New VS Console Project

Next, add the new Project Name and choose the Framework version. Click Create and a new console application will be created:

Console Project Name and Framework Version

A new project will be created. It should look similar to how ours looks in the Solution Explorer below:

New Console Project in VS Solution Explorer

The next step is to right-click on the project and select Manage NuGet Packages. Then, search for PostgreSQL and choose Npgsql. Install the Npqsql data provider as indicated below:

Npgsql Data Provider for PostgreSQL Installed

Finally, implement the following C# PostgreSQL connection code, written using the NpgsqlConnection in the Program.cs file of the console application, which will connect the Azure PostgreSQL database and import data from the employee table:

static void PostgreSQLConnection()
        {
            string strConnString = "Server=remote_server;Port=5432;User Id=user;Password=mypassword;Database=test";
            try
            {
                NpgsqlConnection objpostgraceConn = new NpgsqlConnection(strConnString);
                objpostgraceConn.Open();
                string strpostgracecommand = "select employeeid , employeename , employeesalary  from employee";
                NpgsqlDataAdapter objDataAdapter = new NpgsqlDataAdapter(strpostgracecommand, objpostgraceConn);
                objpostgraceConn.Close();
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message, "Error Occured", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

Read: Postgres Tricks in .NET

Conclusion to PsotgreSQL and C# Database Connection

We hope the above C# programming tutorial has illustrated how to access a PostgreSQL Azure database using C# and the Microsoft.NET framework. That’s all for today. Happy reading!

Read more database programming tutorials and product reviews.

The post Using PostgreSQL in C# and .NET appeared first on CodeGuru.

Most businesses are making the move towards one form of cloud service or another. If you have a new startup project, you may or may not have experienced by yourself the operating costs of having on-premises systems. These can include everything from building special server rooms to securing all kinds of warranties and safety environments.

If you are a veteran system admin, perhaps you have grown tired of concerns involving adjusting temperature and humidity, cyber and physical security threats, fire precautions, power issues, downtime, and connection issues – just to name a few of the issues that revolve around working with your own physical database servers.

Cloud Databases Definition

Cloud databases are databases that are similar to on-premise databases, but they are built and accessed “as-a-service” from a remote cloud computing platform, which can be private, public, or hybrid. Cloud databases can typically be divided into two main deployment models:

• Virtual machine-based independent databases: This option is more similar to on-premise, except it uses a cloud-based infrastructure. You can hire a DevOps or a traditional IT team to control and maintain the database. You can also migrate your existing database, but it is still your responsibility to oversee and manage the database.
• Full cloud database or database as a service (DBaaS): This option provides the user full maintenance of the database needs in real-time, such as scalability, management, security, and availability. It is based on the fees subscription contract. It usually includes automation, backup, scaling, and health monitoring.

Cloud Database vs On-premises Database

This comparison needs an article on its own. However, it is useful to cover some of the basic differences between cloud and on-premise databases and the main differences between both deployment models.

The main differences between cloud databases and on-premise databases include the following:

• Structure and design: The main difference with concerns to structure and design is the physical location of the database servers; on-premise database servers are located within your own company; cloud databases reside with the company deploying them and throughout their geographic zones.
  
• Network: On-premise databases use a local area network (LAN), while the cloud model requires a high-speed Internet connection.
  
• Performance: Sometimes cloud databases can be slower in response time, because it requires a round-trip with every interaction, while on-premise databases have an immediate response.
  
• Cost: In the cloud model, there is no need for any upfront costs for buying servers, just monthly subscription fees.
  
• Customization and control: The on-premise option allows for more customization and control. However, cloud options offer plenty of control and customization configurations that are enough for most needs and requirements.

The Cloud Databases Model

As is the case with the on-premise database model, the cloud databases model is also classified into relational and non-relational:

• Relational cloud database: Usually programmed in structured query language (SQL). They consist of organized linked fields of rows and columns. It is used for highly consistent needs, such as banking transactions since they rely on a specific database schema.
• Non-Relational cloud databases: Also called NoSQL databases, since they do not follow the typical table model associated with SQL databases. They save data as one document regardless of the original structure. They are more suitable for naturally unstructured information like written Internet content, audio, photos, and so forth.

Cloud Database Migration Advantages

Migration to cloud databases can bring you many benefits, regardless of your deployment model. You can try it by migrating your existing data, then by adding new data into the DBaaS.

The advantages of migrating to a cloud database include:

• Fill any IT and security knowledge or skillset gaps in your current team.
• Access databases from anywhere your team exists and at any time.
• Enhanced data processing quality when all apps and systems are in the cloud.
• Reducing costs by eliminating servers and paying only for what resources were used.
• Enabling better business application performance by taking advantage of SaaS deployment.
• Simplifying data management by combining every data source in one place.
• Lower investment barrier for startups and lower financial risk involved.
• Faster deployment than setting up and configuring an on-premise database solution.
• More reliable security.
• Increasing innovation and agility possibilities due to its ease of use and speed.
• Deliver your products or software to market faster.
• Reduction of energy costs; greener and environmentally friendly.

Cloud Databases Vendors

Major cloud computing providers are mainly also the dominant in the cloud database sector. Each one has its own features that differentiate it from others. Below is a list of some of the most well-known cloud database services and providers

• Amazon Web Service (AWS): Amazon is number one in the cloud computing field and the first one to enter this industry. It is also the leader in the market of DBaaS. Its service databases are Redshift, a data warehouse, and Data Pipeline.
• Oracle Database: Oracle was well known for servers and hosting before any other company, including Amazon. It offers enterprise-scale cloud databases with many technical facilities and solutions for data migration.
• Microsoft Azure Database: A cloud computing platform to create and deploy virtual machines and offers various software applications, alongside worldwide infrastructure and comprehensive solutions, security, and ecosystems.
• Google Cloud Platform: It has a no-nonsense solution approach that businesses of all sizes have adopted. Its documentation is comprehensive, which makes it easier for IT professionals to successfully deploy it. It also supports widespread open-source software compatibility.
• IBM DB2: IBM has an amazing relational database solution and migration process. It can provide advanced management, analytics, high performance, actionable insights, data availability and reliability, and other features for any transactional or warehousing workloads. All that is supported for Linux, Unix, and Windows platforms.
• MongoDB Atlas: One of the most well-known open-source NoSQL databases with unique features like powerful scaling, sharding, and automation capabilities. It also has strengths such as a strong support community, quick Installation, flexibility, and delivering models without the need of a database administrator.
• OpenStack Database: One of the important rivals for big players. It’s a database for both on-premise or cloud models. Users talk about its highly customizable and easy-to-understand (and implement) architecture. Moreover, for high-end scaling capabilities it is a great solution.

The post What Are Cloud Databases appeared first on CodeGuru.

Data vs Big Data

The goal here is not necessarily to define what data is in general, but what exactly is “Big” Data and why is it so different and important? Of course, as you may know, data is simply defined as items of information, facts, or statistics that are gathered on a set of values. It can consist of qualitative or quantitative variables. However, It was agreed upon (by and large) in the technology community that it includes any amount of small or reasonably big sets of data that any usual professional with common tools and computers can analyze.

Now, when it comes to the newly emerging amount of increasing data – such as data gleaned from the Internet, networks, written content on websites, search engine indexing, the global social media network, and so forth, we are in front of blobulous monster that can overwhelm not only personal capabilities but even any business IT server’s database infrastructure. The amount of data is so huge that it can not truly be measured except in terms of terabytes or petabytes.

Big Data, in fact, is a huge collection of very massive and extremely complex data sets and volumes produced from many sources. The phenomenon started with Google spiders and did not end in Twitter and Facebook personal users’ data. With the emergence of smartphones and the Internet of things (IoT), it is exploding more and more. It has become the pinnacle of what lovers of great records and giant archives dream of. Big Data is also like the superstitious blob monster referenced above in that, the more data you covet, the bigger and greedier it gets!

Big Data Challenges and Promises

Now that we have discussed what Big Data is, let’s discuss some of the challenges Big Data and data scientists face:

• Capturing
• Storing
• Accuracy
• Curating
• Analysis
• Searching
• Visualization
• Transferring
• Privacy securing

It is obvious that dealing with enormous sets of data can be impossible via traditional software such as Excel and even Access, not to mention SQL queries or other relational database management systems (RDBMS). Therefore, various scalable and cloud database tools and approaches have been established. They are continuously evolving to keep track of the avalanche that is big data.

Read: Best Database Software for Developers.

For instance, Hadoop as a big data processing software and batches of NoSQL databases were enough at first for big data releases, but now you need to at least run frameworks of an additional combination of technologies, such as Apache Spark.

You can ask, then, if Big Data is challenging to this degree, why should we take care of it? What are the benefits? Yes, that is also a great question. Let’s now see some of the big data benefits and promises:

• Used by companies systems to enhance operations and decision-making.
• Enhance provision of customer service based on more data feedback.
• Empower creating more targeting marketing campaigns to increase income.
• Gaining possible competitive points over other competitors in the market.
• Improving differentiation in marketing and being more conscious of customer needs.
• Increase health care organizations’ ability to identify the disease and medicines better.

Big Data Characteristics

Most references talk a lot about 3-6 “Vs characteristics of big data”. They were first introduced as “three Vs” by Gartner’s Doug Laney in 2001. But it seems also these Vs are stretching, from just Volume, Velocity, and Variety to also include Veracity, Value, and Variability. They could also technically reach up to ten Vs, to further include Validity, Vulnerability, Volatility, and Visualization. In extreme case, you may even hear of 17 Vs, but that is beyond the scope of this article.

For now, let’s focus on six of the Big Data Vs:

Volume: Big volume differentiates big data from usual data, it’s a critical character.
Velocity: The speed of data through the system, especially processed in real-time.
Variety: Diversity and uniqueness are some of big data nurture due to diverse sources.
Veracity: The accuracy of big data is a real challenge since its Magnitude and complexity.
Variability: The Variety of data makes diversity of its quality which needs filtration.
Value: This is the main goal and purpose of how to provide value from the data.

How Does Big Data Work?

Now you may be wondering, how exactly does Big Data work? There are a lot of approaches to take advantage of big data (such as the above mentioned), but today we will focus on just three main options:

• Integration: The usual data methods of extraction, transforming, and loading (ETL) can’t be done for Big Data. Instead, you need more powerful technologies to integrate data to be useful.
  
• Management: Between cloud and on-premises solutions, you can store and manage the data as suitable for your project needs.
  
• Analysis: This is known as “the picking fruits” phase of the whole process. Get smarter actions and decisions based on the outcome of petabytes of Big Data and explore future possible data sets.

The post Introduction to Big Data appeared first on CodeGuru.