I have recently changed IP addresses on my MS SQL cluster (the new IP
addresses belongs to a different network). Both physical node's IP
addresses and resource's IP addresses have been changed.
Everything seems to work fine. The cluster is up and responding
normally. The resources failover to another node when needed correctly.
However, I have noticed that the time it takes for the resource to
failover to another node is much longer than in the past (before IP
addresses change). I have noticed that "Network Name" is the resource
type that causes the delay. Could you please advise if there is
something that I overlooked and needs to be changed.updated as well, so
that groups fail over faster?
IF you need to change the IP Address of a SQL Server Failover Cluster instance, here is the steps:
1. Open up Failover Cluster manager. 2. Expand the MS Cluster Instance that your SQL Cluster instance resides on. 3. Expand Services and applications. 4. Select the SQL Server Service. 5. In the main window pane (Middle Screen), you should see your SQL Server Cluster name, expand the plus to the left of it. 6. Under your SQL Server Cluster name, you should now see an 'IP address' section, right click on it and go to properties. 7. In the properties pane, you can change your IP address by entering a static IP.....or assigning a DHCP enabled IP.
I am asking about a virtual IP for SQL Server, is there a way we can assign a different IP to SQL Server other than the server's(host) IP address? like the same what we do in a clustered env.
We are planning to change all IPs of PRODUCTION Failover Cluster Setup. In my cluster setup ... we have 2 Physical Nodes with windows-2008, Roles are MSDTC and SQL-2008R2.
IP change for:
1. Both Nodes(Physical) 2. MSDTC 3. SQL Server 4. windows Cluster
So Almost... All IPs are going to change.
Im DBA here, I need to take care of SQL cluster and MSDTC. But I haven't performed this activity before.So I'm worrying about Impacts and consequences of this change. steps how should I perform this activity.
I have inherited two separate SQL clusters. One cluster instance is called DESQLSC1INST1 and the other is MKSQLSC1INST1.
On MKSQLSC1INST1 I can connect by using MKSQLSC1INST1, MKSQLSC1 and the IP address that the cluster is running on. On DESQLSC1INST1 I can only connect using DESQLSC1INST1.
I have checked configuration manager but it does not show me how this is set up.
We have many tables which have cluster index on column with datatype 'Char(200)'. Does anyone have script to change cluster index to noncluster for all user tables which have clustered index on a column with 'char(200)' datatype.
We could use charindex, substring and len to cut the 4 parts of the ip and rebuild it in the right format.
Starts with SELECT CHARINDEX('.',mycolumn) ,LEFT(mycolumn, CHARINDEX('.',mycolumn) - 1) as part_four_of_ip ,CHARINDEX('.', SUBSTRING(mycolumn,CHARINDEX('.',mycolumn) - 1),len(mycolumn) as position_of_second_dot ........ and so on.....
This will cause an awful long code. How to shorten that code?
I have an installation of SQL Server Express 2005 installed on a Windows 2003 server. Through the forums on the board I have been able to turn on and make sure that port 1433 and port 1434 are turned on and listening. However, both ports show that they are listening on IP address 0.0.0.0. and I need for port 1433 to either listen on the server's main IP address or at the very least on 127.0.0.1. I have set them to enabled in the Configuration manager but they still don't appear to be listening on either of those IP addresses. Could some one tell me if there's another configuration change I might need to make?
I'm usning xp_sendmail to send e-mails to customers. In the From filed of e-mail it shows the user id which runs this process. Can I mask or change this like FROM: Customer service
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.
I just moved our SSRS server from one machine to another. It was sending our report emails from a service account. So when my end users get the emailed report it was from a service account (reports@blah.com) .
Now that I moved the DB to another server - the reports get emailed successfully - but they are being sent from my email address!
I have checked the rsreportserver config file. I also checked in the report server smtp setup and they both show the service account. I even checked the subscription owners and accounts.
SSRS is using my account, but I can't find where it's getting it from. Where else can I look?
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.
Once the SQL2005 cluster is installed can I change the "physical" server name of the 2 server nodes in the cluster. I do not want to change the virtual server name but the "physical" Windows server name....
We have 2 node sql 2012 cluster and we have a scenario where domain,IPs,Hostanmes and SQL network name are changing.
1> Can SQL cluster role be brought online by changing the sql network name and its IP once windows team reconfigure the OS cluster or there will be any challenges.
2>should I uninstall sql cluster on both nodes then windows will destroy OS cluster and they then they need to change IP,hostname and domain and then fresh installation of sql cluster
Complete: SetPackageInstallStateAction at: 2007/9/23 7:33:43, returned true Running: DeterminePackageTransformsAction at: 2007/9/23 7:33:43 Complete: DeterminePackageTransformsAction at: 2007/9/23 7:33:43, returned true Running: ValidateSetupPropertiesAction at: 2007/9/23 7:33:43 Complete: ValidateSetupPropertiesAction at: 2007/9/23 7:33:43, returned true Running: OpenPipeAction at: 2007/9/23 7:33:43 Complete: OpenPipeAction at: 2007/9/23 7:33:43, returned false Error: Action "OpenPipeAction" failed during execution. Running: CreatePipeAction at: 2007/9/23 7:33:43 Complete: CreatePipeAction at: 2007/9/23 7:33:43, returned true Running: RunRemoteSetupAction at: 2007/9/23 7:33:43 <Func Name='CProcessCtrl::GetInstallPath'> <EndFunc Name='CProcessCtrl::GetInstallPath' Return='0' GetLastError='0'> Error: 0x80070050 TaskScheduler::NewWorkItem for SQL Server Remote Setup Error: 0x80070005 TaskSchedulerWorkItem failed to save the task [SQL Server Remote Setup ] Complete: RunRemoteSetupAction at: 2007/9/23 7:33:43, returned false Error: Action "RunRemoteSetupAction" failed during execution. Error information reported during run: Attempting to determine log files for remote install. Connection to remote computer's scheduler service. Creating new workitem. Deleting existing work item and trying again... Starting remote setup onSQL1N2 Error: 80070005 Access is denied. Running: PopulateMutatorDbAction at: 2007/9/23 7:33:43 Complete: PopulateMutatorDbAction at: 2007/9/23 7:33:43, returned true Running: GenerateRequestsAction at: 2007/9/23 7:33:43 SQL_Engine = 3 SQL_Data_Files = -1 SQL_Replication = -1 SQL_FullText = -1 SQL_SharedTools = -1 SQL_BC_DEP = -1 Analysis_Server = -1 AnalysisDataFiles = -1 AnalysisSharedTools = -1 RS_Server = -1 RS_Web_Interface = -1 RS_SharedTools = -1 Notification_Services = -1 NS_Engine = -1 NS_Client = -1 SQL_DTS = -1 Client_Components = -1 Connectivity = -1 SQL_Tools90 = -1 SQL_WarehouseDevWorkbench = -1 SDK = -1 SQLXML = -1 Tools_Legacy = -1 TOOLS_BC_DEP = -1 SQL_SSMSEE = -1 SQL_Documentation = -1 SQL_BooksOnline = -1 SQL_DatabaseSamples = -1 SQL_AdventureWorksSamples = -1 SQL_AdventureWorksDWSamples = -1 SQL_AdventureWorksASSamples = -1 SQL_Samples = -1 Complete: GenerateRequestsAction at: 2007/9/23 7:33:44, returned true Running: CreateProgressWindowAction at: 2007/9/23 7:33:44 Complete: CreateProgressWindowAction at: 2007/9/23 7:33:44, returned false Error: Action "CreateProgressWindowAction" failed during execution. Running: ScheduleActionAction at: 2007/9/23 7:33:44 Complete: ScheduleActionAction at: 2007/9/23 7:33:45, returned true Skipped: InstallASAction.11 Waiting for actions from remote setup(s) Breaking wait state and aborting package due to cancel code received: 1602 Remote setup(s) are ready Notify package action is determined: 1602 Error Code: 0x800700e9 (233) Windows Error Text: No process is on the other end of the pipe.
Source File Name: remotemessageliboverlappedpipelistener.cpp Compiler Timestamp: Sat Oct 7 09:43:54 2006 Function Name: sqls:verlappedPipeListener::writePipe Source Line Number: 294
Notification failed to send. ---- Context ----------------------------------------------- sqls::HostSetupPackageInstallerSynch::installAction
Removing machine from list of targets to sync. Skipped: Action "InstallASAction.11" was not run. Information reported during analysis: All installs have been cancelled, so package: "sqlsupport", referred by package: "as", will not be installed. Skipped: InstallASAction.18 Skipped: Action "InstallASAction.18" was not run. Information reported during analysis: All installs have been cancelled, so package: "owc11", referred by package: "as", will not be installed. Skipped: InstallASAction.22 Skipped: Action "InstallASAction.22" was not run. Information reported during analysis: All installs have been cancelled, so package: "bcRedist", referred by package: "as", will not be installed. Skipped: InstallASAction.9 Skipped: Action "InstallASAction.9" was not run. Information reported during analysis: All installs have been cancelled, so package: "msxml6", referred by package: "as", will not be installed. Skipped: InstallDTSAction Skipped: Action "InstallDTSAction" was not run. Information reported during analysis: All installs have been cancelled, so package: "dts", will not be installed. Skipped: InstallDTSAction.11 Skipped: Action "InstallDTSAction.11" was not run. Information reported during analysis: All installs have been cancelled, so package: "sqlsupport", referred by package: "dts", will not be installed. Skipped: InstallDTSAction.12 Skipped: Action "InstallDTSAction.12" was not run. Information reported during analysis: All installs have been cancelled, so package: "sqlncli", referred by package: "dts", will not be installed. Skipped: InstallDTSAction.18 Skipped: Action "InstallDTSAction.18" was not run. Information reported during analysis: All installs have been cancelled, so package: "owc11", referred by package: "dts", will not be installed. Skipped: InstallDTSAction.22 Skipped: Action "InstallDTSAction.22" was not run. Information reported during analysis: All installs have been cancelled, so package: "bcRedist", referred by package: "dts", will not be installed. Skipped: InstallDTSAction.9 Skipped: Action "InstallDTSAction.9" was not run. Information reported during analysis: All installs have been cancelled, so package: "msxml6", referred by package: "dts", will not be installed. Skipped: InstallNSAction Skipped: Action "InstallNSAction" was not run. Information reported during analysis: All installs have been cancelled, so package: "ns", will not be installed. Skipped: InstallNSAction.11 Skipped: Action "InstallNSAction.11" was not run. Information reported during analysis: All installs have been cancelled, so package: "sqlsupport", referred by package: "ns", will not be installed. Skipped: InstallNSAction.12 Skipped: Action "InstallNSAction.12" was not run. Information reported during analysis: All installs have been cancelled, so package: "sqlncli", referred by package: "ns", will not be installed. Skipped: InstallNSAction.18 Skipped: Action "InstallNSAction.18" was not run. Information reported during analysis: All installs have been cancelled, so package: "owc11", referred by package: "ns", will not be installed. Skipped: InstallNSAction.22 Skipped: Action "InstallNSAction.22" was not run. Information reported during analysis: All installs have been cancelled, so package: "bcRedist", referred by package: "ns", will not be installed. Skipped: InstallNSAction.9 Skipped: Action "InstallNSAction.9" was not run. Information reported during analysis: All installs have been cancelled, so package: "msxml6", referred by package: "ns", will not be installed. Skipped: InstallRSAction.11 Skipped: Action "InstallRSAction.11" was not run. Information reported during analysis: All installs have been cancelled, so package: "sqlsupport", referred by package: "rs", will not be installed. Skipped: InstallRSAction.18 Skipped: Action "InstallRSAction.18" was not run. Information reported during analysis: All installs have been cancelled, so package: "owc11", referred by package: "rs", will not be installed. Skipped: InstallRSAction.22 Skipped: Action "InstallRSAction.22" was not run. Information reported during analysis: All installs have been cancelled, so package: "bcRedist", referred by package: "rs", will not be installed. Skipped: InstallSqlAction Clustered feature detected: SQL_Engine Clustered feature detected: SQL_FullText Loaded DLL:C:Program FilesMicrosoft SQL Server90Setup Bootstrapsqlsval.dll Version:2005.90.3042.0 Windows Error Text: User cancelled installation.
Source File Name: sqlchainingsqlchainingactions.cpp Compiler Timestamp: Thu Nov 16 20:32:00 2006 Function Name: sqls::ReportChainingResults:erform Source Line Number: 3667
---- Context ----------------------------------------------- sqls::RunRemoteSetupAction::waitForRemoteSetupComplete king package: "patchRS2000" as failed due to cancel code received from cancel source: 1602 sqls Delay load of action "UploadDrWatsonLogAction" returned nothing. No action will occur as a result. Message pump returning: 1602
Env: SQL Server 2000It actually waited for 50 seconds instead of 5, triedWAITFOR DELAY '000:00:005' and WAITFOR DELAY '000:00:5' respectively,and got same behavior. Bug or ?TIA
I am parsing a file where along the flow I use a conditional split. One path of the split is the primary table (with IDENTITY) values. The rest of the paths have a FOREIGN KEY to the primary table.
It seems that SSIS is trying to insert the rows at the same time (which makes sense) but this is causing a problem with the secondary tables and their FK constraint since the primary table is not yet written.
Is there a way to delay the secondary tables until the primary table is done?
(I guess one way is to run through the file twice... once for the primary table and another for the rest but that seems wasteful to me...)
I am using Sql 2005 SP1 and merge replication on a database. One of the tables is used for an audit trail and has a dynamic filter applied so that it doesn't replicated every audit trail record to every subscriber.
Our sp's tend to insert records in to the audit trail table when someone inserts a new product (for example). The problem is that just recently the insert of new products has been taking >2 seconds, this is relatively slow compared to how it used to be 2 months ago.
Using profiler I have found that it is the insert in to the audit trail table that is taking all the time, and this is taking a long time because of something replication is doing. From profiler I have found that the following statement is the culprit. This is something that replication is doing but why it take so long I don't know:
select count(*) from [dbo].[MSmerge_repl_view_000CC979122E4C88AF27FE08CDCC84EB_B5F96F71937D4D9A949DEECFE540D0C4] [AUDIT_TRAIL_DETAIL] with (rowlock) where [RowGUID] in (select [AUDIT_TRAIL_DETAIL].[RowGUID] from inserted [AUDIT_TRAIL_HEADER], [dbo].[MSmerge_repl_view_000CC979122E4C88AF27FE08CDCC84EB_B5F96F71937D4D9A949DEECFE540D0C4] [AUDIT_TRAIL_DETAIL] with (rowlock) where (AUDIT_TRAIL_HEADER.ID = AUDIT_TRAIL_DETAIL.FKAuditTrailHeaderID))
The AUDIT_TRAIL_DETAIL table currently has 1.1 million row in it.
Can anyone give me any clues as to what I should do help improve the performance once again? Should I stop filtering on this table?
It works well but some times I get long delay in connection and read data.is it any way to solve the problem?
for more information whene is working well I can connect to database and get all information I need in .1 sec. when is going to be late this action may takes 20 sec
Hi, I created a stored procedure that run as a service in the SQL Server , as long as the server is up. It queries a table , does some work on the rows one by one , and after each row is done - deletes it from the table. If there is no data - it goes to 'sleep' - waitfor delay ('00:00:03')
does anyone knows if that waitfor command releases the cpu , or just counting and uses the cpu ? Eyal.
I`ve been following the newsgroups, and the consensus had seemed to be that 7.0 would be released around November. However, I spoke to a Microsoft partner last week who told me that the release date would be sometime in the second quarter of 1999. Does anyone know whether if this is true/untrue?
This bit of SQL script can be used to insert a pause of one hundredth of a second in a trigger, stored procedure or SQL script. This can be useful for when MSSQL’s built-in WAITFOR DELAY function is just too long (it’s minimum increment is a full second!)
declare @later datetime declare @now datetime set @later = current_timestamp+'00:00:00.01' set @now = current_timestamp while @now < @later begin set @now = current_timestamp end
I'm sure it could be further tweaked to become a function.. And heck, maybe one of the gurus will tell me that there's something i don't know about WAITFOR or whatnot.. Feedback appreciated!
-- Doug Castell GoldMine Guru www.castellcomputers.com
Hi, when call a sub-package from the parent package with the executeoutofprocess=true I have about 5-10 sec delay before sub-package starts running. ( this is a big delay for me as the sub_package is in a foreach loop)
the sub_package itself it's very simple and contains just one script task with a small script in in it. even if this delay is for validation I still can't understand why it is taking 5-10 ses. cheers
I've recently implemented a CLR stored procedure on a couple of databases but i've noticed the first time i use the stored proc in a given database i get a delay of around 10-15 seconds. Subsequent calls in new connection sessions execute immediately. I'm assuming the delay is due to the loading of the CLR and the assemblies needed. The assembly being loading by the CLR is not signed. Has anyone else seen this or know a way round this as it is impacting the user experience of our application.
Many thanks for any help you can provide in advance
I have researched this extensively and have seen a few similar posts on this site, and on the rest of the Internet, but have found nothing which solves my problem. This is absolutely crippling my productivity at work and I would greatly appreciate any advice anyone can suggest.
I have several packages which I need to run at quite regular intervals, and initially they worked absolutely perfectly. They ran quickly and efficiently. As time has passed though, seemingly at random, the pre execute phase of some of these has started to take unreasonable amounts of time. There have been times where I have left it on overnight and found the pre execute phase still at 0% the next morning. This is for queries which shouldn't take more than about 15 minutes to run.
However, seemingly at random, every so often one of the packages works absolutely perfectly. For example this afternoon one of these packages completed in 7 minutes, whereas this morning I had left it pre executing for 2.5 hours until I got tired of it and hit the stop button.
What affects the pre-execute phase and what sort of thing would cause this kind of behaviour? I work for a pretty large charity and I have had most of the IT team look into this at some point, and none of them can see any server activity which would explain it.
As an example of a package I am running which displays this behaviour, all that happens is an SQL statement runs, writes the results to a csv file and then emails me to say that it's finished. This is the SQL it uses:
select
REConstituentID as ID,
convert(varchar(10),DeceasedTickedDate,103) as dDate,
coalesce(Title1,'') as Title,
coalesce(FirstName,'') as FirstName,
Surname,
Gender,
AddressLine1,
AddressLine2,
AddressLine3,
AddressLine4,
City,
County,
PostCode,
coalesce(HomePhone,'') as Phone,
coalesce(convert(varchar(10),DeceasedDate,103),'') as DeceasedDate,
coalesce(
convert(varchar(10),(
select min(Date) from dbo.WH_ConstituentAttributes ca where ca.ConstituentID = WH_Constituent.ID and Type = 'Death Notification Date' and (left(Value,8) not in ('Experian', 'Ebiquita') or Value is null)
),103),
convert(varchar(10),(
select top 1 date from dbo.WH_Action where ConstituentID = WH_Constituent.ID and type = 'DM Remark' and ID in (select ActionID from dbo.WH_ActionAttribute where value = 'Deceased notification') order by date asc
),103),
'') as 'Date Notified of Death',
case
when exists (select top 1 null from dbo.WH_ConstituentAttributes ca where ca.ConstituentID = WH_Constituent.ID and Type = 'Death Notification Date' and Value = 'Golden Charter: Funeral Plan Confirmed')
then 1
when exists (select top 1 null from dbo.WH_Action where ConstituentID = WH_Constituent.ID and Category = 'Phone Call' and type = 'DM Remark' and ID in (select ActionID from dbo.WH_ActionAttribute where value = 'Deceased notification'))
then 3
else 5
end as 'Notification Method Code'
from
WH_Constituent
where
Deceased = 'Yes'
and AddressLine1 is not null
and PostCode is not null
and Country = 'United Kingdom'
and DeceasedTickedDate >= {d '2006-09-20'}
and not (
exists (
select top 1 null
from dbo.WH_ConstituentAttributes ca
where
ca.ConstituentID = WH_Constituent.ID
and Type = 'Death Notification Date'
and left(Value,8) in ('Experian', 'Ebiquita')
)
and not exists (
select top 1 null
from dbo.WH_ConstituentAttributes ca
where
ca.ConstituentID = WH_Constituent.ID
and Type = 'Death Notification Date'
and coalesce(left(Value,8),'') not in ('Experian', 'Ebiquita')
)
and not exists (
select top 1 null
from dbo.WH_Action
where
ConstituentID = WH_Constituent.ID
and type = 'DM Remark'
and ID in (
select ActionID
from dbo.WH_ActionAttribute
where value = 'Deceased notification'
)
)
)
There are no lookups, fuzzy groupings or anything else even slightly complicated.
Hi, when I try to run my package with dtexec.exe, it starts fine but in the process it package calles another subpackage and at the time there is big delay before start processing the subpackage. the subpackage has been setup so executionoutofprocess pramater has been set to true. any idea what migth be the problem. I have to metion even when I run this with in the visual studio still I have a big delay.
Can someone advise if there is a delay in data being written to the database following a tableadapter.update(datatable) command?
I save transactions which are subjected to the above and then a listview is updated to reflect them.
As I work through all is OK and the transactions appear in view.
I then run a backup through my app using a backup object to do this and this reports all OK
I then close the app and re-open and as as I am in debug the database is empty.
I perform a restore through my app using a restore object and selecting the backup file I created previoulsy which reports all OK
The retore procedure calls application.restart to allow the app to initialise to the restored data.
The problem is quite a bit of my data in missing from the restore as if the last block I did prior to backup never actaully made it to the database?
I also rememeber noting that at times when the update method is performed the actual timestamp on the physical databse is not updated....until I close the app and return to the designer?
So does this mean then prior to performing a backup I have to somehow force the app to ensure it has written all changes to the databse?
