:::: MENU ::::

Blog

  • Jul 02 / 2008
  • 0
DBA Interview questions, dbDigger, Hardware and Platform, System Administration

Multi processors and multicore processors systems

Multiprocessor systems contain multiple CPUs that are not on the same chip. Today, multiprocessors are commonly found on the same physical board and connected through a high-speed communication interface. Multiprocessor systems are less complex than multicore systems, because they are essential single chip CPUs connected together. The disadvantage with multiprocessor systems is that they are expensive because they require multiple chips which is more expensive than a single chip solution.

  • Jul 02 / 2008
  • 0
dbDigger, Security and Permissions

SQL Server 2008 Security Overview

SQL Server 2008 provides rich security features to protect data and network resources. It is much easier to install securely, since all but the most essential features are either not installed by default or disabled if they are installed. SQL Server provides plenty of tools to configure the server, particularly the SQL Server Surface Area Configuration Tool. Its authentication features are stronger because SQL Server more closely integrates with Windows authentication and protects against weak or ancient passwords. Granting and controlling what a user can do when authenticated is far more flexible with granular permissions, SQL Server Agent proxies, and execution context. Even metadata is more secure, since the system metadata views return information only about objects that the user has permission to use in some way. At the database level, encryption provides a final layer of defense while the separation of users and schemas makes managing users easier.

Much has been written and discussed about Microsoft’s Trustworthy Computing initiative that guides all software development at the company. The four essential components of this initiative are:

  • Secure by design: Software requires a secure design as a foundation for repelling attackers and protecting data.
  • Secure by default: System administrators should not have to work to make a fresh installation secure; it should be that way by default.
  • Secure in deployment: Software should help to keep itself updated with the latest security patches and assist in maintenance.
  • Communications: Communicate best practices and evolving threat information so that administrators can proactively protect their systems. These guiding principles are evident throughout SQL Server 2008, which provides all the tools you need to secure your databases.

SECURE CONFIGURATION

The first requirement of a secure SQL Server installation is a secure environment. You need to

  • physically secure the server
  • back up data regularly
  • put it behind one or more firewalls if it’s connected to a network
  • avoid installing SQL Server on a computer with other server applications
  • enable only the minimum network protocols required.

Use Windows Update to automatically apply SQL Server 2008 patches automatically and reduce threats caused by known software vulnerabilities.

SQL Server 2008 comes packed with numerous features, many of which are installed in a disabled state. For example, CLR integration, database mirroring, debugging, Service Broker, and mail functions are installed but are not running and not available until you explicitly turn them on or configure them.

There are far too many configuration options to take the time to write this kind of code – especially when you have multiple instances of SQL Server deployed throughout the organization. SQL Server 2008 includes a policy-based management technology called the Declarative Management Framework (DMF). The DMF provides a number of configuration Facets, each of which defines a set of related configuration settings or properties. You can use these facets to create Conditions that specify the desired settings for the configuration options, and enforce these conditions as Policies to SQL Server instances across the enterprise.

One the of the facets included in SQL Server 2008 is the Surface Area facet, and you can use this facet to define a policy that controls the status of various SQL Server 2008 features. By creating a policy that defines the desired surface area settings for your servers, you can easily enforce a minimal surface area on all SQL Server instances in your organization, and reduce the possibility of malicious attack.

AUTHENTICATION
SQL Server 2008 improves on the SQL Server Authentication option. First, it supports encryption of the channel by default through the use of SQL-generated certificates. Administrators do not have to acquire and install a valid SSL certificate to make sure that the channel over which the SQL credentials flow is secure. With SQL Server 2008 automatically generating these certificates, it encrypts the channel automatically by default when transmitting login packets. This occurs if the client is at the SQL Server 2005 level or above.

Note: The native certificate generated by SQL Server protects against passive man-in-the-middle attacks where the attacker is sniffing the network. To secure your systems more effectively against active man-in-the-middle attacks, you should deploy and use certificates that the clients trust as well.

SQL Server 2008 further enhances SQL Server Authentication because, by default, the database engine now uses Windows Group Policy for password complexity, password expiration, and account lockout on SQL logins when used in combination with a Windows 2003 server or above. This means that you can enforce the Windows password policy on your SQL Server accounts.

AUTHORIZATION
After authentication, it is time to think about what an authenticated login can do. In this area, SQL Server 2008 and SQL Server 2005 are more flexible than earlier versions. Permissions are now far more granular so that you can grant the specific permissions required rather than grant membership in a fixed role that probably carries with it more permissions than are necessary. You now have far more entities, securables, to which you can assign permissions that are more granular.

In addition to the enhanced protection for user data, structural information and metadata about a particular securable is now available only to principals that have permission to access the securable.

Furthermore, it is possible to create custom permission sets using a mechanism that allows one to define the security context under which stored procedures can run.

