Category Archives for Manage (SQL Server Admin)

SSRS – Grant Execute to All Stored Procedures

You need to grant execute permissions on all stored procedures in the database without explicitly knowing what those procedures are called.  A classic example for SQL Server Reporting Services is for your embedded Data Source credential to access procedures in your Reporting database. You have created your login on SQL Server, typed the login and password in the SSRS Data Source and now need to grant execute on all stored procedures in the database.  Use the following code to grant execute on all stored procedures…


USE ReportingDatabase

GO

CREATE ROLE db_executerole

GO

GRANT EXECUTE TO db_executerole

GO

EXEC sp_addrolemember 'db_executerole', 'ReportingorActiveDirectoryLogin'

GO

Burning an ISO installer image to USB Flash Drive

Becoming skilled in B.I. requires skills in installing images and virtual machines.   To install a new O.S. from a USB flash drive in Windows mount the ISO image using your favourite virtual drive software.  Now open a CMD prompt (run as Administrator or you will get Access Denied) then issue the following xcopy command :


xcopy D:\*.* E: /s/e/f

…where D: is the location of your mounted ISO image and E: is the location of your USB flash drive.

As long as your BIOS is set to boot from USB – Happy days.

Enough of the Hand Rolling – Easy Stored Procedures for your SELECTs

The problem with the I.T. industry in general, is everyone still wants to hand roll.

Here is a handy helper block supplied by Microsoft to help you create stored procedures for your tables.   The only requirement is you need to create the helper functions and procedures in the same database as your tables and your tables must have a primary key.  The following article works through how to use this helper code to create SELECT stored procedures for your tables.

For a start you will need to download and install the helper functions and stored procedures from MSDN Hand Roll Quit Kit.  Be sure to create the functions first, followed by the stored procedures.

Now, to generate a SELECT stored procedure for your table as output but not execute (my sample table is called “FactError”) …

EXEC pr__SYS_MakeSelectRecordProc 'FactError', 0

…and to output the script code but create the stored procedure in the same go …

EXEC pr__SYS_MakeSelectRecordProc 'FactError', 1

The output of the script for my FactError demo table produced the following code.  You can then go and modify the procedure as required to pull back any additional columns and joins, like your dimension tables.

IF EXISTS(SELECT * FROM sysobjects WHERE name = 'prApp_FactError_Select')

       DROP PROC prApp_FactError_Select

GO

----------------------------------------------------------------------------

-- Select a single record from FactError

----------------------------------------------------------------------------

CREATE PROC prApp_FactError_Select

       @FactErrorId int,

       @Error_Id int,

       @Date_Id int,

       @Time_Id int,

       @Process_Id int,

       @Server_Id int

AS

SELECT FactErrorId,

       Error_Id,

       Date_Id,

       Time_Id,

       Process_Id,

       Server_Id,

       ErrorCount

FROM   FactError

WHERE FactErrorId = @FactErrorId

 AND   Error_Id = @Error_Id

 AND   Date_Id = @Date_Id

 AND   Time_Id = @Time_Id

 AND   Process_Id = @Process_Id

 AND   Server_Id = @Server_Id

GO

As an added bonus the helper procedures and functions can also create UPDATE, INSERT and DELETE procedures however just the SELECT is good enough for the purposes of business intelligence.

Part 1 : Xray Vision for SQL Server Error Logs

You could be forgiven for not looking at your error logs….but only once….after all, look what happened to Krypton!

In this multi part series, we walk through how to transform the volumes of mundane SQL Error logs for your entire SQL Server fleet, into insightful, integrated and simplified 30,000 foot view dashboards.  Consolidating your error logs and creating Powerful Centralised Dashboards on a common Sharepoint 2010 platform then creates other flow on benefits, such as delegating those monitoring tasks to someone else – like the helpdesk !

In this first part of a multi part series we consolidate the error logs for your entire SQL Server fleet into one table.   I decided not to reinvent the wheel here….after all, Rodney Landrum has already done an outstanding job of creating a SSIS package for you already - download the source code here.

Rodney also has published many other useful methods of monitoring your SQL Server databases and an excellent Ebook to download called SQL Server Tacklebox full of work smarter not harder solutions, but enough of the Rodney plug…

Skills required for this walkthrough are Database and SSIS.   If you are new to business intelligence then this will be a perfect end to end learning tool, allowing you to apply these techniques to data you are very familiar with….SQL Server Error Logs !  Lets move on with staging and consolidating the data we are interested in.

1. To get started, download the original package here and extract the solution files to a common folder where you will be able to add additional solutions from this series later.   If you are having trouble with downloading Rodney’s package just Download All My Part 1 Files Here containing the SSIS solution, the specific database objects required and the entire database backup.  Whilst SQL 2012 now released I have used SQL 2008 R2 versions of files for better compatibility.

2. Create yourself a database that will contain your consolidated error logs, then create the database objects required.  You can either create the database objects manually or just restore the starter database both of which are available in my Part 1 Download Link.

4. Populate the SSIS_ServerList configuration table to include one row for each server you want to manage. 

As an example you could build a series of insert statements

INSERT dbo.ServerList_SSIS

(Server, Connect, DMZ)

VALUES

('YourSQLInstanceName',1,0)

5. Open the SSIS solution and update the Connection Manager named “DBA_Rep” to reflect the correct connection string of your new database.

6. Set the Package Protection Level property accordingly (e.g. don’t save sensitive) then save the package.

7. Create a SQL Server Agent Job that runs this package.  This will require adding a job step that runs the SSIS package either from file location or MSDB database (whatever deployment method you choose for the package).  If unsure just go for the file location option.  A good general reference for running SSIS packages through SQL Server Agent jobs is outlined here.

8. Schedule the package to run once daily (say, 7am).  This will capture the overnight logs which will set us up well for Part 2 of this series.

9. Run the package and make sure the package runs successfully.  Note –  before we move onto Part 2 of the series, you need to ensure there are rows in the consolidated error log table for each of the servers you configured in the ServerList_SSIS table. 

 SELECT Server, COUNT(1) AS CountErrorLogRows

FROM SQL_ErrorLog

GROUP BY Server

ORDER BY Server

In Part 2 of this Xray Vision for SQL Server Error Logs series - we use a second SSIS package to funnel our consolidated error logs into a predesigned Kimball Star Schema data model. Stay tuned !

If you have any quesitons please Get in Touch on Linked In.

SQL Server Licensing Just Got Way More Expensive … Or Did It ???

Before we kick off, June 30 2012  will have been and gone and you would have missed the opportunity to purchase SQL Server 2012 Enterprise in the Server plus CAL license model – after which will be core only.

Some other notes when working out SQL 2012 licensing :

• The price of the SQL Server CAL goes up about 25%
• The per server license for Standard edition is about the same
• The per server license for Business Intelligence edition is about the same as Enterprise 2008 R2
• The per core price for SQL 2012 standard and enterprise is a quarter the per proc licence as the equivalent editions in Enterprise 2008 R2, hence if you have more than 4 cores per proc in your servers, your price goes up from there.

But my SQL Server 2008 R2 works just fine thank you….why do I need to upgrade ?   For starters, if you have software assurance it won’t cost you anything (only shore-ups from the licensing differentials).   Otherwise, you won’t need much prompting…your business people will be begging you for the 2012 release as there are a host of much needed improvements for more productive and more colourful Business Intelligence championed by PowerView and PowerPivot.  For Sharepoint folk there is now even more Business Intelligence capability. Easier, tighter integration, new tools and features to lower the cost of information delivery, to get your message to an even broader audience, and to give the business what they want – “to build their own B.I. solutions without engaging I.T.”! 

For the DBA’s SQL Server 2012 sports a host of high availability options with the gem being shared nothing multi site high availability (particularly when you combine contained databases with “Always On” for seamless application database failover), better support for data tier applications, and better support alround for cloud and portability basically.  Those with VSphere might want to compare VSphere’s HA functions with SQL Server 2012 Always On. 

If you have drafted some basic calculations by now, you may be seeing the cost of upgrading your current infrastructure going up up up.    The only real way to save money is to create a private SQL Server cloud, using consolidation as the vehicle to drive down costs. Your savings will come from the ability to run as many copies of SQL Server (SQL Server instances or Virtual machines) as your farm will allow (licensed on the number of cores in the physical hardware).  

I personally think the licensing changes will provide a more colourful business case for consolidation as the SQL 2012 licensing changes now truly reward those who consolidate and punish those who don’t.

For more info, you may find the Microsoft Assessment Planning toolkit for SQL Server 2012 useful. It has licence info plus discovery capabilities MAP Planning Tool 2012.

And just for gags – you could price the new Microsoft HP Consolidation Appliance for SQL Server Private Cloud SQL Server Private Cloud Appliance.

Show me the Licenses ! SQL Server on VSphere for SQL 2008 R2 and 2012

Some notes on SQL Server virtualisation on Vsphere for SQL Server 2008 R2 and 2012. You may find your existing licenses cover you for more virtual machines than you have planned to deploy…

http://blogs.vmware.com/apps/2012/03/virtualizing-sql-server-on-vsphere-licensing.html

 

Syntax for INSERT INTO vs SELECT INTO

One is commonly confused for the other….so ….

SELECT INTO inserts data into a new table and creates the new table on the fly (Table doesn’t exist) …


SELECT * INTO dbo.NewTableThatDoesntExist
from dbo.TableYouWantToCopyFrom

INSERT INTO inserts data into an existing table …


INSERT INTO dbo.NewTableThatAlreadyExists (Column1, Column2)
SELECT Column1, Column2
FROM dbo.TableYouWantToCopyFrom

Reference from Pinal Dave.