Category Archives: DBA

Create a Date Table in Oracle

Occasionally you may be working with non Microsoft data sources such as Oracle over front end tools like Power Pivot or Reporting Services.

Some options for a date table include creating a linked table in Power Pivot or generating a table in the data source. Generate a date table quickly using your Oracle data source with the following example which generates a virtual date table, one row from the sample date of 11th April 2013 until 720 days after…

SELECT
       TO_NUMBER (TO_CHAR (TO_DATE('11/04/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'), 'yyyymmdd')) AS date_key,
n AS Date_ID,
TO_DATE('11/04/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day') AS Full_Date,
TO_CHAR(TO_DATE('11/04/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'DD') AS Days,
TO_CHAR(TO_DATE('11/04/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Mon') AS Month_Short,
TO_CHAR(TO_DATE('11/04/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'MM') AS Month_Num,
TO_CHAR(TO_DATE('11/04/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Month') AS Month_Long,
TO_CHAR(TO_DATE('11/04/2013','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'YYYY') AS Year
FROM (
select level n
from dual
connect by level <= 720
);

SQL Server Data Compare

To download the admin install go to http://msdn.microsoft.com/en-us/data/hh297027, download the ssdtsetup executeable for SQL Server 2014, and also vs2010, vs2012, vs2013.  From the command prompt run ssdtsetup /layout <destination> where <destination> is the path of the USB stick / network / local drive you want to install to.

This may take a while to download the full install files.

If that’s all a bit to hard just smell this cool SSDT feature here http://msdn.microsoft.com/en-us/library/dn266029(v=vs.103).aspx.

The entry point is pretty simple, just right click your database and select Database Comparison.

datacompare

 

 

 

SSAS: Access Denied – The file specified in the restore command is damaged or is not an AS backup file

You have attempted a database restore in Analysis Services from a .abf file and get the error : “The file specified in the restore command is damaged or is not an AS backup file.  The following system error occurred : Access is denied”.   The key clue in the error is the last part.

accessdenied

Go to the actual file, i.e. not the folder, right click, select properties, the click the Security tab.  Edit the file’s security so that the Analysis Services service account has sufficient permissions i.e. full permissions will work.  If you don’t know the Analysis Services Services account, go to SQL Server Configuration Manager and find your Analysis Services service where you will see the service account.

SSRS Migration: Data Sources Supported in Reporting Services 2012/2014

Migrating your Reporting Services instances to SQL Server 2012/2014 has a few key considerations.

First, the oldest version you can upgrade from is Reporting Services 2005 and that must be patched to SP4 (9.00.5000).

Next, any SQL Server 2005 report data sources are still supported in SQL Server 2012/2014. Here are the full list of data sources supported in SQL Server 2014 http://msdn.microsoft.com/en-us/library/ms159219.aspx. However your Report Server databases themselves can’t be hosted on SQL Server 2005 database instances http://support.microsoft.com/kb/2796721/en-nz.

Importantly, those pesky Report Models are depreciated in Reporting Services 2012/2014 http://msdn.microsoft.com/en-us/library/ms143509.aspx. They will migrate but you won’t be able to make changes after the migration – so at some point they will need to have their datasets rewritten. Getting a handle on how many reports hang off Report Models and giving your customers some options is a good way to manage this issue.

Finally, the full migration process is outlined here step by step http://msdn.microsoft.com/en-us/library/ms143724.aspx. There are risks in any major version migration, but running side by side environments and getting a business acceptance sign-off is the best way to mitigate those risks.

Always On Availability Groups: Do I need a File Share Witness or No ?

This blog sets about doing a shallow dive – to simply understand how a File Share Witness is used and required in an Always On Availability Group cluster.

An Always On Availability Group cluster requires no shared storage – Windows 2012 supports up to 16 nodes, SQL Server 2012 supports up to 5 nodes in an Availability Group, and SQL Server 2014 supports up to 9 nodes.  A cluster can sustain itself  as it loses nodes and Quorum is described as the state of the cluster.

The basic rule for determining how many nodes your cluster can sustain losing is a “majority” …. I love a good analogy… here is one … Your cluster is like a bar stool … quorum is its ability to remain standing… or rather, how many legs it can lose before it falls over.

So… File Share Witness, Yes or No ? … Mathematically speaking the short answer is …

…Even number of legs = Yes

…Odd number of legs = No

A bar stool with an odd number of legs isn’t an issue, an odd number of legs means a majority can always be determined, but for an even number of legs, losing half of the legs creates an issue – a majority can’t be determined, unless you have a witness (a backup leg).

Lets take a really simple example – a bar stool with 2 legs – this is your 2 node cluster.  Each leg is considered a vote, until it is removed, in which case its vote doesn’t count.

The state of the bar stool (quorum) is determined by how many legs are still standing (votes).  It would be pretty pointless setting up 2 node Always On cluster if you had no redundancy.  The mathematical  formula which explains how many legs a bar stool can afford to lose is… (legs/2)-1 … that’s (2/2)-1 … which equals zero legs.  Without a backup leg (file share witness), the bar stool could not sustain losing any legs… so it has no redundancy.

2legsfailure

File Share Witness -> With a backup leg however (file share witness), the bar stool can lose half its legs (1), form a majority and meet the number of Votes required to keep the bar stool standing.

2legsfailurewitness

Let take a 4 leg bar stool example…because lets face it, they usually do … how many legs can a bar stool afford to lose to keep standing ? …The formula is (legs/2)-1 … that’s (4/2)-1 … which is equal to 1 leg.  If a 4 leg bar stool loses 1 leg … this still forms a majority and the stool maintains its quorum…

4legtable2legsfailure

But if it loses two legs, (half of its legs), we are in trouble… a count of standing legs won’t reach a majority and the quorum can’t be sustained… unless…

4legtable2legsfailure

File Share Witness -> For a bar stool with an even number of legs, a bar stool CAN lose up to half its legs, as long as a backup leg (file share witness) is used.   You can see when half of the nodes are missing, a majority can still be determined and satisfied, when a file share witness is used.

4legtable2legsfailurewitness

In advanced Always On architecture, a node can be configured so that it as no vote.   It still participates in the cluster and the availability group, but for the purposes of counting a majority, the vote doesn’t count.  So a 4 node cluster, with 1 node set to no vote, gives a 3 node vote.  A majority can be determined with an odd number of votes so a file share witness is not needed.   Locating your file share witness in a third datacentre can also provide another level of resilience.

😀

TSQL: <3 Search all your SQL Server Databases for a Specific Table …

We’re all just searching for something …

Bigger than we’re all able to find …

Chasing all the things that keep us young …

We won’t stop running til we reach the sun …

We’re just chasing what we can’t ever get … ever have …

EXEC sp_MSforeachdb 'USE ?
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''YourTableName'')
BEGIN
SELECT ''?'' AS DATABASE_NAME, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ''YourTableName''
END'
Simple substitute your own table name into the code above and find the table you are looking for in any database on your SQL Server instance… Enjoy ❤