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
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
You can by default install all the products and try them for Free
or pick ApexSQL Complete 2012
Once the install is complete, Please launch SQL Server 2012 Management Studio,
ApexSQL Complete menu has the following options
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.
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
ApexSQL Complete also check your query as you type and shows them in “Error List” window
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
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.
ApexSQL Complete Options
The following are the different options available under General section, but I would like to highlight the “Decrypt Encrypted Objects”
The below view “dbo.vwAddress_Enc” is an encrypted view
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
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
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
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
When you select the Alias, it automatically fills the referenced object name
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
Using Snippet Manager, You can modify the existing Snippet and customize them or you can add new Snippets as well
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 !!!
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
2. Local or Network Folder
3. Drop Box
5. Amazon S3
7. Google Drive
Professional Version also supports SFTP and FTPS and AES Encryption of Backup files
Please follow the below steps for installation
The installation has completed successfully, Let us launch SQL Backup and FTP and create a Backup
Select the databases you want to Backup
Click on “Add backup destination” to specify the backup destination
For our demo, Let us select Drop box option
Click on “Authorize…” and Login to your Drop Box account
Click “Allow” to approve the Drop Box access
Once Drop Box access is authorized, Please click on “Finish” to proceed. You can configure automatic cleanup of backups as well.
Specify the Folder to which Backup has to be done
Click “Test” to make sure it works
Once Test is completed, Click “Ok” to proceed
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
If you want to enable Email notifications, Please select and specify the email address
Click on “Email Settings” icon to Test and configure, You can you use your own SMTP Server, if you prefer
Click “Send Test e-mail” to make sure the notifications are working and click “Ok” to proceed
Scheduling the Backups
If you want the backup to run automatically at a specific time, Please enable the “Schedule this job” option
Please click on “Schedule Settings” to customize the schedule based on your requirement, once finished, Please click on “Save & Close” to proceed
Please click “Run Now” to test the job manually
Once test is completed, Now click on “Save” button on the tool bar to save the job.
Hope this helps !!!
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
Click “Next” to proceed
Select the installation folder and then click “Next” to proceed
Click “Next” to confirm the installation and proceed
Click “Close” to finish the installation
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”
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
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
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
I hope you all find this information useful. Please post your comments !!!
In this post we will see How to generate Reports in SQL Nexus
The following are the list of reports available
Blocking and Resource Wait Statistics Report
Bottleneck Analysis Report
Bottleneck Analysis –Drill down sub report
Bottleneck Analysis –Drill down sub report – Wait Statistics
SQL Server 2005 Performance Statistics Reports
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.
SQL Server 2008 Performance Statistics Reports
Blocking and Wait Statistics, Bottleneck Analysis and Spin Lock Stats reports links to the above reports we saw.
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
Query Hash Report – Performance Overview Drilldown report
Query Hash Report – Top Unique Batches Drilldown charts
Query Hash Report – Top Unique Batches Details report
Query Hash Report – Resource Consumption report
Query Hash Report – Top Unique Batches Details Report
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
Interesting Events – Drilldown Report
I hope you all find this information useful… Please post your comments !!!
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
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
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
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.
Query to copy database diagrams
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.
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)
After installing all prerequisites, download the SQLNexus from the above site and store it any local folder and then extract the zip file SQLNexus126.96.36.199.zip
Double click or Run “SQLNexus.exe”
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
Without RML Utilities, Trace files (.trc) will not be imported
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
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”
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
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
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
After the reports are generated, the Report link icons will change as follows
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 !!!