PowerShell scripts to general SQL Server workload against AdventureWorks database schemas
Provides sample SQL Scripts and PowerShell scripts to automate the generation of load against the AdventureWorks schema.
Current supported versions are:
- SQL 2008 / R2
- SQL 2012
- SQL 2014
- SQL 2016
- SQL 2017
- SQL Azure
This tool is currently only supported on Windows and PowerShell. It has not yet been tested on PowerShell Core. It will though work remotely against SQL Server on Linux.
NOTE: For SQL Server 2014 and higher, use the non-version specific scripts.
To install and use this toolset follow these steps.
- Create a folder C:\Workload or other suitable location
- Download the project source or one of the releases to that location you just created
- Create a wrapper script or start the RunWorkload.ps1 script from the console depending on your needs. Refer to the examples for various ways to execute this workload.
This example runs a single workload session against a database running in SQL Azure with a fast frequency between batches
Set-Location C:\WorkLoad
.\RunWorkload.ps1 -SQLServer myazuredb.database.windows.net -Database DemoDB01 -UserName DemoUsr -Password ?????? -TSQLFile C:\WorkLoad\AdventureWorksAzureBOLWorkload.sql -Frequency 'Fast'
For further examples see the files provided in [.\Examples](Examples) folder.
To run a larger workload you may need to execute this script multiple times. To achieve this create a .cmd file with multiple lines such as:
Start PowerShell.exe -File "C:\Workload\RunWorkload.ps1" -SQLServer "SqlServer.contoso.com" -Database "DemoDB01" -TSQLFile "C:\Workload\AdventureWorks2012BOLWorkload.sql" -Frequency "Fast"
For an example see the file .\Examples\LoadTestExample.cmd
As this workload is generated through a PowerShell script, it is not excempt from PowerShell Execution Policies. The default policy is Restricted which will stop you from executing the workload script. In production environments it is not recommended to set this to Unrestricted, instead you should use RemoteSigned or AllSigned and then code sign the script files with your organisations certificate.
As a workaround you could run the workload script from a .cmd file with the PowerShell.exe -ExecutionPolicy parameter set to RemoteSigned or as appropriate. This will only change the policy for the process and still allow you to execute the workload. However, if your policy is set through Group Policy then this will not take effect.
Start PowerShell.exe -ExecutionPolicy RemoteSigned -File "C:\Workload\RunWorkload.ps1" -SQLServer "SqlServer.contoso.com" -Database "DemoDB01" -TSQLFile "C:\Workload\AdventureWorks2012BOLWorkload.sql" -Frequency "Fast"
For further information see: Get-Help about_Execution_Policies https://technet.microsoft.com/en-au/library/hh847748.aspx
The SQL Server host name and instance to connect to (e.g. Server01\Prd01)
The name of the database to run the script against. This allows for restoring the AdventureWorks database schema to custom databases.
If SQL Authentication is required this is the UserName to authenticate with. If not provided then Windows Auth is used.
If SQL Authentication is required this is the Password to authenticate with. Not required for Windows Auth. Warning: This is currently stored in plain text
The path to the TSQL file which contains the sample statements to execute.
The path to the TSQL file which contains the setup statements to execute before starting the process (if required).
The frequency of which to run the statements at (Fast, Normal, or Slow)
The duration to run the workload for (seconds). 0 is unlimited, otherwise maximum allowed is 172800 (48hrs)
A full list of changes in each version can be found in the change log.