Is it possible to run SQL server clustered instance on 2 different IP on two different segment? or two different IP's on the same segment? We are using SQL server 2014 STD and Windows server 2012r2 STD...
View 3 RepliesI am trying to increase the performance of a small database with a table
that occupies 60% of the disk space.
Aside from archiving some data , I am thinking of moving the only index now
-- the clustered index -- on to its own segment in an effort to speed up
performance. I am sure if:
1) a clustered index can be on a separate segment since the data, I think,
would move with it? Is hat correct?
2) If it is corect, should I do that? How ?
Thanks.
David Spaisman
Hi everyone,
I'm new to SQL Server, but have worked a lot with oracle. I'm not sure if some of the terms I'll use is specific to oracle or if SQL server DBAs also use it. Please bear with me.
I was wondering if I could change the default temporary database or file group (sql equivalent to oracle's default temporary tablespace)for a user.
I also would like to change the rollback segment for a transaction. In oracle it would be: set transaction use rollback segment 'seg_name'.
My sql server version is 2000 by the way.
I would be very greatful if anyone could help.
I have a 3 node cluster on which I have installed SSAS as it's own insntance. I have created this as a named instance and can connect to it by serverinstance if I'm on the server itself. However from my desktop I get the error saying instance was not found on server name.
I have defined an alternate port and setup firewall rules and can connect via server:port but not serverinstance. Prior to making this change SSAS was running on default port of 2383 and I could connect just by servername.
I have read many articles for previous versions saying that clustered SSAS will always use 2383 and that you must connect just using servername. However and this is were it gets strange. I have a 2 node UAT cluster with SSAS setup exactly the same way I've described above and I can connect from my desktop as serverinstance.
Should I be able to connect as serverinstances for a named clustered instance in 2012 ?
We are in the process of migrating our databases from one pair of Servers running SQL Server 2000 using a Failover Cluster to another pair.
Unfortunately, we have some programs that are hard-coded to the existing instance and it will take time I don't have at this time to rewrite those programs.
Our old Clustered SQL Server is named SQLCluster and the new one is named SQLCluster2
We have backed up and restored the databases themselves, but cannot seem to rename the new SQL Server Cluster to SQLCluster.
Is there a way to do this?
'til later,
Scott
I've installed SSAS 2012 as a failover cluster instance on a 2-node Windows 2012 R2 cluster. Â All was fine in that regard but I now need to change the instance name so am going about the process of uninstalling and re-installing. Â I've successfully removed the second node from the configuration using the "Remove cluster node" wizard from the Setup options, but when it comes to removing it from the final node I'm getting the error "the cluster group **** could not be moved from node x to node 'null'". Â At this point the uninstall fails and the cluster resource remains in cluster administrator. Â It's obviously done some work/damage because the service won't start now, but it's left in limbo land where I can't do anything with it.
Is there a manual way of removing the instance to overcome this issue (file deletions, registry entry deletions etc.)?
I will be installing a second SQL 2005 instance on an existing SQL 2005 clustered installation. We've added a set of disks for the new instance. My question is, will the existing clustered instance need to be taken offline for the installation, or will the nodes need to be rebooted after installing the new instance?
I haven't been able to confirm this on MS website or by Googling.
I understand we need to ensure no one is logged in to the remote nodes during the install as well.
Thank you
Grant
In our lab we have 2 clustered instance of Sql server 2018R2 as follows
Virtual Server Name                                                  Â
 IP SqlVir 10.1.1.6
SqlVirDr   10.1.1.12
The Data is SqlVir is replicated manually every day to SqlVirDr.
We had to change the Virtual Server name of SqlVirDr to SqlVir so that all dot net applications accessing SqlVir could continue to access the database without changing the application string.
For that purpose I deleted the computer name SqlVir from the domain and its IP 10.1.1.6 from DNS. Then I went to the failover cluster manager of SqlVIrDr right clicked the Sql services selected the properties and changed the DNS name from SqlVirDr to SqlVir.The applications then could access the data.However when I changed the network IP address from 10.1.1.12 to 10.1.1.6 the Sql services was found to be down.
Perhaps the procedure I followed in deleting the computer name from domain and the IP from DNS was wrong.What exactly is the steps that I should follow to achieve the above objective.
Approach 1:
Prod - shared storage between server 1 and 2
Server1: clustered SQL instance with availability group as primary
Server2: Passive server for clustered instance of PROD
DR - shared storage between server 1 and 2
Server1: Clustered SQL instance with availability group as replica
Server2: Passive server for clustered instance of DR
Approach 2: Using replicated SAN
Prod -
Server 1: Standalone instance with availability group as Primary
Server 2:Standalone instance with availability group as replica
DR -
Server 1: Offline until Disk group 1 (Prod server 1) has been broken and brought online at DR
Server 2: Offline until Disk group 2 (Prod server 2) has been broken and brought online at DR
Both these approaches will work wont they? I have only built and played with normal availability groups across servers, not mixing it with clustered instance replicated SAN
I get "there was an unknown error applying the filestream settings("0x800710dc ") configuring my sql clustered instance with configuration manager. Not clear for me what I have to set for "Windows shared name".. Is it just an aliases or do I have to give the name of a shared disk ??
Get the same issue with SQL2012 and SQL2014..
We have an Sql Server 2008R2 Clustered production instance by name 'ProdVir' configured in 2 nodes(Active-passive) withWIndows Server 2008 R2. We also have another clustered instance as disaster recovery by name 'VirDr' configured again in another 2 nodes of Windows Server 2008 R2. Every day morning there is mainatenace plan which backups all the database in production and another maintenace plan in the disaster recovery server 'VirDr' which restores the backups into the VirDr instance.
I would like to know that in an eventuality of a disaster in the clustered production instance of 'ProdVir'. could we rename the the instance VirDr(meant for disaster recovery) to ProdVir and also change the Ip addresses accordingly so that the application programs do not have to change the details for the datasource in the connection strings.
We have SQL Server 008 R2 failover clustered instance.
Installation and everthing went smooth. But I'm getting a weired error
Unable to connect to SQL Server clustered instance from one of the nodes
When SQL Server Resources are on sql1:
Connect from SSMS from sql2--> NOT Able to connect to SQL instance (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2))
Connect from SSMS from sql1-->Working fine
When SQL Server Resources are on sql2:
Connect from SSMS from sql1-->Working fine
Connect from SSMS from sql2-->Working fine
We have installed 2014 sql server on a stand alone VM and can create alerts without issue. We recently did a cluster install of 2014, and when trying to create alerts get the following error:
Msg 14262, Level 16, State 1, Procedure sp_verify_performance_condition, Line 52
The specified object_name ('Databases') does not exist.
Specifically, we are trying to create a database alert on percent log used that will kick off a job to back up the log.
We are on Windows 2012R2 o/s. In the drop downs in SSMS under alerts, we do not have all the options we have in the stand alone instance - they are not there. Is this a bug? Is there a CU?
How can i add additional SQL server instance to existing clustered environment on SQL 2005.
View 7 Replies View RelatedI am getting this error in SQL 6.5:
Error: [Microsoft][ODBC SQL Server Driver][SQL Server]. Can’t allocate space for object ‘Syslogs’ in database ‘ER’ because the ‘logsegment’ is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
Should I increase the size? Its already 350 MB. I would like to start over with a new transaction log. Can I do this?
Hi,I am an Oracle developer and new to SqlServer. Is there rollbacksegment in SqlServer? If there is, does it use only for the purposeof rollback or to maintain read consistency as well?If there is no rbs in SqlServer. How does it handle rollback and readconsistency?Thanks!Susan
View 1 Replies View RelatedHi all,
I encountered error 1105 (Can`t allocate space for object .....) while running a query.
I had dump transaction with no log sucessfully and retry running my query but
still encounter error 1105.
I had checked my database size and following are the info:
Datasize : 2500MB Database size available : 753.39
Log space : 500MB Log space available : 500MB (this was acheive after
after dump trans. twice. it was around 480MB before)
Thus looks like the data segment is full!!!
I expanded the size of the disk device D on which the segment device A is on by 50MB. When I did a sp_helpdevice D, the expansion is reflected.
Next I run sp_extendsegement, A, D.
However running sp_helpsegment A doesn`t show that segment A is expanded.
Please help!!! How do I expand the size of the segment? (without creating a new disk device, is it possible)
Any other way to resolve this problem?
Also how to check if a particular segment is full? eg. the %usage of data/log segment.
Thanks in advance.
Hi,
I´m trying to transfer a large amount of data from Oracle 8 to Sql Server. For each table, I have an OleDBSource with DataAccessMode Command (select with one inner join), Data Conversion and OleDBDestination. Sometimes I´m getting an ORA-01555 snapshot too old error, so I´d like to set a bigger rollback segment, but don´t know how. Please, can anybody help me?
Thanks
Janca
Hey - this is a SQL 6.5 Question. I get an error that goes something like this...
Can't allocate space for object 'Syslogs' in database 'test_database' because the 'logsegment' segment is full. If you ran out of space in the Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size fo the segment.
Okay, i understand that the log is full. But since I have been working mainly on SQL 7, I don't completely understand the 'dumping'. I know that everyone is busy, and if you don't have time to explain this to me, could you tell me where to look? I am going to check MS Knowledge Base, but any advice or direction is appreciated.
Thanks guys and gals!!!
We are going to use SQL Sever change tracking. The problem is that some of our tables, which are to be tracked, have no primary keys. There are only unique clustered indexes. The question is what is the best way to turn on change tracking for these tables in our circumstances.
View 4 Replies View RelatedI desire to have a clustered index on a column other than the Primary Key. I have a few junction tables that I may want to alter, create table, or ...
I have practiced with an example table that is not really a junction table. It is just a table I decided to use for practice. When I execute the script, it seems to do everything I expect. For instance, there are not any constraints but there are indexes. The PK is the correct column.
CREATE TABLE [dbo].[tblNotificationMgr](
[NotificationMgrKey] [int] IDENTITY(1,1) NOT NULL,
[ContactKey] [int] NOT NULL,
[EventTypeEnum] [tinyint] NOT NULL,
[code]....
I have created two tables. table one has the following fields,
                     Id -> unique clustered index.
        table two has the following fields,
                     Tid -> unique clustered index
                     Id -> foreign key of table one(id).
Now I have created primary key for the table one column 'id'. It's created as "nonclustered, unique, primary key located on PRIMARY". Primary key create clustered index default. since unique clustered index existed in table one, it has created "Nonclustered primary key".
My Question is, What is the difference between "clustered, unique, primary key" and "nonclustered, unique, primary key"? Is there any performance impact between these?
Hello,
How do I restore a sql database that is on a clustered server from a sql database backup file that is on a non_clustered server?
Thanks,
Serey
I have large table with 10million records. I would like to create clustered or non-clustered index.
What is the quick way to create? I have tried once and it took more than 10 min.
please help.
Hi there, I have a table that has an IDENTITY column and it is the PK of this table. By default SQL Server creates a unique clustered index on the PK, but this isn't what I wanted. I want to make a regular unique index on the column so I can make a clustered index on a different column.
If I try to uncheck the Clustered index option in EM I get a dialog that says "Cannot convert a clustered index to a nonclustered index using the DROP_EXISTING option.". If I simply try to delete the index I get the following "An explicit DROP INDEX is not allowed on index 'index name'. It is being used for PRIMARY KEY constraint enforcement.
So do I have to drop the PK constraint now? How does that affect all the tables that have FK relationships to this table?
Thanks
What is the easiest way to remember the definitions of clustered and non clustered indexes.
View 9 Replies View RelatedI have a really super slow stored proc that does something simple. it updates a table if certain values are received.
In looking at this the matching is done on the Primary Key, which is set as a Clustered index, looking further I have another constraint, that sets the same column to a Unique, Non-Clustered.
I am not sure why this was done, but it seems to be counter productive. I have read only references to Which one is better on a primary key, but not can their be both and if it is "Smart".
Hello,
I've a table with primary key defined as non-clusterd, now without dropping it can I modify the existing index to clustered through tsql as I had to write some migration script and in that script I wanna do this.
Thanks in Advance,
Rohit
I would like to find information on Clustered and Non-clustered indexes and how B-trees are used. I know a clustered index is placed into a b-tree which makes sense for fast ordered searching. What data structure does a non-clustered index use and how? I tried to find info. on the web but couldn't get much detail...
View 3 Replies View RelatedI have a table<table1> with 804668 records primary on table1(col1,col2,col3,col4)
Have created non-clustered index on <table1>(col2,col3,col4),to solve a performance issue.(which is a join involving another table with 1.2 million records).Seems to be working great.
I want to know whether this will slow down,insert and update on the <table1>?
the query:
SELECT a.AssetGuid, a.Name, a.LocationGuid
FROM Asset a WHERE a.AssociationGuid IN (
SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada
WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
takes 30-60 seconds to run on my machine, due to a clustered index scan on our an index on asset [about half a million rows]. For this particular association less than 50 rows are returned.
expanding the inner select into a list of guids the query runs instantly:
SELECT a.AssetGuid, a.Name, a.LocationGuid
FROM Asset a WHERE a.AssociationGuid IN (
'0F9C1654-9FAC-45FC-9997-5EBDAD21A4B4',
'52C616C0-C4C5-45F4-B691-7FA83462CA34',
'C95A6669-D6D1-460A-BC2F-C0F6756A234D')
It runs instantly because of doing a clustered index seek [on the same index as the previous query] instead of a scan. The index in question IX_Asset_AssociationGuid is a nonclustered index on Asset.AssociationGuid.
The tables involved:
Asset, represents an asset. Primary key is AssetGuid, there is an index/FK on Asset.AssociationGuid. The asset table has 28 columns or so...
Association, kind of like a place, associations exist in a tree where one association can contain any number of child associations. Each association has a ParentAssociationGuid pointing to its parent. Only leaf associations contain assets.
AssociationDataAssociation, a table consisting of two columns, AssociationGuid, DataAssociationGuid. This is a table used to quickly find leaf associations [DataAssociationGuid] beneath a particular association [AssociationGuid]. In the above case the inner select () returns 3 rows.
I'd include .sqlplan files or screenshots, but I don't see a way to attach them.
I understand I can specify to use the index manually [and this also runs instantly], but for such a simple query it is peculiar it is necesscary. This is the query with the index specified manually:
SELECT a.AssetGuid, a.Name, a.LocationGuid
FROM Asset a WITH (INDEX (IX_Asset_AssociationGuid)) WHERE
a.AssociationGuid IN (
SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada
WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
To repeat/clarify my question, why might this not be doing a clustered index seek with the first query?
I am transferring data from oracle and getting below error message.
I using 4 data flow tasks with in a single control flow and all the 4 tasks quueries same table but populates data in to different sql tables based on the where contidion
[OLE DB Source 1 [853]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "ORA-01652: unable to extend temp segment by 64 in tablespace TEMP ".
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:
When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004
Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing?
If so does this "pseudocode" example also cause this to occur:
sqlstring="select * from item where itemid=12345"
rs.open sqlstring, etc, etc, etc
rs.Fields("ItemName")="My New Item Name"
rs.Fields("ItemPrice")=1.00
rs.Update
Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?