Skip to main content

Posts

SQL Server Reporting Services Tools.

Reporting Services Tools Reporting Services supports the full reporting life cycle. This support is provided through a collection of tools that come with Reporting Services, as identified in the following table. Authoring Management Delivery Report Designer Report Manager Report Manager Report Builder1.0 SharePoint reports library HTML Viewer Report Builder 2.0 Reporting Services Configuration Manager SharePoint libraries and web parts Third – party authoring tools SQL Server management applications Command - line utilities Report Viewer controls Reporting Services Web service Subscriptions

The Reporting Life Cycle

  The reporting life cycle is often described as a process consisting of three sequential phases. A report is first designed and developed in the authoring phase, made accessible to end-  users in the management phase, and then placed in the hands of end - users in the delivery phase. Authoring • End-User • Report Specialist | Management • Content  • System  | Delivery • On-Demand (Pull)  • Subscription (Push)   Authoring   The  authoring phase  of the reporting life cycle starts with the gathering of requirements through formal and informal processes. These requirements then drive the design of queries that provide data for the report. Data is integrated with charts, tables, matrices, or other presentation elements to form the basic report. Formatting and layout adjustments are then applied to produce a draft report that is validated for accuracy and consistency with the requirements before being published t...

SQL Server Reporting Service Modes

 (SSRS) Reporting Services runs in one of two modes: Native or SharePoint Integrated. In the Native mode, Reporting Services manages its content using its own internal, or “  native, ”  functionality. This is the traditional mode under which Reporting Services instances have been run.  Reporting Services deployments using Enterprise, Developer, Enterprise Evaluation, or Standard editions are capable of running in the SharePoint Integrated mode. In this mode, content management is handled through SharePoint. Native content management interfaces, such as Report Manager, are no longer accessible.  The SharePoint Integrated mode is a very appealing option for many organizations that want to leverage SharePoint as their enterprise content - management solution; however, there are some limitations, such as the lack of support for linked reports in this mode.  For those organizations that want to run Reporting Services in Native mode but still want to disp...

SQL Server Editions

SQL Server 2008 comes in nine editions, the following seven of which include Reporting Services:     Enterprise   Standard   Developer   Enterprise Evaluation   Workgroup   Web   Express with Advanced Services      The Enterprise and Standard editions are the only editions supported in a production environment. The Enterprise edition provides access to the full set of features available with Reporting Services and runs on 32 - bit, x64 64-  bit, and IA64 64 - bit platforms. The Standard edition provides access to a reduced feature set and does not support the IA64 64 - bit platform. It costs less than the Enterprise edition and may be more appropriate for smaller installations.   The Developer and Enterprise Evaluation editions provide access to the same features available through the Enterprise edition. The Developer edition is very inexpensive and is intended for development and testing environ...

Dedicated Administrator Connection

Dedicated Administrator Connection      It is rare, but SQL Server can stop responding to normal queries and appear to be frozen. How could this happen? Consider that when you bought the system that houses SQL Server, you likely planned for future growth and usage. No matter how much time and effort you put into this plan, though, some companies just outgrow their servers too quickly, and the servers can’t handle the workload. In a scenario like this, it is possible that SQL Server might stop responding to normal queries. To troubleshoot this issue, or any kind of problem where SQL Server just isn’t responding to normal calls, you need the Dedicated Administrator Connection (DAC).      The DAC is a special diagnostics connection that is always available for connection, even under normal operating circumstances. As the name implies, only administrators (members of the sysadmin server role) can connect to the DAC to run diagnostic queries and trou...

SQL Server 2012 Database Security Tasks.

By taking appropriate precautions, most of these breaches can be prevented or detected before they get out of hand.  1. Use a dedicated server for your database Host your database on a dedicated server. Whether it is local or in the cloud, spend the extra cash on a dedicated server to prevent security leaks and breaches. 2. Harden the Operating System On your dedicated server, the first step is to implement operating system hardening. Many hardening techniques exist. At a minimum, you need to: Change the default ports, as described below. Hide SQL instances from showing in the network, as described below. Allow only network protocols that are needed. CONNECT permission should be granted only on endpoints to logins that need to use them. If there is a need to work with SQL Login, install an SSL certificate from a trusted CA rather than SQL Server's self-signed certificates. Avoid the exposure of SQL Server to the public internet/intranet. Change default ...

SQL Server - Table backup.

     Now we are going to see how we are going to take a table backup. Backup single table exec master .. xp_cmdshell 'BCP export1.dbo.tab1 out new.txt -c -U sa -P sa -S ION-0481AD1D360\SQLTEST' Restore from file to table exec master .. xp_cmdshell 'BCP export1.dbo.tab1 in new.txt -c -U sa -P sa -S ION-0481AD1D360\SQLTEST'

SQL Server - Database Mail Configuration.

I recently had to setup Database Mail on dozens of SQL Server instances.   Rather than perform this tedious task using the SSMS GUI, I developed a script that saved me a lot of time which I'm sharing here.    My needs were simple so I only needed a single SMTP account and profile.  I decided to make the profile the default public one so that all msdb users would use this profile unless a different sp_send_dbmail @profile value was explicitly specified.  You might want to extend this script if you need other accounts/profiles, such as separate ones for administrative alerts or user reports. Setup Script Below is the template script I used for my task.  The sysmail_add_account_sp @username and @password parameters might be required depending on your SMTP server authentication and you will of course need to customize the mail server name and addresses for your environment. -- Enable Database Mail for this instance E...