Installing SQL Server 2014 Data Tools

I noticed that SQL Server Data Tools (a.k.a. BIDS) was not listed under Shared Features on the SQL Server 2014 Developer DVD.

image

So where did it go?  With SQL Server 2014, there is a link on the Installation Center window under Tools.

image

On the Developer DVD, the link points to http://www.microsoft.com/en-us/download/details.aspx?id=42313

The change is a positive one.  When BI Developers need to install Data Tools the download from Microsoft is available and there aren’t any concerns about installing more components that are needed to develop cubes and reports.

Posted in Database Administration, Development, SQL Server Integration Services, SQL Server Reporting Services | Tagged , , | Leave a comment

Teradata SQL Assistant Options for SQL Server Users

At first glance SQL Assistant seems quite different from SQL Server Management Studio.  However there are two options that go a long way in making SQL Assistant behave more like SSMS. 

The first option to set will display all databases for which the user has access.  This will make the database tree view in SQL Assistant a little more familiar for SSMS users.

Select Tools—>Options from within SQL Assistant.

image

Then click on DB Tree.  Click on the 2nd radio button in the list.  “Load the databases and users from a Table or View”.

image

 

The second option can be a lifesaver for a SQL Server DBA new to Teradata.  By default SQL Assistant will run every script in the window regardless of what is highlighted.  There is an option to change that.

On the Options window click on the Query tab and select – “Submit only the selected query text when highlighted”.

image

 

Hopefully these two options will ease the transition between SSMS and SQL Assistant.

Posted in Database Administration, Teradata | Tagged , , | Leave a comment

My First PASS SQL Summit

I just got back from my first PASS SQL Summit.  From talking with previous attendees I had high expectations.  As advertised it was a who’s who of the SQL Server Community.  There were 16 sessions running for 75 minutes at a time for 3 days straight.  There was the high profile keynote presentations the first two days.  Overall it was worth the investment in knowledge gained and connections made. 

For those who cannot afford to attend SQL Summit, don’t despair.  A SQL Saturday has similar content, albeit on a much smaller scale.  What a SQL Saturday doesn’t have is all the marquee names.  It also doesn’t have the flashy keynote presentations.  But beyond that they are remarkably similar.  The presentations are given by knowledgeable people passionate about technology.  The event is attended by people looking to improve their craft.  If you can make it to a Summit you should attend.  If not a local SQL Saturday or two is a nice substitute.

Posted in Database Administration, Training | Tagged , , , | 1 Comment

SQL 2012 Statistics & Query Plans

I noticed that updating statistics did not remove what I determined to be a bad query plan.  I thought this was odd.  I searched connect and found a bug submitted by Kimberly Tripp.

https://connect.microsoft.com/SQLServer/feedback/details/769338/update-statistics-does-not-cause-plan-invalidation

I thought a-ha!  If she thinks it is a bug it is probably a bug.  But it turns out that this is by design and the data has to change in order for the plan to change. While this makes sense it is different than previous versions.

The morale of the story is to think before recollecting stats.  Did the data change?  If not then don’t update stats. In order for stats to have an impact it means the data changed but did not hit the threshold for auto update stats to have run.

If you think the plan is bad for some reason then recompile or delete the plan handle. 

Posted in Database Administration | Tagged | Leave a comment

SQL Saturday #299 Summary

Another great SQL Saturday event in Columbus, OH.  These events are well worth the time and there are always people to meet and new things to learn.  Anybody you meet at  SQL Saturday is passionate enough about what they do to spend a Saturday improving their skills.  If you aren’t learning something new, you are falling behind.

Once again this year I planned my day then changed my schedule at the last minute after a conversation with a co-worker on Friday. 

Service Broker

There were two great sessions on using service broker.  This feature can be used for all sorts of tasks that need asynchronous data transfer.  Want to move some data around in near real time but CDC or transactional replication seem to heavy?  Service Broker to the rescue.  Daily updates to your data warehouse are not frequent enough to meet the business need?  Service Broker to the rescue.

Policy Based Management

I think database sprawl is an issue for a lot of companies.  Providing documentation for audits is also an issue for many companies as well.   If the instances are 2008 or newer Policy Based Management can help solve those problems. The documentation on this feature is a little thin but the presenter provided some good resources that fill the gap.

Products

ApexSQL – I will have to admit, I didn’t know what ApexSQL was but I do now.  It has a set of tools for developers and DBAs.  Features such as schema compare, data compare, and the ability to mount a transaction log and view/modify transactions.

Confio Ignite – I was aware of Ignite but I did not know it could monitor SQL Server and Oracle from the same dashboard.  I also learned it could detect VMWare Vmotion events and provide visibility into host and the other guests that are on it.  Confio was purchased by Solarwinds and the Ingnite product will change names in the future.

New Features

Not up to speed with always on?  There is a session for that.

Don’t know anything about on Hadoop?  There is a session for that. 

What are the pros and cons of SQL 2014 in-memory tables?  There is a session for that.

Posted in Database Administration | Tagged , , | Leave a comment

SQL Server on Azure VMs

Here is a great checklist from Microsoft.  Some of it is no-brainer recommendations for any SQL instance like disable auto shrink.  But there is a lot of good Azure specific content.  I find the page compression recommendation very interesting.

http://msdn.microsoft.com/en-us/library/azure/dn133149.aspx

Posted in Database Administration | Tagged | Leave a comment

Converting from Teradata Timestamp to SQL Server Datetime ‏

Teradata has a timestamp data type that appears to be equivalent to a SQL Server datetime.  It is not. Attempting to use the SSIS Data Conversion task will likely fail with a truncation error. 

While the solution below is extremely slow, it does work between  Teradata 14.10 and SQL Server 2008 R2 and SQL Server 2012.  It will still require a SSIS Data Conversion task to take the output and convert it to a DT_DBTIMESTAMP.

select CAST(CAST(<teradata_column> AS DATE FORMAT ‘YYYY-MM-DD’) AS CHAR(10))  || ‘ ‘ || TRIM(EXTRACT(HOUR FROM (<teradata_column>))) || ‘:’|| TRIM(EXTRACT(MINUTE FROM (<teradata_column>))) || ‘:’ || TRIM(CAST(EXTRACT(SECOND FROM (<teradata_column>)) AS INTEGER))

Posted in SQL Server Integration Services, Teradata | Tagged , , , , | Leave a comment