Blog – Fabiano Neves Amorim

Administrando SQL Server 2008 R2 – Mirroring/Resource Governor e BackupCompression

Galera conforme prometido, segue o link para o PowerPoint e os arquivos utilizados na WebCast de hoje.

http://cid-52eff7477e74caa6.office.live.com/self.aspx/Publica/Administrando%20SQL%20Server%202008%20R2%20%e2%80%93%20Mirroring^J%20Resource%20Governor%20e%20BackupCompression

Abraços

Fellows after some time of hard working and to be very busy as usual, I’m back with the operator of the week articles at simple-talk,

Please take a look,

http://www.simple-talk.com/sql/learn-sql-server/showplan-operator-of-the-week—stream-aggregate/

Please, votes are wellcome 🙂

Thanks

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

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

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

Quick post…

I put a comment in the Greg’s article on Simple-Talk, http://www.simple-talk.com/sql/database-administration/creative-solutions-by-using-a-number-table/

The solution is, to change that:

To that:

Outra pergunta que recebi de um cliente foi.

Posso instalar o SQL Server em uma máquina e o AS em outra? Como fica em relação ao licenciamento?

Infelizmente não pode, caso queira fazer isso terá que comprar outra licença do SQL para a máquina que irá rodar o AS. De fato, qualquer componente extra do SQL instalado em uma nova máquina, precisa de uma nova licença, seja AS, SSRS ou SSIS.

Segue o texto oficial falando sobre isso…

BUSINESS INTELLIGENCE / COMPONENTS IN ADDITIONAL SERVERS

The Business Intelligence components for SQL Server 2008 include Analysis Services, Reporting Services, and Integration Services. To use any of these components, the server on which Business Intelligence is installed must have a valid SQL Server 2008 license. If these components are in a separate server than the main database server, then they require an additional license of each additional server where they are installed.

Semana passada estive em um cliente para fazer uma demonstração de um Cubo no Analysis Services, tudo ia bem quando ele me questionou se seria possível conectar no AS utilizando uma conexão ODBC, pois eles gostariam de conectar no AS utilizando um software que eles já possuem.

A partir do AS 2005 não é possível fazer conexão diretamente com o AS utilizando ODBC. O recomendado é utilizar a conexão OLE DB.

Segue alguns links que tratam do assunto:

http://msdn.microsoft.com/en-us/library/ms143742(SQL.90).aspx

ODBC data sources are not supported

While previous versions of Analysis Services allowed you to use ODBC data sources, this functionality is no longer supported in Microsoft SQL Server 2005 Analysis Services.

http://technet.microsoft.com/en-us/library/aa964120(SQL.90).aspx

Today I worked in a very interesting T-SQL problem…

Look at the following queries:

DECLARE @i Int, @x Int, @y VarChar(250)

SET @i = 2

SELECT @i = 1,

       @x = @i + 1, — @i(1) + 1

       @y = CASE

WHEN @i = 1 THEN ‘Return 1’

WHEN @i = 2 THEN ‘Return 2’

END

SELECT @x x, @y y

GO

DECLARE @i Int, @x Int, @y VarChar(250)

SET @i = 2

SELECT @i = 1,

       @x = (SELECT @i + 1), — @i(2) + 1

       @y = (SELECT (CASE

WHEN @i = 1 THEN ‘Return 1’

WHEN @i = 2 THEN ‘Return 2’

END))

SELECT @x x, @y y

They are very similar, the difference is that in the second batch I’m using a SubQuery to compute the value of the variables @x and @y.

As you can see, the variable @i is being used in the same SELECT instruction three times, first it receives the value “1”, then the variable @x use the variable @i and the variable @y is also using the @i.

The main point here is, this is not a good practice but, this work fine, let´s look at the results to understand the problem.

Results at SQL Server 2005 SP3, 2008 SP1 and 2008R2:

The second batch returns a different value because the SubQuery was executed first, when the value of @i was 2.

Things start to get confuse when we run it in SQL Server 2000.

Look and guess what is the results in SQL Server 2000 SP4.

Pay attention, It should be included into our migration check list, because it was changed from SQL 2000 to 2005. So, be very carefully in write/analyze these kind of queries.