[EN] First date with Killer ApexSQL Tools

New year started few weeks ago. For me one of my personal goals is to be more active blogger than last year. Especially with this interesting (in my – ond not only – opinion) special series: The Sunday Market. Name (genesis is too big word) of this series is simple. In many countries You cannot buy something interesting for Your environment, because Financial Department are close in Your company. But You always can get something for free from the web. Get-Test-Check > put into your budget.

Captura_ApexSQL_killer

Last Wednesday I presented short (introduction type) session at my local SQL User Group (Polish SQL Server User Group in Wrocław LINK) about free tools from ApexSQL. Some of my folks talked about it few times on previous meetings. It was first session, second will be presented soon with more funny experiments. In this blog series I want show few moments from “dating with Apex”.  In this post I will just introduce about installation of three tools. All are free:

ApexSQL Complete | LINK http://bit.ly/1i3IeV3 | size 7MB | in few words: SSMS and VS integration,   SQL syntax checking, SQL object descriptions, SQL code completion, SQL code visualization, Snippet management

ApexSQL Refactor | LINK http://bit.ly/1i3IjrC | size 8MB | in few words: SQL parameter management, SQL formatting, Consistent code layout, Database object refactoring, Batch formatting, One-to-many relationship replacement

ApexSQL Search | LINK http://bit.ly/MkFvvR | size 7MB | in few words: Smart renaming, Text search, Database object search, Easy SSMS tab navigation, SQL code cleaning, Graphical dependencies

 

Phase one: installation

Captura_ApexSQL_Complete01 Captura_ApexSQL_Refactor08 Captura_ApexSQL_Search08

Pic.01 | Three tools, same installation interface – I really LOVE it. Ordnung muß sein!

All screenshots below (till phase two) are the same for all tools.

Captura_ApexSQL_Complete02

Pic.02 | Reading – yes read it – and accepting license.

Captura_ApexSQL_Complete03

Pic.03 | Choosing installation folder – for me as alway it is ‘non default folder’ *

Captura_ApexSQL_Complete04

Pic.04 | We can integrate these tools with all part of environment which is installed on machine: in this case I have SSMS for SQL 2008 & 2012, anf VisualStudio for 2005, 2008, 2010 & 2012 versions. It’s interesting because I don’t heve Visual Studio 2013 and installer is prepared for instalaltion (suppose that last row will be un-grayed when I install VS2013). But I have SQL Server 2014 on my miachine and this installer is not ready for this version.

Captura_ApexSQL_Complete06

Pic.05 | Choosing (or not) selected Start Menu – even on Windows 8.1 **

Captura_ApexSQL_Complete07

Pic.06 | Ready to install with summary.

Captura_ApexSQL_Complete08

Pic.07 | installation in progress.

Asterix:

* in this case I got several problems with using non defaul folders, but not only – I had not vell functional addins from other software vendor – these addins figting for place and acces for dll’s.

** definitely too much, really too much links in start menu by default:

image

Phase two: result

Capturaerror6

Pic.08 | First execution of SQL Server management Studio 2012 and I got this error, next: SSMS crash. I tried again and again and again.

Capturaerror7

Pic.09 | More interesting error I got when I run Visual Studio 2012 (of course add-ons hasn’t been installed).

I didn’t enought time for full investigation, becaus it happener just few hours befor my user group meeting. So I executed my disaster repair (not recovery) procedure in few steps:

  1. deinstallation al ApexSQL addins
  2. deinstallation all other vendor add-ons
  3. cleaning reistry (by wise registry cleaner)
  4. cleaning folders (by wise dick cleaner)
  5. restart physical machine
  6. installation all three ApexSQL tools again

 

Phase three: final result

Finally I got all tools in my SQL Server Management Studio:

image

Pic.10 | ApexSQL Complete integrated with SSMS 2012.

image

Pic.11 | ApexSQL Refactor integrated with SSMS 2012.

image

Pic.12 | ApexSQL Search integrated with SSMS 2012.

 

Next date with Apex on Sunday.

ps: to kill or to be killed…

