Solid Quality Mentor's Blogs

Problemi nella modifica dei Job con SSMS di SQL Server 2008 R2

dmauri — 2010-09-06T19:02:40+02:00

Se alla modifica di uno step di un job, dopo aver installato SQL Server 2008 R2, ed in particolare il Management Studio, vi capita di ricevere questo fastidioso errore, in modo assolutamente saltuario:

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)

significa che è ora di installare la Cumulative Update 3

http://support.microsoft.com/kb/2261464/en-us

che, tra le altre cose, lo risolve, come documentato in questa KB:

http://support.microsoft.com/kb/2315727/

SQL Server Data Mining Architecture

MarkTab — 2010-09-04T13:00:26+02:00

Data Mining with Microsoft SQL Server 2008 Review Chapter 15 As the authors state, if you are only interested in data mining analysis you could skip this chapter. I believe this book leans toward the Microsoft technology, and therefore the goal of the book is not necessarily to make someone a better data mining analyst [...]

Presenting Online Wednesday September 8

MarkTab — 2010-09-03T20:14:06+02:00

I will be presenting online on Wednesday September 8. The name of the presentation is Data Mining with PowerPivot 2010. More dtails are at http://bi.sqlpass.org

SQL Trace Collection Set for the Management Data Warehouse

James Miller — 2010-09-02T21:26:00+02:00

There are a number of helpful links on the net to get you started using the Management Data Warehouse (MDW) which was released in SQL 2008, and continued in SQL 2008 R2. However I have not seen a lot written to date on the 'SQL Trace Collection Set' which has prompted this blog entry. The 'SQL Trace Collection Set' can provide unique and insightful analytics, which can be used for highly targeted performance tuning, and for examining performance trends over time.

I'll start by pointing you into the right direction for creating the SQL Server Management Data Warehouse, and then turn the focus to configuration of the SQL Trace Collection Set.

Setting-up the Management Data Warehouse (MDW) and Data Collection Components
The MDW is designed to collect performance related statistics that is stored in a SQL Server 2008 database. SQL Server 2008 has a ‘Configure Management Data Warehouse’ wizard that helps you to get started with this SQL Server performance monitoring extension:

Note: You will want to initiate this set-up process on the SQL Server instance that you would like to use as the official ‘Management Data Warehouse’ repository. This instance can be separate from the SQL Server instance(s) that are being monitored for this performance data.

This wizard will step you through creating the MDW database, and configuring the standard System Data Collections Sets (Disk Usage, Query Activity, and Server Activity). For ‘step-by-step’ assistance on using this wizard refer to these articles on msdn and/or Database Journal.

Note: Any name can be provided for the resulting ‘Management Data Warehouse’ database. As a matter of practice, ‘MDW’ is often used, and is assumed in the following examples. Also, be aware that once you configure and enable the MDW there is no method to remove the corresponding SQL Agent jobs which are created. You can disable data collection, which will also disable the SQL job, but they will continue to reside in SQL Agent’s list of jobs.

Once you have the configured the Management Data Warehouse for your environment, you will want to manually add the ‘SQL Trace Collection Set’ since it is not automatically set-up by the MDW wizard. As mentioned earlier, the ‘SQL Trace’ collection set will provide key metrics such as ‘Query Duration’, ‘Database Reads’, and ‘CPU Time’ that will be populated within the MDW database.

Configuring the 'SQL Trace' Data Collection Component
Albeit a slight detour, the best way to set-up the ‘SQL Trace Collection Set’ for the Management Data Warehouse is to use ‘SQL Server Profiler’ and create an active trace. This trace definition will then be saved as a script which can be used to configure the SQL Trace data collector. To get started, start SQL Server Profiler:

Once in ‘Profiler’, click on ‘File’, ‘New Trace…’, and after connecting to SQL Server you will be able to set the desired columns and events to capture. The standard (default) template automatically sets most of the events and columns of interest for query monitoring. To minimize server overhead for data collection, you will most likely want to remove all events with the exception of ‘RPC:Completed’ and ‘SQL:BatchCompleted’. You can click on the ‘Events Selection’ tab to specifically choose the items to collect:

After clicking on the Run button, you can then immediately stop the trace, since you only need the extract the definition. You can then export the trace definition script for a trace collection set as shown below:

The resulting script can then be loaded into SQL Server Management Studio, and executed. However before executing, you will want to provide a friendlier name for the collection set, such as ‘SQL Trace’ which is highlighted in the code snippet below:

...

-- *** with the name you want to use for the collection set.

-- ***

DECLARE @collection_set_id int;

EXEC [dbo].[sp_syscollector_create_collection_set]

@name = N'SQL Trace',

@schedule_name = N'CollectorSchedule_Every_15min',

@collection_mode = 0, -- cached mode needed for Trace collections

@logging_level = 0, -- minimum logging

@days_until_expiration = 5,
...

Note: the parameter option above: ‘@days_until_expiration = 5’ determines how many days the trace data will be stored in the MDW database before it is automatically deleted. This value can be increased for greater data retention, but keep in mind that this also increases the monitoring overhead of the MDW database.

After running this script you will be see a new Data Collection set called ‘SQL Trace’ (right-click and refresh the ‘Data Collection’ container if necessary). To start the collection set, right click the ‘SQL Trace’ collection, and then click ‘Start Data Collection Set’

You can also double-click on the SQL Trace ‘Data Collection’ to view its’ configured properties:

You will want to be aware of the configurable collection frequency, which in this case is every 900 seconds (15 min), and how long the data is retained in the MDW (the default script is set to 5 days). Because the resulting Trace table ‘[snapshots].[trace_data]’ can be very large, especially in high volume environments, you may prefer to reduce the number of days for data retention. This however lessens the ability to trend reoccurring query patterns. To retain the ability to trend, another practice to consider is to create a simple ETL routine to export (and perhaps summarize) only the trace rows of interest. This is especially beneficial when trending stored procedures, or query patterns over a long period of time.

Note: You can also reduce the size of the resulting trace table by adding filters to the original trace, created in Profiler, and used to create the collection set.

When you start data collection sets, corresponding SQL Agents jobs are created to schedule and manage the data collection in the MDW. The following query will return details of collection sets, including the ‘collection_set_id’ which can be used to correlate with the actual SQL Agent jobs:

Following is a comprehensive list of the SQL Agent jobs that are created to collect, upload, aggregate, and purge data stored in the Management Data Warehouse (MDW) database:

This completes the set-up of the MDW and standard data collectors. In approximately 15 minutes, or based on the collector's schedule, you should notice rows in the table called: [snapshots].[trace_data] in the MDW database.

Querying the [trace_data] Table
Based on the earlier ‘SQL Trace’ configuration, the key measures we are collecting in this MDW table consist of

CPU Time
Disk I/O
Query Duration

These were also set to be recorded based on the completion of the following events:

RPC:Completed
SQL:BatchCompleted

This table you can be queried based on any of the key metrics listed above. For example, the top 10 queries based on ‘duration’ could be retrieved by using the following code:

SELECT TOP 10 *

FROM [snapshots].[trace_data]

ORDER BY [Duration] DESC -- or BY [Reads], or [CPU]

The result set should list the 10 longest running queries that completed based on data collected from all the SQL server instances monitored by this Management Data Warehouse (scroll to the right to see the ‘Server Name’ column). You may chose to filter this query based on a single 'Server Name'.

You may find it helpful to group by a substring of the [TextData] column. This may better illustrate reoccurring queries that perhaps vary within the WHERE clause, but are based on the same table joins and columns. An example of this, using just the first 80 characters of the [Text Data] column, and based on ‘disk reads’ is displayed below:

SELECT TOP 10

COUNT(*) AS [RunCount]

, SUM([Reads]) AS [SumReads]

, LEFT([TextData],80) AS [TextShort]

FROM [snapshots].[trace_data]

GROUP BY LEFT([TextData],80)

ORDER BY [SumReads] DESC

Note: the technique of grouping by the first 80 characters is used here in an attempt to find re-occurring code. You may need to alter this method to better suit the SQL code patterns in your database environment. The objective is to find re-occurring code which carries a high disk I/O (read) cost, and also be able to then identify the origin/source of the query for subsequent tuning efforts.

Another option is to trend SUM and AVG query metrics over time. The following query again groups by the first 80 characters of the [TextData] column to spot a reoccurring query pattern, and then uses a secondary ‘group by’ on date to show a performance trend. A ‘like’ predicate is also placed on the [TextData] column to narrow the query to those referencing the [Sales] table:

SELECT CONVERT(VARCHAR(10), [EndTime], 120) AS [DisplayDate]

, LEFT([TextData],80) AS [TextShort]

, COUNT(*) AS [TimesRun]

, SUM(Duration) AS [SumDuration]

, SUM(Reads) AS [SumReads]

, SUM(CPU) AS [SumCPU]

, AVG(Duration) AS [AvgDuration]

, AVG(Reads) AS [AvgReads]

, AVG(CPU) AS [AvgCPU]

FROM [snapshots].[trace_data]

WHERE [TextData] LIKE '%Sales%'

GROUP BY LEFT([TextData],80), CONVERT(VARCHAR(10), [EndTime], 120)

ORDER BY [TextShort], [DisplayDate]

This I hope gives you an idea of the possibilities for analytics using the [trace_data] table, which is enabled via the 'SQL Trace Collection Set' of the MDW. In a follow-up article (under construction), we will examine additional performance based reporting options that are available when collecting this 'SQL Trace' data.

SQL Trace Collection Set for the Management Data Warehouse

James Miller — 2010-09-02T21:26:00+02:00

...

-- *** with the name you want to use for the collection set.

-- ***

DECLARE @collection_set_id int;

EXEC [dbo].[sp_syscollector_create_collection_set]

@name = N'SQL Trace',

@schedule_name = N'CollectorSchedule_Every_15min',

@collection_mode = 0, -- cached mode needed for Trace collections

@logging_level = 0, -- minimum logging

@days_until_expiration = 5,
...

CPU Time
Disk I/O
Query Duration

These were also set to be recorded based on the completion of the following events:

RPC:Completed
SQL:BatchCompleted

This table you can be queried based on any of the key metrics listed above. For example, the top 10 queries based on ‘duration’ could be retrieved by using the following code:

SELECT TOP 10 *

FROM [snapshots].[trace_data]

ORDER BY [Duration] DESC -- or BY [Reads], or [CPU]

SELECT TOP 10

COUNT(*) AS [RunCount]

, SUM([Reads]) AS [SumReads]

, LEFT([TextData],80) AS [TextShort]

FROM [snapshots].[trace_data]

GROUP BY LEFT([TextData],80)

ORDER BY [SumReads] DESC

SELECT CONVERT(VARCHAR(10), [EndTime], 120) AS [DisplayDate]

, LEFT([TextData],80) AS [TextShort]

, COUNT(*) AS [TimesRun]

, SUM(Duration) AS [SumDuration]

, SUM(Reads) AS [SumReads]

, SUM(CPU) AS [SumCPU]

, AVG(Duration) AS [AvgDuration]

, AVG(Reads) AS [AvgReads]

, AVG(CPU) AS [AvgCPU]

FROM [snapshots].[trace_data]

WHERE [TextData] LIKE '%Sales%'

GROUP BY LEFT([TextData],80), CONVERT(VARCHAR(10), [EndTime], 120)

ORDER BY [TextShort], [DisplayDate]

SQL Trace Collection Set for the Management Data Warehouse

James Miller — 2010-09-02T21:26:00+02:00

...

-- *** with the name you want to use for the collection set.

-- ***

DECLARE @collection_set_id int;

EXEC [dbo].[sp_syscollector_create_collection_set]

@name = N'SQL Trace',

@schedule_name = N'CollectorSchedule_Every_15min',

@collection_mode = 0, -- cached mode needed for Trace collections

@logging_level = 0, -- minimum logging

@days_until_expiration = 5,
...

CPU Time
Disk I/O
Query Duration

These were also set to be recorded based on the completion of the following events:

RPC:Completed
SQL:BatchCompleted

This table you can be queried based on any of the key metrics listed above. For example, the top 10 queries based on ‘duration’ could be retrieved by using the following code:

SELECT TOP 10 *

FROM [snapshots].[trace_data]

ORDER BY [Duration] DESC -- or BY [Reads], or [CPU]

SELECT TOP 10

COUNT(*) AS [RunCount]

, SUM([Reads]) AS [SumReads]

, LEFT([TextData],80) AS [TextShort]

FROM [snapshots].[trace_data]

GROUP BY LEFT([TextData],80)

ORDER BY [SumReads] DESC

SELECT CONVERT(VARCHAR(10), [EndTime], 120) AS [DisplayDate]

, LEFT([TextData],80) AS [TextShort]

, COUNT(*) AS [TimesRun]

, SUM(Duration) AS [SumDuration]

, SUM(Reads) AS [SumReads]

, SUM(CPU) AS [SumCPU]

, AVG(Duration) AS [AvgDuration]

, AVG(Reads) AS [AvgReads]

, AVG(CPU) AS [AvgCPU]

FROM [snapshots].[trace_data]

WHERE [TextData] LIKE '%Sales%'

GROUP BY LEFT([TextData],80), CONVERT(VARCHAR(10), [EndTime], 120)

ORDER BY [TextShort], [DisplayDate]

Bleeding Edge Conference – Gorizia

dmauri — 2010-09-02T19:12:31+02:00

Il 30 settembre ci sarà la “Bleeding Confenrence” a Nuova Gorica / Gorizia. Visto che nell’est dell’Italia non ci sono molte possibilità di assistere a conferenze con comodità, per tutti coloro che sono di quelle parti, direi che questa è un’occasione piuttosto interessante.

La track in inglese (le altre sono in sloveno) è succosa:

Up to the Next Level of Customizability with ASP.NET MVC Dynamic Filters – Dino Esposito
Hardcore .NET Production Debugging - Ingo Rammer
Evolution of data Access: Long Journey from SP to EF4 - Dean Vitner, Bernard Katić
Blazing Fast Queries: When Indexes Are Not Enough – Davide Mauri

http://www.bleedingedge.si/237/Urnik.aspx

Se ci siete, ci vediamo li!

Solid Quality Journal - Agosto

dmauri — 2010-09-01T23:08:52+02:00

E’ disponibile, ancora in modo gratuito per chiunque, l’edizione di Agosto del Solid Quality Journal.

Questo mese c’è anche un mio articolo sul tema NoSQL dato che ormai è un argomento molto caldo. Ci sono anche articoli di Gianluca Hotz su PowerShell e SQL Server, di Francesco De Chirico su MDX e Reporting Services ed un lungo articolo di Gilberto Zampatti (che ormai dovreste conoscere visto che firma l’editoriale della newsletter di UGISS) dedicato a SQL Server in salsa Sharepoint 2010.

Ovviamente tutto in inglese :)

Non perdetevelo, e non dimenticate di farci avere i vostri feedback!

http://www.solidq.com/sqj/Pages/Home.aspx

SolidQ Journal Agosto 2010.

Daniel A. Seara — 2010-09-01T21:50:43+02:00

La puedes leer aquí: http://www.solidq.com/sqj/Documents/2010%20August%20Issue/SQJ%20002.pdf

Y a propósito, estamos en proceso de la versión en español y en portugués.

Pronto habra novedades!!!!

Pergunta Geek

Fabiano Amorim — 2010-09-01T20:38:22+02:00

O que você acha que a consulta abaixo irá retornar?

use tempdb
go

if object_id('t1') is not null

drop table t1
create table t1 (Col1 Int)
insert into t1 values(1)
GO

select * from tempdb . . t1
where tempdb . dbo . t1 . col1 = 1

SQL Azure farà si che la Normalizzazione sia una pratica richiesta da tutti?

dmauri — 2010-09-01T10:54:04+02:00

Ho scritto un articolo a proposito sul mio blog in inglese, dopo aver letto un’analisi di quanto costa memorizzare una riga in SQL Azure.

Il punto fondamentale di tutto è, come avrete capito, la normalizzazione dello schema di un database. Semplicemente sconosciuta o ritenuta inutile da molti sviluppatori, con SQL Azure potrebbe ritornare importate anche ai loro occhi (o – meglio – agli occhi dei loro manager), come strumento per risparmiare spazio e quindi soldi (Posto che tale risparmi è solo un effetto secondario, e non il principale, della normalizzazione).

Risparmio che può andare da un 30 ad un 80%, e tenendo conto che il costo di storage per un database di 150GB è di 18000$ l’anno, non si sta parlando di bruscolini.

In aggiunta al post, i feedback lasciati sono molto interessanti, e ve ne consiglio vivamente la lettura:

http://sqlblog.com/blogs/davide_mauri/archive/2010/08/30/will-sql-azure-make-normalization-popular-again.aspx

SolidQ Journal for August is now available

Greg Low — 2010-08-31T14:26:00+02:00

Well, we only just managed to squeek it into August but the articles for the August edition of SolidQ Journal are now on our global website. You'll find them here: http://www.solidq.com/sqj/Pages/Home.aspx I'm really looking forward to reading some of...(read more)

Fabiano VS Política no Brasil

Fabiano Amorim — 2010-08-30T20:44:00+02:00

Pois é esse vídeo não tem nada a ver com SQL Server, mas como estamos em tempo de eleições no nosso País achei MUITO bom o vídeo abaixo e quero compartilhar com vocês…

Eu acredito no Brasil e tenho orgulho de ser brasileiro, estes dias eu até brinquei com isso no twitter @mcflyamorim mas estou cansado ver e ouvir tanta MERDA e um bando de idiotas egoístas e egocêntricos falando na televisão em horário político com campanhas absurdas, e candidatos tão bizarros que chegam a ser tristes.

Precisamos de educação, e de um Milagre para mudar nosso país, só posso orar para que Deus tenha misericórdia de nós e que Ele mude nosso país, e acabe com esta miséria cerebral que se instalou em nós…

http://www.youtube.com/watch?v=dAQkMjebkeA

Will SQL Azure make Normalization popular again?

Davide Mauri — 2010-08-30T19:46:41+02:00

Normalization should be very popular and widespread. Unfortunately I can say that the 99% of developers that work with database (and thus design the database schema) don’t know it. Even worse they don’t even realize that normalization is a key point in application development.

AFAIK developers and project managers don’t care too much of normalization because they think that it only helps in lowering the storage required by the database and nothing more. Since today space is cheap, why bother of normalizing database?

As already told, normalization is not a way to spare space, but is the key to preserve data integrity and to have overall good performance. Of course, since a part of the process is related to remove redundancy, the result is that space is also saved.

Now, with SQL Azure, space won’t be that cheap anymore. An interesting post by the SQL Azure team, show how much cost storing a row in SQL Azure database, given that the prices for 1GB is set to 9.99$.

And that’s the interesting part, where normalization returns to be an interesting practice to put in place. By designing table using correct data types and normalizing the schema correctly you easily gain from 30% to 80% (as a very general rule of thumb, depending on the data model and the data you have to store).

Now, let’s make some simple calculations. Imagine that we have a three database of 50GB each. Every month the cost is 3 * 499.5$. That sum up to 17,998.2$ per year. Let’s say 18,000$ to make it easier. Sparing 30% means that you can save 5,400$ per year.

Given that I’ve seen database smaller than 100GB only in small companies, I can tell you that sparing 5,000$ for them is very important!

This turns also to be true also for bigger database, where, of course, the money spared is proportional. I’ve seen tables with more than 100 columns, many of them completely redundant or “put here just in case”, like the ColInt1, ColInt2 and so on.

So, now that space means money, maybe normalization will become popular again.

I really hope so, because this will also lead to better data and higher quality solutions.

I didn’t mention this, but keep in mind also that you’ll pay space used by indexes. So the worse your table is design the more you’ll pay for any index you’ll add, since they will be bigger that what they could have been if the database was normalized.

Engineering BI (Or “On my PASS seminar”, prelude)

Davide Mauri — 2010-08-28T15:27:33+02:00

I’ve always stated that creating a software solution is half and art and half an engineering process. Just like building an house, or a car, or any mechanical or electrical device, the art is that part of the work where someone has to create something new to satisfy the customer need.

Architects are the figures that plays the artistic role. Sure, they don’t re-invent everything from scratch every time: Architectural Design Pattern are here to demonstrate that ideas can be reused successfully. But, still, architects put patterns and new ideas together in order create something new.

Engineers are the more practical side of this role-playing game and they are more concerned with processes and implementation of the solution so that it can feasible in first place, but also be maintainable, predictable, replicable and cost-effective.

In the software industry we have Architects but my feeling is that we’re missing the Engineers. This means that a lot of what is being produced from the software industry is more an art than a science and this drives to the fact that the quality, and the maintainability of the final product is completely dependent on the people working on it. In addition to that, since there are no standards to follow, each one will do as it wish. If you put several people working together you’ll obtain a cauldron of different styles that leads inevitably to higher maintenance cost and low stability and predictability. (I don’t know if you have the same feeling, but I would like to have a feedback on this, if you care to share.)

Of course I’m generalizing a lot here – I complete discussion on this matter would easily fill a book – and there are several exception to this, but I think I can say without any doubt that if I had to make a picture of my perception of the software industry I’d draw a conglomerate of craftsman workshops and not a factory district.

In the last years I struggled a bit with this lack of the “engineering” discipline: particularly I found an evidence of this situation in BI projects. While there’s at least two well-know architectural approach (Kimball and Inmon), with other options rising (like the Data-Vault), but still there are no rules and tools that can make the creation of a BI solution more standard.

In order to be able to add people to an existing project quickly, being sure that they would create something that anyone of the existing team can easily understand, that the infrastructure of all of our BI solution would be similar in order to make its development faster and easier to maintain, I’ve defined a sets of rules and tools that helps to make the work easier and higher in quality.

The idea is that the rules will define the standards, and once we have the rules, we can also create the tools to apply them quickly. This will shorten a lot the time need to build the solution (which is the part with the lowest added-value) and leave much more time to design it (where there is the “core” value of it).

I’ve got this idea watching “How it’s made” on Science Discovery channel, watching people building the awesome Airbus 380. If such complex machine can be assembled by several people everyone doing a small part of the work, we should be able to do it also with BI solution! We “only” need rules that will drive organization of work and tools to make work easier and faster for anyone. Of course this also leads to a solution that is flexible enough to accomodote quickly new customers requests or changes of direction.

Apply the idea to Business Intelligence and you’ll have and “Engineered BI Solution” which will be both sound and agile. I like to call it “Adaptive”. That’s what I’ve already presented on PASS European Summit 2010 in an specific session that had very good feedbacks and that’s what I’m going to show in my “Creating a BI Solution from A to Z” post-conference seminar (8 hours of deep dive, blending theory and practice, architecture and engineering, making it a unique session of its kind), in my next BI Virtual Chapter meeting (more on this in another post) and also in the next posts of this series.

If you’re new to BI or you’ll starting a new BI project in the next months, or you simply want to learn more on this approach, don’t miss the opportunity, and share your feedback!

Data Mining with SQL Server Integration Services (SSIS)

MarkTab — 2010-08-28T13:00:25+02:00

Data Mining with Microsoft SQL Server 2008 Review Chapter 14 So last week was about Analysis Services, and the most relevant cousin technology to data mining within the Microsoft BI stack. This week is about Integration Services, what I conisder the GUI-driven service facility for moving and manipulating data. Manipulation is not a evil action [...]

Speaking in Raleigh NC for SQL Saturday #46

Andrew Kelly — 2010-08-28T05:16:30+02:00

I hope to see a bunch of you in Raleigh on the 18th of September for SQL Saturday #46. I will be giving a presentation on Query Plan Reuse but there will be tons of good sessions from lots of great speakers. Check it out here: http://www.sqlsaturday.com/46/eventhome.aspx...(read more)

Presenting at Atlanta MS BI September 27

MarkTab — 2010-08-28T00:00:39+02:00

Hi Everyone This post is a combination of facts: Atlanta now has a Microsoft Business Intelligence Users’ Group I will be the speaker for the second meeting on September 27 I was prompted to blog about this topic by another announcement, that my friend Andrew Brust from NYC is soon launching a similar group (I recently [...]

BackUp Compression no SQL Standard Edition ?

Fabiano Amorim — 2010-08-27T22:00:22+02:00

Dica interessante...

Para tristeza de muitos a Microsoft limitou o Backup Compression para apenas versão Enterprise... Acho que depois de muito reclamarem, no SQL Server 2008 R2 a versão Standard já suporta esta feature. :-) ... Show...

http://technet.microsoft.com/en-us/library/bb964719.aspx

SQLU Summit 2010 Sesiones grabadas y contenidos

Daniel A. Seara — 2010-08-27T11:56:26+02:00

Hola!

Si quieres ver algo de lo que se hizo en el SQLU Summit 2010, aquí hay algunas sesiones grabadas http://summit.solidq.com/madrid/2010/Paginas/Sesiones%20grabadas.aspx

Y también las presentaciones efectuadas en http://summit.solidq.com/madrid/2010/Paginas/Materiales-Summit-2010-Madrid.aspx

Publicadas varias de las sesiones de nuestro Summit 2010

Miguel Egea — 2010-08-27T11:00:10+02:00

Este año, en el evento de formación que todos los años organizamos en Solid Q todos los años, como experiencia piloto hemos grabado las sesiones, no están todas, y no tienen calidad video, sino live meeting. Aún así creo que son muy interesantes. Os dejo el enlace