In addition, SQL Agent uses a flexible proxy scheme to allow job steps to run and access required resources. All these features make SQL Server more complex but far more secure.

In SQL Server 2008 a principal is any individual, group, or process that can request access to a protected resource and be granted permission to access it. The following list shows the hierarchy of SQL Server 2008 principals, excluding the fixed server and database roles, and how you can map logins and database users to security objects. The scope of the influence of the principal depends on the scope of its definition, so that a Windows-level principal is more encompassing than a SQL Server-level principal, which is more encompassing than a database-level principal. Every database user automatically belongs to the fixed public role.

AUDITING IN SQL SERVER 2008
An important part of any security solution is the ability to audit actions for accountability and regulatory compliance reasons. SQL Server 2008 includes a number of features that make it possible to audit activity.

SQL Server 2008 includes auditing support through the Audit object, which enables administrators to capture activity in the database server and store it in a log. With SQL Server 2008, you can store audit information in the following destinations:

  • File
  • Windows Application Log
  • Windows Security Log

To write to the Windows Security Log, the SQL Server service must be configured to run as Local System, Local Service, Network Service, or a domain account that has the SeAuditPrivilege privilege and that is not an interactive user.

DML TRIGGERS
DDL triggers were introduced in SQL Server 2005. Unlike DML triggers that execute Transact-SQL code when data in a table changes, a DDL trigger fires when the structure of the table changes. This is a great way to track and audit structural changes to a database schema.

The syntax for these triggers is similar to that of DML triggers. DDL triggers are AFTER triggers that fire in response to DDL language events; they do not fire in response to system-stored procedures that perform DDL-like operations. They are fully transactional, and so you can ROLLBACK a DDL change. You can run either Transact-SQL or CLR code in a DDL trigger. DDL triggers also support the EXECUTE AS clause similar to other modules.

For more information:

  • Jul 02 / 2008
  • 0
dbDigger, Enhancement in new versions

SQL Server 2008 Compared to Oracle11g

SQL Server 2008 meets or exceeds Oracle 11g in every area that matters to the business. The security and reliability is unrivaled, the integration is unparalleled, and the TCO is unmatched. SQL Server 2008 provides the best solution in terms of both performance and value for money at every level from PDAs to data centers. In coming lines we will compare some critical features in both.

SQL Server 2008 has many new features that Oracle 11g does not have as shown in the following table. A brief description of these features follows the table.

  • Resource Governor provides consistent and predictable response times to end users. Organizations can allocate resources and define priorities for different workloads so that concurrent workloads do not interrupt consistent performance to end users. Resource Governor provides SQL Server with several important advantages over Oracle. By specifying minimum CPU and memory usage, you can prioritize workloads in order to guarantee that SLAs (service level agreements) are met for particular workloads in the database. Resource Governor also enables you to limit the amount of memory per resource pool, thereby preventing runaway queries.

  • Partition-aligned indexed views enable you to create and manage summary aggregates in your relational data warehouse more efficiently and use them in scenarios where you previously could not use them effectively. Partition-aligned views improve query performance. In a typical scenario, a fact table is partitioned by date. Indexed views (or summary aggregates) can be defined on the fact table to help speed up queries. When you switch in a new table partition, the matching partitions of the partition-aligned indexed views defined on the partitioned table switch, too, and do so automatically.

  • SQL Server PowerShell is a new provider for browsing and managing SQL Server databases, tables, and other database objects. The Windows PowerShell™ command-line interface supports more complex logic than Transact-SQL scripts to allow for more robust administration scripts. You can also use PowerShell scripts to administer other Microsoft server products so that administrators use a common scripting language across servers.

  • Policy-Based Management is a new system for managing one or more instances of SQL Server 2008 by using SQL Server Management Studio. Use it to create policies to manage entities such as instances of SQL Server, databases, and other SQL Server objects on the database server. It gives database administrators (DBAs) full control of their database servers from an entirely new perspective. It is an easy-to-use and powerful tool for the DBA to use to implement standard configurations in the SQL Server environment.

  • Filtered indexes enable indexing on a subset of rows in a table and provide numerous benefits. They provide space-saving and performance improvements when you insert or update content. Filtered indexes can greatly improve data-warehousing performance. For example, you can index only the data for the current month rather than the data for an entire year. You can create more filtered indexes per table to speed up queries. The SQL Server Database Engine Tuning Advisor can recommend filtered indexes for database tables. Filtered indexes provide support for heterogeneous table data in applications such as content management systems (Microsoft Office SharePoint® Server, for example) that have multiple properties for each data type such as a retail product catalog that has entries for books, CDs, and clothes in the same table, but the properties of these items differ. You can create filtered indexes for data according to properties or categories.Sparse columns efficiently manage empty data in a database because they enable NULL data to consume no physical space. SQL Server 2008 sparse columns can support wide tables that have up to 100,000 columns, whereas Oracle’s current limit is 1,000. Column sets support property-bag scenarios in content management systems such as Office SharePoint Server.

  • Multithreaded partition access enables SQL Server 2008 to improve query-processing performance on partitioned tables for many parallel plans. Furthermore, multithreaded partition access changes the way in which parallel and serial plans are represented, and enhances the partitioning information that is provided in both compile-time and run-time execution plans.

  • Column-prefix compression is part of the SQL Server 2008 advanced page compression techniques (dictionary-page compression and column-prefix compression). With column-prefix compression, SQL Server looks for a common byte pattern at the beginning of a column across all rows on the page. If it finds at least two instances of columns that have a common byte pattern, it stores that byte pattern once on the page and refers to this byte pattern from the respective columns

  • Module signing using certificates gives SQL Server the ability to sign modules such as stored procedures, functions, triggers, or assemblies, within a databases. This means that you can temporarily elevate privileges without switching the user context. In addition, it is not possible to tamper with or modify the certificate (otherwise it is invalidated)

  • SQL Server Data Services is a highly scalable, cost-effective, on-demand data storage and query-processing Web service. It is built on robust SQL Server technologies and helps guarantee a business-ready service level agreement that covers high-availability, performance, and security features. SQL Server Data Services is accessible by using standards-based protocols such as SOAP and REST for quick provisioning of on-demand data-driven and mashup applications. Businesses can store and access all types of data from origination to archival by using SQL Server Data Services. Users can access information on any device, from desktop computers to mobile devices.

