Tuesday, August 28, 2012

SQL Server Snapshots

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

A snapshot is a read-only copy of another database, made at a point in time. Any changes to the original database cause the version of the data when the snapshot was taken to get written to the file used by the snapshot. Therefore, there's a performance hit involved, but it can be very useful for knowing exactly what your database looked like at some point in the past (when you told the snapshot to be created).


It's definitely worth noting that the snapshot contains no data of its own when first created, as it can reference the original database for it, at least until the original database is changed.


When a snapshot is first created, it is an empty shell that delegates all queries (a snapshot is read only) to the original database.

As changes are made to the original database, the pages involved are copied to the snapshot. Queries of the snapshot at this point will be performed on a logical database that is the result of layering the pages in the snapshot over those in the original database.


The effect is that the snapshot appears to be a complete copy of the original database that was made at the same time as the snapshot was created.

One scenario in which this can be useful is in deploying changes. The snapshot can be a very inexpensive form of insurance if something goes wrong. Assuming that only a subset of the pages within the original database were modified during the deployment, only that subset of the pages will need to be copied back from the snapshot to the original database during a restore

Get Oracle Certifications for all Exams
Free Online Exams.com

Thursday, August 23, 2012

The “NOT FOR REPLICATION” option in SQL Server Replication:

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

The NOT FOR REPLICATION option allows you to specify that the following database objects are

treated differently when a replication agent performs an operation:


* Foreign key constraints

The foreign key constraint is not enforced when a replication agent performs an insert, update, or

delete operation.


* Check constraints

The check constraint is not enforced when a replication agent performs an insert, update, or delete

operation.


* Identity columns

The identity column value is not incremented when a replication agent performs an insert

operation.


* Triggers

The trigger is not executed when a replication agent performs an insert, update, or delete
Get Oracle Certifications for all Exams
Free Online Exams.com

What is filtered index

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

A filtered index is an optimized nonclustered index, especially suited to cover queries that select

from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table.


A well-designed filtered index can improve query performance, reduce index maintenance costs,

and reduce index storage costs compared with full-table indexes.


Filtered indexes can provide the following advantages over full-table indexes:


Improved query performance and plan quality

Reduced index maintenance costs

Reduced index storage costs

Get Oracle Certifications for all Exams
Free Online Exams.com

How to set the instance to utilize one thread for searches which owns an estimated execution cost less than 6.

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Change the sp_configure cost threshold for parallelism option.

 

Use the cost threshold for parallelism option to specify the threshold at which Microsoft SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration.

 

Only set cost threshold for parallelism on symmetric multiprocessors.

Longer queries usually benefit from parallel plans; the performance advantage negates the additional time required to initialize, synchronize, and terminate the plan. The cost threshold for parallelism option is actively used when a mix of short and longer queries is executed. The short queries execute serial plans while the longer queries use parallel plans. The value of cost threshold for parallelism determines which queries are considered short, thus executing only serial plans.

 

In certain cases, a parallel plan may be chosen even though the query's cost plan is less than the current cost threshold for parallelism value. This is because the decision to use a parallel or serial plan, with respect to cost threshold for parallelism, is based on a cost estimate provided before the full optimization is complete.

 

The cost threshold for parallelism option can be set to any value from 0 through 32767. The default value is 5.

 

If your computer has only one processor, if only a single CPU is available to SQL Server because of the affinity mask configuration value, or if the max degree of parallelism option is set to 1, SQL Server ignores cost threshold for parallelism.

 

cost threshold for parallelism is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change cost threshold for parallelism only when show advanced options is set to 1. The setting takes effect immediately (without a server stop and

restart).

Get Oracle Certifications for all Exams
Free Online Exams.com

Wednesday, August 22, 2012

How to Create Full SQL Database backup commands

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com
Execute the BACKUP DATABASE statement to create the full database backup, specifying:

* The name of the database to back up.
* The backup device where the full database backup is written.

The basic Transact-SQL syntax for a full database backup is:

BACKUP DATABASE database
TO backup_device [ ,...n ]
[ WITH with_options [ ,...o ] ] ;
Option Description
database
Is the database that is to be backed up.
backup_device [ ,...n ]

Specifies a list of from 1 to 64 backup devices to use for the backup operation. You can specify a
physical backup device, or you can specify a corresponding logical backup device, if already
defined. To specify a physical backup device, use the DISK or TAPE option:

{ DISK | TAPE } =physical_backup_device_name
For more information, see Backup Devices.
WITH with_options [ ,...o ]

Optionally, specifies one or more additional options, o. For information about some of the basic
with options, see step 2.

2.

Optionally, specify one or more WITH options. A few basic WITH options are described here. For
information about all the WITH options, see BACKUP (Transact-SQL).
* Basic backup set WITH options:

{ COMPRESSION | NO_COMPRESSION }

In SQL Server 2008 Enterprise and later only, specifies whether backup compression is performed
on this backup, overriding the server-level default.
Get Oracle Certifications for all Exams
Free Online Exams.com

Reports Cache Directory in Oracle E-Business R12 is Growing Rapidly

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

The cache files are not being deleted when the cacheSize property is exceeded and files are also not deleted even after enabling and setting maxCacheFileNumber, which is the documented method.

Solution

Workaround:

  1- Shutdown the Concurrent Processing server.
  2- Manually delete all the temporary files under the $INST_TOP/logs/ora/10.1.2/reports/cache path
  3- Startup up the Concurrent Processing server.

OR

Change the Oracle Reports configuration file $INST_TOP/ora/10.1.2/reports/conf/rwbuilder.conf and bouncing the services. 

CHANGE: 
<property name="cacheSize" value="50"/> to 
<property name="cacheSize" value="0"/> 

