Wednesday, May 27, 2015

Before SQL Saturday in New York City

Next Saturday, May 30st, we have a big event in NYC.

I do not know the reason why NYC skipped SQL Saturday the last year.
I'd guess there was a problem with a venue.

About the venue. The old Microsoft office was pretty small to host SQL Saturday. Sponsor's tables were in a narrow hall-way and people had been squeezing through. See my old blog post with pictures.
This time I expect everything will be different. Previous SQL Saturday in NYC had only 46 scheduled sessions, and now we have 13 tracks in their schedule!

How many people they expect 400? 500? Or even more?


That will be my third SQL Saturday in NYC and the first one I will be speaking there. I think that is a big honor to be along with such a big names like  Bradley Ball with "", Grant Fritchey with "" and many others.
That would be pretty hard for me to compete for an audience, but I will try.

My session will be about "Extended Properties" of SQL Server objects.
What are they? When you first time opened your SQL Server Management Studio and created your first table, you probably wanted to make a description to one of your fields to note what has to be there as a reminder for you or a guidance for the one who will be after you.
However, when you move your table to production all your comments disappeared, you become frustrated and never used it again.
However, your project manager required you to create a full documentation for all your objects with understandable descriptions, which added even more frustration to spend your valuable developer's time for something that can be done by Business Analyst.
In my session I will show the structure of SQL Server objects with their Extended Properties (aka Descriptions), you will learn that one object can have more than one property/description and finally, I'll share with you a way to automatically document your database.

I know it will be hard decision for you to decide which session to attend Bradley Ball and Grant Fritchey are wonderful speakers, but I'd perosonally go for Jorge Novo with his SSIS Framework or Ravi Kumar with SSRS.
Hopefully, my session will have enough interest to fulfill all 18 seats in the Ambassador room on the sixth floor (That how I interpreted "6-18" in a room name) at 9:15.

Now will go through some later sessions.
I do not want to touch sessions in big rooms, such as "Central Park", "Radio City", "Music Box", "Winter Garden" and "Broadway". You can guess that organizers were trying to make a good job and use bigger rooms for speakers/sessions they think will attract most of the audience.
So, will only talk about hidden gems in smaller rooms.

Second schedule row at 10:35.
Will go for "Geospatial Data Types in SQL Server 2012" by Leonard Lobel. Would love to go to Tim McAliley's auditing of Azure solutions or Kevin Feit's "Predicting Customer Response".

Third schedule row at 01:05.
That will be the time for best sessions. It is hard to decide to choose between Sam Vanga with SSIS under the Hood or Konstantin Melamud with In Mepory tables performance, while on the sixth floor will be about Big Data by Josh Luedeman, Regular Expressions by Sean McCown and Dynamic SQL by Edward Pollack.

Fourth schedule row at 02:25.
To learn real SQL you have to go to Kennie Pontoppidan's "Anti Pattern" session and if you are DBA then Jennifer McCown's session about automation of your job by Power Shell is must go.

Fifth schedule row at 03:45.
I personally interested in Daniel Lewandowski's SSAS Security, but might prefer  Stephane Frechette with Database Graphing, which should be must go for all DB architects.
There also will be Microsoft advertizing session on the sixth floor on How to get MS certification by John Deardurff. If yuo are tired at the end of the day then it is for you.

Anyway, event is too big to describe it in a single post.
Be tuned, see you there and look at my event pictures next week.



Thursday, May 7, 2015

Two days before SQL Saturday in Baltimore. Main prize is finalized.


Before the Great SQL Saturday in Baltimore I did a survey about what kind of books people would like to get at the event as a prize and what the main raffle prize should be.

60 People helped me to do the survey. Here are the results:
As you can see the winner was "Microsoft SQL Server 2014 Business Intelligence Development Beginners Guide"  by Reza Rad :
The second one is "Professional Microsoft SQL Server 2014 Integration Services" by Brian Knigh:
The third book was "Professional Microsoft SQL Server 2012 Analysis Services with MDX and DAX" by Sivakumar Harinath:

All three books will be included in Sessions' door prizes. And not only these books, we have a lot of other ones, which even not in the list. Some books will be left for final raffling too.

The question about main raffle prize collected 58 responses:


The winner is "Samsung - Galaxy Tab 3 10.1", BUT ....
Hold the breath .....

The  Second one, which is "Amazon - Fire 7" is also in the play!!!
During the survey I left a free field for that question and three people mentioned "SurfacePro" for the main prize.

Common guys, I'd love to give it to you, but it is a priority of our sponsor Microsoft to bring it to SQL Saturday. Hope they will do it this time too.
Would you like to see it?


And by the way, if did not register for SQL Saturday in Baltimore, you are probably too late.
We count hours until the event.

