SQL server journal...

Hekaton – SQL Server’s in-memory database engine

2012-11-21T06:06:00.003-08:00

http://blogs.technet.com/b/dataplatforminsider/archive/2012/04/09/the-coming-in-memory-database-tipping-point.aspx

Microsoft have just went public at the PASS Summit in Seattle about a new SQL Server engine that they’re working on which is optimized for high-memory servers – an in-memory OLTP database engine which is built-in to SQL Server rather than a separate entity. This means that you can move just the performance critical parts of your database to Hekaton.
The new engine really pushes the performance boundaries by eliminating as many instructions as possible:

Main memory optimized tables which are decoupled from on-disk structures;
Everything is lock and latch free;
More work is pushed to compile time so your T-SQL code is compiled natively into low-level code.

Server Configuration script-returns SQL server information

2012-07-08T22:16:00.001-07:00

-- Returns SQL Server information (SQL Server 2008)
SELECT 'ServerName' "Name", SERVERPROPERTY('ServerName') "Value"
UNION ALL
SELECT 'InstanceName', SERVERPROPERTY('InstanceName')
UNION ALL
SELECT 'MachineName', SERVERPROPERTY('MachineName')
UNION ALL
SELECT 'ComputerNamePhysicalNetBIOS', SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
UNION ALL
SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')
UNION ALL
SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')
UNION ALL
SELECT 'Edition', SERVERPROPERTY('Edition')
UNION ALL
SELECT 'EditionID', SERVERPROPERTY('EditionID')
UNION ALL
SELECT 'EngineEdition', SERVERPROPERTY('EngineEdition')
UNION ALL
SELECT 'EngineEditionDesc',
CASE SERVERPROPERTY('EngineEdition')
    WHEN 1 THEN 'Personal/Desktop Engine'
    WHEN 2 THEN 'Standard'
    WHEN 3 THEN 'Enterprise'
    WHEN 4 THEN 'Express'
END
UNION ALL
SELECT 'FilestreamShareName', SERVERPROPERTY('FilestreamShareName')
UNION ALL
SELECT 'FilestreamConfiguredLevel', SERVERPROPERTY('FilestreamConfiguredLevel')
UNION ALL
SELECT 'FilestreamEffectiveLevel', SERVERPROPERTY('FilestreamEffectiveLevel')
UNION ALL
SELECT 'BuildClrVersion', SERVERPROPERTY('BuildClrVersion')
UNION ALL
SELECT 'Collation', SERVERPROPERTY('Collation')
UNION ALL
SELECT 'CollationID', SERVERPROPERTY('CollationID')
UNION ALL
SELECT 'LCID', SERVERPROPERTY('LCID')
UNION ALL
SELECT 'SqlCharSet', SERVERPROPERTY('SqlCharSet')
UNION ALL
SELECT 'SqlCharSetName', SERVERPROPERTY('SqlCharSetName')
UNION ALL
SELECT 'SqlSortOrder', SERVERPROPERTY('SqlSortOrder')
UNION ALL
SELECT 'SqlSortOrderName', SERVERPROPERTY('SqlSortOrderName')
UNION ALL
SELECT 'ComparisonStyle', SERVERPROPERTY('ComparisonStyle')
UNION ALL
SELECT 'IsClustered', SERVERPROPERTY('IsClustered')
UNION ALL
SELECT 'IsFullTextInstalled', SERVERPROPERTY('IsFullTextInstalled')
UNION ALL
SELECT 'IsIntegratedSecurityOnly', SERVERPROPERTY('IsIntegratedSecurityOnly')
UNION ALL
SELECT 'IsSingleUser', SERVERPROPERTY('IsSingleUser')
UNION ALL
SELECT 'ResourceLastUpdateDateTime', SERVERPROPERTY('ResourceLastUpdateDateTime')
UNION ALL
SELECT 'ResourceVersion', SERVERPROPERTY('ResourceVersion')
UNION ALL
SELECT 'LicenseType', SERVERPROPERTY('LicenseType')
UNION ALL
SELECT 'NumLicenses', SERVERPROPERTY('NumLicenses')
UNION ALL
SELECT 'ProcessID', SERVERPROPERTY('ProcessID');