Business Intelligence

It is relevant to mention here the great improvement in BI. Using same capabilities in Oracle will cost about 800 more.

SQL Server 2008 includes a fully integrated BI solution at no extra cost. The product includes support for enterprise-level data warehousing, online analytical processing (OLAP), reporting, scorecards, data mining, ETL, and key performance indicators (KPIs).

Unlike Oracle, these solutions are fully integrated so you can develop, manage, schedule, and deploy them by using the familiar SQL Server tools.

Data Warehousing
SQL Server 2008 has an extensive array of new data warehousing features including data and backup compression, partitioned table parallelism, star join query optimization, resource management, grouping sets, change data capture, the MERGE SQL statement, and scalable Integration Services.

Values for Teradata Customers
Teradata is the Microsoft ISV for Business Intelligence. By integrating the Microsoft BI solution and Teradata technologies, you can take advantage of the data warehousing and BI solutions from Teradata along with the enterprise-ready BI and performance-management solutions from Microsoft. By extending access to critical data to the decision makers who can affect business performance most, the combination of Microsoft and Teradata technologies helps organizations gain additional value from their Teradata data warehousing environment. The Microsoft/Teradata partnership ensures easier implementation of Microsoft BI solutions, faster availability, and easier adoption of new Microsoft and Teradata feature releases.

Integration with the Microsoft Office System
The Microsoft Office System is effectively the standard for desktop productivity. The integration of SQL Server data with the Office System brings the power of information to knowledge workers, reduces development time, reduces training and support costs, and increases productivity. Achieving the integration that is available at no extra cost with SQL Server 2008, would cost $30,000 per processor with Oracle.

Data-Mining Add-ins for Microsoft Office 2007
Data mining add-ins for the 2007 Office System are freely downloadable and improve the integration between the Office System and SQL Server 2008. They provide data mining, a prediction calculator, shopping-basket analysis, cross-validation, and reporting tools.

Total Cost of Ownership
Not only does SQL Server have a lower license cost when compared to Oracle 11g, but its better security, reliability, and productivity gives a greater return on investment. Furthermore, SQL Server 2008 includes features that in the Oracle product would cost much more per processor in addition to the cost of the Oracle base license.

The license price as not the only criterion to consider when you compare the costs of your system. The TCO is the price that you will pay and sometimes the least expensive licenses have the highest TCO. Oracle has a higher license cost than SQL Server with many other hidden costs, whereas SQL Server includes the tools that you require at no extra cost.

Inclusive Functionality
SQL Server includes the functionality that you require for an enterprise database solution; Oracle charges extra for this functionality. The following table shows the software price comparison between SQL Server 2008 and Oracle 11g for a standard, single processor, quad-core server. With SQL Server, the cost remains the same regardless of how many cores your processors have.

  • May 29 / 2008
  • 1
dbDigger, Enhancement in new versions, T-SQL Enhancements

SQL Server 2008 major enhancements

As with SQL Server 2008, there are many new features that have major impact regarding to optimization and control. Major updates are

  • compression
  • plan freezing
  • changes in MERGE statement
  • star join performance
  • parallel scan of partitioned tables
  • External Key Management
  • Enhanced Auditing
  • Enhanced Database Mirroring
  • Hot Add CPU
  • Performance data collection
  • Extended Events
  • backup compression
  • May 21 / 2008
  • 2
