DisCopy


Friday 29 August 2014

Some Important, Interesting and useful Options and set Operators..

There would be times, we need to format the output in a specific way and ignore or disable the default output format/parameters. Here some very interesting Items.


1.      To Disable the Column/Row headings of the Query Result:
use -b option with the isql.

2.      To Disable the Row count of the Query Result:
set nocount on.
suppress the lines reporting the number of rows affected to generate output with no extra messages using the set nocount on option.

3.      To see the Query Plan with out executing the Stored procedure.
It captures plans in stored procedures without actually executing them.


4.      How to disable return status when executing stored procedure or a T-SQL Query?
set proc_return_status on|off
controls sending of a return status TDS token back to the client. set proc_return_status off suppresses sending the return status token to the client, and isql client does not display the (return status = 0) message. The default for this parameter is on.


5.      How to avoid Arithmetic Overflow errors causes the process termination:
This option determines how Adaptive Server behaves when an arithmetic error occurs. The two arithabort options  arith_overflow and numeric_truncation, handle different types of arithmetic errors. You can set each option independently or set both options with a single set arithabort on or set arithabort off statement.
·        arithabort arith_overflow – specifies the Adaptive Server behavior following a divide-by-zero error, range overflow during an explicit or implicit datatype converson, or a domain error. This type of error is serious. The default setting,arithabort arith_overflow on, rolls back the entire transaction in which the error occurs. If the error occurs in a batch that does not contain a transaction, arithabort arith_overflow on does not roll back earlier commands in the batch; however, Adaptive Server does not execute any statements in the batch that follow the error-generating statement.
If you set arithabort arith_overflow off, Adaptive Server aborts the statement that causes the error, but continues to process other statements in the transaction or batch.
·        arithabort numeric_truncation – specifies the Adaptive Server behavior following a loss of scale by an exact numeric type during an implicit datatype conversion. (When an explicit conversion results in a loss of scale, the results are truncated without warning.) The default setting, arithabort numeric_truncation on, aborts the statement that causes the error, but Adaptive Server continues to process other statements in the transaction or batch. If you setarithabort numeric_truncation off, Adaptive Server truncates the query results and continues processing.


6.      To Handle divide-by-zero errors:
This option determines whether Adaptive Server displays a message after a divide-by-zero error or a loss of precision. By default, thearithignore option is set to off. This causes Adaptive Server to display a warning message after any query that results in numeric overflow. To have Adaptive Server ignore overflow errors, use set arithignore on. You can omit the optionalarith_overflow keyword without any effect.


7.      How to insert/update identity values of a table:
set identity_insert TEST on
go
insert TEST (syb_identity)
values (21)
go
set identity_insert TEST off
go

set identity_update TEST on
go
update TEST set c2 = 10 where c1 =1
select * from TEST
c1              c2
--------     -------
1                 10

set identity_update TEST off


8.      How to update systabstats w/o affecting sysstatistics:
update table statistics updates statistics that are stored in systabstats table, such as rowcount, cluster ratios, and so on. update table statistics does not affect column statistics stored in sysstatistics.

9.      Differences among update all statistics,  update index statistics and update statistics:

update all statistics
update index statistics
update statistics

update all statistics regenerates and update the table statistics stored in systabstats for each data and index partition of the table.


Specifying the name of an unindexed column or the nonleading column of an index generates statistics for that column without creating an index.



When you create a nonclustered index on a table that contains data, update statistics is automatically run for the new index. When you create a clustered index on a table that contains data, update statistics is automatically run for all indexes.



Saturday 23 August 2014

Sybase Adaptive Server vs Microsoft SQL Server High Availability and Disaster Recovery Solutions.



I have been Administering, Maintaining and Mentoring both Sybase Adaptive Server and Microsoft SQL Server for the past decade with Sybase is my primary skillset but slowly transforming into MS SQL Server as Primary due to the requirements in the Projects.

It is very difficult to make the comparison between SQL Server and Sybase ASE to identify which is the better product as both are siblings.

In a Quick nutshell:

        1984 - Sybase founded and creates first RDBMS designed for online transactions.
        1990 - Sybase entered into a technology sharing agreement with Microsoft which resulted in Microsoft marketing its own SQL Server. Microsoft releases SQL Server 1.1 for OS/2 with the engine of Sybase SQL Server.
        1993 – SQL Server 4.2 for NT was released with Microsoft taking more control.
        1994 – Sybase and Microsoft split ways and launched their own products but with same architecture and features.
        1995 – SQL Server 6.0 is released; the first version without Sybase Support.

