Question

Best way to setup SQL Server

Hello everyone! I want to create a droplet Memory-Optimized and install SQL Server 2019 in the server, with this i want to add two volumenes for SQLData and SQLLogs. The question is: it is a good idea to add volumenes with data and logs separated with the server? My fear is i can lose read / write speed in the volumenes…

please help!


Submit an answer


This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Bobby Iliev
Site Moderator
Site Moderator badge
August 2, 2023

Hi there,

In addition to what has been mentioned already, I could suggest using Docker.

Microsoft offers an official image for SQL server which you can use to start a new instance with a single command.

You can start by installing Docker on your Droplet by following the steps here:

https://www.digitalocean.com/community/tutorials/how-to-install-and-use-docker-on-ubuntu-20-04

And then once you have Docker up and running, you can start the SQL server container with:

sudo docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong@Passw0rd>" \
   -p 1433:1433 --name sql1 --hostname sql1 \
   -d \
   mcr.microsoft.com/mssql/server:2022-latest

For more information you can follow the steps from the official documentation here.

Hope that this helps!

Best,

Bobby

KFSys
Site Moderator
Site Moderator badge
July 28, 2023

Heya @javiercalderon,

Creating separate volumes for SQLData and SQLLogs on a Memory-Optimized DigitalOcean droplet is generally a good idea for several reasons, but it’s essential to consider the potential impact on read/write speed.

Advantages of separate volumes for SQLData and SQLLogs:

Performance Isolation: By placing data and logs on separate volumes, you can avoid contention for disk I/O between the two. This means that if there is heavy logging activity, it won’t affect the performance of data read/write operations and vice versa.

Fault Isolation: If there is a failure or corruption in one volume (e.g., SQLLogs), it is less likely to affect the other volume (e.g., SQLData). This separation can help protect your data in case of any unexpected issues.

Scalability: If your application’s data and log volume usage patterns are vastly different, it’s easier to scale each volume independently based on its specific needs.

Now, about your concerns regarding read/write speed:

Separating data and logs onto different volumes can indeed introduce a slight overhead due to the extra I/O operations involved. However, the impact on read/write speed is usually minimal, especially if you are using fast SSDs provided by DigitalOcean. Modern SSDs have high I/O capabilities, and the performance difference might not be noticeable in most cases.

Also, consider that the benefits of having separate volumes often outweigh the minor performance trade-off. SQL Server can be highly I/O intensive, and ensuring better I/O distribution can improve overall system performance and stability.

To optimize the performance of your setup, here are some tips:

  1. Choose High-Performance Droplet: Select a droplet with sufficient CPU and RAM resources, along with SSD storage for better overall performance.

  2. Utilize Local SSDs: If available, use DigitalOcean’s local SSDs for the best performance. Local SSDs offer better I/O capabilities compared to network-attached storage.

  3. Monitor Performance: Regularly monitor your server’s performance to identify any bottlenecks and address them accordingly.

  4. Consider RAID Configuration: For added redundancy and performance, you can explore using RAID configurations with multiple disks.

Overall, the advantages of having separate volumes generally outweigh the minor performance trade-offs in most scenarios. It’s a common practice to separate data and logs, especially for critical applications like SQL Server. Just ensure that you have a well-optimized server with adequate resources to support your workload.

Lastly, what do you mean by an SQL server, do you mean an MySQL, Postgresql or an MSSQL server?

Have you taken a look at the Managed Database solutions as well:

https://www.digitalocean.com/pricing/managed-databases

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel