More often than not, I typically don't touch DTC on clusters anymore; however on a project where the vendor states that it's required. So a couple things here.
1) Do you really need DTC per instance or one for all?
2) Should DTC be in its own resource group or within the instance's group?
2a) If in it's own resource group, how do you tie an instance to an outside resource group? tmMappingSet right?
We have two locations in US, I am thinking of having 2 node SQL cluster for Lync 2010, I alardy have One DB server running in one location, now we got new site where we are planning to have one more DB for redundancy.
View 5 Replies View RelatedWe are running SQL Server 2012 Enterprise on Windows 2012 R2. We have set up A WSFC with the primary Node in our East Coast Data Center and the Secondary Node in our West Coast Data Center each node is in a dfferent subnet. Since we have high latency between the sites we run in Asynchronous mode with manual failover only. My quesion concerns Quorum, everything I read would indicate we need a third node (odd number) (e.g. a fileshare Witness) with only the file share and the primary node having a vote...What I don't see is any need for a file share if we can only do manual failover.
View 7 Replies View RelatedDisaster Recovery Options based on the following criteria.
--Currently running SQL 2012 standard edition
--We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately
--Recovery needs to happen within 1 hour (Not sure that this is realistic
-- We are building a new data center and building dr from the ground up.
What I have looked into is:
1. Transactional Replication: Too Much Data Not viable
2. AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances
3. Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options with be a nightmare.
When I fail an availability group between subnets, I am finding that the DNS entry in DNS is staying. So what happens is the Availablity Group listener has 2 records in DNS, one for each IP. This causes the App to timeout at times, since DNS will return either of the two IP's.
View 9 Replies View RelatedWe are implementing a multi-site (Windows Server Failover Cluster) WSFC to enable Always On between our primary and DR site. We are not going to use SQL clustered instances. We are not planning to use shared disks. Each node is running a standalone instance of SQL 2012.
I have successfully configured a 3 node multi-site Windows failover cluster with no shared storage. For quorum, I have defined a File Share Witness (FSW). The FSW has voting rights and is in the DR site. The setup looks like this –
WSFC –
•Node A – Site #1 (voting right = 1)
•Node B – Site #1 (voting right = 1)
•Node C – Site #2 (voting right = 0)
•FSW – Site #2 (voting right = 1)
Again - There are no shared disks in our setup. We are not going to use SQL clustered instance. We are going to use Always On with these 3 nodes.
SQL Always On –
•Node A – Site #1 (Primary Replica)
•Node B – Site #1 (Readable Secondary)
•Node C – Site #2 (Readable Secondary)
All the setup including the “availability group” works properly under this setup. However, a failover to site #2 under DR situation is not working and I know why but don’t know what needs to be done to fix the problem.
The following works fine –
•Automatic failover between nodes A and B (same site – site #1)
•Forced failover to node C in site #2 provided at least one of the nodes in site #1 is up (non – DR situation) - this will ensure the cluster is up
The following is not working –
•Forced failover to node C in site #3 when both nodes in site #1 are lost (true DR situation) – This is because the cluster is not up at this point.
I know I have to bring the cluster up somehow and I have not been able to do so by restarting the cluster service.
I tried to run the command to start cluster service.
Question –
How can I FORCE the cluster to come up in Site #2 on node C when it has no voting rights?
I have always worked with even number of nodes and shared disks with traditional clustering. I am not sure what needs to be done in this scenario with 3 nodes and a FSW.
I'm contemplating running two availability groups on a two node WSFC. The WSFC is setup with a file share witness (i.e. no shared storage). Can I safely run 1 AG on one primary node, and the other AG on the other node (as primary). Each AG would have replicas on the passive node. This would effectively allow both servers to be in use at the same time. In a failover event, I understand that both workloads would transfer to a single server - so the box needs to be sized appropriately.
View 1 Replies View RelatedWe are in the process of building a 3 node SQL Server Cluster (Server 2012/ SQL Server 2012), and we have configured the quorum so that all 3 nodes have a vote (no file share witness as we already have an odd number of nodes).
As I understand it, this should allow the cluster to run as long as 2 of the nodes remain online.
However, the validation report states that 2 node failures would be acceptable and, when we tested this by powering off two of the nodes, the cluster did indeed continue to run on a single node.
the stored procedure don't delete all the records
need help
Code Snippet
DECLARE @empid varchar(500)
set @empid ='55329429,58830803,309128726,55696314'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
TNX
I understand most of the concepts for the Active/active setup. I am setting up a 2 node cluster. My question is can I use the default instance name (one default instance name per group) when setting up each instance or do I have to use named instances . I am assuming that since each group is a virtual machine that I can get away with using the default instance, but I cant find any documentation to confirm my frequently wrong perceptions.
I want to set it up like this
Instance1 Group
Group resources
Default SQL Instance Name Installation
Preferred Owner Node1
Instance2 Group
Group resources
Default SQL Instance Name Installation
Preferred Owner Node2
Thanks
Hello,I am trying to construct a query across 5 tables but primarily 3tables. Plan, Provider, ProviderLocation are the three primary tablesthe other tables are lookup tables for values the other tables.PlanID is the primary in Plan andPlanProviderProviderLocationLookups---------------------------------------------PlanIDProviderIDProviderIDLookupTypePlanNamePlanIDProviderStatusLookupKeyRegionIDLastName...LookupValue....FirstName...Given a PlanID I want all the Providers with a ProviderStatus = 0I can get the query to work just fine if there are records but what Iwant is if there are no records then I at least want one record withthe Plan information. Here is a sample of the Query:SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,pl.InvalidDataFROM Plans plnINNER JOIN Lookups l3 ON l3.LookupType = 'REGN'AND pln.RegionID = l3.Lookupkeyleft outer JOIN Provider p ON pln.PlanID = p.PlanIDleft outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderIDleft outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'AND pl.ReasonMain = l1.LookupKeyleft outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'AND pl.ReasonSub = l2.LookupkeyWHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNumI know the problew the ProviderStatus on the Where clause is keepingany records from being returned but I'm not good enough at this toanother select.Can anybody give me some suggestions?ThanksDavid
View 5 Replies View RelatedCan I add a new node to an existing sql server 2012 cluster witthout stopping all the current resources?
Currently, there are 2 nodes and 3 sql server instances, which can not be shutdown. Is the process of adding nodes a "online" process?
I have installed SQL Server 2012 on Node1 successfully and trying add node on Node2 and its failing with below message. I attached the screen shot.
Here we are not doing multi-subnet failover clustering. Why we are getting extra and asking for IP and what IP should we enter there?Add Node option should automatically detect the IP we configured on Node1.
[Error Message] To support SQL Server multi-subnet failover clustering, you must select at least one valid IP address for every subnet in the cluster.
[Details] Microsoft.SqlServer.Configuration.Cluster.ClusterIPAddressPublicValidationException: To support SQL Server multi-subnet failover clustering, you must select at least one valid IP address for every subnet in the cluster.
What I have- Sql server 2012 (Standard Ed) Cluster on Windows 2012 R2 with both instances running on the same node- just to save on License, i.e. technically it’s Act/Pas cluster.
What I am looking for- how to configure cluster (e.g. via quorum, etc) to force both instances failed together? Means if for some reason 1-st instance will fail to node 2 another instance should follow (otherwise it will be Act/Act cluster and 2-nd license is required).
If there is no standard way (cluster configuration I mean) to do it I should create some custom process to monitor where each instance is running.
Few questions on a SQL Server 2012 two node active/passive cluster installation on Win2012.
1. What are the permissions required for the user used to install SQL Server 2012 cluster. Does it need to have any rights on DC or anywhere else apart from the local nodes ?
2. Can we give ANY meaningful name to "SQL Server Network Name" during installation ? Do we need to manually configure it anywhere else before or after the installation ?
3. On what scenarios we need to check/uncheck DHCP check box ?
It is an active passive cluster which doesn’t allow any testing. All instances have to be failed over together, we aren’t allowed to just failover 1 even for testing purposes. Node 1 is the active node and we can failover to node 2 for 30 days free of charge but services have to then be failed back.
We need to run the cluster with node 1 as the primary node always and 2 just use for failover testing or for less than 30 day periods whilst performing cluster patch upgrades etc.
Now l am sure we could fail over 1 instances at a time for testing and diagnosing issues plus if add a new instance that's not production to get to the platform level as the rest of the instances this would avoid taking production down in the fail over process.
I am new to Reporting Services and hope that what I am looking to do is within capabilities :-)
I have many identical schema databases residing on a number of data servers. These support individual clients accessing them via a web interface. What I need to be able to do is run reports across all of the databases. So the layout is:
Dataserver A
Database A1
Database A2
Database A3
Dataserver B
Database B1
Database B2
Dataserver C
Database C1
Database C2
Database C3
I would like to run a report that pulls table data from A1, A2, A3, B1, B2, C1, C2, C3
Now the actual number of servers is 7 and the number of databases is close to 1000. All servers are running SQL2005.
Is this something that Reporting Services is able to handle or do I need to look at some other solution?
Thanks,
Michael
When adding a node to a SQL Server 2012 Standard edition cluster, how I do I identify the location for SQL server shared components and the rest of the SQL Server installation binaries?
When adding a node to a SQL Server 2012 Standard edition cluster all the binaries went to the C: drive default location. We put those files on a different drive when installing the first node. What needs to be done so both nodes have the binaries on the same drives and folders?
Can we backup a DB from SQL Server 2012 (Ent. edition) two node cluster to a local disk ?. Is it possible ?
View 7 Replies View Relatedlet me know apart from the following what else is required
2 for private network
2 for public network ......
1 for virtual server/windows cluster ip which is used to connect application
I am trying to install the MS SQL Server 2012 Failover Cluster on Windows Server 2012 . I successfully Installed the Failover cluster instance on my primary node.
But when I am running the installation process on passive node to add node in the failover cluster I am stuck with very unique kind of issue.
I am following the Standard process of Installation and I am getting the same windows for each next process, but after License Agreement window when I get the Cluster Node Configuration window, then in "SQL Server Instance Name" drop down box I am not getting the Name of Instance which is already installed on the primary node. But this Instance complete information is appearing in below given box.
Only issue is Instance name is not appearing in the drop down list, that's why I am not able to select and when I click on next it trough error and do not proceed.
I read these instructions:
http://msdn2.microsoft.com/en-us/library/ms191545(SQL.90).aspx
But I'm not sure if I have to install SQL Server first on node 2, then add it to the cluster. Or does adding it to the cluster also install the software?
Thanks
We have a BI-Stack configuration for server SQL 2012 that includes a gateway server that houses the app, a second server which houses SQL Server DB and SSIS, and a third server that houses SSAS and SSRS. Where should SSDT tools be installed? I assume on the gateway? This is a production environment, so should VS and/or SSDT not be here at all?
View 1 Replies View RelatedI have a multi-value parameter that I am having a hard time writing a COUNT expression for in SSRS. Here is the situation:
1. If the "(Select All)" in the drop down is selected, COUNT all last names for ALL of the Auditor parameter
2. If a specific or multiple auditors are selected from the drop down, COUNT all last names based on that selection for the Auditor parameter
Currently, I am having it COUNT by ALL and it works but if a specific or multiple auditors are chosen, then the COUNT doesn't work.
i design SP for insert data in 2 tables i need to store list of array in one parameter to complete my query i try the table value but it`s not good for me because table value is readonly and i need to insert data with list of array .....
View 5 Replies View RelatedI want to maintain all configurations in Single table, what is the best way to approach it.
View 5 Replies View RelatedWe have a SQL 2014 AlwaysOn availability group running on two Windows 2012 R2 servers that are in the same subnet. We created a new server in a second subnet, installed SQL, joined the server to the Windows cluster, added a new IP resource for the new cluster, and performed the other remaining steps to add a new AG replica to the SQL instance on this new server. When we try to move the core cluster resources to the new node to test failover, we get an error. Here's the command we've been using:
Move-ClusterGroup "Cluster Group" -Node node3
and it returns the error: The operation failed because either the specified cluster node is not the owner of the group, or the node is not a possible owner of the group...I've checked the ownership of the cluster groups and the cluster resources and it looks like they are set appropriately:
>Get-ClusterGroup | Get-ClusterOwnerNode
Cluster Object Owner Nodes
---------------- ---------------
Available Storage {}
Cluster Group {node1,node2,node3}
SQLAG {node1,node2,node3}
[code]....
We've double-checked that all IP resources are in the right subnets and that the dependencies for the Cluster Name resource and the Listener Name resource are set appropriately. I'm not sure what else to check since the PowerShell commands seem to indicate that node3 is an owner of the appropriate resources. What other things need to be checked or if the ownership being checked isn't the same as what PowerShell is checking?
I am setting up SQL 2014 always on. I was able to set up the replicas between 2 servers in the same subnet.Their IP addresses are say like this:
100.20.200.200
100.20.200.201
When I am trying to introduce another node into the cluster which has IP address like 100.10.101.102, I am getting an error that the server isn't reachable.
I have a DB that is currently not normalized and will be getting about 100K concurrent users that will mostly be doing Read-Only operations from multiple tables.
I am trying to figure out if I should start thinking of having a DB per client (1000 clients) or if I should normalize the database and keep it as a single DB with good indexes and partitioning.
Hardware is not a problem but 100K concurrent users is.
I configure Windows 2003 R2 and SQL 2005 two nodes Cluster. When I move cluster resource from one node to anther node it takes around 30 seconds to become online. So in that time if any query is running it stops responding.
So please suggest in this regard
We have an application that runs Jobs, each of which affect ## number of child objects (usually around 1M). When a thread gets to 5000 updated child objects it bulk inserts into a table called ActionLog with the child Id and JobId.
When the job is complete a sproc SUMs the children from the ActionLog table:
select sum(id) from ACTIONLOG where JOBID = @JobId;
It then updates the Jobs table AffectedObjectCount column with the sum(*) from above.
Instead of writing to the ActionLog table and calculating the SUM at the end I would like to do this 'real time'. After the bulk insert I would like to update the AffectedObjectCount column with the number of rows that were just bulk inserted. I tried this in the past and ran into major contention issues. There are usually 20 threads running a job so there exists a lot of potential for deadlocks.
Is there a recommended way to handle updating one column on one row from multiple threads? What is the best practice for a counter like this?
Greetings,We are trying to set up a set of "Leading Practices" for ourdevelopers, as well as ourselves, and hope some gentle reader canrecommend some documentation in favor of what appears to be the rightposition to take.We do not allow third party applications to run on our SQL Servers. Wewant to include DTS Packages under the definition of third partyapplications, insisting instead that the developers save theirpackages as COM Formatted files into their source code control systemsand run them from their app servers. The devlopers would like to hearthis from someone besides ourselves.While strong recomendations to remove guest access to MSDB altogetherabound, I have been unable to find a straight forward discussion ofthe advantages of structured file storage and app server off load ofDTS packages.Can anyone suggest any articles, white papers, rants, etc attemptingto formulate a solution to the benefits of taking msdb away fromguest, with the advantages of running DTS from an App server orworkstation platform, with the packages protected in source codecontrol?Thank youJohn Pollinsjpollins @ eqt . com
View 2 Replies View RelatedDuring the installation of Adding node to a SQL Server failover cluster(On passive node) getting error like.. The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start.We run the below commands but didn’t get any resolution & got the same above error .
1<sup>st</sup> Method…
1. Open console command (Run->CMD with administrator privileges).
2. net stop winmgmt
3. Rename folder %windir%System32WbemRepository to other one, for backup purposes (for example _Repository).
4. net start winmgmt
2<sup>nd</sup> Method..
1. Disable and stop the WMI service.
a) Command : - sc config winmgmt start= disabled
b. Command : - net stop winmgmt
2. Run the following commands.
a). Command: Winmgmt /salvagerepository %windir%System32wbem
b). Command: Winmgmt /resetrepository %windir%System32wbem
3. Re-enable the WMI service
Command: sc config winmgmt start= auto
Last command to run after above steps
4. Command: mofcomp "%programfiles(x86)%Microsoft SQL Server100Sharedsqlmgmproviderxpsp2up.mof"
File not found Error for above command.