sql4You

Today during installation of SQL Server (Denali in fact) I got very interesting message ( as below).

No additionat information, no help and no possibility to continue the installation process. I wanted to find out how to resolve this problem, but in fact there was only one way to solve this – downloading the new iso image of SQL Server. I also find out that this “very interesting” message can appear in two cases:

1. Installation files coruption

2. .Net Framework problems ( in this case try to reinstall .net framework)

Hyper-V is the Best Solution for SQL Server

Quick overview of document content is the table below:

Features	Microsoft Hyper-V	VMware
Near Native Performance	V	V
Full support for SQL Server Failover Clustering	V
Physical and Virtual Management for Hyper-V and VMware virtual machines	V
In-guest Monitoring	V
Lower Total Cost of Ownership (TCO)	V

If You need to change the default port for SSRS just perform these simple steps:
1. Run Reporting Services Configuration Manager



2. Go to web service configuration and change the port number



3. Go to Report Manager and change the port number by clicking Advanced configuration

Recently I had to prepare a data-driven subscriptions – what can be difficult in creating data driven subscription. Look at these 3 simple steps:
Step 1 – Prepare Your report. Let’s say there are two parameters in this report and one of them is called CurrentUser – this parameter holds information about current user, default value of parameter is from global parameter UserID, but you can also choose this parameter from a list)
Step 2 – Deploy Your report to SSRS Server and try to run this report.
Step 3- Go to Report manager and click Subscriptions, then Data driven subscription and…. We have got a problem. You can’t create subscription. Why?
Step 3a – Go to data source and check if credentials are saved in the data source ( for subscriptions and snapshots You have to store credentials in the data source)
Step 3b –Try to create a data-driven subscription once again and…..a problem once again.
Data source was checked in step 3a, this is not a linked report, so what’s the problem? Parameters? Well, there are two parameters in this report, but I planned to get the values for these parameters from my dataset, from prepared view/table. The problem is that we are not able to create a data driven subscription for report which uses UserID as a parameter value – even it’s a value which will be overwritten by parameter value from subscription. Sorry folks…

Few days ago MSFT published Microsoft® SQL Server® code-named ‘Denali’ – Community Technology Preview 1 (CTP1)

With SQL Server code-named ‘Denali’ customers will benefit from the following added investments:
•Enhanced mission-critical platform: A highly available and scalable platform designed to with greater flexibility, lower TCO, ease of use, and the performance required by the most mission-critical applications.
•Developer and IT Productivity: New additional tools will help developers build innovative applications with reduced time-to-market while IT professionals benefit from greater operational control and ease of use.
•Pervasive Insight: Stunning new managed self-service experiences for end users and holistic data

I have just downloaded iso file and …. It’s time for testing….I will try to show all new features on this blog so come back here in few day…”Deanli Academy” has just started.

Step 1 – Read what’s new http://msdn.microsoft.com/en-us/library/bb500435(SQL.110).aspx

Step 2 - Download

Step 3 – Start installation…..

Step 4 – To be continued….

If You are still using Excel 2003 and You need to analyze data from cubes in Excel, You will really need a support. Try to install special SSAS Excel Add-in and enjoy new features. The Microsoft Excel Add-In for SQL Server Analysis Services fills a gap by providing flexible free-form reports coupled with structured reports. The structured reports are similar in many ways to PivotTable reports, but with some added functionality, most notably the following:
• Reports automatically support advanced Analysis Services capabilities such as actions, what-if analysis, and Drillthrough.
• Advanced techniques are available for specifying members from a dimension, including wild card searches and data-driven selection

Microsoft decided not to support this add-in so it’s hard to get it, but you can download it from here, but remember you use it at Your own risk…

Just after standard installation of SSIS you have to use a defeault sql server instance for SQL Server packages storage.
Is it possible to you use a differnet instance? Let’s say the named instance of SQL Server. It’s possible of course, but You have to change the standard cofiguration file.

The Integration Services service relies on a configuration file for its settings. By default, the name for this configuration file is MsDtsSrvr.ini.xml, and the file is located in the folder, %ProgramFiles%\Microsoft SQL Server\100\DTS\Binn

If You made the standard installation with default local instance and you accept the default ssis configuration you really do not have to edit this file. If You want to “pin” your SSIS instance to named SQL Server instance, just change the . to ServerName\InstanceName

after this modification Your file should looks like this:

Important
The Integration Services service loads the configuration file when the service is started. Any changes to the registry entry require that the service be restarted.

If You want to create your own SSIS package temaplate and access it everytime you create a new Package you can place the prepared template in 

  C:\Program Files\Microsoft Visual Studio X\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems

X – means 9 for SQL Server 2008 or 8 for SQL Server 2005

It’s a standard location for such templates:

Now adding new package looks like this:



Before creating Package Template:



After creating Package template:



Important to do !!!

After adding new package from template remember to generate new GUID for this package to avoid packages with the same ID

Service Pack 2 for SQL Server 2008 contains updates for SQL Server Utility, Data-Tier Application (DAC), as well as integration capability for Microsoft Reporting Services with the Microsoft SharePoint 2010 Technologies. Service Pack 2 introduces support for a maximum of 15,000 partitions in a database, and includes SQL Server 2008 SP1 Cumulative Update 1 to 8.

 What’s New:

• 15K partitioning Improvement. Introduced support for a maximum of 15,000 partitions in tables and indexes in Microsoft SQL Server 2008 Service Pack 2 in the Enterprise, Developer and Evaluation Editions. For details on how this support can be enabled and disabled, along with recommended operation paramaters, see the white paper Support for 15000 Partitions.

• Reporting Services in SharePoint Integrated Mode. SQL Server 2008 SP2 provides updates for Reporting Services integration with SharePoint products. SQL Server 2008 SP2 report servers can integrate with SharePoint 2010 products. SQL Server 2008 SP2 also provides a new add-in to support the integration of SQL Server 2008 R2 report servers with SharePoint 2007 products. For more information see the “What’s New in SharePoint Integration and SQL Server 2008 Service Pack 2 (SP2)” section in What’s New (Reporting Services).

• SQL Server 2008 R2 Application and Multi-Server Management Compatibility with SQL Server 2008.

• SQL Server 2008 Instance Management.With SP2 applied, an instance of the SQL Server 2008 Database Engine can be enrolled with a SQL Server 2008 R2 Utility Control Point as a managed instance of SQL Server. For more information, see Overview of SQL Server Utility in SQL Server 2008 R2 Books Online.

?Data-tier Application (DAC) Support.Instances of the SQL Server 2008 Database Engine support all DAC operations delivered in SQL Server 2008 R2 after SP2 has been applied. You can deploy, upgrade, register, extract, and delete DACs. SP2 does not upgrade the SQL Server 2008 client tools to support DACs. You must use the SQL Server 2008 R2 client tools, such as SQL Server Management Studio, to perform DAC operations. A data-tier application is an entity that contains all of the database objects and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects. For more information, see Designing and Implementing Data-tier Applications.

You can download Service Pack 2 from 

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=8fbfc1de-d25e-4790-88b5-7dda1f1d4e17&displaylang=en

You want to configure SSRS to use basic authentication with a SMTP Server – FORGET IT – it is not supported…but You can try a way to get around this problem…have a look at this solution…

1. You need your own virtual SMTP server and set it to relay messages with basic authentication to the remote server.
2. You need to install the SMTP feature on your Windows Server instance. You can do this by Add Feature in Windows Server 2008 /2008 R2 or Add/Remove Programs in earlier version

3. We select SMTP from the list of feature and install with all required role services.

Click on “Close” after the installation completes.

4. After installation,You have to set up new SMTP server to relay emails via the remote SMTP server. Click on Start, Administrative Tools and then on Internet Information Services (IIS) 6.0 Manager

5. Expand the tree and go to virtual SMTP server.

6. Choose Properties (right click on virtual SMTP server). Under the general tab, select the IP address of for the SMTP server.

7. Use Access tab and click on “Relay”. It is recommended to restrict access to this virtual SMTP server only to computer with SSRS installed, so if You wish to limit the computers that are allowed to relay through this virtual SMTP server by specifying them explicitly then keep the “Only the list below” option selected and add the IP addresses

8. Accept configuration – “OK” and go to the Delivery tab. On the Delivery tab click on “Outbound security”.

9. THE MOST IMPORTANT ELEMENT TO CONFIGURE: Select “Basic authentication” and specify the username and password that you need to use to authenticate to the remote SMTP server and Accept configuration with “OK”

10. Go to “Outbound connections” and set the remote SMTP Server port number.

11. Finally go to Advanced and spcify your remote server address in the “Smart host” textbox.

12. The last element of your configuration is to configure SSRS – You can do that by running the Reporting Services Configuration Manager utility.

13. Connect to you SSRS instance and then click on the “Email Settings” option.

Set the email address that will be shown as the sender when SSRS sends emails.

In the “SMTP Server” textbox type the IP address that you selected for the virtual SMTP server which was configured by you in this case.

Click on “Apply” and then “Exit” after the settings have been applied successfully.

Congratulations!

You have just configured Reporting to send emails via the local virtual SMTP server which will relay the emails using the Remote SMTP server with basic authentication