NOTE: This change will be lost when Autoconfig is run. Apply patch 8602992 as soon as it becomes available.

Permanent Fix:

  1- Apply the following Patch 8602992.

Important Note: If facing Intermittent Oracle Reports REP-0069: REP-57054 Errors after applying the patch above please review Note 1237834.1 - Intermittent Oracle Reports REP-0069: REP-57054: Error

Get Oracle Certifications for all Exams
Free Online Exams.com

EBS R12 Oracle Reports Cache Directory Not Being Cleaned Up

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Applies to:

Oracle Applications Technology Stack - Version 12.0 to 12.0 [Release 12.0]
Information in this document applies to any platform.
Oracle Reports, Cache, Cleaned Up, $INST_TOP, purge, disk space, temporary, files


Symptoms

In E-Business Suite (EBS) Release 12, when concurrent requests of the 
execution method of Oracle Reports are submitted, temporary files are present 
under the path of $INST_TOP/logs/ora/10.1.2/reports/cache. 

The temporary files are identical to the file placed under the 
$APPLCSF/$APPLOUT path, however. These temporary files are not purged when 
the "Purge Concurrent Request and/or Manager Data" (FNDCPPUR) program runs. 

Cause

New Bug 8449763 / 8602992 due to the new architecture of EBS R12

Solution


1. Perform both of the following actions:

a) Apply patch 8602992 "APPS1012: CACHE DIRECTORY NOT BEING CLEANED OUT"--currently available via MOS (Metalink). 

b) Apply technology stack template fix patch 9032800 "SET CACHESIZE=0 RWBUILDER_CONF_1012.TMP"--as soon as it becomes available.

As a current workaround to patch 9032800:

Change the Oracle Reports configuration file $INST_TOP/ora/10.1.2/reports/conf/rwbuilder.conf and bouncing the services. 

CHANGE: 
<property name="cacheSize" value="50"/> to 
<property name="cacheSize" value="0"/> 

NOTE: This change will be lost when Autoconfig is run. Apply patch 8602992 as soon as it becomes available. 

NOTE 2:   Patch 8602992 will ensure that the Oracle Report rwrun executable deletes the cache file after the report runs, however. Any existing cache file prior to the fix will need to be deleted manually

Get Oracle Certifications for all Exams
Free Online Exams.com

Saturday, August 18, 2012

Clustered and Non-Clustered Index in SQL

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com


When you first create a new table, there is no index created by default. In technical terms, a table without an index is called a "heap". As you would expect, the data we will insert into the table will be returned in the same order. A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.


A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.


Clustered index is good when you know in which order you will be returning the records in most cases. You can create clustered index and after that you don't need to use ORDER BY statement. This will be much more faster. If the order is not important for you and will not create clustered index by yourself, then primary key will be clustered index by default. There is nothing bad not to have the clustered index, it can speed up inserting rows.



You can access www.free-online-exams.com for the whole latest version of the exam



Get Oracle Certifications for all Exams
Free Online Exams.com

Thursday, August 16, 2012

Alter Schema in SQL Server, how to move a table, such as ABC_table, from its current scheme (scheme1) to another scheme (scheme2)

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

ALTER SCHEMA schema2 TRANSFER schema1.ABC_table;

ALTER SCHEMA can only be used to move securables between schemas in the same database.
To change or drop a securable within a schema, use the ALTER or DROP statement specific to
that securable.

You can access www.free-online-exams.com for the whole latest version of the exam


Get Oracle Certifications for all Exams
Free Online Exams.com

How to allow users on SQL Server to use the OPENROWSET() function to search remote information sources.

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com
01. sp_configure 'show advanced options', 1
02. RECONFIGURE
03.sp_configure 'Ad Hoc Distributed Queries', 1
04. RECONFIGURE
05. GO
06.
07. SELECT a.*
08. FROM OPENROWSET('SQLNCLI', 'Server=Madrid;Trusted_Connection=yes;',
09. 'SELECT GroupName, Name, DepartmentID
10. FROM AdventureWorks.HumanResources.Department
11. ORDER BY GroupName, Name') AS a;
12. GO


By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET and
OPENDATASOURCE. When this option is set to 1, SQL Server allows ad hoc access. When this
option is not set or is set to 0, SQL Server does not allow ad hoc access.

Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions to
connect to remote data sources that use OLE DB. OPENROWSET and OPENDATASOURCE
should be used only to reference OLE DB data sources that are accessed infrequently. For any
data sources that will be accessed more than several times, define a linked server.


You can access www.free-online-exams.com for the whole latest version of the exam



Get Oracle Certifications for all Exams
Free Online Exams.com

ALTER LOGIN CHECK_POLICY = { ON | OFF }

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com
If you have the following below requirements:

1. SQL Server 2008 logins must have the same password complexity rules which are enforced by
Windows Server for authentication.

2. Full compliance of all users with regarding to adhering to the password complexity rules


Answer:

Applies only to SQL Server logins. Specifies that the Windows password policies of the computer
on which SQL Server is running should be enforced on this login. The default value is ON.

ALTER LOGIN CHECK_POLICY = { ON | OFF }

You should consider using the ALTER LOGIN ... CHECK_POLICY = ON statement to change
the entire login of all users.

Create a policy using Policy-Based Management that matches the requirements

Policy-Based Management is a system for managing one or more instances of SQL Server 2008.
When SQL Server policy administrators use Policy-Based Management, they use SQL Server
Management Studio to create policies to manage entities on the server, such as the instance of
SQL Server, databases, or other SQL Server objects.

You can access www.free-online-exams.com for the whole latest version of the exam



Get Oracle Certifications for all Exams
Free Online Exams.com