Custom top navigation in SharePoint 2010

Guillermo Bas — 2010-08-26T20:14:00+02:00

Last month I was trying to override the SharePoint 2010 top navigation, but I couldn't. I thought that it would be resolved for the 2010 version but It wasn't. I was asking for a couple of days in technet forums and mailing lists but nobody told me about any workaround to this particular issue.

However, It wasn't all lost… Reading exhaustively the msdn documentation about the AspMenu Control (top navigation control that inherits from System.Web.WebControls.Menu) here (insert link) I found an answer to have my own native and dynamic top navigation in SharePoint 2010.

The Answer: Instantiation

Yes, we can instantiate an AspMenu Control and also modify any property of this instance, even the site navigation Data Source.

How can we implement our own Top Navigation? This is my approach. We create a new class that inherits from Menu class (the control what AspMenu inherits from). I named the new custom control CustomTopNavBar.

Imports Microsoft.SharePoint
Imports Microsoft.SharePoint.Publishing
Imports Microsoft.SharePoint.Publishing.Navigation
Imports Microsoft.SharePoint.WebControls

<DefaultProperty("Text"), ToolboxData("<{0}:CustomizeTopNavBar runat=server></{0}:CustomizeTopNavBar>")> _
Public Class CustomizeTopNavBar
    Inherits System.Web.UI.WebControls.Menu

Inside this class we declare and instantiate an AspMenu and a custom PortalSiteMapDataSource that will be provide the SiteMap navigation to our Top Navigation bar.

    Dim solidQCookieName As String = "SolidQRegionCookie"
    Dim tempSolidQCookieName As String = "TempSolidQRegionCookie"
    Dim customMenu As AspMenu = New AspMenu()
    Dim siteDataSource As PortalSiteMapDataSource = New PortalSiteMapDataSource()

Overriding the CreateChildControls method we can configure any property of our Top Navigation bar. If we want to make It more flexible, we can “expose” Its properties at the markup page (this case I used it at the SharePoint Master Page) like I did with the AccessKey assigning the parent class property value to the customMenu property. Most of properties are common with Menu class and can be configured by this way.

    Protected Overrides Sub CreateChildControls()
        customMenu.EnableViewState = False
        customMenu.AccessKey = Me.AccessKey
        customMenu.UseSimpleRendering = True
        customMenu.UseSeparateCSS = False
        customMenu.Orientation = Orientation.Horizontal
        customMenu.StaticDisplayLevels = 1
        customMenu.MaximumDynamicDisplayLevels = 1
        customMenu.SkipLinkText = ""
        customMenu.CssClass = "SolidMenu"

About the navigation DataSource of our custom menu we have two options here.

First option is use the native DataSource through its ID “SiteMapGN”.

        'customMenu.DataSourceID = "SiteMapGN"

Second option is to customize our PortalSiteMapDataSource making our menu source navigation dynamic.

        siteDataSource.EnableViewState = False
        siteDataSource.SiteMapProvider = "GlobalNavigation"
        siteDataSource.StartFromCurrentNode = False
        siteDataSource.StartingNodeOffset = 0
        siteDataSource.ShowStartingNode = False
        siteDataSource.TrimNonCurrentTypes = NodeTypes.Heading

        If Page.Request.Cookies(tempSolidQCookieName) IsNot Nothing _
            AndAlso Not String.IsNullOrEmpty(Page.Request.Cookies(tempSolidQCookieName).Values("RegionCode")) _
        Then
            siteDataSource.StartingNodeUrl = "/" & Page.Request.Cookies(tempSolidQCookieName).Values("RegionCode")
        ElseIf Page.Request.Cookies(solidQCookieName) IsNot Nothing _
            AndAlso Not String.IsNullOrEmpty(Page.Request.Cookies(solidQCookieName).Values("RegionCode")) _
        Then
            siteDataSource.StartingNodeUrl = "/" & Page.Request.Cookies(solidQCookieName).Values("RegionCode")
        Else
            siteDataSource.StartingNodeUrl = "/"
        End If

        customMenu.DataSource = siteDataSource

        Me.Controls.Add(customMenu)

    End Sub