[EN] Fridays with Azure Tools (01) – Azure Storage Explorer

One of my good colleagues – SQL Server MVP from Poland:  Damian Widera {TWITTER|LINKEDIN} asked me for sending few files, not very big, just about 20 GB. His request enforced me tow start writing a new series on my blog – series about Tools for Windows Azure Platform. I have a plan for writing about many different tools: for usung storage, database, programming, management, backup and other targets. I will try focusing on free tools, but if some tool will be not very expensive, I’ll write about it too. In this post I want to introduce You for Azure Storage Explorer.

First  phase –  download this app from CodePlex site {LINK}.

Second phase – install application:

Captura_AzureExplorer_01

Pict.1.1 | Welcome Setup Wizard.

Captura_AzureExplorer_02

Pict.1.2 | Choose installation folder.

Captura_AzureExplorer_03

Pict.1.3 | Confirm installation.

Captura_AzureExplorer_04

Pict.1.4 | Wait, sometimes pleeeease wait.

Captura_AzureExplorer_05

Pict.1.5 | Installation is complete.

Third phase – run application and play:

Captura_AzureExplorer_06

Pict.1.01 | Welcome screen

Captura_AzureExplorer_07

Pict.1.02 | Adding new starage account: we need name (from Azure Management Portal) and key (form Manage Access Key feature)

Captura_AzureExplorer_08

Pict.1.03 | Important information – be patient for first connection

Captura_AzureExplorer_10

Pict.1.04 | First fiew for my Blob Storage 

Captura_AzureExplorer_11

Pict.1.05 | First view for Container Folder

Captura_AzureExplorer_12

Pict.1.06 | We can download object from container

Captura_AzureExplorer_13

Pict.1.07 | We can get information: the format provided the path is not supported. And I don’t want downloading bacpac file via ASE now.

Captura_AzureExplorer_14

Pict.1.08 | We can look for details about this blob file

Captura_AzureExplorer_15

Pict.1.09 |And of course for metadata (with information about eg. database)

Captura_AzureExplorer_16

Pict.1.10 | Of course we can use ASE for managing vhd files – my plan is to delete old file.

Captura_AzureExplorer_17

Pict.1.11 | I’m asking my portal to delete this file…

Captura_AzureExplorer_18

Pict.1.12 | ..and I got an error with deleting process. In fact this file “is not free” so I moving for a moment to Windows Azure management Portal.

Captura_AzureExplorer_19

Pict.1.12.1 | As we see this machine (which is tored on blob which we want delete) is stopped. That means: she’s still alive a little.

Captura_AzureExplorer_20

Pict.1.12.2 | Execution ordered: Shut Down this machine

Captura_AzureExplorer_21

Pict.1.12.3 | Execution in progress

Captura_AzureExplorer_22

Pict.1.12.4 |Finally machine is stopped, but…

With this case it is not finish. Because one of vhd disks is still attached to this machine, we cannod delete it via ASE. Workarround for this problem is here: Error deleting VHD: There is currently a lease on the blob and no lease ID was specified in the request

Captura_AzureExplorer_23

Pict.1.13 | In the meantime we can delete something else – like bacpac file.

Captura_AzureExplorer_24

Pict.1.14 | next we can back for our blob, and delete file from menu: Blob/Delete

Captura_AzureExplorer_25

Pict.1.15 | Last step is deleting whole container

 

THANK YOU DAMIAN!

[EN] Sunday Market – SQL Server Maintenance Solution by Ola Hallengren

On last SQLBits 9 – Query Across Mersey (September 2011, Liverpool, UK) I had great opportunity and pleasure to be a host as my SQLBits Helper role for not very famous, but full professional specialist from Denmark (work place)/Sweden (living place) – Ola Hallengreen at his session. Just back for the past for a moment – here is the abstract of his session: How do you do database maintenance in an enterprise environment? I have developed a stored procedure based solution that has become extremely popular in the SQL Server community. In this session I will go through how the solution works, how it can be used in different scenarios for backup, integrity check, index and statistics maintenance and talk some about the background for the design. You can also bring your questions about the solution or about database maintenance in general. And links for video recording is here: Slide Deck 75 KB + Demo Code 3 KB + MP4 Video Low 64 MB + MP4 Video Med 87 MB + MP4 Video High 205 MB +  MP4 Video HD 382 MB.

