Archive

Archive for the ‘SQL Server’ Category

ApexSQL Complete–Advanced SQL Server Intellisense for Free !!!

March 16, 2013 Leave a comment

SQL Server 2012 has built-in Intellisense, ApexSQL Complete takes Intellisense to next level.

It available as a Free Tool and its one of the best Free Tools available for SQL Server in market.

Its available for download in this below link

http://www.apexsql.com/sql_tools_complete.aspx

The download also includes all the tools from ApexSQL Developer Studio (15 Developer Tools, 3 of them are Freely available)

The installation process is quite simple

image

image

You can by default install all the products and try them for Free

image

or pick ApexSQL Complete 2012

image

 

image

Once the install is complete, Please launch SQL Server 2012 Management Studio,

ApexSQL Complete menu has the following options

image

Please click on “Enable Auto-complete” to use ApexSQL Complete instead of built-in Intellisense

You can open a new Query window and start typing the query and you can activate the Intellisense by pressing Ctrl + Space

You can choose the columns which you want to be included in Query and If you noticed, It automatically add Alias to the Table name automatically as well, which will be very helpful for complex queries and using in joins.

image

Also when you select a table, you can see the CREATE Script for the table as well and you have option to “Copy” the script as well

image

ApexSQL Complete also check your query as you type and shows them in “Error List” window

image

ApexSQL Complete Error List window works similar to Visual Studio Error List window, double clicking on the Error takes you to the exact Line and Column so that you can easily fix it

image

ApexSQL Code Structure

This is one of the most powerful feature I liked in ApexSQL which is not available in any of the other competitive production

It parses your SQL code and display the Code Structure as Tree, so that you can easily navigate the code and click and move to the preferred statement easily.

image

ApexSQL Complete Options

General Options

The following are the different options available under General section, but I would like to highlight the “Decrypt Encrypted Objects”

image

The below view “dbo.vwAddress_Enc” is an encrypted view

image

Since I have enabled the Decrypt option, ApexSQL Complete is able to show the definition of the Encrypted View, so that I can copy the script

image

Insert Options

image

Hints Options

If you don’t want to see System tables or views in the Intellisense list, you can deselect it using “System Tables” option, similarly you can customize the other hints too

image

Aliases Options

ApexSQL Complete by default create aliases for tables or views selected from Intellisense window, but if you want use your own custom defined Alias, you can define them here

image

I have created an alias named “Emp” for the Employee table, when I write the query, now the ApexSQL Complete Intellisense window displays the Alias on top

image

When you select the Alias, it automatically fills the referenced object name

image

Schema Cache Options

Here you can see the currently cached Database and its object counts, you can select them and right click and Delete or Refresh them

image

Snippet Manager

Using Snippet Manager, You can modify the existing Snippet and customize them or you can add new Snippets as well

image

image

image

Also after doing the customization, You can Export the options and share with your colleagues, and they can import and use the Snippets you created or use the same Aliases you have created.

ApexSQL Developer Studio also has the following products available for free as well

1. ApexSQL Search

2. ApexSQL Refactor

I hope you find this information useful !!! Have fun coding using ApexSQL Complete !!!

How to backup your SQL Database and Upload to FTP or Cloud Automatically ?

March 12, 2013 Leave a comment

Its always a better idea to Keep Offsite backup of databases, “SQL Backup and FTP” allows you to do that freely

SQL Backup and FTP is available for download for free at http://sqlbackupandftp.com

It supports direct backup to

1. FTP

2. Local or Network Folder

3. Drop Box

4. Box

5. Amazon S3

6. SkyDrive

7. Google Drive

Professional Version also supports SFTP and FTPS and AES Encryption of Backup files

Please follow the below steps for installation

image

image

image

image

image

image

image

The installation has completed successfully, Let us launch SQL Backup and FTP and create a Backup

image

Select the databases you want to Backup

image

Click on “Add backup destination” to specify the backup destination

image

For our demo, Let us select Drop box option

image

Click on “Authorize…” and Login to your Drop Box account

image

Click “Allow” to approve the Drop Box access

image

Once Drop Box access is authorized, Please click on “Finish” to proceed. You can configure automatic cleanup of backups as well.

 

image

Specify the Folder to which Backup has to be done

image

Click “Test” to make sure it works

image

Once Test is completed, Click “Ok” to proceed

image

Let us now connect to SQL Server and do a backup, By default, if you have a SQL Server default instance running, it will automatically connect and display the databases, Please select the database which you want to backup