At the second line of this piece of code we assign the SiteMapProvider to the PortalSiteDataSource, with this we are providing our navigation with the items that we entered at the Navigation section in each Site Settings section, avoiding to create complicate xml for our own SiteMapProvider. But we maintain dynamic this menu changing this the StartingNodeUrl property of our data source forcing the Top nav to change depending on context cookies that contains our RelativeURL context.

Here is the key: Replace completely the Menu control for our own AspMenu Control, override the base render and render only our custom AspMenu control.

    Protected Overrides Sub Render(ByVal writer As System.Web.UI.HtmlTextWriter)
        customMenu.RenderControl(writer)
    End Sub

End Class

SQL Saturday Raleigh NC — September 18

MarkTab — 2010-08-25T13:00:34+02:00

I have been added to the schedule for SQL Saturday 46 in Raleigh NC on September 18. I found out early on September 24, and tweeted the news. Already, the mention of “Raleigh” earned me a new Tweet follower: Destination Raleigh. I also was retweeted by some SAS bloggers too because I mentioned #SAS (even more [...]

SQL Server 2008 R2: MDS setup HTTP Error 500.19 - Internal Server Error - Error Code 0x80070021

Greg Low — 2010-08-23T04:41:00+02:00

I've (fortunately) ended up with a new notebook recently and had to reinstall everything. One problem I ran into was with Master Data Services. The config program seemed happy but it shouldn't have been. What threw me was that it seemed to have a dependency...(read more)

Mining OLAP Cubes

MarkTab — 2010-08-21T13:00:33+02:00

Data Mining with Microsoft SQL Server 2008 Review Chapter 13 Outline for this blog post: Where is OLAP among BI acronyms? Is the Unified Dimensional Model (UDM) a cube? Creating a data mining model from OLAP sources Where is OLAP among BI acronyms? As the authors state on page 399, OLAP stands for Online Analytic [...]

Solutions to the Travel Puzzle

Itzik Ben-Gan — 2010-08-20T16:50:00+02:00

Itzik covers solutions to the T-SQL challenge provided in his previous post.

QO Generating a Bad Plan

Fabiano Amorim — 2010-08-20T14:36:20+02:00

Hi, I just created a connect Item about a query that IMHO is a problem with Query Optimizer.

I appreciate if you take a look and vote.

https://connect.microsoft.com/SQLServer/feedback/details/587729/query-optimizer-create-a-bad-plan-when-is-not-null-predicate-is-used

Regards

New SolidQ Managed Metadata Exporter in CodePlex

José Quinto Zamora — 2010-08-19T19:32:00+02:00

Hi all,

I have plublished in CodePlex a tool that helps you to manage managed metadata in SharePoint 2010. It can create and delete terms and term sets, import term sets, EXPORT term sets in a .csv file, ... This solution also shows how to access Managed Metadata by the web service TaxonomyClientService.

Download here: http://metadataexportsps.codeplex.com/

Regards!

Virtualizzare SQL Server

dmauri — 2010-08-18T18:57:53+02:00

La virtualizzazione è ormai una richiesta costante. Non sempre è bene virtualizzare SQL Server e quando si decide di procedere in tal senso è bene farlo facendo molta attenzione a fare tutto correttamente, pena un pesante impatto in negativo sulle performance (non tanto relativo alla virtualizzazione in se, quanto al fatto che spesso e volentieri si consolida un pò troppo e senza fare i dovuti test di performance, il che porta inevitabilmente verso dei problemi).

Se avete deciso che volete virtualizzare SQL Server, questo eClinic free (un corso online, in sostanza) è sicuramente un buon punto di partenza, sopratutto se non avete molta familiarità con HyperV:

Clinic 10528: Introduction to Virtualization of Microsoft SQL Server
https://www.microsoftelearning.com/eLearning/courseDetail.aspx?courseId=195173&tab=overview

MicroStrategy Symposium – Eu vou!

Fabiano Amorim — 2010-08-18T15:18:29+02:00

Galera, no final deste mês estarei em São Paulo para participar do MicroStrategy Business Intelligence Symposium que será no dia 31 de agosto.

http://www.microstrategy.com/events/symposium/detail.asp?eventid=7153

Vai ser show… espero conheçer mais de BI, na minha opinião a anos a Microstrategy tem batido na MS no quesito FrontEnd… a Microsoft tem trabalhado para melhor isso, mas ainda esta longe de ter um frontEnd como o da MicroStrategy…

E você o que acha?

Abraços