Over the past 2 decades, Microsoft has facilitated multiple features, tools and utilities for the ease of access and maintenance and administering compared to Sybase which provided Sybase central only. From a DBA standpoint MS SQL Server is easier to configure and maintain with its GUI tools but with sound scripting knowledge and right configuration Sybase is still robust. The performance of your databases depends rather from the experience of the database developers and database administrator than from the Product. You can use both of these RDBMS to build stable and efficient system. 

Microsoft SQL Server and Sybase ASE have a common architecture because, until version 4.2, Microsoft simply licensed Sybase's database server software. So these products had one kernel. After version 4.2, these products have diverged. Microsoft has produced versions 6.0, 6.5, 7.0, 2000, 2005, 2008 and 2012 of Microsoft SQL Server, while Sybase has produced versions 4.8, 4.9, System 10 and System 11 of Sybase SQL Server and versions 11.5, 11.9, 12, 12.5, 15 and 15.7 of Sybase Adaptive Server Enterprise.

High Availability and Disaster Recovery Concepts. 

When we compare the HA nd DR features till the year 2010 i.e. SQL Server 2008 and Sybase ASE 15, I found setup and configuration of Replication is easier in SQL Server due to its GUI based tools, but Sybase has clearly an edge with the features and availabiity.
        Major advantage of Sybase replication is the Secondary replica can be used for the Reporting purpose for all kinds of Replication.
        Fine tuning the Replication process and Trouble shooting is easier.
        Database level Replication to multiple replicas can be setup easily.
You can make the best selection of a database technology for a high availability and disaster recovery solution when all stakeholders have a shared understanding of the related business drivers, challenges, and objectives of planning, managing, and measuring RTO and RPO objectives. But, recent times and especially, I was pleased to see the path-breaking new feature provided in SQL Server 2012 which combines all the pros and features of HA and DR called AlwaysOn Availability Groups. It's really an edge over all the available HA/DR options. As per the requirements in the recent Projects, I have set up multiple times the AlwaysOn Availability Groups and could see tangible improvements in the Databases Availability, Performance and ease of Maintenance. 

I have not considered SAP ASE 16 or MS SQL 2014.

For a given software application or service, high availability is ultimately measured in terms of the end user’s experience and expectations. The principal goal of a high availability solution is to minimize or mitigate the impact of downtime. A sound strategy for this optimally balances business processes and Service Level Agreements (SLAs) with technical capabilities and infrastructure costs.

The resulting value is often expressed by industry in terms of the number of 9’s that the solution provides; meant to convey an annual number of minutes of possible uptime, or conversely, minutes of downtime.

Number of 9’s
Availability Percentage
Total Annual Downtime
2
99%
3 days, 15 hours
3
99.9%
8 hours, 45 minutes
4
99.99%
52 minutes, 34 seconds
5
99.999%
5 minutes, 15 seconds

Data redundancy is a key component of a high availability database solution. Transactional activity on your primary SQL Server instance is synchronously or asynchronously applied to one or more secondary instances. When an outage occurs, transactions that were in flight may be rolled back, or they may be lost on the secondary instances due to delays in data propagation.

        Avoiding downtime. Outage recovery costs are avoided all together if an outage doesn’t occur in the first place. Investments include the cost of fault-tolerant and redundant hardware or infrastructure, distributing workloads across isolated points of failure, and planned downtime for preventive maintenance.
        Automating recovery. If a system failure occurs, you can greatly mitigate the impact of downtime on the customer experience through automatic and transparent recovery.
        Resource utilization. Secondary or standby infrastructure can sit idle, awaiting an outage. It also can be leveraged for read-only workloads, or to improve overall system performance by distributing workloads across all available hardware.

SQL Server AlwaysOn

