Within the past 2 months, there has been a couple of key tools introduced for SQL Server. Recently on 20 April 2012, Microsoft released the Reporting Services Migration Tool and on 18 May 2012, they released Max. Server Memory calculator.
The Reporting Services Migration Tool (Beta) was mentioned here. I do want to point out that the source and target server must be at least SQL Server Reporting Services 2008 R2 or later. In the most simple term, this allows you to have a backup and restore tool for SQL Server Reporting Services. (SSRS) It does allow you to restore or even migrate your existing native report server via a PowerShell script.
You can download this tool here. The downloaded file MigrationTool.zip has the following components:
The tool comes in two flavors
1. Command-line tool (RSMigrationTool.exe):
RSMigrationTool.exe [-n ] -i [-h] -f [-p ]
-n Native report server url (e.g. http:///reportserver). Optional if native instance name is specified. You should specify this option if WMI does not return the correct url.
-i Name of the native report server instance. Migration tool will use WMI to connect to the remote instance. If the connection cannot be made, native report server url must be specified, and passwords and history snapshots will not be backed up.
-h Report history will be exported. Please note that migrating report history requires direct modification to data in report server catalog and is not publicly supported. Backing up the catalog before migration and verifying correctness of the reports after migration are strongly recommended.
-f A folder where the files are to be stored. The folder will be created if it doesn’t exist.
-p Password that will be used to encrypt password in data source and subscription definitions. If this option is not specified, passwords will not be backed up.
2. GUI tool (RSMigrationUI.exe):
You can either type in the parameters in the textboxes, or define your input in an xml file and click “Load Configuration”. A sample configuration xml file is provided (MigrationSettingsSample.xml).
3. Either tool will generate a PowerShell script (Migration.ps1) and a set of files that represents a backup of your server. Usage of the restore script Migration.ps1:
PS E:\tmp\DenaliMigration> .\Migration.ps1 -TargetSiteUrl http://SharePointSite -Password -TargetFolderUrl “http://SharePointSite/Shared Documents” -TargetCatalogConnectionString “Data Source=;Initial Catalog=;Integrated Security=True”
4. Log files:
- Logs generated by the tool is located in the “Logs” folder under the same folder where the tool is located.
- Logs generated by the PowerShell script is located in the “Logs” folder under the folder for migration files.
I do not recommend using this calculator listed below and you can find out more here. (Updated 1 June 2012)
For the next tool, Sakthivel Chidambaram wrote a calculator after he noticed a lot of people were referencing his blog. (Importance of setting Max Server Memory in SQL Server and How to Set it) You can download this Calculator: here. It is essentially an Excel spreadsheet that allows you to enter values, as seen below:
Step 2 in the spreadsheet will require you to gather the results of a SQL Query:
Please note: This calculator will not help on server having more than 1 instance of SQL Server because the memory required for OS, SSIS, Antivirus etc., are shared so the calculation becomes little complex.
You can monitor the health of SQL Server Memory using these Performance monitor counters:
1. Page Life Expectancy Counter under SQL Server Buffer Manager which is the average number of seconds a page stays in cache. It should be atleast = 300 * (Total RAM in GB)/4. Though our recommendation earlier was PLE should be 300, if a page is staying just for 5 minutes on a server with 128 GB RAM is not a right value to say that there is no memory pressure.
2. Memory Grants Pending, in the Perfmon object SQL Server Memory Manager (Close to 0 or lower is better)
3. Memory grant queue waits in the Perfmon object SQL Server Wait Statistics Object (Close to 0 or lower is better)