Wish to see all of you there!

Monday, April 27, 2015

Doing SQL Saturday in Baltimore



There is almost nothing about SQL Server in that post, other than just about brand new - first time "SQL Saturday in Baltimore".


FAQ:

What is SQL Saturday?
- SQL Saturday is FREE full day learning event about SQL server technologies and around.

What "BI Edition" means?
- We decided to make our first SQL Saturday event dedicated to "Business Intelligence". That means we give more time not for SQL Server engine, administering or SQL programming, but to SSIS, SSAS and SSRS technologies. We also include different types of reporting, such as: SharePoint and Office 365.

Why do I have to attend SQL Saturday in Baltimore?
Here are cases when you should go:
- If you are working with SQL Server as DBA or Developer;
- If you are Java/VB/.NET or any other developer who uses Database Server on the back end;
- If you are Business Analyst, whose job is to analyze the data and make conclusions and predictions;
- If you are Team Lead or Project Manager, whose team is using any database technologies;
- If you are looking in a new career and want to learn about new technologies;
- If you are a student and want to work in IT;
- Finally, if you want to be on the top of the edge of technology and want your head above  of all your peers.



If you are interested in participating follow the link and click "Register": http://www.sqlsaturday.com/395/eventhome.aspx

Tuesday, March 24, 2015

SQL Monitoring Tools - Idera

That post will start the series of SQL Monitoring Tools' reviews. Product installations and overall experience.

Will start with Idera (V.9.0.0.1189 x64).
Why "Idera"? - because that company is almost always sponsoring SQL Saturday events, which help to develop local SQL talents.


At first, I've looked at Idera.com and found "SQL Diagnostic Manager".
In order to download I had to register and provide my email and phone number.

When I click to download ZIP file the page was loading for several minutes before the actual download started.

After extracting Executable file and starting it I was asked for another place to unpack. That seems not very convenient, everything could be packed into the ZIP file, now I have two copies of installation.

When installation started and I got following window I was little bit confused what do I have to do:
After I've figured out which option to choose I've got the window that I couldn't comprehend:
In my assumption here I have to specify parameters of my workstation.
Two big questions are not answered: Why do I have to create "Idera_Service_Account" with no rights and what port 9292 stands for?

After creating dummy "Idera_Service_Account" and specifying in the form, I was asked to accept the License Agreement.
Then I provided installation path and hit another bump:
The installer just asked me for a service account a minute ago.
Can I use the same account or it should be different?
Why it can't run under current system account? HELP!!!!

Obviously, the same account did not work. I had to put my domain account.
Can anybody guarantee that info was not sent immediately to hackers or not stored locally in the easy to break format?

Mystery of port 9292 is solved:


Have a bump again:

After Specifying the SQL Server instance Installer was satisfied with previously entered credentials.
Two more screens/clicks and installation has started and finished almost immediately.

What do I have to do next?
Search in Start Menu brought me hidden "Idera Dashboard", which was actually a link to following location: https://localhost:9291

Now I have to provide my credentials again...
 After logging in I've finally got to "Idera Dashboard". Is it the one?
In order to try Idera SQL Monitoring I went to "Manage Instances" tab:
I did not expect to see anything in the list, but I couldn't locate "ADD" button anywhere.

However I've found "Help" button.
Search on "Add SQL Server Instance" lead me to only one page: "http://wiki.idera.com/pages/viewpage.action?pageId=38339658" - Why?

Then I realized that there are two search options and only one of them really works:


Finally I've found how to Register new SQL Server:
Right here I've stopped. Help document states that I have to do something obvious, which does not exist in the menu.

Almost gave up when found hidden installation window. It looks like installation is not finished yet.

That installer looks differently, it asked me to accept license agreement again. Why?

It looks like  previous install was "Idera Dashboard" installation and that one is actual "Diagnostic Manager":

After couple of screens I had to provide my local server name again:

But live is not so easy. While Idera Dashboard easily found my server "Diagnostic Manager" gave me following message:

Very nice. After I Enabled "TCP/IP" protocol it recognized my server and asked for my credentials again. I could use SQL server Authentication, but was too lazy to create a new account.

It looks like it was the last challenge.The fact that two installers do not pass data to each other and connect to servers.

Finally got to "Diagnostic Manager" screen.
Added new server. After all previous stupidity it was pretty straight.

At start, "Diagnostic Manager Console" immediately scared me by red crosses:

 I've tried following simple query:
SELECT
    physical_memory_kb/1024 AS [Physical Memory (MB)],
    committed_target_kb/1024 AS [Committed Target Memory (MB)]
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

