Two Ways to Get System and Server Information on SQL Server from Management Studio / Enterprise Manager

To collect information for your SQL Server Infrastructure, there are two ways I can recommend. 
The first, as mentioned from Technet recently, is to execute the following parameter details on any SQL Server installation (I tested back to 2000), by run the following command.

exec xp_msver "ProductName", "ProductVersion", "Language", "Platform", "WindowsVersion", "PhysicalMemory", "ProcessorCount"

-- result set is a table, with a row for each parameter

The second, and my preference as best pratice for gathering essential server information in a single row with more details, is the following, including the Collation, Clustering, Service Pack Level (product level):

select serverproperty('MachineName') MachineName

,serverproperty('ServerName') ServerInstanceName

,replace(cast(serverproperty('Edition')as varchar),'Edition','') EditionInstalled

,serverproperty('productVersion') ProductBuildLevel

,serverproperty('productLevel') SPLevel

,serverproperty('Collation') Collation_Type

,serverproperty('IsClustered') [IsClustered?]

,convert(varchar,getdate(),102) QueryDate,

case

when  exists (select * from msdb.dbo.backupset where name like 'data protector%') then 'HPDPused'

else 'NotOnDRP' -- where you would replace the

--data protector string with your third party backup solution

end

 -- thanks to my highly organised DBA buddy Pollus Brodeur, for introducing me to ServerProperty command several years ago

To run either of these queries across multiple servers in SSMS 2008 (assuming that you have more than one), under Registered Servers, right click on Local Server Groups, and select New Query.

References:  See all the recent Technet SQL Server Tips

Free E-Book on SQL Server 2008 R2

 

Free E-Book on SQL Server 2008 R2

Ross Mistry and Stacia Misner have done a wonderful job on this free e-book

http://blogs.msdn.com/microsoft_press/archive/2010/04/14/free-ebook-introducing-microsoft-sql-server-2008-r2.aspx

You can download the full ebook in XPS format here and in PDF format here.

 

 

 

The book contains 10 chapters and 216 pages:

 

PART I   Database Administration

 

CHAPTER 1   SQL Server 2008 R2 Editions and Enhancements 3

CHAPTER 2   Multi-Server Administration 21

CHAPTER 3   Data-Tier Applications 41

CHAPTER 4   High Availability and Virtualization Enhancements 63

CHAPTER 5   Consolidation and Monitoring 85

 

PART II   Business Intelligence Development

 

CHAPTER 6   Scalable Data Warehousing 109

CHAPTER 7   Master Data Services 125

CHAPTER 8   Complex Event Processing with StreamInsight 145

CHAPTER 9   Reporting Services Enhancements 165

CHAPTER 10   Self-Service Analysis with PowerPivot 189

 

 

Latest Simple-Talk.com Article Published on Deployment Management

After several months of reflection/rewrites with colleagues (because orignally I forgot the Rollback plan - oops), and reworking the bilingual templates, the great editors at Simple-Talk.com have pushed the latest work to the front page back in April:

 Deployment Management is Worth IT: These Templates Should Make You Believe It Too

by Hugo Shebbeare | 14 April 2010 |

Even experienced managers of IT operations can stumble over the most critical part- the documentation. If only there were useful examples around! We commissioned Hugo Shebbeare to explain what is required of such documentation, and why. He has also provided the... Read more...

Enjoy the FREE TEMPLATES for Change/Risk/Deployment Management, and, as always, look forward to your comments :)

HP Data Protector - SQL Server 2008 Supported, but Compatibility Pack 2005 Required

 

Recently, we found out that HP Data Protector had an update, and I took for granted that this update would make the product more compatible with SQL Server 2008 - but it still doesn't support SQL Server 2008 without installing the Backwards Compatibility Pack from SQL Server 2005. 
Not entirely such a pain, but just be aware, HPDP fans, that this warning message 'SQL DMO Component is not available which will cause MS SQL Integration to fail' really means to say you should download the SQL Server 2005 Backwards Compatibility Pack (SQLServer2005_BC_X). If you read the fine print of their Support Manual, you`ll notice on page twelve, point seven, that the SQL DMO download is required.

If you haven't installed the Backwards Compatibilty Pack first, then when using HP Data Protector with SQL Server 2008, you will see this error (HR 0x80040154] Class not registered): 

Or notice this error in your Debug.txt (set this in properties) in your Sysroot\Program Files\Omniback folder:

25/03/2010 11:27:48 AM  OB2BAR_Main.796.2948 ["integ/sql_7/sql7_dmo.cpp /main/hsl_dp61/hsl_hpit2_2/1":288] A.06.11 b243
[SQL7_Login] Failed creating object instance. Err code: -2147221164, REGDB_E_CLASSNOTREG
Function trace (currentLevel=3):
 Level  3: SQL7_Login()
 Level  2: SQL7_CheckConfig()
 Level  1: SQL7_Config()
 Level  0: main()

When Installing the SQL Server 2005 Backwards compatibility Setup, you only need the SQL DMO object components for HP Data Protector to work correctly with SQL Server 2008.

Developing Your DBA Skills Further Thanks to Experience with Multiple Relational Database Management Systems

Any DBA who really understands databases and SQL standards can make out with other RDBMSs quite well, and if they have not tried yet, I recommend it for the reasons I shall discuss below.

Specialists maintain, with reason, that you cannot be a Master/Mistress of multiple database management systems.  Fine, if you have decided that for your future, that there is not the possibility of being proficient in another system, then you have closed up your options before even trying perhaps(?).

Just in the same way as knowing multiple languages can help you know your own language better, I believe the same goes for database management systems.  This competition between vendors of varying DBMSs allows for great features to be passed from one database engine to the other and benefits us, the DBAs, greatly. The benefits of taking the deep step into another RDBMS can further enlighten your approach to resolving problems or creating unique solutions that embrace a resolution no matter what source they come from.  

After several months working back with Ellison's baby (trained on Oracle 8 in 1998), Oracle 11g and its solid architecture are impressive.  I especially appreciate the recovery approach Oracle takes, explicitly the redo/unlog log files: relying on the SCN (system change number, defined during a checkpoint) for bombproof recovery. The real issue with learning multiple backup systems running on MSSQL is that you could lose your backup recovery chain (unable to match LSN numbers) with mixed Tape/Disk backups, whereas with Oracle’s use of the SCN, this is not an issue.  The hardest part initially, was learning a whole new set of Acronyms related to Oracle's architecture for example: SMON, PMON, DBWn, RECO, ARCn et al.

Oracle Developer 2.1 is a pretty cool management tool also with easy result set export to XLS/CSV/etc., although I prefer SQL 2008's Management Studio. But also not to be ignored, is the excellent web-based Enterprise Manager from Oracle, which can take a bit of getting used to – with respect to finding your way around – but is very rich once you are familiar with how to take advantage of it.

Seasoned Oracle DBAs I enjoy working with have mentioned the lack of spooling in Oracle's developer (UPDATE, actualy spool does work fine in O.Dev 2.1), and after going back to using SQL+ for a while they have a very good point.  Oracle Developer and SSMS always have the code / results split up (unless in SQL DMO?), which doesn't always make it easy to associate the error with the section of code you are working on (unless you click on the error in SSMS report pane that is).  Setting the Spool on and off after working on production provides pretty bomber evidence for auditing operations on your production databases, or results during testing and development.

In a future post I will discuss some fun I have had with MySQL – and unfortunate tendency I have found also, that MySQL developers (who have worked very hard to build their system, and are naturally defensive) will ask the DBA for help in desperation, and as soon as the access is sorted, it only takes a short time before the real fixes become obvious and the developers’ knee-jerk reaction becomes to circle around their team in defence instead of accepting DBA intervention (we are there to help (!), and ultimately improve the bottom line for the company, or success of the respective project).  Anyway, that is par for the course often enough for DBAs, as mentioned to an extreme in my last post.  

PS enjoying a little work with PostgreSQL also recently, converting a schema to SQL Server and Oracle.

Really enjoying the tool SQuirrel SQL Universal SQL Client Version 3.1

Troubleshooting and Optimising SQL 2008 Reporting Services Configuration for Microsoft Dynamics CRM 4.0

Recently we have been struggling with configuration and installation of SQL Server Reporting Services 2008 when there was a co-existing 2005 Reporting Services instance. The following attempts to describe how to make sure your reports work between Microsoft Dynamics CRM and SQL Server Reporting Services.  This is a follow up to my previous post on optimising performance for CRM applications.

The obvious major change between the Reporting Services versions: IIS was required in SQL Server Reporting Services (SSRS) 2005, but no longer since SSRS has its own built-in Web Server to run reports on port 80. However, if you wish to have SSRS 2005 and 2008 co-exist on the same server (for perhaps masochistic reasons?), one has to decide which of the two Reporting Services versions will have to use a port OTHER than port 80.

Prerequisites for the data connector install

Configure your Data source so that the Report Servers Computer_name$ has DBO rights on the OrganisationName_MSCRM, then test that Reports actually work in Report Manager.
Make sure the NetworkServices account runs Reporting Services.

Before installing the Microsoft Dynamics CRM Connector, you most likely run into the Error:

Unable to validate SQL Server Reporting Services Report Server installation.  Please check that it is correctly installed on the local machine.
This will involve editing your install-config.xml file:

Make sure to modify this file to the name of your specific instance, on the following line:

<reportserverurl>http://ServerName/reportserver$SSRS08instanceName</reportserverurl>--> 

(where the Instance name should be written after the dollar sign) 

Run the following from command line afterwards:

SetupSrsDataConnector.exe /CONFIG "D:\Downloads\SrsDataConnector\install-config.xml"

You may have to uninstall and reinstall the Data Connector if you had the ‘Unable to validate…’ error.

If you require support for your configuration, please beware that Microsoft’s Customer Relationship Management team, or Microsoft Dynamics Team, vis-à-vis the Reporting Services Support do not overlap responsibilities  most likely if you report server responds http://ReportingServicesServerName/Reports

If you are able to see the above link, but form the CRM side when you look at a Report and you receive: ‘Reporting error

The Report cannot be displayed; check the Microsoft Dynamics CRM Community for solutions’ then you should try what I mention in this post before contact CRM support.

The Data Connector Optimisation

Data Source configuration should be done to get the most out of Reporting Services running with CRM Dynamics. To do so:

Click on the organisation, then right-hand side, on the toolbar, click Show Details, scroll down to the bottom and click on MS Data Source, and set up your Connection type as below.

Verify that Connection Type is set to "Microsoft CRM Data Extension" and Connect using: is set to "Credentials supplied by the user running the report"

and finally, click Apply. 

Connection String: MSCRM Data Connector Connection String

 Note, that if the CRM SRS Data Connector is not installed on the SRS Server, please install it and verify it is properly configured before choosing the Data Source Type above.

 If the Report Server login dialogue box shows up: "Type or enter a user name and password to access the data source: ", you are going to need to identify input the user's SystemUserID GUID and their Organization GUID.  To help us determine this, please run the following statement against your organization database:

 SELECT SystemUserId,OrganizationId, FullName FROM SystemUserBase WHERE Domain Name = 'domain\userid'

For "Log In Name:", please copy the SystemUserId GUID from the query and paste it in the text box.

For "Password:", please copy the OrganizationId GUID from the query and paste it in the text box.

 At this point if the reports do not run then transfer your case to the SQL Server Reporting Services team ( BIG THANKS TO THEM for their help! ) to continue troubleshooting.

Troubleshooting CRM/SSRS should also include a look at the Event Viewer, although at times, the ‘Please contact Microsoft CRM Dynamics Team for support’ or Reporting Services error explains little - thus check your SRS log files on the Reporting server in the following folder:

Drive:\Program Files\Microsoft SQL Server\MSSQL.*\Reporting Services\LogFiles