MSSQLTips - Latest SQL Server Tips

Automate SQL Server Analysis Services Tasks with PowerShell - Part 2

Fri, 17 May 2013 08:00:00 GMT

In my last tip about PowerShell for SQL Server Analysis Services, we offered an introduction to PowerShell. In this second tip we will demonstrate how to do a few different discrete tasks including: creating a backup with the current date, using variables to specify the partition to be processed, run an XMLA script to create a new partition and call MDX scripts using PowerShell. Check out this tip to learn more.

Introduction to Data Explorer for Excel

Thu, 16 May 2013 08:00:00 GMT

I have been hearing about a new tool from Microsoft called Data Explorer. I would like to know what this tool is all about, how to install it, and how to get started with it.

Creating a multi-server query SSRS report using Central Management Servers

Wed, 15 May 2013 08:00:00 GMT

We would like to have a single SQL Server Reporting Services (SSRS) report that will display SQL Servers versions for all of our SQL Servers that are registered in Central Management Server.

Rename SharePoint Content Database

Tue, 14 May 2013 08:00:00 GMT

I've been tasked to manage the SharePoint 2010 and 2013 database servers after deployment. While looking at the list of databases, I saw several content databases that have GUID values appended to their names. In this tip we look at how you can rename the database and remove the GUID value from the name.

Centralizing SQL Server Express Job Scheduling

Mon, 13 May 2013 08:00:00 GMT

We're running multiple versions of SQL Server (SQL Server 2005, 2008, 2012 and Express Editions) in our Windows Domain. I'm interested in monitoring all my SQL Server backups, but for SQL Server Express we're currently using scheduled backups using Windows Task scheduler on each SQL Server Express since SQL Agent does not come installed on Express Editions. Is there an alternative where I can utilize my existing SQL Server infrastructure? In this tip we will utilize SQL Agent on an existing SQL Server to help centralize SQL Express Backup Scheduling to replace Windows Task scheduler.

Using source data from an SSRS report in PowerPivot for Excel

Fri, 10 May 2013 08:00:00 GMT

Operational reports developed in SSRS can return huge amounts of data and they have already gone through the development and testing effort to make sure the results are correct. Such a report might not have the data in the form to analyze in a customizable manner and many users may want to analyze similar data in Excel. In this tip we would look using an existing SSRS report to provide the data for PowerPivot in Excel.

Updating the TCP Port on a SQL Server Cluster Node

Thu, 9 May 2013 08:00:00 GMT

When installing 2 SQL Server instances on a Windows Cluster, the installation program requires a Named Instance during the installation of the second node and a dynamic TCP port is created. Our failover procedure for SQL Server instances in a cluster has a DNS name mapped in ActiveDirectory to a specific Cluster Server Name. So, when someone tries to connect to the SQL Server instance by the new DNS name, the connection fails for the named instance because it is not on TCP port 1433. In this tip we look at how to update the TCP port to 1433 after the installation in a clustered environment.

Extending SQL Server DDL Triggers for more functionality: Part 1

Wed, 8 May 2013 08:00:00 GMT

A previous tip, SQL Server DDL Triggers to Track All Database Changes, generated a lot of discussion involving ways to make the provided DDL trigger more useful.

Upgrading to SQL Server 2012 Express

Tue, 7 May 2013 08:00:00 GMT

Sometime back one of my friends was asking about upgrading SQL Server 2008 R2 Express to SQL Server 2012 Express. As you may know, the SQL Express edition is free and is often used for smaller database applications and most times there are not full-time DBAs supporting these installs, so I decided to share some online resources and steps to do the upgrade.

Using PowerShell for SQL Server Analysis Services tasks - Part 1

Mon, 6 May 2013 08:00:00 GMT

There are several repetitive SSAS tasks that I perform on a regular basis and I want to know what options there are to automate some of these tasks such as processing partitions, creating backups and monitoring SSAS. In this tip we look at how to use PowerShell to automate some of these tasks.