In this post I want to write some words about his great solution. Great because is powerful tool, and great because is free (like all tools in Sunday Market series). Based on Ola’s web site: The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012. The Solution is based on stored procedures, functions, the sqlcmd utility, and SQL Server Agent jobs.

In one Maintenance Solution script package you can find a lot of scripts in three very important components, where all l are:
Supported versions: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012
Depends on: CommandExecute, DatabaseSelect and CommandLog

SQL Server Backup
Procedure name: DatabaseBackup
Purpose: Back up databases
Example below:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[DatabaseBackup]

 

@Databases nvarchar(max),

@Directory nvarchar(max) = NULL,

@BackupType nvarchar(max),

@Verify nvarchar(max) = ‘N’,

@CleanupTime int = NULL,

@Compress nvarchar(max) = NULL,

@CopyOnly nvarchar(max) = ‘N’,

@ChangeBackupType nvarchar(max) = ‘N’,

@BackupSoftware nvarchar(max) = NULL,

@CheckSum nvarchar(max) = ‘N’,

@BlockSize int = NULL,

@BufferCount int = NULL,

@MaxTransferSize int = NULL,

@NumberOfFiles int = 1,

@CompressionLevel int = NULL,

@Description nvarchar(max) = NULL,

@Threads int = NULL,

@Throttle int = NULL,

@Encrypt nvarchar(max) = ‘N’,

@EncryptionType nvarchar(max) = NULL,

@EncryptionKey nvarchar(max) = NULL,

@LogToTable nvarchar(max) = ‘N’,

@Execute nvarchar(max) = ‘Y’

 

AS

#####  #####  #####

SQL Server Integrity Check
Procedure name: DatabaseIntegrityCheck
Purpose: Check the integrity of databases
Example below:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[DatabaseIntegrityCheck]

 

@Databases nvarchar(max),

@PhysicalOnly nvarchar(max) = ‘N’,

@NoIndex nvarchar(max) = ‘N’,

@ExtendedLogicalChecks nvarchar(max) = ‘N’,

@TabLock nvarchar(max) = ‘N’,

@LogToTable nvarchar(max) = ‘N’,

@Execute nvarchar(max) = ‘Y’

 

AS

##### ##### #####

SQL Server Index and Statistics Maintenance
Procedure name: IndexOptimize
Purpose: Rebuild and reorganize indexes and update statistics
Example below:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[IndexOptimize]

 

@Databases nvarchar(max),

@FragmentationLow nvarchar(max) = NULL,

@FragmentationMedium nvarchar(max) = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,

@FragmentationHigh nvarchar(max) = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,

@FragmentationLevel1 int = 5,

@FragmentationLevel2 int = 30,

@PageCountLevel int = 1000,

@SortInTempdb nvarchar(max) = ‘N’,

@MaxDOP int = NULL,

@FillFactor int = NULL,

@PadIndex nvarchar(max) = NULL,

@LOBCompaction nvarchar(max) = ‘Y’,

@UpdateStatistics nvarchar(max) = NULL,

@OnlyModifiedStatistics nvarchar(max) = ‘N’,

@StatisticsSample int = NULL,

@StatisticsResample nvarchar(max) = ‘N’,

@PartitionLevel nvarchar(max) = ‘N’,

@TimeLimit int = NULL,

@Indexes nvarchar(max) = NULL,

@Delay int = NULL,

@LogToTable nvarchar(max) = ‘N’,

@Execute nvarchar(max) = ‘Y’

 

AS

##### ##### #####

 

From Ola’s WEBSITE You can download separated scripts like:

CommandLog.sql / DatabaseSelect.sql / CommandExecute.sql / IndexOptimize.sql / DatabaseIntegrityCheck.sql / DatabaseBackup.sql 

or get all in one file

 MaintenanceSolution.sql   

 

Enjoy!