June | 2016 | SQL with Manoj

Using some best practices for SQL Server in Azure VM (IaaS) to get MAX performance

June 30, 2016 2 comments

I was setting up an environment on Azure Premium Storage (G-Series) server for my Project, and was not getting the performance I was expecting. The comparison was against an existing Database Server setup on an On-Premise VM.

Microsoft Azure Premium Storage delivers high-performance, low-latency disk support for VMs running I/O-intensive workloads. Uses SSD (Solid State Drives) to store your data and Caching capability to provides highest throughput and low latency. You can configure disk caching policy on the Premium Storage disks as ReadOnly, ReadWrite or None, based upon your workloads, and type of data processing.

The highest Premium Storage Disk type, P30 provides you Disk size of 1TB, with 5000 IOPS, which can provide throughput of 200 MBPS.

Here are some of the Best Practices [suggested by Microsoft] to ensure that your SQL Server instance is optimized upto its maximum on an Azure Virtual Machine:

1. Use Premium Storage. Standard storage is only recommended for dev/test.

2. Keep the storage account and SQL Server VM in the same region.

3. Format the Data disks, and use NTFS 64-KB allocation unit size for data and log files.

4. Stripe multiple Azure data disks to get MAX IOPS and throughput.

5. Avoid using operating system or temporary disks for database storage or logging.

6. Enable read caching on the disk(s) hosting the data files and TempDB.

7. Do not enable caching on disk(s) hosting the log file.

8. Setup SQL Server tempDB on D: Drive which is a SSD (Solid State Disk) in providing MAX throughput on handling heavier workloads.

9. Enable SQL Server database page compression.

10. Enable instant file initialization for data files, to reduce the time that is required for initial file allocation. Grant the SQL Server (MSSQLSERVER) service account with SE_MANAGE_VOLUME_NAME and add it to the Perform Volume Maintenance Tasks security policy. Database File Initialization.

11. Disable autoshrink and Limit autogrow on the database.

12. Move all databases and error log & trace file directories to data disks, including system databases.

13. Enable the Lock Pages in Memory Option (Windows level). This security setting determines which accounts can use a process to keep data in physical memory, which prevents the system from paging the data to virtual memory on disk. Exercising this privilege could significantly affect system performance by decreasing the amount of available random access memory (RAM).

14. Set MAX SQL Server memory to 64GB.

15. Run a full Index REBUILD (without REORGANISE) and UPDATE STATS with FULL SCAN option on all tables in the database.

16. And last and not the least, tune your Queries. Badly created Queries and Indexes won’t run on highly optimized systems 🙂

Categories: Microsoft Azure Tags: Azure Best Practices, Azure Premium Storage, Azure Virtual Machine, Azure VM, SQL Server IaaS

SQL Error – Windows could not start the SQL Server (MSSQLSERVER) on Local Computer (moved Master DB)

June 29, 2016 2 comments

I was trying to move my Master DBs to another folder by using steps mentioned in my one of [previous blog post]. But after all steps, when I tried to Start the services I got error with following popup:

Master DB move 01

Error Description:

Windows could not start the SQL Server (MSSQLSERVER) on Local Computer. For more information, review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 3417.

–> So, I checked the error log file and it showed the reason for error.
Default location: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\

…
2016-06-27 12:18:28.02 Server The service account is ‘NT Service\MSSQLSERVER’. This is an informational message; no user action is required.
2016-06-27 12:13:33.30 Server Registry startup parameters:
     -d E:\SystemDatabases\Master\master.mdf
     -e C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG
     -l E:\SystemDatabases\Master\mastlog.ldf
2016-06-27 12:13:33.30 Server Command Line Startup Parameters:
     -s “MSSQLSERVER”
