Category: Sql Server

Installing Microsoft® Windows PowerShell Extensions for Microsoft SQL Server 2016 (AKA sqlps)

The PowerShell modules (aka sqlps) are installed by default when installing Microsoft SQL Server.

You can manually install the PowerShell Extensions for SQL Server 2016 by installing the following components from the Microsoft® SQL Server® 2016 Feature Pack:

  1. Microsoft® System CLR Types for Microsoft SQL Server® 2016 (SQLSysClrTypes.msi)
  2. Microsoft® SQL Server® 2016 Shared Management Objects (SharedManagementObjects.msi)
  3. Microsoft® Windows PowerShell Extensions for Microsoft SQL Server® 2016 (PowerShellTools.msi)

sqlps-install-list

Check if the module has been successfully installed:

# Import the SQL Server Module.
Import-Module Sqlps -DisableNameChecking

# To check whether the module is installed.
Get-Module -ListAvailable -Name Sqlps

Check-out the official complete guide SQL Server PowerShell.

Performance best practices for SQL Server in Azure Virtual Machines (link to resources)

Porting from SQLIO to DISKSPD: How to Test Disk Performance for SQL Server

DISKSPD TESTS

Original post related to SQLIO took from brentozar blog.

The latest update to DiskSpd can be downloaded from https://aka.ms/diskspd.

diskspd -w100 -t8 -d60 -o32 -r -b8K -Sh -L -c20G C:\test.dat
diskspd -w0 -t8 -d60 -o32 -r -b8K -Sh -L -c20G C:\test.dat
diskspd -w100 -t8 -d60 -o32 -si -b64K -Sh -L -c20G C:\test.dat
diskspd -w0 -t8 -d60 -o32 -si -b64K -Sh -L -c20G C:\test.dat

What Do the DISKSPD Parameters Mean?

While we’re looking at that set of commands, here’s a quick breakdown:

  • -w100 and -w0: means we’re testing writes (100% writes) or reads (0% writes).
  • -t8 and -o32: means 8 threads with up to 32 outstanding requests at once.
    DISKSPD isn’t CPU-bound at all, and you can use more threads than you have processors.
    The more load we throw at storage, the faster it goes – to a point.
  • -d60: means the test will last 60 seconds
  • -b8K and -b64K: the size of our IO requests in kilobytes.
    SQL Server does a lot of random stuff in 8KB chunks,
    and we’re also testing sequential stuff in 64KB chunks.
  • -r and -s[i]: random versus sequential access.
    Many queries jump around randomly in the database, whereas things like backups,
    bulk loads, and table scans generally work sequentially. If the optional interlocked (i) qualifier is used, a single interlocked offset is shared between all threads operating on a given target so that the threads cooperatively issue a single sequential pattern of access to the target.
  • -L: measure latency statistics.
  • -Sh: equivalent -Suw, disable software caching, equivalent to FILE_FLAG_NO_BUFFERING and enable writethrough (no hardware write caching), equivalent to FILE_FLAG_WRITE_THROUGH.
  • -c20G: create a file of 20GB. Size can be stated in bytes or KiB/MiB/GiB/blocks.

You’ll notice I’m using a lot of general terms here about how some application patterns work.

Every SQL Server database has its own access patterns – random, sequential, big chunks, small pieces, and so on.

The four test lines you see above are shorthand examples of how some SQL Server IO patterns work.

Check all the command line parameters on the official github repo wiki.

Sql Server: Backup all the databases of the instance

security8

They told me (Alessandro Alpi), to don’t blog this 🙂 because there’s a lot of this stuff out there, but this my blog, and it is too cool when developing in load testing environment, and you need to backup, try load test data, and restore previous version, so forgive me about this:

sys.sp_MSforeachdb @command1 = N'BACKUP DATABASE [?] TO DISK = N''C:\_Databases\_Backup\Databases\?.bak'' WITH NOFORMAT, INIT'

Change the “C:\_Databases\_Backup\Databases\” into your default BackupDirectory found in the instance facets, to avoid security problem.

DON’T USE THIS CODE IN PRODUCTION!!

Azure Cloud: Sql Virtual Machine – Access with Management Studio over Internet

Today I was trying, using Sql Server Management Studio (SSMS), connecting to a Sql Server 2012 instance, installed on a Windows Azure virtual machine, and even if I followed each step of the easy MSDN guide I’ve not been able to connect to it, as if something was blocking the communication port 1433. In fact, despite having opened the endpoint on the virtual machine from the windows azure control panel, I could not connect, when I realized that probably some ports are blocked by default from Microsoft, then I tried to change to a different public port from 1433 to one that has got a number as high as were the others I seen in the control panel, and everything worked out:

Sql Server SSMS To Azure

Honestly, I do not have documentation that actually Microsoft blocks the ports of known services (maybe for security reason), but I’ve not found a way to be able to use the classic 1433 as the public port, instead the random 55890 port perfectly worked.

This is the MSDN guide that I followed

I hope this advice will face save valuable minutes if not hours