SQL Tact

Thanks to all 130+ folks who joined, suffered my jokes and handwriting, and asked fantastic questions during my presentation to the SQL PASS Data Architecture Virtual Group meeting.

We covered three large project templates, including an end-to-end application and Data Warehouse, a SharePoint-list driven Data Warehouse, and an "Access Jailbreak" complete with a rude drawing.

Because many people asked: I presented "whiteboard" style using Wacom UCTH470 Bamboo Capture Pen & Touch Tablet and a regular stylus (not powered, no "eraser"). Aside from some hiccups with OneNote 2013 recognizing my handwriting as text and switching to text mode (I think that's the reason), the illustration-style presentation went well. I am no artist or penmanship champion, but I hope this was in the very least a thought-provoking exercise!

To download my whiteboards from OneNote from today's presentation in PDF format click here.

To view the recording of the video on Youtube: http://youtu.be/9VRQtkwtD6U

I recently had the challenge of migrating an installation of Microsoft Team Foundation Server 2010 from a shared SQL Server 2008 R2 Enterprise instance to its own SQL Server 2008 R2 Standard instance on a new server.

Because of some Enterprise edition-specific feature usage, this turned out to have two problems during the restoration:

Use of database compression on some TFS 2010 tables
Use of perspectives in the TFS 2010 SSAS database "TFS_Analytics"

Neither feature is available in Standard edition and conveniently the error message when restoring the SQL or SSAS databases from Enterprise to Standard clearly indicated this. After making the client aware, the decision made was to try and remove these features from their TFS installation.

After removing these Enterprise features from copies of the databases, I was able to back up and restore the copied databases (during the migration outage) to the new server without any more edition failures.

Here's how:

Remove Compressed Indexes

You may encounter this error if you attempt to restore any database that uses data compression from Enterprise to Standard edition:

cannot be started in this edition of SQL Server because part or all of object 'foo' is enabled with data compression or vardecimal storage format

Here is a script to look through all tables for compressed partitions (either heaps or indexes) and REBUILD them with DATA_COMPRESSION = NONE. This obviously only works on Enterprise Edition of SQL 2008 or higher.

--Enable WITH (ONLINE = ON) if possible

Declare @sqltext nvarchar(1000), @tbname sysname, @index_id int, @index_name nvarchar(100)

Declare tbl_csr cursor
FOR
select name = '['+s.name+'].['+o.name+']', p.index_id, i.name
from sys.partitions p
inner join sys.objects o on o.object_id = p.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
inner join sys.indexes i on i.index_id = p.index_id and i.object_id = o.object_id 
where p.data_compression <> 0
and o.type_desc <> 'INTERNAL_TABLE'

Open tbl_csr

Fetch Next from tbl_csr into @tbname, @index_id, @index_name

 While (@@FETCH_STATUS=0)
 Begin

   If @index_id =0 
    begin
     --catches heaps
     set @sqltext=N'ALTER Table '+@tbname + N' REBUILD WITH (DATA_COMPRESSION=NONE)' --, ONLINE = ON
     exec sp_executesql @sqltext
     print 'rebuild heap ' + @tbname 
    end
   else
    begin
     set @sqltext=N'ALTER INDEX ' + @index_name + ' ON '+@tbname + N' REBUILD WITH  (DATA_COMPRESSION=NONE)' --, ONLINE = ON
     exec sp_executesql @sqltext
     print 'rebuild index ' + @tbname 
    end

   Fetch next from tbl_csr into @tbname, @index_id, @index_name

 End

Close tbl_csr
Deallocate tbl_csr

Below is a demo you can use to simulate the script as it finds clustered indexes, nonclustered indexes and heaps to rebuild appropriately, while also ignoring XML indexes (which could present a problem if you take a blanket ALTER INDEX ALL ... REBUILD.)

use adventureworks
go

--test lab
if not exists (select 1 from sys.schemas where name = 'testschema')
exec (N'create schema testschema')

go
if exists (select 1 from sys.objects where name = 'testfeature_index') 
drop table testschema.testfeature_index
go
create table testschema.testfeature_index (id int not null identity(1,1) primary key , bigint1 bigint not null, xml1 xml null)
insert into testschema.testfeature_index (bigint1) values (123456789123456789),(1234567891234567891),(1234567891234567892),(1234567891234567893)

go
set nocount on 
insert into testschema.testfeature_index (bigint1)
select bigint1+5 from testschema.testfeature_index 
go 10
set nocount off
alter index all on testschema.testfeature_index rebuild with (data_compression = page)
create nonclustered index idx_nc_testfeature1 on testschema.testfeature_index (bigint1) with (data_compression = page)
create primary xml index idx_nc_testfeaturexml1 on testschema.testfeature_index (xml1) 
create xml index idx_nc_testfeaturexml2 on testschema.testfeature_index (xml1)  USING XML INDEX idx_nc_testfeaturexml1 FOR PATH
go
if exists (select 1 from sys.objects where name = 'testfeature_heap') 
drop table testschema.testfeature_heap
go
create table testschema.testfeature_heap (id int not null identity(1,1)  , bigint1 bigint not null)
insert into testschema.testfeature_heap (bigint1) values (123456789123456789),(1234567891234567891),(1234567891234567892),(1234567891234567893)
go
set nocount on 
insert into testschema.testfeature_heap (bigint1)
select bigint1+5 from testschema.testfeature_heap 
go 10
set nocount off
go
alter table testschema.testfeature_heap rebuild  with (data_compression = PAGE)
create nonclustered index idx_nc_testfeature1 on testschema.testfeature_heap (bigint1) with (data_compression = page)

go

--Enable WITH (ONLINE = ON) if possible

select name = '['+s.name+'].['+o.name+']', case p.index_id when 0 then 'Heap' when 1 then 'Clustered Index' else 'Index' end
from sys.partitions p
inner join sys.objects o on o.object_id = p.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
where p.data_compression <> 0
and o.type_desc <> 'INTERNAL_TABLE'
go
Declare @sqltext nvarchar(1000), @tbname sysname, @index_id int, @index_name nvarchar(100)

Declare tbl_csr cursor
FOR
select name = '['+s.name+'].['+o.name+']', p.index_id, i.name
from sys.partitions p
inner join sys.objects o on o.object_id = p.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
inner join sys.indexes i on i.index_id = p.index_id and i.object_id = o.object_id 
where p.data_compression <> 0
and o.type_desc <> 'INTERNAL_TABLE'

Open tbl_csr

Fetch Next from tbl_csr into @tbname, @index_id, @index_name

 While (@@FETCH_STATUS=0)
 Begin

   If @index_id =0 
    begin
     --catches heaps
     set @sqltext=N'ALTER Table '+@tbname + N' REBUILD WITH (DATA_COMPRESSION=NONE)' --, ONLINE = ON
     exec sp_executesql @sqltext
     print 'rebuild heap ' + @tbname 
    end
   else
    begin
     set @sqltext=N'ALTER INDEX ' + @index_name + ' ON '+@tbname + N' REBUILD WITH  (DATA_COMPRESSION=NONE)' --, ONLINE = ON
     exec sp_executesql @sqltext
     print 'rebuild index ' + @tbname 
    end

   Fetch next from tbl_csr into @tbname, @index_id, @index_name

 End

Close tbl_csr
Deallocate tbl_csr


go

select name = '['+s.name+'].['+o.name+']', case p.index_id when 0 then 'Heap' else 'Index' end
from sys.partitions p
inner join sys.objects o on o.object_id = p.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
where p.data_compression <> 0
and o.type_desc <> 'INTERNAL_TABLE'

Remove Perspectives from SSAS Database

You may encounter this issue when restoring any SSAS database from Enteprise to Standard editions, not just in TFS.

Errors related to feature availability and configuration: The 'Perspectives' feature is not included in the '64 Bit Standard Edition' SKU.

The solution is multi-step but straightforward.

Here's a breakdown of the steps. The XMLA code to accomplish this will follow:

Backup the SSAS database (TFS_Analytics) on the Enterprise SSAS instance.
Restore the SSAS database with a new name (TFS_Analytics_std) to a new DbStorageLocation on the Standard SSAS instance.
In Management Studio Object Explorer, script out the database as an ALTER statement. Find the <Perspectives> section of the code. (Note - "Perspectives" is plural.) Drag and select to the </Perspectives> tag. Be sure to capture all the <Perspective>...</Perspective> sections. Delete.

Easier way? Collapse the box to the left of the <Perspectives> tag. Select the collapsed line for the <Perspectives> tag. Delete.

Execute the XMLA script. (Hit F5.) This will remove the perspectives from the database.
Backup the TFS_Analytics_std database to a new location.
Restore the backup of the TFS_Analytics_std database to the Standard Edition server, renaming the database back to TFS_Analytics.

Code examples below. Be aware that you may need to apply the <AllowOverwrite>true</AllowOverwrite> element to overwrite any .abf files during a backup, or databases during a restore. For safety reasons, this option has been set to false for these code examples.

On the old Enteprise server, backup the SSAS database (TFS_Analytics).

<backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 <object>
  <databaseid>Tfs_Analysis</DatabaseID>
 </object>
 <file>M:\MigrationBackups\TFS_analysis_migrationbackup_2014.abf</file> <allowoverwrite>false</allowoverwrite>
</backup>

On the old Enteprise server, restore the SSAS database with a new name (TFS_Analytics_std) to a new DbStorageLocation.

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 <File>M:\migrationbackups\TFS_analysis_migrationbackup_2014.abf</File>
 <DatabaseName>TFS_Analysis_std</DatabaseName>
<DbStorageLocation xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">m:\migrationbackups\</DbStorageLocation
>

In Management Studio Object Explorer, script out the database as an ALTER statement.
Find the <Perspectives> section of the code and remove it.

Execute the XMLA script. (Hit F5.) This will remove the perspectives from the database.

On the old Enteprise server, backup the TFS_Analytics_std database to a new location.

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>Tfs_Analysis_std</DatabaseID>
 </Object>
  <File>M:\MigrationBackups\TFS_analysis_migrationbackup_2014_std.abf</File>
</Backup>

Create a new XMLA script on the target Standard Edition server. Restore the backup of the TFS_Analytics_std database to the Standard Edition server, renaming the database back to "TFS_Analytics".

If there are no other Enterprise-only features in use in the SSAS database, this backup should restore successfully.

Note also that the restore here occurs across the wire, using the UNC path to a temporary folder share. The SSAS service account on the new Standard edition server must have permissions to view this folder share.
```
<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 <File>\\serverpathnamehere\MigrationBackups\TFS_analysis_migrationbackup_2014_std.abf</File>
<DatabaseName>TFS_Analysis</DatabaseName>
 <AllowOverwrite>false</AllowOverwrite>
```

SQL Tact

Thursday, February 27, 2014

Data Architecture Virtual Group - Whiteboard Data Architecture Presentation

Wednesday, February 26, 2014

Acadiana SQL Server User Group - Introduction to SQL Server Security Principals and Permissions

Monday, February 24, 2014

Challenges with Downgrading TFS 2010 from SQL Enterprise to SQL Standard Edition