This tag is for SQL Server 2012, major build version 11.00.xxxx.

learn more… | top users | synonyms (2)

4
votes
1answer
1k views

Specifying the failover partner when using Availability Groups

We have a situation where we are using replicated databases in an environment that's making use of Availability Groups. As we wanted no manual intervention when failing over, we decided to set up ...
4
votes
1answer
283 views

What are the best practices for backing up a secured SQL Server 2012?

I want to state right off the bat that I'm not looking for information on how to backup a SQL Server. I've got that taken care of. I'm looking for some form of documentation that will tell me the ...
1
vote
1answer
157 views

Set up AlwaysOn in virtual environment: How to add nodes to domain? [closed]

On a beefy server, I have a few Virtual Machines (VMs) running. On one of those, I have Domain Controller (DC) with a domain name (ex:SQL.AD.COM) running successfully. Now, I need to add the other VMs ...
4
votes
1answer
606 views

Query records that chain together based on date gaps

I have a table in SQL Server 2012 with the below simplified schema and I need to define a query that grabs the most recent record and then returns the chain of records that have less than 30 days of a ...
3
votes
1answer
509 views

SQL Server 2012 defrag job runs interactively, but not as a job - why?

I have recently started using the dba_indexDefrag_sp stored procedure from SQL Fool (http://sqlfool.com/2011/06/index-defrag-script-v4-1/) against my production databases running on SQL Server 2012 ...
4
votes
1answer
663 views

Are there problems with using SQL Server AlwaysOn Availability Groups with Windows 2008R2?

[Cross post from StackOverflow] Brent Ozar published this webcast (slides, video with free registration) entitled "Real-Life SQL Server 2012: AlwaysOn Lessons Learned", in which he repeatedly states, ...
2
votes
1answer
492 views

AlwaysOn Availability Groups: Confusion with some concepts

In Sql Server 2012, I am trying to install Failover Clustering to configure AlwaysOn Availability Groups. When I run Cluster Validation test wizard, it comes back with a lot of errors. One of them ...
3
votes
2answers
4k views

Resetting a SQL Server 2012 sequence

I'm in the process of testing and populating a specific table that leverages the SEQUENCE object. In this process I'm testing populating the table with tens of thousands of insert lines (as I'm ...
4
votes
2answers
759 views

Restore SQL Server database from .sql files

So we had an encrypted database that puked and killed our whole SQL Server setup. Sucks about our data, but we were smart enough to have our data structures / stored procedures / functions in Git The ...
3
votes
1answer
951 views

LIKE character length limitation

The background to this question can be found here. The following part of my stored procedure does not work. Is there a character length limitation for the LIKE clause? t1.ProductList sometimes can be ...
2
votes
2answers
196 views

Using SQL Server 2012 for Business Intelligence Features Only, No Database Engine

I would like to implement SQL Server 2012 purely for the Business Intelligence Features: SSIS, SSAS and SSRS on its own server. I current have SQL Server 2008R2 Database Engine installed on another ...
2
votes
1answer
1k views

Should I use Asynchronous Mirroring?

I plan on setting up mirroring on a SQL 2012 database which is currently in production. My concern though is how much it will affect the performance of the principal database. I am using mirroring ...
4
votes
1answer
301 views

Error in SQL Server 2012 OUTPUT clause

Given the following SQL code against the AdventureWorks Database: begin transaction; drop trigger Sales.iduSalesOrderDetail; alter table Sales.SalesOrderDetail add LineNumber int null; go with ...
2
votes
1answer
353 views

Parameter triggers different execution plan, but why?

I have a table CREATE TABLE [dbo].[Numbers] ( [Date] [date] NULL, [Time] [time](3) NULL, [Value] [char](10) NULL ) and the table has > 10 Billion rows, therefore it ...
1
vote
1answer
528 views

Commit Insert but Hold Lock

I have implemented what is effectively a reliable queue through the following: CREATE TABLE Queue (MessageID UNIQUEIDENTIFIER, Message varchar(255)) --Enqueue Command DECLARE @MessageID ...
2
votes
2answers
3k views

32-bit ODBC driver on 64-bit SQL Server [closed]

I am unable to use an ODBC 64-bit driver with SSMS 2012 running on a SQL Server 2012 64-bit instance. However, I believe that the 32-bit driver works. My question is can I use SSMS 2005 32-bit to ...
7
votes
1answer
822 views

selecting more fields causes catastrophic failure

I have a connection to a linked server from sql server 2012 to pervasive sql. When I do select field1, field2, field3 from mytable everything works! However, when I do select field1, field2, field3, ...
4
votes
2answers
384 views

Integration Services Catalogs - Version Description

In Integration Services Catalogs I can see what versions of a package/project are available and if necessary revert to an older version. On this dialog I see a field "description" however I found no ...
0
votes
1answer
562 views

How can I install a provider for linked server?

I have a provider for a linked server called PervasiveOLEDB.11.0 installed on a sql server 2005 machine. It always me to have a connection to a pervasive sql database. I would like to use this same ...
1
vote
1answer
628 views

How can I change SQL Agent Log file to use unicode format?

I've created a database with tables that heavily leverages nvarchar for most of the columns. One of the challenges I'm running into is that when I go to view the SQL Server Agent Log files, I get the ...
2
votes
0answers
342 views

SQL71561 Error on Schema compare after initial import from database

I have created a new sql server project with SSDT 2012, then I went to import an existing database and it created all my scripts. After that I did a schema compare between the project and the database ...
5
votes
3answers
14k views

Can SQL Server Management Studio Express be installed standalone?

I was wondering if the SQL Server Management Studio Express (mainly for Microsoft SQL Server) be installed on a standalone machine (without any of the SQL Services and stuff) to connect to a remote ...
5
votes
1answer
503 views

TFS 2012 - Database Project - User with NO LOGIN map to Windows - best practices

I have a SQL Server Database Project (.NET 4.5) in TFS 2012. As a DBA, i'm looking for a mechanism whereby the developers can create 'CREATE USER' sql scripts and assign their permissions within the ...
9
votes
2answers
524 views

Best way to defrag/compact a database for archival purposes

We've got an SQL Server instance that's used for email archiving (courtesy of a 3rd party archiving package). Every so often, the software is rolled over to a new empty database. We've done this ...
6
votes
3answers
2k views

Change data capture - how to know who made the change?

Tracking who made the change identified by CDC. Along the lines of my datetime hack I tried the same approach by adding suser_sname as a new field with default value on the cdc change track table. ...
0
votes
1answer
333 views

Installation of SQL Server 2012 does not contain Virtual Interface Adapter?

I have installed the entire Enterprise edition of SQL Server 2012 and have noticed that I do not have the Virtual Interface Adapter. What is the cause of this?
1
vote
2answers
410 views

Query Performance Tuning on a huge table

I have the following table Transactions. The table looks like this: CREATE TABLE [dbo].[Transactions]( [TransactionID] [bigint] IDENTITY(1,10) NOT NULL, [PlayerID] [bigint] NOT NULL, ...
2
votes
1answer
1k views

SQL Server Create Access Denied even after adding MSSQLSERVER user permissions

I am aware that SQL Server instances need write access not for my user account (which is in fact irrelevant to that service's context) but for the user account or security principal under which the ...
1
vote
0answers
47 views

when can AG primary replica be a different host to WFC primary?

To set up an Availability Group with two servers, the servers both participate in a Windows Failover Cluster. I've seen cases after certain failover modes where the primary IP address in the cluster ...
2
votes
1answer
1k views

Error: 9245, Severity: 16, State: 1. / During the last time interval XXX query notification errors were suppressed

I've got this sequence of error messages showing up regularly in my server error log: Error: 9245, Severity: 16, State: 1. During the last time interval 257 query notification errors were ...
5
votes
3answers
5k views

Removing Databases in Recovery Pending Status after AG group was removed

I have removed a High Availability Group, disabled the option in sql configuration manager and restarted and I have also removed the Failover cluster components from windows and restarted. ...
2
votes
1answer
407 views

Inside SQLOS – Scheduling

Using SQL Server 2012 Enterprise Edition SP1 running on Windows Server 2008 R2 SP1 For each logical processor core on SQLOS has a scheduler assigned to it. Those scheduler can be viewed as status ...
6
votes
1answer
1k views

Warning in query plan “Cardinality Estimate”

create table T(ID int identity primary key) insert into T default values insert into T default values go select cast(ID as varchar(10)) as ID from T where ID = 1 The query above has a warning in ...
2
votes
1answer
5k views

Active/active SQL Server Clustering in Installation Center (setup.exe)

I like to know which option to choose in the setup.exe in SQL Server 2012/2008? What I have here is Windows Active/Active Cluster setup with one quorum and one msdtc configured. I installed my first ...
4
votes
1answer
788 views

Enable TDE for existing AlwaysOn databases

I need to do a little benchmarking of AlwaysOn, both with and without TDE enabled. All the instructions I see cover adding encrypted databases to an availability group, but I see no mention of ...
2
votes
0answers
194 views

dropping and adding subscription only picking up new article on some subscribers

I have used the following script to drop aand re-add a table from a subscription so I could move replace the ntext fields with nvarchar(max) columns. the script worked fine, but when I ran the ...
1
vote
4answers
3k views

Cannot remove Mirroring / nor restore or delete database:Error 3456 (SQL Server 2012)

I have a database that was a mirrored partner and went into suspended state. (Mirror Server was shut down). I took the principal database out of mirroring using SSMS. But now I am unable to restore ...
0
votes
0answers
2k views

0x84BB0001 Error when installing SQL Server 2012. Could not create a handshake [closed]

I have hopelessly attempted to install SQL server 2012 onto my 64 bit Windows 8 machine. The database engine always fails to install with error 0x84BB0001. Native reporting also fails, I reckon from ...
2
votes
0answers
236 views

tempdb objects creator wanted, bufferpool issue caused by tempdb objects

I have a number of tables in tempdb named like #A0030B77 #A01D908C #A06D1FCB #A0F72FB0 #A1614404 #A1EB53E9 #A205D8FE #A255683D How (if) can I get spid of the process which created them? These ...
2
votes
1answer
196 views

How can I rename a table, but also all FK etc. references to that table?

I would like to e.g. rename my Applicant table to ApplicantProfile, as applicant is an abstract entity that has a profile. The profile is not the applicant. Simply renaming the table is hopelessly ...
2
votes
1answer
72 views

How to create an object and then alter it

I'm using sql server 2012: I would like to script out creation of an object(if it doesn't exist first), and then alter it. I'm trying to avoid the usual drop/create as it would impact stats. It ...
-2
votes
1answer
333 views

SSIS SQL Server 2012 cluster Availibility Group [closed]

We have a three server cluster using AlwaysOn technology, and have created an Availability Group for the SSISDB catalog. How would you change the code to only execute on the primary server?
2
votes
1answer
1k views

Decryption not working when SQL Server database copied to new server

I am copying a database from an old server to a new server. The database has an encrypted column. From my searches I see that I need to backup the master key from the old server and restore it in ...
3
votes
0answers
457 views

SqlDependency permissions

The db is SQL Server 2012. All data tables are in the dbo schema. All data access is done via stored procedures in a different schema (user rt). I want to add query notifications for some tables, so I ...
2
votes
1answer
1k views

Why do I have to drop a snapshot in SQL Server 2012 to restore a database?

I never had to drop a snapshot with SQL Server 2008, but now when I try to restore in SQL Server 2012, I get the error: System.Data.SqlClient.SqlError: The operation cannot be performed on a ...
1
vote
1answer
785 views

Snapshot not picking up new articles in SQL Server 2012 transaction replication when added through script

I am trying to add new articles to an existing publication in SQL Server 2012. I am adding the article to the publication with the syntax --add the table into the correct publication sp_addarticle ...
1
vote
1answer
210 views

Do locks on readonly secondary databases propagate to the Read/Write Database

I'm thinking about using SQL Server Availability Groups in SQL Server 2012 to offload reporting on our main database to a second read only server that is kept in sync through the availability group. ...
1
vote
0answers
94 views

Page Header DB Frag ID Property

I am using SQL Server version: Microsoft SQL Server 2012 - 11.0.2100.60 (X64). I created a single table containing one and only one integer-valued column. I then added one and only one row to the ...
2
votes
1answer
8k views

Cannot connect remotely to sql server 2012 instance using SSMS

I have installed sql server 2012 Express on a local network machine, then added a new login: "sqlUser" mapped to "database1", and configured the instance to accept the remote access with both Windows ...
1
vote
0answers
619 views

SQL Server keeps crashing [closed]

I have had SQL Server 2012 on my server for about 6 months. Over the last few weeks, a problem has developed, whereby the SQL Server stops functioning, and any attempt to connect or query returns the ...