Do you want to create your own database server? Here we will provide details how to create an SQL database server. You can use it with your site or business application or both.
We have summarized the entire process in 10 steps. As a result in the end we hope you will be able to create a database server, using a Microsoft SQL Server on a Windows computer.
What is a database server?
The database server follows the traditional client-server model and provides a database application. The host will have all the resources to run the server, and the client software – to access it.
When talking about servers, there is often no clear separation between software and hardware. You can hear about a database server, a physical server or software on a device.
Why do you need a database server?
You need a database server to store and organize databases of information and provide access to users who have access authorization. You can have one of these servers for your company and run software that accesses the database from different devices. You can have a local server or use a different variation of a remote server.
Recently, a service called Database-as-a-Service (DbaaS) has emerged. You can get just software and use the databases directly, connecting them with your applications. You will lose complete control and the databases will be stored on a server that belongs to the provider, but for many people, it is convenient.
When talking about the software part, you can find a database server as “Database Management System” or “Database Server Software”.
Popular choices of server software are:
- Microsoft SQL – Number 3 in popularity right now, it is the best DB software that Microsoft can offer. You can use it locally or from the cloud. Microsoft has versions, not only for Windows but also for Linux. The only big problem is that it could be too expensive for enterprise use.
- IMB DB2 – A good database software that can work both with XML and JSON files. It can be installed on Windows, Linux-based, and Unix-based OSes. One of the key features it has is the so-called BLU Acceleration that boosts the DB performance. The problem here is that it is expensive.
- Oracle RDBMS. This one is ancient. First released in 1979, it was considered the first-ever commercially available SQL-based database. It has been updated a lot over the years. This is a really feature-rich software, currently being the number one database software when it comes to the number of users.
- MySQL. You all know the dolphin icon, right? MySQL is an extremely popular web-based DB. There is a free version, which makes it very attractive. It has a good set of features which is why you can find it working with most of the WordPress websites on the web. Yes, it falls behind some of the other competitors here, but free is free, and that makes it number 2 in the userbase now.
- SQLite – Free and open-source is always very welcome. It is a lighter version of SQL. The great thing about it is that the tables are very light and it will take a lot less space than some other DBs. Because of the size, it is often used for Android systems or iOS.
- AmazonRDS – We can’t skip the cloud behemoth Amazon. It offers instances that you can configure and start using in a short time. As you can imagine, it is a cloud service so you won’t be able to make any OS-level tweaking. Another disadvantage is that you will need to pay extra for a detailed log or other features.
- PostgresSQL – Another Free DB software that you can run locally, on a cloud, or in a virtual environment. What’s great about it is that you can import data from other DBs and it can handle loads of information – greater than terabytes!
- MongoDB – The list of free database software keeps going, right? MongoDB is a DB that works with structured and unstructured data. It is fast, but it has one big problem. It does not work with SQL natively and needs a translator for it.
- MariaDB – MariaDB has a free and a paid version. It has the benefit to work well with MySQL, commands and MySQL APIs. It offers encryption and many plugins to improve the feature set. It is fairly new, but it’s going in the right direction.
*The ranking comes from DB-Engines
Before starting the database server installation
A server. You will need to have a Dedicated server or a Cloud server. If you only need a database server and the database itself won’t be large, to lower the database server costs, you can try the minimum requirements of 1 GB RAM and 1 CPU. We strongly recommend at least 4 GB and multiple CPUs so you can use advanced features and have a better experience. Software. In our case, we will use Microsoft SQL Server 2019 Express. The SQL Server Express is a free version of the Microsoft SQL Server database management system. It has a good set of features and an easy-to-use graphical interface. We are following the installation process on Windows Server, but there are versions for Windows 10, Linux (Ubuntu, Suse, Red Hat, and more), and macOS. You will need to download the SQL Server Express from Microsoft’s site.
A step-by-step guide on how to install a database server
- Install SQL Server Express from the file you downloaded. The process is easy and straightforward.
- Start the SQL Server. The program will ask you exactly which server you would like to connect to. In our case, since we are starting from scratch and we want to create a database on our device we will create a local database and put the default database name to “.”. Leave the authentication type to Windows Authentication or the one you prefer.
- Create a new database. You will see the Object Explorer, and in it there is the database folder on the left. Click on the “+” symbol. There, you will need to press the right button of the mouse and select “New Database”. Then you will be able to configure it, but for the test, let’s leave it all to default and just add a name. When you click “OK” you will see two new files – Data and Log. Data will have all the database data, and the log will show the updates.
- Create a table. To have a database, you will need tables where the information is stored. Again, with the Right-click, you can get to the “New Table…”. After you have created it, Windows will open it, and you can edit or fill it with information.
- Create a Primary Key. Having a Primary key will serve as an ID, so you can easily manage your table. Choose the first column of the table and set the name to “ID”, choose “int” for the data type, and don’t allow null values. Click on the icon with a key symbol to make it the Primary Key for the table. You will also need to open the Column Properties and find “ls Identity” and set it to “Yes” to allow auto-numbering of the entries.
- Think about the columns that you need. The table that you have created has columns, rows, and fields. The columns are important items. In the case of a database of products, the columns can be “color”, “size”, “price”, etc.
- Create the other columns that you need. It all depends on your needs but make sure to use the right data types: – (* nchar(#) – for text, like names, phone numbers, addresses, and more. The “#” indicates the limit of symbols for the field; * Int – a whole number; * Decimal (x, y) – decimal numbers. The “x” is for the total number of digits of the number and “y” for digits after the “.”. For example, decimal (4, 1) is 0000.0.)
- Save the table. Find the familiar “save” icon and press it. Save it with the appropriate name that you can use easily.
- Fill in the table. If you can’t see the table, press “Refresh”. Go to the table, Right-click, and click on the “Edit Top 200 Rows”. The ID will be created automatically, the rest you can fill in manually. Add all the items you want.
- Execute the table and save it. By clicking the “exclamation mark” icon you will execute the SQL and it will save the table. If you have filled in the table properly, you are ready. If not, you will see an error that will indicate the problem.
After installing the SQL database server
Now you can start using it! Go and perform some data queries and see how it works. You can also expand the table or create more tables and relationships between them. You are ready!4
If you’d like to read another “How to” article, here it is: