There is a lot of debate on this subject but the short answer to this question is 'whichever database your IT department already uses.' Modern relational database all have the same basic functionality, but slightly different ways of doing things. If your IT department already supports Microsoft SQL Server (MSSQL), then they already have the tools and knowledge to manage those databases. Because they are so similar (and Ignition takes care of so much for you), it is almost always easier to learn to use an existing database than to add IT support for a second type. Not to mention that adding a second type might mean hiring new personnel in the IT department.
If your company does not already have a database preference, then it's up to you to decide which is best based on your needs. They all have a free version, but different limitations. For example: MSSQL has a cap on how much data can be stored in their free version. MySQL does not have a cap like this, but also does not allow phone support for their free version.
You can install your database anywhere that Ignition has access to through the network. There are two main options for installing your database: one is on the computer that Ignition is installed on, and the other is installed on a different server in your network. Technically, there is a third option to connect Ignition to a database that is in a remote location using a VPN or some other way to access it. This third option will work, but because of latency and the data being physically very far away, it is not recommended for storing data that will be accessed often like Tag History.
For production systems, we recommend that your database is on its own server, not installed on the computer with Ignition. This is helpful for many reasons, but mostly because databases can potentially take up a lot of resources on a computer. If the database is on its own computer, you don't have to worry about other programs starving for memory or CPU. If you do this and install your database on another computer, just make sure to adjust your firewalls and pay attention to the database connection security. Most databases don't allow the default username to connect remotely.