At the moment I’m working on a (Orchard) project which is deployed to Windows Azure and uses a SQL Azure database. As my team needed to fix some issues which occurred in the Acceptance and Production environment, I wanted to get a recent database dump so we would be able to reproduce the issues on the development machines.

I couldn’t find an easy way to synchronize the databases or create a backup which I could restore. After doing some extensive searching I discovered there is a way you can restore a SQL Azure database to a local SQL Server 2012 environment.

To do this, you have to create an export in the Azure Management portal and import this export into your SQL Server 2012. I’ll describe the necessary steps below.

First, head to the Azure Management portal and go to the Database tab. On this screen you’ll see the option Export the database at the bottom of the screen.

Clicking on this button pops up a modal dialog. In this dialog you need to specify the name of the export, a bacpac file, which storage account you’ll use, container to save the file and the login credentials of the database.

A few days ago I listened to a new episode of the .NET Rocks podcast, with Kimberly Tripp on the show. While she was going through the list of things every developer needs to know, one in particular caught my interest. She mentioned we should use the different types consistently in the database. Not using types consistently within your database can cause quite some performance loss. This is because SQL Server ‘upcasts’ the minor type to the higher type (example: varchar to nvarchar). As the term ‘quite some performance loss’ is a bit broad, I wanted to know how much this performance loss is exactly and if it’s worth rewriting/redesigning your database.

To test this case I’ve created a small database, named Performance, with a lot of records inside it. The database has 3 tables, named AsciiTable, MainTable and UnicodeTable. All three of them contain 2 columns, ID and a text column. The AsciiTable-table it’s text column has the type varchar(30), the MainTable and UnicodeTable it’s text column has the type nvarchar(30). I’ve created these to test how long some specific queries will take. At the end of this post I’ve inserted the script to create this database if you want to try it for yourself.

Onlangs moest ik enkele records restoren in een SQL database, omdat die waren verwijderd. Op zich geen probleem, afgezien van het feit dat er een primary key op een van de kolommen zat die automatisch werd verhoogd. Nogal vervelend als je een record moet toevoegen met een specifieke primary key.

Ik kreeg dan ook de volgende melding:

_An explicit value for the identity column in table ‘<TABLE_NAME>’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

Na even zoeken heb ik toch een oplossing gevonden. Blijkbaar kun je het genereren van een nieuw ID tijdelijk uitschakelen.

Hier heb je het commando

SET IDENTITY_INSERT  table_name ON

Je kunt nu zelf een ID opgeven. Zodra je klaar bent dient deze feature wel weer uit te worden gezet door

SET IDENTITY_INSERT table_name OFF

Nog iets dat gedaan moest worden is het expliciet opgeven van de kolommen die ik wilde invoeren. Ook geen probleem, maar je moet het wel weer allemaal uit typen.

Vandaag kreeg ik het verzoek om in een view alle gekoppelde medewerkers in 1 kolom te tonen die bij een client horen. Dit kan een behoorlijk lastige klus zijn en ik wist ook niet precies hoe ik zoiets zou moeten gaan oplossen. Een CURSOR zou mogelijk zijn, maar dat gebruik ik liever niet in de view die ik nu moet maken.

Na even zoeken kwam ik achter een nieuwe functie die bij SQL 2005 is geimplementeerd en gelukkig ook in SQL 2008 werkt, namelijk de CROSS APPLY.
Hiermee is het mogelijk om kruistabellen te maken (zo leg ik het maar even uit) en die gegevens in 1 kolom te tonen. Dat is tenminste waar ik het momenteel voor misbruik. Door bijvoorbeeld een query als volgt op te stellen krijg je 1 kolom met het ID van een persoon en een tweede kolom met daarin alle gekoppelde medewerkers.

SELECT stamtabel.case_id, hulpverleners = LEFT(o.list, len(o.list)-1) 
FROM stamtabel 
    CROSS APPLY ( SELECT CONVERT( NVARCHAR(255), hulpverlener) +', ' AS [text()] 
    FROM medewerkerkoppeltabel bhpc 
    WHERE bhpc.case_id = stamtabel.case_id FOR XML PATH('') ) o (list) 
WHERE stamtabel.case_id IN (65186, 46443, 57451, 45327)

Het resultaat dat je hier uit krijgt is als volgt:

57451 | 100, 200, 300, 400 65186 | 100, 122, 10 etc...

Precies wat ik nodig heb. Nu moet ik nog wel even de naam van de medewerkers ophalen in plaats van het ID, maar het concept werkt in ieder geval. Dit is toch een functie die enorm handig kan zijn.

Iedere keer wanneer we een Full-Text catalogus opnieuw moeten populaten is het weer een probleem. De optie Repopulate catalog is grijs en kun je dus niet starten via de interface. In SQL 2000 kon dit nog wel. Inmiddels weten we dat dit niet kan en iedere keer lossen we het weer op door even 5 minuten te Googlen. Zelf vergeet ik namelijk altijd hoe dit gedaan moet worden, omdat je dit eigenlijk nooit doet. Om daar nu van af te zijn post ik hier hoe je in MS SQL Server 2005 je full-text catalog opnieuw kunt populaten. Het commando dat uitgevoerd dient te worden in SQL is het volgende:

EXEC sp_fulltext_catalog 'catalogname', 'start_full'

Logischerwijs is de catalogname uiteraard de naam van je full-text catalog. Dat behoeft volgens mij geen verdere uitleg. Hopelijk was het vandaag de laatste keer dat ik dit op Google op moest zoeken.

Wat ook handig is, is de FULLTEXTCATALOGPROPERTY ('catalog_name' ,'property') functie.

Mogelijke properties zijn:

AccentSensitivity

Accent-sensitivity setting.

IndexSize

ItemCount

LogSize

PopulateCompletionAge

PopulateStatus

UniqueKeyCount

Bij ons op het werk hebben we een standaard virtuele pc die we kunnen inzetten voor verschillende doeleinden/klanten. Dit werkt perfect en is ook zeker aan te raden voor ontwikkel werkzaamheden met Sharepoint opdrachten. Het nadeel van zo’n standaard image is dat de machine 1 naam heeft.

Als alle ontwikkelaars dus zo’n machine opstarten, dan zijn er verschillende machines met dezelfde computernaam in het netwerk. In hoeverre het netwerk daar blij mee is weet ik niet, maar zelf vind ik het in ieder geval niet zo mooi. Nu kun je heel eenvoudig de machine naam wijzigen via het Configuratiescherm, dat is dan ook geen probleem. Het probleem ligt echter in MS SQL Server.

Wanneer de computernaam wijzigt, kan dit vervelende consequenties hebben voor SQL Server. Nu wil het toeval dat op deze standaard machine ook SQL Server staat geinstalleerd. Er was mij verteld dat het niet mogelijk is om de computernaam van de SQL Server te wijzigen en dat wanneer SQL Server eenmaal is geinstalleerd op de machine, je verder niet meer van dit soort rigoreuze systeemwijzingen moest gaan doorvoeren.

Eigenwijs als ik ben, ben ik op zoek gegaan naar een oplossing voor dit probleem. Ik vond het namelijk absurd dat je een computernaam niet meer mag wijzigen nadat SQL Server is geinstalleerd.
Mijn zoeken werd al snel beloond veel waarschuwingen, doemdenkers en gelukkig ook 1 oplossing. Dit is namelijk wat je moet doen wanneer je de servernaam in SQL Server wilt wijzigen.

Momenteel ben ik bezig met een project waarbij ik heb gekozen om gebruik te gaan maken van SQL replicatie tussen een SQL Server 2005 Standard en een SQL Server 2005 Express editie. Door gebruik te maken van de standaard replicatie methoden hoef ik zelf geen applicatie meer te maken welke eigenlijk precies hetzelfde doet, namelijk tabellen, stored procedures en functies synchroniseren tussen 2 databases.

Het opzetten van een Publication en Subscription is helemaal niet lastig. Microsoft heeft het systeem zo opgebouwd dat je eigenlijk alleen maar de wizard hoeft te volgen. Het enige waar je echt bij na hoeft te denken is welke tabellen, stored procedures en functies je wilt gaan repliceren.

Zodra je de publicatie klaar hebt kun je de client gaan opzetten, hier moet een Subscription worden gemaakt. Dit is eigenlijk ook niet veel lastiger dan de wizard te volgen. Waar wel op gelet moet worden is dat je de Publication Server benoemd met z’n echte naam, dus niet IP-adres.

Je krijgt ook niet echt de kans om het wel op IP-adres te doen, want dan volgt er gewoon een foutmelding. Nadat je de publicatie en het abonnement hebt ben je klaar, nou ja, dat verwacht je. Op een normale SQL Server omgeving is dit inderdaad het geval, ware het niet dat je nu met SQL Server Express te maken hebt en die niet echt normaal is. In Express zit namelijk geen SQL Agent, waardoor de database niet zelf in de gaten houdt of er een update klaar zou kunnen staan. Gelukkig kwam ik na even zoeken achter de oplossing op de MSDN website.

Vandaag kwamen we op het werk een interessant stukje SQL injection tegen. Hoe je ’normaal’ een SQL injection aanval doet op een website is mij wel redelijk bekend. Gewoon in de querystring iets toevoegen als ';SELECT%20Naam%20FROM%20Tabel of iets in die trend. Niet echt hogere wiskunde dus. Als de variabele niet goed is beveiligd in de website danwel de database, dan kun je voor verassende dingen komen te staan. Vandaag zagen we echter een variant op het bovengenoemde fenomeen, namelijk een binaire string toevoegen in je querystring. Blijkbaar is dit een redelijk nieuwe methode een artikel op Bloombit ( https://www.bloombit.com/Articles/2008/05/ASCII-Encoded-Binary-String-Automated-SQL-Injection.aspx ) schrijft hier namelijk ook over en dat artikel is van mei 2008. Blijkbaar wordt de volgende code

DECLARE @S NVARCHAR(4000) SET @S = CAST(0x44004500 ... 06F007200 AS NVARCHAR(4000)) EXEC(@S)

zo vertaald:

DECLARE @T VARCHAR(255) DECLARE @C VARCHAR(255) DECLARE Table_Cursor CURSOR FOR SELECT [A].[Name], [B].[Name] FROM sysobjects AS [A], syscolumns AS [B] WHERE [A].[ID] = [B].[ID] AND [A].[XType] = 'U' /* Table (User-Defined) */ AND ([B].[XType] = 99 /* NTEXT */ OR [B].[XType] = 35 /* TEXT */ OR [B].[XType] = 231 /* SYSNAME */ OR [B].[XType] = 167 /* VARCHAR */) OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE (@@FETCH_STATUS = 0) BEGIN EXEC('UPDATE [' + @T + '] SET [' + @C + '] = RTRIM(CONVERT(VARCHAR, [' + @C + '])) + ''''') FETCH NEXT FROM Table_Cursor INTO @T, @C END CLOSE Table_Cursor DEALLOCATE Table_Cursor

Afgezien van de formattering en het commentaar natuurlijk.

Ik denk trouwens dat de …-reeks in het eerste voorbeeld staat voor heel veel binaire code’s, aangezien je anders natuurlijk nooit al die karakters kunt krijgen van het 2e voorbeeld.

In de data access laag van m’n projecten werd het nu toch wel een drukke boel met verschillende LINQ to SQL queries. Aangezien je namelijk niet goed dynamische LINQ-queries kunt maken, moet je eigenlijk voor iedere ‘uitzondering’ een aparte query maken. Je krijgt dan zoiets als dit:

if (criteria.PeriodeVan != null && criteria.PeriodeTot != null) 
{ 
    var query = (from post in dataContext.GetTable() 
                    where post.Item.CreatieDD >= criteria.PeriodeVan && 
                    post.Item.CreatieDD <= criteria.PeriodeTot 
                    orderby post.Item.CreatieDD descending 
                    select post)
                .ToArray(); 
    if (criteria.TopList > 0) 
    { 
        return query.Take(criteria.TopList); 
    } 
    else 
    { 
        return query; 
    } 
} 
else 
{ 
    var query = from post in dataContext.GetTable() 
    orderby post.Item.CreatieDD descending 
    select post; 
    if (criteria.TopList > 0) 
    { 
        return query.Take(criteria.TopList); 
    } 
    else 
    { 
        return query; 
    } 
}

Dit is een hele lap code voor eigenlijk maar 2 acties, namelijk een periode kiezen en een selectie van de bovenste items. Voor mijn relatief kleine projecten is dat wel te doen, maar bij grotere projecten zeker niet. Zelfs dit vind ik al lastig qua onderhoud. Nu ben ik vandaag op zoek gegaan naar een goed alternatief.

Als vrij snel kwam ik op een Chris Rock z’n weblog waar hij dit probleem ook beschrijft in de post die hier is te vinden: https://www.rocksthoughts.com/blog/archive/2008/01/24/linq-to-sql-dynamic-queries.aspx Eerst komt hij met het voorstel om de Dynamic LINQ Query Library te gebruiken. Deze had ik zelf ook al een keer gezien, maar dat is eigenlijk iets wat je totaal niet wilt gaan gebruiken. Je krijgt dan gewoon tekst queries in je code welke niet meer door je compiler worden gecontroleerd. Dit haalt Chris ook al aan. Daarna begint hij over de LINQ Predicate Builder.

De afgelopen tijd merkte ik op dat de grootte van een bepaalde database waar ik vaak mee werk behoorlijk groot wordt in korte tijd. Om te onderzoeken aan welke tabel dit te wijten was moest ik weten hoe groot iedere tabel was. Dit kan uiteraard door de eigenschappen van iedere tabel op te vragen, maar als je meer dan 50 tabellen hebt duurt dit veel te lang.
Dat moet ook automatisch kunnen. Ik hoefde niet lang te zoeken voor een script dat ik zocht. Ene Zxtreme had al een mooi script on-line geplaast, te bezichtigen op deze website: https://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=732&amp;lngWId=5.

Hiermee kun je precies zien hoe groot (in KB’s) je tabellen zijn. Eigenlijk komt het er op neer dat je een soortgelijke query als die van hieronder moet uitvoeren.

DECLARE @TempTabel TABLE ( [Name] NVARCHAR(255), [Rows] INT, [TabelGrootte] NVARCHAR(50), [Used] NVARCHAR(50), [IndexUsed] NVARCHAR(50), [Unused] NVARCHAR(50) ) 
INSERT INTO @TempTabel EXEC sp_msforeachtable 'sp_spaceused "?"' SELECT * FROM @TempTabel

Zodra je dit in je database doet krijg je per tabel precies te zien wat de grootte er van is. Wel handig. Zo kwam ik er vandaag achter dat er een historie tabel veel oude data bevatte dat wel weg kon.