AlwaysOn is a new integrated, flexible, cost-efficient high availability and disaster recovery solution. It can provide data and hardware redundancy within and across data centers, and improves application failover time to increase the availability of your mission-critical applications. AlwaysOn provides flexibility in configuration and enables reuse of existing hardware investments.
An AlwaysOn solution can leverage two major SQL Server 2012 features for configuring availability at both the database and the instance level:
        AlwaysOn Availability Groups, new in SQL Server 2012, greatly enhance the capabilities of database mirroring and helps ensure availability of application databases, and they enable zero data loss through log-based data movement for data protection without shared disks.
Availability groups provide an integrated set of options including automatic and manual failover of a logical group of databases, support for up to four secondary replicas, fast application failover, and automatic page repair.
        AlwaysOn Failover Cluster Instances (FCIs) enhance the SQL Server failover clustering feature and support multisite clustering across subnets, which enables cross-data-center failover of SQL Server instances. Faster and more predictable instance failover is another key benefit that enables faster application recovery.
Significantly Reduce Planned Downtime
The key reason for application downtime in any organization is planned downtime caused by operating system patching, hardware maintenance, and so on. This can constitute almost 80 percent of the outages in an IT environment.
SQL Server 2012 helps reduce planned downtime significantly by reducing patching requirements and enabling more online maintenance operations:
        Windows Server Core. SQL Server 2012 supports deployments on Windows Server Core, a minimal, streamlined deployment option for Windows Server 2008 and Windows Server 2008 R2. This operating system configuration can reduce planned downtime by minimizing operating system patching requirements by as much as 60 percent.
        Online Operations. Enhanced support for online operations like LOB re-indexing and adding columns with default values helps to reduce downtime during database maintenance operations.
        Rolling Upgrade and Patching. AlwaysOn features facilitate rolling upgrades and patching of instances, which helps significantly to reduce application downtime.
        SQL Server on Hyper-V. SQL Server instances hosted in the Hyper-V environment receive the additional benefit of Live Migration, which enables you to migrate virtual machines between hosts with zero downtime. Administrators can perform maintenance operations on the host without impacting applications.

Few more significant merits not found in previous versions:
        Using the WSFC APIs to perform failovers. Shared storage is not required
        Utilizing database mirroring for the data transfer over TCP/IP
        providing a combination of Synchronous and Asynchronous mirroring
        providing a logical grouping of similar databases via Availability Groups
        Creating up to four readable secondary replicas (upto 2 Sync)
        Allowing backups to be undertaken on a secondary replica
        Performing DBCC statements against a secondary replica
        Employing Built-in Compression & Encryption
        Automatic Page Repair
Eliminate Idle Hardware and Improve Cost Efficiency and Performance
Typical high availability solutions involve deployment of costly, redundant, passive servers. AlwaysOn Availability Groups enable you to utilize secondary database replicas on otherwise passive or idle servers for read-only workloads such as SQL Server Reporting Services report queries or backup operations. The ability to simultaneously utilize both the primary and secondary database replicas helps improve performance of all workloads due to better resource balancing across your server hardware investments.
Easy Deployment and Management
Features such as the Configuration Wizard, support for the Windows PowerShell command-line interface, dashboards, dynamic management views (DMVs), policy-based management, and System Center integration help simplify deployment and management of availability groups.
Contrasting RPO and RTO Capabilities
The business goals for Recovery Point Objective (RPO) and Recovery Time Objective (RTO) should be key drivers in selecting a SQL Server technology for your high availability and disaster recovery solution.

This table offers a rough comparison of the type of results that those different solutions may achieve:
High Availability and Disaster Recovery
SQL Server Solution
Potential Data Loss (RPO)
Potential Recovery Time (RTO)
Automatic Failover
Readable Secondaries(1)
AlwaysOn Availability Group - synchronous-commit
Zero
Seconds
Yes
0 - 2
AlwaysOn Availability Group - asynchronous-commit

Seconds
Minutes
No
0 - 4
AlwaysOn Failover Cluster Instance
NA
Seconds
-to-minutes
Yes
NA
Database Mirroring(2) - High-safety (sync + witness)

Zero
Seconds
Yes
NA
Database Mirroring(2) - High-performance (async)

Seconds
Minutes
No
NA
Log Shipping
Minutes
Minutes
-to-hours
No
Not during
a restore
Backup, Copy, Restore(3)
Hours
Hours
-to-days
No
Not during
a restore
Sybase WarmStandBy
Seconds
Minutes
No
1
Sybase MSA
Seconds
Minutes
No
Multiple