It returned me the following result:
That means Server Memory is not allocated 100% to the SQL Server.
However, it is possible that other processes eat the rest of the memory and server is paging to the disc.
I've logged to the server and checked current state of the memory:
Obviously, we still have 9 Gb of free memory.
What 100% "Diagnostic Manager Console" refers to?

Looking at "Memory branch" to discover the problem:

There is no case that 100% of memory is used. As you can see, SQL Server is actually using just little over 60% of all allocated memory. Page Life Expectancy is huge. Everything looks healthy.



Now I'm trying to discover tool's functionality.
That is a real time view of SQL connections:

Real time view of system resources:

I also could go through all databases' configurations, their sizes, file allocations, tables' and indexes' sizes, backups and mirroring.
Tool allows to rebuild indexes and update statistics.

Next area is Services: Agent, Full-Text Search and Replication. There is "Critical Error" warning sign because the monitor can't access to the server for service monitoring. Who said it should?

And the last one is logging area for SQL Server and Agent logs.

Here is the full tree of available metrics:
As part of my evaluation I've tried to chat with help desk regarding "100% Memory usage" alert.
Unfortunately, the guy at the help desk couldn't help because all evaluations are serviced only by sales personal. So, I have to use email or phone and that would be "advertizement service", not the real one  I was trying to evaluate.

At the end I want to measure my experience for the product on the scale of 10:

Installation 5 (because of 5 problems and reasonable questions during the process)
Web interface (Dashboard): 1 (can't use it at all)
Diagnostic Manager: 8 (false memory alert and inability to acces to services alert)

General impression: Idera Diagnostic Manager provides pretty accurate real time monitoring of Key SQL components, but it is not very usable to do monitoring from the historical perspective.
That is "nice to have" tool when you constantly dealing with problems, but in stable environment you can use it only as alerting tool.

















Monday, February 23, 2015

Drawing a Hyperbola in SSMS

<= Drawing a Parabola in SSMS


The last and most common shape of motion in the space is Hyperbola.

Small objects from outer space change their orbits insignificantly under heavy gravitation of bigger bodies.

Here is the formula I've used to draw hyperbolas:
Where:
X and Y - Hyperbola's coordinates;
A and B - imaginary central point;
L - slop.


Here is a classic hyperbola script for SQL Server:

DECLARE @MP VARCHAR(MAX)='';

DECLARE @l DECIMAL(8,4) = 1;    -- Slope
DECLARE @bx DECIMAL(8,4) = 0;    -- X-Center point
DECLARE @by DECIMAL(8,4) = 0;    -- Y-Center point
DECLARE @s DECIMAL(8,4) = 10;    -- X-Span of Hyperbola
DECLARE @Step DECIMAL(8,4) = 0.1;    -- Step
DECLARE @k DECIMAL(8,4) = 0;

DECLARE @cx DECIMAL(8,4) = -@s;    -- X-Current point
DECLARE @x1 DECIMAL(19,9);
DECLARE @y1 DECIMAL(19,9);
DECLARE @x2 DECIMAL(19,9) = @cx + @bx;
DECLARE @y2 DECIMAL(19,9) = @by + @k*@x2 + @l / (@x2 - @bx);

WHILE @cx < @s
SELECT
    @cx += @Step,
    @x1 = @x2, @y1 = @y2,
    @x2 = @cx + @bx,
    @y2 = CASE @cx WHEN 0 THEN @y1
                ELSE @by + @k*@x2 + @l / (@x2 - @bx) END,
    @MP = @MP + CASE WHEN @x2 = @bx  
                OR (@x1 <= @bx and @x2 >= @bx ) THEN '' ELSE '('
        + CAST(@x1 as VARCHAR) +  ' '
        + CAST(@y1 as VARCHAR) + ','
        + CAST(@x2 as VARCHAR) +  ' '
        + CAST(@y2 as VARCHAR) + '),' END ;

SELECT CAST('MULTILINESTRING(' + LEFT(@MP,LEN(@MP)-1) + ')' as geometry);

Here is the result of that script in SSMS:

By adjusting variables you can get variety of hyperbola shapes:

DECLARE @l DECIMAL(8,4) = -2;    -- Slope
DECLARE @bx DECIMAL(8,4) = 12;    -- X-Center point
DECLARE @by DECIMAL(8,4) = 3;    -- Y-Center point
DECLARE @s DECIMAL(8,4) = 25;    -- X-Span of Hyperbola
DECLARE @Step DECIMAL(8,4) = 0.1;    -- Step
DECLARE @k DECIMAL(8,4) = 0.5;

Sunday, February 22, 2015

Drawing a Parabola in SSMS

<= Drawing an Ellipse in SSMS

Drawing a Hyperbola in SSMS =>


Some asteroids are first and last time visitors in our Solar System.
They travel between stars in Milky Way galaxy and sometimes come so close to our Sun that completely change their orbit.
Usually their orbit is changed in shape of hyperbola, but sometimes they are going by parabola shaped orbit.

The formula I've used is not a conventional one, but it served my needs pretty well:
Where:
X and Y - parabola's coordinates;
A and B - coordinates of parabola's vertex;
L -  parabola's slop;
n - parabola's power;


So, it is a time will draw a Parabola in SQL Server management studio.

Here is a script to draw classical shaped parabola:
DECLARE @MP VARCHAR(MAX)='';

DECLARE @l DECIMAL(8,4) = 1;    -- Slope
DECLARE @bx DECIMAL(8,4) = 0;    -- X-bottom point
DECLARE @by DECIMAL(8,4) = 0;    -- Y-bottom point
DECLARE @p DECIMAL(8,4) = 2;    -- Parabola's Power
DECLARE @Step DECIMAL(8,4) = 0.01;    -- Step
DECLARE @s DECIMAL(8,4) = 5;    -- X-Span of Parabola

DECLARE @cx DECIMAL(8,4) = -@s;    -- X-Current point
DECLARE @x1 DECIMAL(19,4);
DECLARE @y1 DECIMAL(19,4);
DECLARE @x2 DECIMAL(19,4) = @cx + @bx;
DECLARE @y2 DECIMAL(19,4) = @by + @l * POWER(ABS(@x2 - @bx),@p);

WHILE @cx < @s
SELECT
    @cx += @Step,
    @x1 = @x2, @y1 = @y2,
    @x2 = @cx + @bx,
    @y2 = @by + @l * POWER(ABS(@x2 - @bx),@p),
    @MP = @MP + '('
        + CAST(@x1 as VARCHAR) +  ' '
        + CAST(@y1 as VARCHAR) + ','
        + CAST(@x2 as VARCHAR) +  ' '
        + CAST(@y2 as VARCHAR) + '),';

SELECT CAST('MULTILINESTRING(' + LEFT(@MP,LEN(@MP)-1) + ')' as geometry);

As the result I've got that beautiful picture:

By changing slope, parabola power and the coordinates of the bottom point you can get very different parabola shapes.

Here is one example of parameter set:
DECLARE @l DECIMAL(8,4) = -0.5;    -- Slope
DECLARE @bx DECIMAL(8,4) = 7;    -- X-bottom point
DECLARE @by DECIMAL(8,4) = 3;    -- Y-bottom point
DECLARE @p DECIMAL(8,4) = 1.5;    -- Parabola's Power


In this case parabola's bottom point has coordinates (7,3), it has a negative slope and even its power not a whole number.

Try to reshape it by changing variables.

Saturday, February 21, 2015

Drawing an Ellipse in SSMS


<= Building Sky Map in SSMS


Drawing a Parabola in SSMS =>



I was looking at orbits of planets, comets and asteroids and questioned myself if I can reproduce their tracks in SQL Server.

The first try is drawing Ellipse:

DECLARE @MP VARCHAR(MAX)='';

DECLARE @theta DECIMAL(8,4) = 0;-- angle that will be increased each loop
DECLARE @h DECIMAL(8,4) = 0;    -- x coordinate of center
DECLARE @k DECIMAL(8,4) = 0;    -- y coordinate of center
DECLARE @step DECIMAL(8,4) = 2 * PI()/100;  -- amount to add to theta each time (degrees)
DECLARE @a DECIMAL(8,4) = 10;    -- major axis
DECLARE @b DECIMAL(8,4) = 5;    -- minor axis

DECLARE @x1 DECIMAL(8,4), @y1 DECIMAL(8,4);
DECLARE @x2 DECIMAL(8,4) = @h + @a * COS(@theta);
DECLARE @y2 DECIMAL(8,4) = @k + @b * SIN(@theta);

WHILE @theta <= 2 * PI()
SELECT
    @x1 = @x2, @y1 = @y2,
    @x2 = @h + @a * COS(@theta),
    @y2 = @k + @b * SIN(@theta),
    @theta += @step,
    @MP = @MP + '('
        + CAST(@x1 as VARCHAR) +  ' '
        + CAST(@y1 as VARCHAR) + ','
        + CAST(@x2 as VARCHAR) +  ' '
        + CAST(@y2 as VARCHAR) + '),';

SELECT CAST('MULTILINESTRING(' + LEFT(@MP,LEN(@MP)-1) + ')' as geometry);

In this script you can change following parameters:
- Coordinates of the center of an ellipse - @h & @k;
- Lengths of Major and Minor axes' - @a & @b;
- Size of one step incremental angle - @step. That parameter also controls number of steps.

With a step size - 1/100 of a circle and axis' sizes 10 and 5 I've got that pretty Ellipse: