BLOGS

ByDr. SubraMANI Paramasivam

SQL Server 2008 New Features

With SQL Server 2008 version there are quite a number of new features are added in. Those are listed below. (I know this is pretty old update, but new when SQL 2008 was released).

  1. Installation of SQL Server 2008 with more options
  2. Compressed Backups
  3. Enhanced configuration
  4. Management of Audits with CDC (Change Data Capture)
  5. New Table Value Parameter
  6. Filestream Data types
  7. Sparse Columns
  8. Encryption Enhancements
  9. High Availability
  10. Performance
    1. Performance Data Management
    2. Resource Governor
    3. Freeze Plan
  11. Declarative Management Framework (DMF)
  12. Development Improvements
    1. LINQ Supports
    2. MERGE Statement
  13. Spatial Data type
  14. Analysis Services Improvements
  15. ETL Enhancements
  16. Few on Reporting Services
    1. No longer use of IIS
    2. Better Graphing
    3. Export to Word Support
  17. Some of the Deprecated & Discontinued Features
ByDr. SubraMANI Paramasivam

Memory upgrade can lead to performance issues

I had a live scenario where my servers were upgraded with the memory and all my ETL & Replication were totally down. Couldn’t figure out the actual issue and need to reset the whole process and run manually. I have about 6TB of database and 4Bn records. My initial workout with the admin guy is to find out the server problem itself and then came out with the server is kind of parallelized with the faulty motherboard. I have 2 processor and equal amount of RAM supplied to both of them. When the memory was upgraded equally one part of the processor couldn’t recognize the memory and found the faulty motherboard. One side it was good to find out the actual memory in place helped to identify and replace the faulty one. But on the other side the production system was totally disastrous. Then need to configure with the resource governor to distinguish the workload to help more for replication and also the default one. After the resource governor was setup properly, the following query produced the results to show average time take per millisecond.

SELECT
rpool.name as PoolName,
COALESCE(SUM(rgroup.total_request_count), 0)as TotalRequest,
COALESCE(SUM(rgroup.total_cpu_usage_ms), 0)as TotalCPUinMS,
CASE
WHEN SUM(rgroup.total_request_count) > 0 THEN
SUM(rgroup.total_cpu_usage_ms) /SUM(rgroup.total_request_count)
ELSE
0
END as AvgCPUinMS
FROM sys.dm_resource_governor_resource_pools AS rpool LEFT OUTER JOIN
sys.dm_resource_governor_workload_groups AS rgroup
ON rpool.pool_id = rgroup.pool_id
GROUP BY rpool.name
GO

Result:

PoolName TotalRequest TotalCPUinMS AvgCPUinMS
——————————————————————————–
PP_BusUsers 0 0 0
PP_SSUsers 0 0 0
distribution 2613809 30510900 11
default 5730860 179191854 31
internal 0 4966873 0

ByDr. SubraMANI Paramasivam

How to create a Linked Server?

This article will help you create your a new linked server from one instance to another instance.

1. Connect to the 1st instance of SQL Server.

2. Under “Server Objects” look for “Linked Servers” and right click on it and select “New Linked Server”. A new popup box will appear like the below one.

 

3. In the linked server text box, type the second instance name.

4. In the server type choose Other data source and then select SQL Server Native Client 10.0.

5. In the product name and data source key in the same instance name that was entered in point 3 above. The entry in point 3 is to make sure that we know what we are connecting to.

6. In the catalog key in the specific database name that you want to connect to.

7. In the security tab, click “Be made using this security context” option and key in a remote login & password that already has access to the second instance. If not available then create a new one and put that information here.

8. Thats it, you are now done creating Linked Server.

If you have any issue in creating a linked server then you may not have enough permission on the instance. Even if you have sysadmin access and have problem creating it then get in touch with me on info@yoursqlman.com for further information.

All the best!

ByDr. SubraMANI Paramasivam

How to browse the database objects through Linked Servers

Without actually connecting to another instance of SQL server, is it possible to browse the database objects like tables & views? The answer is YES. How do I do that? Well, for a working scenario we need at least 2 SQL instances either on same server or on a different servers. If you have already not created the Linked server, click here to see this article and then do the below steps.

1. Connect to the 1st instance of SQL Server in Management Studio.

2. Browse through the folders to see “Server Objects” and look for “Linked Servers”. Click the plus sign on it to expand.

3. Now click on the plus (+) sign that is already configured and choose the name of the linked server and then expand Catalogs.

4. Here you can see the number of databases and expand it.

5. You will only see Tables & Views and not the stored procedures or any other objects.

6. You can right click on any table and select “Script Table as” and click on “Select to” => New Query window.

7. You can see a query automatically created pointing to the table in a 4 point convention (…).

Note: Writing any linked server queries for production usage will obviously have performance problems. If the SLA is agreeable then you can proceed or you need to take extra measure. If you have any issue in tackling these type of issues, do get in touch with info@yoursqlman.com for further information.

 

ByDr. SubraMANI Paramasivam

Group Container in SSIS

A group container helps to have more than 2 data flows to be grouped in one container but does not have any properties for itself. Also there is no precedence constraint in or out to this group container, instead it needs to be used with the tasks itself.

To have this group, select more than 2 tasks and right click and select the group option. This also helps in hiding and showing the tasks within the group.

If you have any questions drop me an email on mani@yoursqlman.com

ByDr. SubraMANI Paramasivam

Data Warehousing – Definition Plus

The data warehousing market consists of tools, technologies, and methodologies that allow for the construction, usage, management, and maintenance of the hardware and software used for a data warehouse, as well as the actual data itself.

In order to clear up some of the confusion that is rampant in the market, definitionplus provides you with fact:


Data Warehouse:

The term Data Warehouse was coined by Bill Inmon in 1990, which he defined in the following way: “A warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management’s decision making process”. He defined the terms in the sentence as follows:

Subject Oriented:

Data that gives information about a particular subject instead of about a company’s ongoing operations.

Integrated:

Data that is gathered into the data warehouse from a variety of sources and merged into a coherent whole.

Time-variant:

All data in the data warehouse is identified with a particular time period.

Non-volatile:

Data is stable in a data warehouse. More data is added but data is never removed. This enables management to gain a consistent picture of the business.

(Source: “What is a Data Warehouse?” W.H. Inmon, Prism, Volume 1, Number 1, 1995).

This definition remains reasonably accurate almost ten years later. However, a single-subject data warehouse is typically referred to as a data mart, while data warehouses are generally enterprise in scope. Also, data warehouses can be volatile. Due to the large amount of storage required for a data warehouse, (multi-terabyte data warehouses are not uncommon), only a certain number of periods of history are kept in the warehouse.

Ralph Kimball provided a much simpler definition of a data warehouse. As stated in his book, “The Data Warehouse Toolkit”, on page 310, a data warehouse is “a copy of transaction data specifically structured for query and analysis”. This definition provides less insight and depth than Mr. Inmon’s, but is no less accurate.

ByDr. SubraMANI Paramasivam

List of Business Intelligence (BI) tools

Business Intelligence (BI) tools are widely used for reporting, dashboarding and analysis. The following BI tools were thoroughly examined on 70 criteria considered important for high productivity and Business Intelligence systems that actually add value to your organization. In random order.


No. Tool Version Vendor
1. Oracle Enterprise BI Server 7.8 Oracle
2. Business Objects Enterprise XI r2 Business Objects (now SAP)
3. SAP NetWeaver BI 7.0 SAP
4. SAS Enterprise BI Server 9.1.3 SAS Institute
5. IBM Cognos TM/1 & Executive Viewer 9.1 Applix (now IBM)
6. BizzScore Suite 7.2 EFM Software
7. WebFocus 7 Information Builders
8. Excel, Performance Point, Analysis Server 2007/2005 Microsoft
9. QlikView 8.5 QlikTech
10. Microstrategy 8 Microstrategy
11. Hyperion System 9 Hyperion (now Oracle)
12. Actuate 9.1 Actuate
13. IBM Cognos Series 8 8.3 Cognos (now IBM)
ByDr. SubraMANI Paramasivam

Instances in SQL Server

Instance is nothing but SQL Server Installation. In SQL Server 7.0 only one installation was allowed and this changed from 2000 Version onwards. Default instance is nothing but connecting to SQL Server using the computer name. Where as the named instance such as Mani will have computername\mani. For example if the computer name is MyServer, then the named instance would be MyServer\Mani. Now I can install many number of named instances and the maximum is based on the edition that you use. The list is provided below for each edition.

The port used by default instance is always 1433 and named instances use dynamic allocation.

The total number of instances including the default instance of SQL Server supported by all editions differ. The following gives a clear idea of the supported instances for each editions.

Enterprise – 50

Developer – 50

Standard – 50

Workgroup – 16

Express – 16

Note: Windows Failover Cluster supports only 25 instances.