dbDigger, User Defined Views

SQL Srever views for preliminary learners

Views are virtual tables whose content is derived from base tables but their structure is stored on disk.
DML statements manipulate views same as base tables with a exception that all data effected in view is actually affected in underlying table (base table). Views are nothing but saved SQL statements, and are sometimes referred as “Virtual Tables”.
Keep in mind that Views cannot store data (except for Indexed Views); rather they only refer to data resent in tables. There are two important options that can be used when a view is created. They are SCHEMABINDING and ENCRYPTION. Before you can identify and create optimal indexes for your tables, you must thoroughly understand the kinds of queries that will be run against them.
View is a list of content of a column or group of columns. Rows in a table are ordered according to their insertion sequence. Indexes keep sorted list of values of a column. Hence it speeds up the search process.
Along with several advantages it has some disadvantages e.g. indexes use additional disk space and when table values of indexed columns are changed, the DBMS needs to maintain the index as well. This makes the insert, delete and update commands to run slow.
SQL SERVER 2000 allows an index to be created on a View. But one important point to be noted here is that the first index on the View should be a UNIQUE CLUSTERED INDEX only. SQL SERVER 2000 will not allow you to create any other INDEX unless you have an UNIQUE CLUSTERED INDEX defined on the view. Views can be used to insert/update and delete data from a table. Views and User-Defined Functions almost serve the same purpose. But the major difference is that User-Defined Function can accept parameters, whereas Views cannot. And also the output of the User Defined Function can be directly used in the SELECT clause, whereas you cannot do it with a View.
It’s not anymore than a named SELECT statement, or a virtual table. You can select data, alter data; remove data and all other things you can do with a table (with some limitations). But there is a difference between a view and a table. The data accessible through a view is not stored in the database as its own object. It’s stored in the underlying tables that make up the view.

Here are some scenarios when a view can be very useful.

  • Restrict data access and/or simplify data access. A view can be used to limit the user to only use a few columns in a table If we use USER_ID(), we can even find out who is using the view, and return only data that is relevant for this user. Furthermore, a view can join several tables, in several databases on several servers, but all the user use is the view’s name. Simple, but powerful!

 

  • Simplify data manipulation. Often with a relational database design, this means that many tables must be joined together. Using a view can simplify this, and the users do not need to know all tables involved.

 

  • Import and export data. A view can also be used to export and/or import data to/from another application. Both the bcp utility and BULK INSERT works with a view.

 

  • Merge data. A so called Partition View can be used to merge data from multiple tables in multiple databases, so it appears as one table only, but it is in fact several tables. This can be accomplished by using the UNION operator.
  • May 21 / 2008
  • 0
dbDigger, T-SQL Interview Questions

what is T-SQL batch

Batch is a group of one or more Transact-SQL statements sent at one time from an application to Microsoft® SQL Server™ for execution.
SQL Server compiles the statements of a batch into a single executable unit, called an execution plan. The statements in the execution plan are then executed one at a time. A compile error, such as a syntax error, prevents the compilation of the execution plan, so none of the statements in the batch are executed. A run-time error, such as an arithmetic overflow or a constraint violation, has one of two effects:

1. Most run-time errors stop the current statement and the statements that follow it in the batch.

2. A few run-time errors, such as constraint violations, stop only the current statement. All the remaining statements in the batch are executed.

The statements executed before the one that encountered the run-time error are not affected. The only exception is if the batch is in a transaction and the error causes the transaction to be rolled back. In this case, any uncommitted data modifications made before the run-time error are rolled back.
It is important to note that CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. The CREATE statement must begin the batch. All other statements that follow in that batch will be interpreted as part of the definition of the first CREATE statement. A table cannot be altered and then the new columns referenced in the same batch.

If an EXECUTE statement is the first statement in a batch, the EXECUTE keyword is not required. The EXECUTE keyword is required if the EXECUTE statement is not the first statement in the batch.
Each batch is compiled into a single execution plan. If the batch contains multiple SQL statements, all of the optimized steps needed to perform all the statements are built into a single execution plan.

GO: Signals the end of a batch of Transact-SQL statements to the SQL Server™ utilities. GO is not a Transact-SQL statement; it is a command recognized by the osql and isql utilities and SQL Query Analyzer. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO. SQL Query Analyzer and the osql and isql command prompt utilities implement GO differently.

A T-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments. Users must follow the rules for batches. For example, any execution of a stored procedure after the first statement in a batch must include the EXECUTE keyword. The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command. This utility command that requires no permissions. It can be executed by any user.

Consult us to explore the Databases. Contact us