SQL Server - How To Tell If There Is A Trace Running

2011-09-07T10:17:00.000-07:00

Server-side tracing is the process of having your SQL Server machine save events to a physical file on that machine without using the Profiler client tool. Server-side tracing is enabled and controlled by using SQL Server system-supplied stored procedures and functions. With these system-supplied processes, you can identify what to trace, when to start and stop tracing, what traces are running, and view trace information stored in the trace file.

Here is how you view the number of traces currently running:

SELECT count(*) FROM :: fn_trace_getinfo(default) WHERE property = 5 and value = 1

Here is how you can find more detail about the running traces:

SELECT * FROM :: fn_trace_getinfo(default)

You can terminate a trace with the 'sp_trace_setstatus' stored procedure using the traceid:

EXEC sp_trace_setstatus 1, @status = 0
EXEC sp_trace_setstatus 1, @status = 2

setting the status to 0 stops the trace
setting the status to 2 closes the trace and deletes its definition from the server

Script to return memory information for the operating system

2011-08-09T09:09:00.000-07:00

SELECT
total_physical_memory_kb / 1024 "PhysicalMem(MB)",
available_physical_memory_kb / 1024 "AvailablePhysicalMem(MB)",
system_cache_kb /1024 "SystemCache(MB)",
(kernel_paged_pool_kb + kernel_nonpaged_pool_kb) / 1024 "KernelPool(MB)",
total_page_file_kb / 1024 "TotalPageFile(MB)",
available_page_file_kb / 1024 "AvailablePageFile(MB)",
system_memory_state_desc "MemoryState",
system_high_memory_signal_state "SystemHighMemState",
system_low_memory_signal_state "SystemLowMemState"
FROM
sys.dm_os_sys_memory;

Query to Find index FileGroup location

2011-08-09T09:05:00.000-07:00

select distinct
       idx.name as IndexName
        ,object_name(idx.id) as TableName
       ,fil.name as FileGroup
       ,dbf.physical_name as PhysicalFileName
   from sys.tables tb
         inner join sys.sysindexes idx
         on idx.id = tb.object_id
         and tb.type = 'U' and idx.name is not null
         inner join sys.filegroups fil
         on fil.data_space_id = idx.groupid
       inner join sys.database_files dbf
         on fil.data_space_id = dbf.data_space_id
       inner join sys.data_spaces dsp
         on fil.data_space_id = dsp.data_space_id

An overview on Peer to peer replication in SQL server 2008

2011-06-17T12:35:00.000-07:00

1. What is Replication?

Ans. Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.
Transactional replication is typically used in server-to-server scenarios that require high throughput, including: improving scalability and availability; data warehousing and reporting; integrating data from multiple sites; integrating heterogeneous data; and offloading batch processing. Merge replication is primarily designed for mobile applications or distributed server applications that have possible data conflicts. Common scenarios include: exchanging data with mobile users; consumer point of sale (POS) applications; and integration of data from multiple sites. Snapshot replication is used to provide the initial data set for transactional and merge replication; it can also be used when complete refreshes of data are appropriate. With these three types of replication, SQL Server provides a powerful and flexible system for synchronizing data across your enterprise.

Transactional Replication Architecture

Transactional replication and peer-to-peer replication use the same architecture to move changes between the servers in a replication topology. The following illustration is an overview of the components involved in transactional replication.

Figure 2: Transactional replication architecture overview

A minimum of three server roles are required for transactional replication:

• Publisher, hosting the publication database

• Distributor, hosting the distribution database

• Subscriber, hosting the subscription database

Depending on the complexity of the replication topology, there may be multiple Subscriber servers or, in the case of peer-to-peer replication, multiple peer servers with the replication stream flowing in both directions between the peers. Furthermore, the roles of the various replication servers can be played by one server or by individual servers (the more common case), and it is possible for a server to play any combination of roles. Regardless, the various servers and databases must be protected to ensure that the replication stream is highly available.

Transactional replication relies on various agents to perform the tasks associated with tracking changes and distributing data. These agents are:

• Snapshot Agent, which runs at the Distributor. This agent prepares schema and initial data files of published tables and other objects, stores the snapshot files, and records information about synchronization in the distribution database.

• Log Reader Agent, which runs at the Distributor. This agent connects to the Publisher and moves transactions marked for replication from the transaction log of the publication database to the distribution database.

• Distribution Agent, which runs at the Distributor for push subscriptions, and at the Subscriber for pull subscriptions. This agent applies the (optional) initial snapshot to the Subscribers and moves transactions held in the distribution database to Subscribers

• Queue Reader Agent, which runs at the Distributor. This agent is only used for transactional replication with updateable subscriptions and moves changes made on the Subscribers back to the Publisher.

2. What is Peer to Peer replication in SQL server 2008?

Ans. Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes. Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.

3. Considerations for configuring Peer to Peer replication in SQL server 2008.

Ans. General Considerations

· Peer-to-peer replication is available only in SQL Server 2008 Enterprise.

· All databases that participate in peer-to-peer replication should contain identical schema and data:

o Object names, object schema, and publication names should be identical.

o Publications must allow schema changes to be replicated. (This is a setting of 1 for the publication property replicate_ddl, which is the default setting. Row and column filtering are not supported.

· We recommend that each node use its own distribution database. This eliminates the potential of having a single point of failure.

· Tables and other objects cannot be included in multiple peer-to-peer publications in a single publication database.

· A publication must be enabled for peer-to-peer replication before any subscriptions are created.

· Subscriptions must be initialized by using a backup or with the 'replication support only' option. MS do not recommend the use of identity columns. When using identities, you must manually manage the ranges assigned to the tables at each participating database.

4. Development implications on Peer to Peer Environment
Ans.

Considerations for Schema Changes.

Schema changes are subject to any restrictions imposed by Transact-SQL. For example, ALTER TABLE does not allow you to ALTER primary key columns.

· If a publication is set to allow the propagation of schema changes, schema changes are propagated regardless of how the related schema option is set for an article in the publication. For example, if you select not to replicate foreign key constraints for a table article, but then issue an ALTER TABLE command that adds a foreign key to the table at the Publisher, the foreign key is added to the table at the Subscriber. To prevent this, disable the propagation of schema changes before issuing the ALTER TABLE command.

· If the schema change references objects or constraints existing on the Publisher but not on the Subscriber, the schema change will succeed on the Publisher but will fail on the Subscriber.

· All objects on the Subscriber that are referenced when adding a foreign key must have the same name and owner as the corresponding object on the Publisher.

· Explicitly adding, dropping, or altering indexes is not supported. Indexes created implicitly for constraints (such as a primary key constraint) are supported.

· Altering or dropping identity columns that are managed by replication is not supported.

· Schema changes that include nondeterministic functions are not supported because they can result in data at the Publisher and Subscriber being different (referred to as non-convergence). For example, if you issue the following command at the Publisher: ALTER TABLE SalesOrderDetail ADD OrderDate DATETIME DEFAULT GETDATE(), the values are different when the command is replicated to the Subscriber and executed.

· It is recommended that constraints be explicitly named. If a constraint is not explicitly named, SQL Server generates a name for the constraint, and these names will be different on the Publisher and each Subscriber. This can cause issues during the replication of schema changes. For example, if you drop a column at the Publisher and a dependent constraint is dropped, replication will attempt to drop the constraint at the Subscriber.

· To add a new column to a table and include that column in an existing publication, execute ALTER TABLE

ADD . By default, the column is then replicated to all Subscribers. The column must allow NULL values or include a default constraint.

· To add a new column to a table and not include that column in an existing publication, disable the replication of schema changes, and then execute ALTER TABLE

ADD .

· Adding an identity column to a published table is not supported, because it can result in non-convergence when the column is replicated to the Subscriber. The values in the identity column at the Publisher depend on the order in which the rows for the affected table are physically stored. The rows might be stored differently at the Subscriber; therefore the value for the identity column can be different for the same rows.

Dropping Columns

· To drop a column from an existing publication and drop the column from the table at the Publisher, execute ALTER TABLE

DROP . By default, the column is then dropped from the table at all Subscribers.

· When dropping a column from a published article, take into consideration any constraints, indexes, or properties of the column that could affect the database. For example:

o You cannot drop columns used in a primary key from articles in transactional publications, because they are used by replication.

o Index changes are not propagated to Subscribers: if you drop a column at the Publisher and a dependent index is dropped, the index drop is not replicated. You should drop the index at the Subscriber before dropping the column at the Publisher, so that the column drop succeeds when it is replicated from the Publisher to the Subscriber. If synchronization fails because of an index at the Subscriber, manually drop the index and then rerun the Merge Agent.

o Constraints should be explicitly named to allow for dropping.

T-SQL script to remove replication in SQL server 2008\2005

2011-06-16T22:45:00.000-07:00

use master
go

-- Remove replication objects from the subscription database on MYDATABASE.
DECLARE @subscriptionDB AS sysname
SET @subscriptionDB = N'MYDATABASE'

-- Remove replication objects from a subscription database (if necessary).
USE master
EXEC sp_removedbreplication @subscriptionDB
GO

use distribution
go
exec sp_removedistpublisherdbreplication @publisher = 'Instance Name'
, @publisher_db = 'MYDATABASE'

How to find the Job owners in a SQL server instance

2011-06-16T22:37:00.000-07:00

use msdb
go
SELECT j.[name] AS 'JobName',
Enabled = CASE WHEN j.Enabled = 0 THEN 'No'
ELSE 'Yes'
END,
l.[name] AS 'OwnerName'
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY j.[name]
GO

2011-06-16T22:30:00.000-07:00

How to read the error logs in SQL server 2005\2008?

exec xp_readerrorlogs

Please read the below link before running it:

http://support.microsoft.com/kb/973524

Sql Server 2008 editions

2009-08-07T07:34:00.000-07:00

What are the Editions of SQL Server 2008?
Microsoft® SQL Server® 2008 is available in a number of editions, each suitable for a particular environment or task.
It is important to understand the differences between the available editions so that you can select the most appropriate one for your needs.
The Developer Edition contains the same functionality as Enterprise Edition, but is restricted, by the license, to testing and development only.
Windows Essential Business Server Premium Edition® (which will replace Small Business Server®) comes with a license for SQL Server 2008 Standard Edition®.
SQL Server 2008 Web Edition enables hosting companies to provide low cost, highly scalable hosting for developers, SMBs, and consumers, all with a low monthly licensing fee, four CPU support, and no limits on memory and database size for increased scalability.
Cross-version instances of SQL Server 2008 are not supported. Version numbers of the Database Engine, Analysis Services, and Reporting Services components must be the same within an instance of SQL Server 2008.
Before upgrading SQL Server, enable Windows Authentication for SQL Server Agent and verify that the SQL Server Agent service account is a member of the SQL Server sysadmin group.
Before upgrading from one edition of SQL Server 2008 to another, verify that the functionality you are currently using is supported in the edition to which you are upgrading.
Cross-platform upgrade is not supported. You cannot upgrade a 32-bit instance of SQL Server to native 64-bit. However, you can upgrade a 32-bit instance of SQL Server to the WOW64: the 32-bit subsystem on a 64-bit server. You can also back up or detach databases from a 32-bit instance of SQL Server, and then restore or attach them to an instance of SQL Server (64-bit) if the databases are not published in replication. In this case, you must also re-create any logins and other user objects in master, msdb, and model system databases.
To upgrade to SQL Server 2008, you must be running a supported operating system.
The installer will check a number of conditions and block the upgrade if certain conditions are not met:
Upgrade will be blocked if there is a pending restart.
Upgrade will be blocked if the Windows Installer service is not running.
Upgrade will be blocked if performance counters are corrupt.
To upgrade an instance of SQL Server to a SQL Server failover cluster, the instance being upgraded must be a failover cluster. To upgrade a stand-alone instance of SQL Server to a SQL Server failover cluster, install a new SQL Server failover cluster and then migrate user databases from the stand-alone instance by using the Copy Database Wizard.