Setup SQL Server on Linux VM

### Troubleshooting References

#### connection Troubleshooting
https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-troubleshooting-guide?view=sql-server-linux-ver15#connection


#### Restore Database
https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-backup-and-restore-database?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/relational-databases/tutorial-sql-server-backup-and-restore-to-azure-blob-storage-service?view=sql-server-ver16&tabs=SSMS

wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2019.list)"
sudo apt-get update
sudo apt-get install -y mssql-server
sudo /opt/mssql/bin/mssql-conf setup
### Command line Tools
sudo apt install curl
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
sudo apt-get update
sudo apt-get install mssql-tools unixodbc-dev
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
sqlcmd -S localhost -U SA -P '<YourPassword>'
### Verification
$ systemctl status mssql-server --no-pager

### References

https://database.guide/how-to-install-sql-server-on-ubuntu-18-04/

https://learn.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu?view=sql-server-linux-ver15

Optimize for Ad-hoc workload

SELECT objtype, cacheobjtype,
    AVG(usecounts) AS Avg_UseCount,
    SUM(refcounts) AS AllRefObjects,
    SUM(CAST(size_in_bytes AS bigint))/1024/1024 AS SizeInMB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc' AND usecounts = 1
GROUP BY objtype, cacheobjtype;


SP_CONFIGURE 'Show Advanced Options', 1
GO
RECONFIGURE
GO
SP_CONFIGURE 'optimize for ad hoc workloads', 1
GO
RECONFIGURE
GO

select * from sys.configurations
where name like '%optimize for ad hoc workloads%'



select * from sys.master_files


USE master;
GO

ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = '/sqlserver/data/tempdb.mdf');
GO

ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = '/sqlserver/log/templog.ldf');
GO

ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev2, FILENAME = '/sqlserver/data/tempdb2.ndf');
GO