image

If you want to enable Email notifications, Please select and specify the email address

image

Click on “Email Settings” icon to Test and configure, You can you use your own SMTP Server, if you prefer

image

Click “Send Test e-mail” to make sure the notifications are working and click “Ok” to proceed

image

Scheduling the Backups

If you want the backup to run automatically at a specific time, Please enable the “Schedule this job” option

image

Please click on “Schedule Settings” to customize the schedule based on your requirement, once finished, Please click on “Save & Close” to proceed

image

Please click “Run Now” to test the job manually

image

image

image

Once test is completed, Now click on “Save” button on the tool bar to save the job.

Hope this helps !!!

SQL Server Compression Estimator

June 10, 2011 Leave a comment

SQL Server Compression Estimator is a free tool available through CodePlex (http://ssce.codeplex.com/) which helps you in estimating the best compression for your SQL server objects. It estimates both Row and Page compression and also provides option to generate script for altering your SQL Server objects to implement compression.

My great thanks to MajikbyboB for providing this tool to the community for free.

Let us see how to install and use this tool

Download and extract the setup from the above site and extract the zip file and run setup.exe

image

Click “Next” to proceed

image

Select the installation folder and then click “Next” to proceed

image

Click “Next” to confirm the installation and proceed

image

Click “Close” to finish the installation

image

Now we have successfully installed the tool, Let us see how to run and estimate compression on databases

Run “Compression Estimator” and specify your Server name and select authentication method and provide required credentials and click “Connect”

image

image

Click on Database combo and select your database, and then specify the savings threshold.

Savings threshold identify what objects to be reports based on the saving in size due to compression

If you want to include index maintenance ratio, select the “Include index maintenance ratio” check box.

Click “Process” to proceed

image

Once the processing is complete, you can see the list of objects which can be compressed with the following details

1. Type of Compression (Page or Row)

2. Current Size

3. Compressed Size

4. $ of Savings

image

You can save the estimation results as CSV file by using “Save Results” option for your reference.

You can select the required changes to be made and then click on “Create Script” to generate the SQL Script to make compression changes

Sample Script generated

  1. — This compression script was created by Compression Estimator
  2.  
  3. — By applying PAGE compression setting, you should obtain
  4. — approximately 53.33% compression
  5. ALTER TABLE [Production].[Product]
  6. REBUILD
  7. WITH (DATA_COMPRESSION = PAGE);
  8.  
  9. — By applying PAGE compression setting, you should obtain
  10. — approximately 53.70% compression
  11. ALTER INDEX IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode ON [Person].[Address]
  12. REBUILD WITH (DATA_COMPRESSION = PAGE);
  13.  
  14. — By applying PAGE compression setting, you should obtain
  15. — approximately 63.64% compression
  16. ALTER TABLE [Production].[BillOfMaterials]
  17. REBUILD
  18. WITH (DATA_COMPRESSION = PAGE);
  19.  
  20. — By applying PAGE compression setting, you should obtain
  21. — approximately 59.09% compression
  22. ALTER TABLE [Purchasing].[PurchaseOrderHeader]
  23. REBUILD
  24. WITH (DATA_COMPRESSION = PAGE);
  25.  
  26. — By applying PAGE compression setting, you should obtain
  27. — approximately 56.25% compression
  28. ALTER INDEX AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode ON [Sales].[CurrencyRate]
  29. REBUILD WITH (DATA_COMPRESSION = PAGE);
  30.  
  31. — By applying PAGE compression setting, you should obtain
  32. — approximately 60.00% compression
  33. ALTER TABLE [Production].[ProductListPriceHistory]
  34. REBUILD
  35. WITH (DATA_COMPRESSION = PAGE);
  36.  
  37. — By applying PAGE compression setting, you should obtain
  38. — approximately 60.00% compression
  39. ALTER INDEX IX_ProductReview_ProductID_Name ON [Production].[ProductReview]
  40. REBUILD WITH (DATA_COMPRESSION = PAGE);
  41.  
  42. — By applying PAGE compression setting, you should obtain
  43. — approximately 60.00% compression
  44. ALTER TABLE [Production].[ProductCostHistory]
  45. REBUILD
  46. WITH (DATA_COMPRESSION = PAGE);
  47.  
  48. — By applying PAGE compression setting, you should obtain
  49. — approximately 60.00% compression
  50. ALTER INDEX AK_Employee_LoginID ON [HumanResources].[Employee]
  51. REBUILD WITH (DATA_COMPRESSION = PAGE);
  52.  
  53. — By applying ROW compression setting, you should obtain
  54. — approximately 60.00% compression
  55. ALTER INDEX IX_ProductReview_ProductID_Name ON [Production].[ProductReview]
  56. REBUILD WITH (DATA_COMPRESSION = ROW);
  57.  
  58. — By applying PAGE compression setting, you should obtain
  59. — approximately 69.62% compression
  60. ALTER TABLE [Production].[WorkOrder]
  61. REBUILD
  62. WITH (DATA_COMPRESSION = PAGE);
  63.  
  64. — By applying PAGE compression setting, you should obtain
  65. — approximately 67.25% compression
  66. ALTER TABLE [Production].[TransactionHistoryArchive]
  67. REBUILD
  68. WITH (DATA_COMPRESSION = PAGE);
  69.  
  70. — By applying PAGE compression setting, you should obtain
  71. — approximately 57.58% compression
  72. ALTER TABLE [Purchasing].[PurchaseOrderDetail]
  73. REBUILD
  74. WITH (DATA_COMPRESSION = PAGE);
  75.  
  76. — By applying PAGE compression setting, you should obtain
  77. — approximately 73.25% compression
  78. ALTER TABLE [Production].[WorkOrderRouting]
  79. REBUILD
  80. WITH (DATA_COMPRESSION = PAGE);
  81.  
  82. — By applying PAGE compression setting, you should obtain
  83. — approximately 64.86% compression
  84. ALTER TABLE [Production].[TransactionHistory]
  85. REBUILD
  86. WITH (DATA_COMPRESSION = PAGE);
  87.  
  88. — By applying PAGE compression setting, you should obtain
  89. — approximately 65.31% compression
  90. ALTER TABLE [Sales].[CurrencyRate]
  91. REBUILD
  92. WITH (DATA_COMPRESSION = PAGE);

I hope you all find this information useful. Please post your comments !!!

SQL Nexus–How to generate Reports from Performance Data collected and imported ?–Part 3

June 5, 2011 2 comments

In previous posts we saw  How to install, configure and collect performance data from SQL Server 2005/2008/2008 R2 ? and  How to import Performance Data using SQL Nexus ?

In this post we will see How to generate Reports in SQL Nexus 

The following are the list of reports available

image

Blocking and Resource Wait Statistics Report

clip_image001

Bottleneck Analysis Report

clip_image002

Bottleneck Analysis –Drill down sub report

clip_image003

Bottleneck Analysis –Drill down sub report – Wait Statistics

clip_image004

clip_image005

SQL Server 2005 Performance Statistics Reports

clip_image006

Bloaking and Wait Statistics and Bottleneck Analysis reports are linking to Reports we saw earlier

Spinlock Statistics Report – Spinlock is a lightweight, user mode synchronization object used to protect a specific structure. The goal of this report is to see there are no collissions and spin.

clip_image007

clip_image008

SQL Server 2008 Performance Statistics Reports

Blocking and Wait Statistics, Bottleneck Analysis and Spin Lock Stats reports links to the above reports we saw.

clip_image009

Query Hash Report – This report show you the list of queries with details of How many times it executed, How many query plans were created, Total CPU time taken, Total Duration to execute the query and Total Logical IO Reads

clip_image010

clip_image012

Query Hash Report – Performance Overview Drilldown report

clip_image013

clip_image014

Query Hash Report – Top Unique Batches Drilldown charts

clip_image015

Query Hash Report – Top Unique Batches Details report

clip_image017

Query Hash Report – Resource Consumption report

clip_image018

clip_image019

clip_image020

clip_image022

clip_image024

Query Hash Report – Top Unique Batches Details Report

clip_image028

Interesting Events Report shows the number of times events like Auto Stats Updation, Lock Escalation, Missing column statistics, etc… is occurring along with Trace Event ID, you can also drill down into each event and see the query caused the event as well

clip_image029

Interesting Events – Drilldown Report

clip_image030

clip_image032

I hope you all find this information useful… Please post your comments !!!

Error Handling in SQL Server 2008 R2–Questions Answered

June 3, 2011 Leave a comment

With reference to the Question posted by Hima Bindu Vejella on http://himabinduvejella.blogspot.com/2011/06/how-did-you-handle-errors-in-sqlserver.html, I have posted these answers, I hope you all find this usefull

How did you handle errors in SqlServer 2008 R2?

We handle errors using

  1. BEGIN TRY
  2. BEGIN TRANSACTION
  3. /* Insert or Update or Delete statements */
  4. COMMIT TRANSACTION
  5. END TRY
  6. BEGIN CATCH
  7. INSERT INTO ErrorLog (ErrorNumber, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure, ErrorRelease)
  8. SELECT ERROR_NUMBER() AS ErrorNumber,
  9. ERROR_SEVERITY() AS ErrorSeverity,
  10. ERROR_STATE() as ErrorState,
  11. ERROR_LINE () as ErrorLine,
  12. ERROR_PROCEDURE() as ErrorProcedure,
  13. ERROR_MESSAGE() as ErrorMessage
  14. IF (XACT_STATE()) = -1
  15. BEGIN
  16. ROLLBACK TRAN
  17. END
  18. ELSE IF (XACT_STATE()) = 1
  19. BEGIN
  20. COMMIT TRAN
  21. END
  22. RAISEERROR(‘Application Error Occurred. Please contact administrator’, 16, -1)
  23. END CATCH()

What are the different ways of handling errors in SQL Server?

1. We can handle errors by checking @@ERROR, if it’s not equal to 0, then we can use RAISERRROR to return error message to application

2. We can handle error by checking @@ERROR and @@ROWCOUNT in combination and then use RAISERROR or RETURN to return error message or code to application

3. We can use TRY..CATCH method

Which is the optimized way to handle errors?

The optimized way to handle errors is to create Error Log table with following columns (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine, UserName, HostName, ErrorDateTime, ApplicationName) and then create a Stored Procedure to get these columns as Input Parameter except ErrorDateTime, for which we can use GetDate() and then call this Stored Procedure inside BEGIN CATCH..END CATCH block

What are the Built-In Objects available related to error handling?

The following are the built in objects related to Error Handling and Information

ERROR_NUMBER() – Returns Error Number of the Error that caused the CATCH block of TRY…CATCH to run

ERROR_SEVERITY() – Returns severity of Error that caused the CATCH block of TRY…CATCH to run

ERROR_STATE() – Returns State Number of the Error that caused the CATCH block of TRY…CATCH to run

ERROR_LINE () – Returns the line number at which an error occurred that caused the CATCH block of TRY…CATCH to run

ERROR_PROCEDURE() – Returns the name of the stored procedure or trigger where an error occurred that caused the CATCH block of a TRY…CATCH construct to be run.

ERROR_MESSAGE() – Returns the message text of the error that caused the CATCH block of a TRY…CATCH construct to be run

@@ERROR – Returns the Error number of the last T-SQL Statement run

RAISERROR – Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.

Sys.Messages – This is a Catalog view which contains the list of system defined and user defined messages

SP_AddMessage – To define a new user-defined error message in a SQL Server instance

sp_dropmessage – To drop a specified user-defined error message from a SQL Server instance

xp_logevent – Logs a user defined message in SQL Server log file and in the Windows Event Viewer. It can be used to send an alert in the background to administrators without showing a message to client

Print – To simply display the message, we can use PRINT statement followed with Error message

How can return Error information to Application ?

We can return error information back to application using RAISERROR Or RETURN

RAISERROR (‘Application Error Occurred’, 16, -1)

RETURN -1 –In Application we have to check for Return value and display corresponding message

SQL Server 2008 R2 Database Diagrams Questions Answered

June 1, 2011 2 comments

What are the database diagrams in SQL Server?

SQL Server Database Diagram is visual model of a database. We can use only Tables in SQL Server database diagrams. Any changes made outside Database Diagram designer will reflect automatically in the diagram and vice versa.

How did you create them?

We can create them using built-in SQL Server Database Diagram designer tool in SQL Server Management Studio. There is no Undo and Redo option, so we need to be careful when deleting columns or removing relationships

Do you know where exactly the database diagrams are stored in SQL Server 2008 R2?

Database Diagrams are stored in dbo.sysdiagrams system table in the database we are creating diagram. This table will be created automatically, when we create the first database diagram in the database.

The structure of this table is as follows

clip_image001

 

Is there any way how you copy one database diagram to another database?

Yes, we can copy the diagram by copy the data of dbo.sysdiagrams table from source database to destination database, but the tables in the diagram should exist in the destination database or else while viewing or modifying the diagram, we will get the below error and corresponding tables will be removed from the diagram

“Table(s) were removed from the diagram because privileges were removed to these table(s) or the table(s) were dropped.

clip_image001[5]

Query to copy database diagrams

  1. INSERT INTO [DESTINATIONDB].[dbo].[sysdiagrams]
  2. ([name]
  3. ,[principal_id]
  4. ,[version]
  5. ,[definition])
  6. SELECT [name]
  7. ,[principal_id]
  8. ,[version]
  9. ,[definition]
  10. FROM [SOURCEDB].[dbo].[sysdiagrams]

 

For copying to remote servers, we can use Linked Servers and use Four part name or use SSIS to transfer data

How will you insert database diagrams into your design document?

We can use “Edit -> Copy Diagram to Clipboard” or “Database Diagram – Copy Diagram to Clipboard” menu option to copy the diagram and then paste it in the document required. If the diagram is bigger, we can paste it to Paint Brush or any image editor and then select specific portions and copy one by one in document.

We can also the print the database diagram as PDF, if we have PDF Print drivers such as BioPDF or CutePDF installed.

SQL Nexus–How to import Performance Data collected using PSSDiag ?–Part 2

May 31, 2011 4 comments

In the last post, we saw how to install, configure and collect performance data from SQL Server 2005/2008/2008 R2, in this post, we will how to import that data using SQL Nexus and get ready to prepare Performance Analysis Reports

SQLNexus is a free tool developed by SQL Server support team and used for importing and analyzing performance data collected using SQL Diag and PSSDiag. Its helps identifying the Bottle necks, blocks, CPU consuming queries, Wait, Locks information, etc…

SQLNexus is freely available for download from http://sqlnexus.codeplex.com/

To run SQLNexus, you need to install the following prerequisites

.Net Framework 3.5

SQL Server 2008 SP1 Report viewer control (http://www.microsoft.com/downloads/details.aspx?FamilyID=bb196d5d-76c2-4a0e-9458-267d22b6aac6&DisplayLang=en)

SQL Server 2005 or above – To store imported performance data. You can also use SQL Server Express Edition

ReadTrace (RML Utilities for SQL Server)

x64 – http://www.microsoft.com/downloads/en/details.aspx?FamilyId=B60CDFA3-732E-4347-9C06-2D1F1F84C342&displaylang=en

x86 – http://www.microsoft.com/downloads/en/details.aspx?FamilyId=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=en

After installing all prerequisites, download the SQLNexus from the above site and store it any local folder and then extract the zip file SQLNexus3.0.0.0.zip

image

Double click or Run “SQLNexus.exe”

image

On loading of the application, it will prompt you to connect to SQL Server instance on which you want to create the SQLNexus database, specify the Server Name, Authentication information and click “Connect”

Its not recommended to run this on your production server, since it will create lot of performance loads while importing the data and running the reports

If RML Utilities is not installed, you will get the below error message

image

Without RML Utilities, Trace files (.trc) will not be imported

image

On successful connection, you will see the above Main Dashboard screen, To start the import process, click on “Import” link left hand bottom side of the screen

image

Click Folder Browse icon and select the folder on which your Output data from PSS Diag and SQL Diag tool is stored.

Please make sure there is no spaces in the folder names, If you have space in the folder name, you will  not be able to import the Trace files

To customize the import options, click on “Options”

image

Select the required options and if you want to retain the options you selected, click on “Save My Options”, for any reason, if you need to reset all the options you selected to default options, click on “Restore Default Options”

Use “Drop Current DB Before Importing” option to drop and recreate the database every time, this option will be helpful, if you want to load clean set of data every time.

Once finished customizing the options, click on “Import” button to proceed with the import

image

You will receive the above warning message, if you have selected “Drop Current DB Before Importing” option. click “Yes” to proceed with drop database and import

image

The above screenshots shows the import process and list of various files imported along with the number of records imported and also shows us whether the file imported successfully or not.

If you notice, the second line, Import for DBCC_MEMORYSTATUS_Startup.out file has failed, this is a known issue, there is no report currently available for DBCC MEMORYSTATUS, due to which this import will fail always, to follow up about this issue, please visit this discussion link CodePlex discussion forums http://sqlnexus.codeplex.com/workitem/11455 

Now the import has completed successfully.

Click on File Menu and then click on “Run All Reports” to generate the reports

image

After the reports are generated, the Report link icons will change as follows

image

Since we don’t have any data related to SQL Server 2000, it will remain unchanged.

In the next post, we will explore the reports generated by SQLNexus and see various information available through them.

I hope you all find this information useful. Please post your comments !!!