…
2016-06-27 12:13:33.41 spid5s [INFO] HkHostDbCtxt::Initialize(): Database ID: [1] ‘master’. XTP Engine version is 0.0.
2016-06-27 12:18:28.12 spid5s Starting up database ‘master’.
2016-06-27 12:18:28.13 spid5s Error: 17204, Severity: 16, State: 1.
2016-06-27 12:18:28.13 spid5s FCB::Open failed: Could not open file E:\SystemDatabases\Master\master.mdf for file number 1. OS error: 5(Access is denied.).
2016-06-27 12:18:28.13 spid5s Error: 5120, Severity: 16, State: 101.
2016-06-27 12:18:28.13 spid5s Unable to open the physical file “E:\SystemDatabases\Master\master.mdf”. Operating system error 5: “5(Access is denied.)”.
2016-06-27 12:18:28.13 spid5s Error: 17204, Severity: 16, State: 1.
2016-06-27 12:18:28.13 spid5s FCB::Open failed: Could not open file E:\SystemDatabases\Master\mastlog.ldf for file number 2. OS error: 5(Access is denied.).
2016-06-27 12:18:28.13 spid5s Error: 5120, Severity: 16, State: 101.
2016-06-27 12:18:28.13 spid5s Unable to open the physical file “E:\SystemDatabases\Master\mastlog.ldf”. Operating system error 5: “5(Access is denied.)”.
2016-06-27 12:18:28.13 spid5s SQL Server shutdown has been initiated

–> The above error log (in bold) tells that the service account NT Service\MSSQLSERVER does not have access to the files moved to new location.

–> So, to provide access, go to the new folder location –> Right click and select Properties –> Go to Security tab, click on Edit –> click on Add, to add the Service Account. Apply the Service Account (here in my case NT Service\MSSQLSERVER) and change the Locations to your PC, as its a local Service Account:

Give “Full Control”, and click OK on popups.

Now Start the “SQL Server” services and this time it will work.

To check and confirm the new file location of Master DB, Execute following SQL query:

USE master
GO

SELECT *
FROM sys.database_files

Categories: DBA Stuff, SQL Errors Tags: Move Master DB

SQL DBA – Move master Database to another drive – in simple steps

June 28, 2016 4 comments

Well, there are times when you want to move your master database from the default location to some other drive. Now this activity cannot be done with the normal ALTER DATABASE statement with MODIFY FILE option. And you need a spacial handling for this case of master DB.

–> Let’s first check the location of master DB:

USE master
GO

SELECT * FROM sys.database_files

–> Now leave SSMS, and open SSCM i.e. SQL Server Configuration Manager. Here select “SQL Server Service”, and Rigth Click on the instance of SQL Server, and choose Properties. Now select the Startup Parameters tab.

Here you will see 3 line items:

1. -d is the path of the master data file.

2. -e is the path of the SQL error log file.

3. -l is the path of the master log file.

So, you need to update the 1st and 3rd ones. As I want to move my files to E:\SystemDatabases\Master\ location, so you just need to replace the existing path with following:

1. master data File:
-dC:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf
-dE:\SystemDatabases\Master\master.mdf

2. master Log File:
-lC:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
-lE:\SystemDatabases\Master\mastlog.ldf

–> Now Stop the SQL Server services, by going to: RUN –> services.msc

–> Manually Copy the master.mdf & mastlog.ldf files to the new location

–> Start the SQL Server services.

–> To confirm the new location, just execute following query and check the path:

USE master
GO

SELECT * FROM sys.database_files

Categories: DBA Stuff Tags: Move Master DB

SQL Error – The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE

June 22, 2016 11 comments

Today after setting up a new Database environment I ran into a small issue. While running a Stored Procedure I was getting following error:

Sql Severity : 16
Sql Message ID : 10314

Message
Executed as user: XYZdomain\XYZaccount. An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: System.IO.FileLoadException: Could not load file or assembly ‘XYZAssemblyName, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null’ or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A) System.IO.FileLoadException: at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection) at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) at System.Reflection.Assembly.Load(String assemblyString) [SQLSTATE 42000] (Error 10314). The step failed.

I checked online and found that after restoring the Databases on DEV from PROD environment, the Stored Procedure that I was executing on DEV was CLR enabled thus it has the external_access or unsafe permission set from the database. And the login that was use to create the database on PROD was not same as in the instance of DEV DB. Thus it was not allowing to execute the CLR SP and resulting into error due to safety concerns.

Thus you can either use the Windows login or the SQL Server login which is common on both the environments (PROD & DEV, in my case), so:

1. First Enable Trustworthy database property

2. And change the database owner to ‘sa’

ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON;
GO

USE [DatabaseName]
GO

EXEC sp_changedbowner 'sa'
GO

This issue could also occur when you are Creating a new Assembly that has the external_access or unsafe permission set in the same database.

Server: Msg 10327, Level 14, State 1, Line 1
CREATE ASSEMBLY for assembly ‘XYZAssemblyName’ failed because assembly ‘XYZAssemblyName’ is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.

Categories: Uncategorized Tags: CLR Stored Procedure