SSDT: Creating a New SQL Project

Download the latest SSDT package if you do not have the product installed already:
http://msdn.microsoft.com/en-us/data/tools.aspx
 
To create a new SQL Project, you can either start by creating a new Project or create a new project from an existing database.
When creating a brand new project, be sure to choose the "SQL Server Database Project".

 
This is not the same as the Database -> SQL Server Project template. Using this will create a usable DB Project, but not the newer SQL Project.

 
Once you've created a new, empty project, you'll need to populate the project in order to be able to use it. You have a couple of options. You can import from an existing database, import from a Dacpac file, or just start creating your objects if this is a completely new project.
 
To Start a New Project from a Connected Database
Open your "SQL Server Object Explorer" window. Connect to the database server containing the database for which you want to create a project. Right-click that database and choose the "Create New Project…" option.

You'll then be asked to choose your project name, location, folder structure, and what to import. You can choose the defaults if you want. If you have an intricate permissions structure, you may want to import the permissions. If your logins, users, and permissions tend to vary by environment, you may want a different way to handle those. That will be covered in the "Permissions" section.
You'll have several options for folder structure. I'd choose the recommended structure unless you have a reason to choose something else. That will structure all of your objects under their appropriate schema, by object type. If you have a relatively simple database model, you may choose to structure your project by schema alone, object type alone, or just put everything in the root of the project. This only affects your project file structure, not your database structure.
Once you're satisfied with your settings, click the "Start" button to begin populating your project.

SSDT: Why Use SQL Projects?

What Are SQL Projects and why use them?
SQL Server has always been lacking in a good solution to control versions of your database. We make changes directly to the database and if we're good, we remember to take backups. If we're really good, we save scripts so we can make those changes elsewhere. Too often, we resort to tools for schema differences or try to save up all of the scripts to replay them later. Both have some advantages. Schema differences guarantee that you can make two environments look the same. Scripts can be replayed in order to keep the systems in sync. However, a schema difference doesn't handle data changes well and can't handle making partial changes when not everything is ready to promote. Sets of scripts can also work well, but fail if you end up needing to push sub-sets of those scripts because certain functionality isn't going to be released yet.
Borrowing from Gert Drapers' excellent discussions on Database Development Challenges:

• Databases are inherently stateful
  • ALTERs are generally run instead of DROP/CREATE
  • Dependencies can greatly complicate the changes and scripts
  • Data needs to be persisted
• Databases are often not integrated well with the application development life cycle
• DB Versions are frequently not persisted or managed well
• SQL Scripts may not handle different SQL Server versions well

Sequential Change Scripts

• Required to run in a set order
  • Script 1, Script 2, Script 3, …
  • Can't do Script 1, Script 4, Script 9
• Validating the end state after script runs vs. an expected end state can be complex
• Could easily miss a script in the sequence without knowing
• Handling a release where scripts need to be "skipped" can cause cascading dependency problems
• Can require extensive use of IF NOT EXISTS statements
• Manual process
• Point in time model
• Used by systems such as DBDiff

Declarative Model

• The model is the desired end state
  • Compare the current state of the target against the desired end state
  • Use the differences to generate an upgrade plan
  • Use the upgrade plan to generate a change script, correctly ordered to bring the target up to the desired end state
• Generated / Programmatic process
• Always current
• Used by Red Gate, Microsoft, DB Ghost

Microsoft and Red Gate have both provided solutions to store versions of database schemas. While Red Gate offers a good product, it also requires some external tools to provide a complete solution for schema control. Until recently, Microsoft's solution wasn't accessible to everyone because it required a copy of Visual Studio to handle even the basics. This improved in Visual Studio 2010, but still required a separate purchase. With SQL Server 2012, Microsoft introduced SQL Server Data Tools and SQL projects.
With SQL projects, developers can store a version of their database objects in a source control system of their choice. Tables, stored procedures, views, and even permissions can be stored in such a way as to provide upgrades to databases and even build a new set of databases from scratch. This could even be used to build and release these databases continuously in an automated fashion to release new code.

Tales of a Lazy DBA–MySQL, SSIS, and “0” Dates

We’ve recently been tasked with converting a lot of MySQL Data into our system from a multi-tenant MySQL Database. We don’t have a fixed schedule to actually execute the imports because they’ll be on a “per customer” basis.  Well, that sounded like a great task for SSIS.  I set up the ODBC driver, connected, set the initial “sql_mode” options for our connections to (‘mssql, allow_invalid_dates’) and started to work.
First problem we ran into with an ADO.NET connection to MySQL and writing a SELECT * from schema.table was when we hit a MySQL “Date” column containing a value of ‘0000-00-00’.  SSIS threw an error, not sure what to do.  Thanks to some others who have solved this problem, I realized that within the MySQL Select statement, we could do something like:

CASE date_created WHEN '0000-00-00' THEN NULL else date_created END as date_created

to pass those dates over as NULL. That solves the implicit conversion to datetime (SQL 2005) and avoids the invalid dates.  I ran something similar for a “Time” column to:

CAST(Time_Created as char(8) ) as Time_Created

 
So that solved one particular table export with about 20 or so CAST and CASE statements.  Needless to say, I wasn’t looking forward to doing this for another 500 tables with a total of almost 6000 columns.
I finally set up a really basic query to generate most of the SELECT statements we would need to pull our MySQL data across without too much pain. Admittedly, a small step and I’d still need to copy/paste when I set up each new ADO.NET source, but it worked reasonably well. I’m adding the code snippet for MySQL here in case anyone else has a similar problem.

select

CONCAT(CASE WHEN ordinal_position = 1 THEN 'SELECT ' ELSE ', ' END,

 CASE Data_Type WHEN 'date'

 THEN CONCAT('CASE ',column_name,' WHEN ''0000-00-00'' THEN NULL else '

    ,column_name,' END as ',column_name)

 WHEN 'time' THEN CONCAT('CAST(',column_name,' AS CHAR(8) ) as ',column_name)

 ELSE column_name END, CASE WHEN Ordinal_Position <> MaxOrd THEN ''

 ELSE CONCAT('

    FROM MySchema.', c.table_name) END

    ) as Select_Column_Name

from information_schema.columns as c

JOIN (select table_name, MAX(ordinal_position) as MaxOrd

        from information_schema.columns

        WHERE Table_Schema = 'MySchema'

        GROUP BY Table_Name) as t1

    ON c.Table_Name = t1.Table_Name

where table_schema = 'MySchema'

order by c.table_name, ordinal_position LIMIT 0, 50000;

I’d love to hear other ideas if anyone has encountered this before and come up with a more elegant solution for translating “0” Date or Time data from MySQL into SQL Server.

Installing SQL Server, Sharepoint, PowerPivot on a single server

I've been trying to set up a single virtual machine for a proof of concept using SQL Server Denali CTP3, Sharepoint 2010, and PowerPivot. I want to get Project "Crescent" running through this as well to prove out some report concepts. I've had a lot of trouble finding the right steps to get everything installed and working correctly on a single box, especially since my Sharepoint knowledge right now is practically non-existent when it comes to BI. Every time I tried to get the Sharepoint / PowerPivot integration working, I hit a roadblock or managed to corrupt my Sharepoint install.

I had the opportunity to speak with Brian Knight ( blog | twitter ) and he pointed me to PowerPivotPro.com which, in turn, led me to PowerPivotGeek.com. They had a page of instructions for single-server installs with a pointer to an MS whitepaper and a private cached file of the whitepaper just in case. This gave me the information needed to get PowerPivot working on my VM.

A couple of notes from my personal experience:

• Install Sharepoint SP1 right after installing Sharepoint 2010. SP1 is required when you're using Denali
• Do NOT configure Sharepoint until the directions tell you to do so.
• Use a Named Instance in order to get PowerPivot working.
• If you plan to use Crescent, make sure that you set up SSAS with the "Tabular Data" option (or whatever the final name ends up being).

Once again, thanks to Brian for pointing me towards the right solution. Thanks to PowerPivotGeek for hosting these files. I imagine the instructions would work for Virtual Server, Virtual PC, VMWare, VirtualBox, or similar Virtual Machine apps. I hope this is helpful to anyone else trying to set up their own VM for testing out the Microsoft BI stack.

Service Broker–WAITFOR and Activated Queues

We’ve implemented Service Broker for handling a small portion of the transactions we want to catch and apply into our Operational Data Store. Mostly we wanted to make sure we accounted for actual DELETE operations and handled those records correctly. We can use Change Data Capture at this time on our source systems and Service Broker seemed to fit the bill. We still handle the remaining inserts and updates through SSIS.

While running Service Broker, we noticed a pretty constant CPU hum on the receiving server. That seemed odd, but we had a lot of trouble tracking it down. Regular Profiler traces didn’t show any running TSQL, “normal” Service Broker traces weren’t showing much, either. We just saw SQL Server running at a pretty constant 20% even with nothing seemingly happening.

I want to give a public thank you to Mark Hill (twitter) for doing a little extra digging and catching the root cause of this. In our tinkering with Service Broker, Queues, Activation, Stored Procs, and such we missed some very important information along the way.

We had written a stored procedure that would be used on the Receiving side of our Service Broker queue to run as the “Activated” proc. Inside the stored procedure, we included a WHILE loop to process anything that was in the queue. If nothing, exit.  That seemed pretty simple – if something comes into the queue, process it and stop when nothing is left.  End of story, right?

Sadly, this is where we missed a small, but important, fact about Service Broker. If you have an activated stored procedure that doesn’t use the WAITFOR command, Service Broker, we execute that stored proc as many times as possible looking for something to process. We looked at it, turned off the Activation on the queue and saw the CPU drop to nothing. We re-activated the queue and saw the CPU shoot up again. We tweaked the stored procedure after that and added a WAITFOR command with a timeout of 60000. After doing this, we saw the stored procedure run to process everything in the queue, then go idle, which was exactly the intended behavior in the first place.

I’m not going to pretend to be an expert on Service Broker. We had tried to code with the intention of being able to use this stored procedure as an Activated stored procedure or as one called from an external process to work through the queue. While that may be possible, it was unnecessary in our actual usage. Adding a WAITFOR command around our queue processing eliminated our extra, non-essential CPU usage and stopped trying to execute a stored procedure for no reason.

Resources

Pro SQL Server 2008 Service Broker ( Amazon | Apress )

WAITFOR (Also see this performance article – it’s the little details that get you)

Quickly script permissions in SQL 2005+

I wanted an easy way to generate a script to recreate all DB object and schema permissions for a database. Searching online, I found several different examples, but none quite did everything I needed. I put this together from some examples and added it to my toolkit. This script will generate the appropriate T-SQL to grant permissions to DB Objects and Schemas.

 

SELECT
state_desc + ' ' + permission_name +
' on ['+ ss.name + '].[' + so.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN sys.objects AS so
     ON sdp.major_id = so.OBJECT_ID
JOIN SYS.SCHEMAS AS ss
     ON so.SCHEMA_ID = ss.SCHEMA_ID
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
     ON sdp.grantee_principal_id = sdpr.principal_id

UNION

SELECT
state_desc + ' ' + permission_name +
' on Schema::['+ ss.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN SYS.SCHEMAS AS ss
     ON sdp.major_id = ss.SCHEMA_ID
     AND sdp.class_desc = 'Schema'
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
     ON sdp.grantee_principal_id = sdpr.principal_id
order by [Permissions T-SQL]
GO

I know that a lot of you may have something that does this already, but figured it can’t hurt to have another snippet available to generate permission statements. If you make any enhancements, let me know. I’d love to keep this up to date so it’s helpful to others.

SQL 2008 R2 – SSIS Data Export Oddities

I don’t know if anyone else has had these sorts of issues, but we work quite a bit with conversions of various source data into our SQL Server database. I recently was handed a backup from SQL 2008 R2. I figured that wasn’t a huge problem. Restore onto my local test box, use SSIS to push over to a SQL 2005 instance so it can work its way to Production. No big deal, right?

The first time I tried this using the SQL Native Client 10.0 on both sides, I ran into an errors with the mappings. I can’t quite figure that one out because from what I can see, there’s nothing at all in the source database using a feature that would not be available in SQL 2005.

I tried exporting to an MS Access MDB file. No luck from SQL 2008 R2 with the default settings because of an invalid size error on a varchar to longtext conversion.

I was able to successfully export using SNAC from SQL 2008 R2 to a SQL 2008 instance. So from there I thought I could upload directly to the SQL 2005 instance. No such luck using SNAC – again. I was able to export from there to an Access MDB file and pull that into SQL 2005.  I don’t quite get why that worked, but figure I’ve got some odd mapping in the XML files defining the defaults that I’m missing.

 

I was recently asked to repeat this task and figured there had to be a better way. This time I restored the DB to my R2 instance – no issues. I then used the SNAC client to access my R2 instance, but set up an OLEDB connection to my SQL Server 2005 target. For some reason, the mappings are just different enough that this worked with no issues. I was able to transfer directly. I now wish I’d tried that the first time, but I’d already blown a couple of hours on it.

If anyone else has encountered that and knows why SNAC from 2008 R2 doesn’t seem to work directly to SNAC on 2005, I’d love to know the reasons as well. If not and you encounter something similar, maybe trying the OLEDB connections will work for you.