I am using robocopy to copy my sql server backups to a fileshare. This is working great except for named instances.I use Ola Hallegren's backups which create a folder called xxxx$xxxx. Robocopy can't seem to handle the $.
View 9 RepliesI had a server with SQL Server 7.0
I installed a named instance of SQL Server 2000 and then i passed all my DB
of the 7.0 instance to the 2000 instance.
Then i removed the 7.0 instance, that was the default instance.
So at the moment there is only the 2000 version, but it isn't the default
instance
Can the 2000 instance become the default instance? (So that clients can
connect to it simply through computer name, and not creating an alias)
thanks
Fede
Is it possible to rename a named instance?
We have a vendor product that can only handle up to 18 characters for the server name and the instance name (we did not know this when we created the named instance) and of course our combined name is 19 characters, so it trys to pass the 18 charater name and then fails with the following error:
[LOG] Unable to read local eventlog (reason: The data area passed to a system call is too small)
So, can i rename the instance, or do i have to create a new named instance?
Thanks
Informatica jobs were erroring out with a sql server driver error
while connecting to a named instance in SQL Server.
The issue was because in sql server named instance, the port was allocated dynamically and Informatica 7.1.4 (provides 4.21 version of Datadirect odbc drivers for SQL server) which dont have support for named instances with dynamic port allocation. This feature is only present from version 5 of the Datadirect odbc drivers for SQL server
To fix this issue, I need to allocate the port for the named instance instead of getting it dynamically.
My questions
1) Whats the impact of doing a port allocation for the named instance on the existing environment.
2) Is there any issues which we can foresee in the applications
3) Things which i need to be aware of
Thanks in advance
sreenath
Hi everyone,
Is there a way to route a specific IP address to a named instance on a SQL 2k system???
thanks
tony
We are using a system that has uses four instances. On each client to the server we used the ODBC settings to connect to the different instances. We have since reinstalled the server, and now we are unable to find or 'see' any of the databases, except for locally.
E.g. The first database we install is named Foo, with database Bar. It is set up on port 1433.
The second named instance is Foo2, with database Bar2. It is set up on a dynamic port.
We now try to connect to the database with ODBC. We connect to SERVERFOO, and then select the BAR db. It works fine.
We then try to connect to the database SERVERFoo2, it lets us in, and then all we can select is the BAR db. I don't know what is causing this. We never before had to edit the settings in the client config for the odbc settings (e.g. to set manually to TCP port 1433) and I have checked the sql config manager to ensure that tcpip settings are allowed and will "ListenAll".
What more do we need to do? I think the browser should be installed, but my collegue disagrees.
Any ideas as to why we can only see the database referenced to 1433 port, and why no other port works, when we never had to do that before?
Thanks a bunch in advance,
Regards
Carl
Using VS05 SP1 Pro SQL Express€¦
In converting a program from VS03/MSDE, long ago I installed SQL 2005 Express with Advanced Services and everything was fine. Windows Update has recently been failing to install SQL 2005 Express with Advanced Services SP2. After no answers from the forums, and logs that were of no help, I decided to uninstall SQL 2005 Express with Advanced Services and reinstall SQL 2005 Express (basic version). As a test run, I did it on a desk top, no problems what so ever.
However, on my laptop, it has been a nightmare.
Question 1) in control panel, add/remove programs, all of SQL 2005 Express with Advanced Services uninstalled fine except one item - I am unable to uninstall MS SQL 2005 Backward Compatibility, getting the error message
A network error occurred while attempting to read from the file
c:windowsinstallersqlserver2005_bc.msi
That file is not on the hard disk.
After installing SQL 2005 Express (the basic version), this problem persists and I cannot uninstall MS SQL 2005 Backward Compatibility for the same reason.
How can I get rid of MS SQL 2005 Backward Compatibility?
Question 2) Windows update still is trying to download and install SQL 2005 Express with Advanced Services SP2, even though I have uninstalled it. How to manually get rid of whatever settings are triggering this?
Thanks!
Bob
I have a server computer named 'X' and I would like to install 2 instances of SQL Server 2005
1. SQLTEST
2. SQLDEV
During installation. if I select Default instance then it will name my sql server instance X . I don't want that. I want to have essentially 2 named instances to make it clear to the users that they are using a test and dev sql server. Whats the best way to do this? I am open to suggestions if there is a different way to do this.
Is it required to have a default instance? Can I have 2 named instances XSQLDEV and XSQLTEST?
Also, on the SQLTEST I will be installing reporting services. By not having a default instance will it cause issues for the SSRS installation.
Please help!
Thanks.
I'm struggling a little with this. I hope someone can help.
Ho do i install SQL Server 2005 Express so i can connect to it with "localhost", not "<MACHINE_NAME>/SQLEXPRESS". I cant seem to find any obvious setting in setup.
Could anyone shed any light on this?
Regards,
Anthony
We've read about a single installation of SSIS applies to all SQL instances on a server. We've also read how by default SQL Server stores SSIS packages in the MSDB database on the default instance and if you want to have packages stored on a named instance you must modify msdtssrvr.ini.xml. Here are our questions.
1 - If msdtssrvr is modified to reference a named instance, such as <ServerName>SQLTESTINST1<ServerName>, when I connect to Integration Services through Management Studio do I still specify the default instance name in the login window? That's the only way we have been able to connect and despite specifying the default instance name, the packages displayed are those of the named instance.
2 - If I am correct that you must always specify the default instance name when connecting to Integration Services, then in order to eliminate confusion regarding what SQL instance's SSIS packages I am viewing under the folder structure I can create SQL Server folders by modifying msdtssrvr.ini.xml. For example:
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB-INST1</Name>
<ServerName>SQLTESTINST1</ServerName>
</Folder>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB-INST2</Name>
<ServerName>SQLTESTINST2</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>
I then restart the SSIS service and can now see the following folder structure.
Running Packages
Stored Packages
+ File System
+ MSDB
+ MSDB-INST1
+ MSDB-INST2
If I expand MSDB-INST1 I will only see packages saved on the instance named INST1. The same is true for INST2. If I expand MSDB I will only see packages saved on the default instance.
Does this sound like I am doing things correctly?
Thanks, Dave
what is default website for named instances in Reporting services?
View 1 Replies View RelatedHello,
I am after a bit of advice about running the Upgrade Advisor tool on a server that has more than 1 SQL Server instance. My particular focus is DTS on each server.
I tried running the tool, setting up an XML file per SQL Server (including named instances). An example of the XML is I used is included below):
<Configuration> <Server>SQL01</Server> <Instance>INSTANCE01</Instance> <Components> <DataTransformationServices /> </Components></Configuration>
When the tool runs, folders get created for the relevant named instances, but the DTS runs against the default instance only. In other words, the default instance DTS.xml file gets overwritten several times.
Does anyone know if the Upgrade Advisor tool can handle multiple SQL instances on the same server, particularly in realtion to DTS?
I had thought this might have been a limitation as the options in the Report Viewer are "Server" then "Instance or Component", when I am really after "Server", "Instance" AND "Component".
Any suggestions welcome.
Thanks in advance, Paul
Hello,
this is my problem:
I installed SQL 2005 Standard Edition first, then MSDE plus its SP4. Both instances are named (MC05WXPPROSQLMC for 2005 and MC05WXPPROUCTO for MSDE). I inadvertently had not allowed the TCP/IP protocol for MSDE during the installation, so I did it manually later, through SVRNETCN.exe which also ships with SQL 2005.
Now:
I CAN connect to the MSDE instance with any SQL2005 tools through shared memory. But I CANNOT connect to it through TCP/IP. I guess that this is because wrong port binding for the SQL2005 instance, MSDE instance and SQL Server Browser, but I'm not sure.
My system is Windows XP Professional.
Please help.
Any hints welcomed,
Pavel
Hello SQL colleagues,
I need to add 5 new named instances on a SQL Server 2005 Enterpise Edition.
Is there a fast way to do it?
Best Regards,
John
I have the following problem;
I fully understand that to connect to a named instance of SQL Server you need to use the ServerNameSQLInstanceName. The problem I have is that I have a SQL Server in a different zone. I can connect to the Default instance by IP Address or the ServerName.zone.domain.org. (e.g. MySQLServer.zone1.mydomain.org).
However, the same thing does not work for the Named Instance. It seems to be named instance or nothing.
How can I connect to this named instance across network zones?:S
We are having all kinds of issues with named instances for SQL 2000.
I am trying to connect to a SQL Server 2000 named instance on a different subnet and get an error. I cannot connect with ODBC or our web app.
I am using the port number for the alias that I created in the SQL Client Utility. We can connect to default instances without a problem, but not the named instances.
The SQL Server is 2000 build 2040 (Service pack 4 with a hot fix.) The server is listening on port 1223. In the ODBC connection I click on the Network Config and create an alias with the named instance such as SQLVSNSQLNI and specify port 1223. I have also tried adding the port to the connection string in the ASP include file (SQLVSNSQLNI,1223). If I do the same thing with a default instance on the network, both the app and ODBC work fine. It is only when I use a named instance.
Very frustrated. Thanks for any help you can provide
Hello, I'm trying to list the default and named instances on our domain. When I run SqlBrowseConnect all I get back is a list of our servers, for example:
COMP01
COMP50
I'd like to get all the named instances as well, a list with:
COMP01
COMP01DD
COMP50
When I run "osql -L" I get the full list, but not with SqlBrowseConnect. I have tried to set SQL_COPT_SS_BROWSE_SERVER to (SQLPOINTER)NULL which, according to the docs, "[...] returns information for all servers in the domain." I have also set the SQL_COPT_SS_BROWSE_CONNECT to SQL_MORE_INFO_YES without any luck.
Any ideas on any property I might be missing?
Cheers,
Chris
Hello, I tried this and it doesnt work, I just started up the service with -T flag on default instance but I havent been able to start it with -T flag 1400 on named instances.
How can I Know that the directory MSSQL.4 is the MIRROR instance??
C:Archivos de programaMicrosoft SQL ServerMSSQL.4MSSQLBinn>sqlservr.exe -T
1400 MIRROR
I tried LUCHOMIRROR, MSSQLSERVER$MIRROR
and It doesnt work
Thanks
Another question I have, in the documentation it says that mirroring is only for evaluation purposes and not for production environments, what does it suppose to mean? MSFT didnt test the mirroring feature enough? If its not suppoerted why its included..?
Microsoft support policies do not apply to the database mirroring feature in SQL Server 2005. Database mirroring is currently disabled by default, but may be enabled for evaluation purposes only by using trace flag 1400 as a startup parameter. (For more information about trace flags, see Trace Flags (Transact-SQL).) Database mirroring should not be used in production environments, and Microsoft support services will not support databases or applications that use database mirroring. Database mirroring documentation is included in SQL Server 2005 for evaluation purposes only, and the Documentation Policy for SQL Server 2005 Support and Upgrade does not apply to the database mirroring documentation.
Ok, here's the deal. This has been killing me for months now.
Here's the setup - SQL 2000 database on a Shared Infrastructure (cluster). I have a database on a named instance. My port number is xxxxx. I have dbo (through Windows Auth) permissions on my database. When I connect at the office, I get through fine, no question. When I connect at home, I get error 26 in my asp.net 2 app (using System.Data.SqlClient), cannot find SQL Server database. I can connect through the SQL 2005 Management Studio using the same servernameinstance name I use at the office. I can connect through the SQL 2000 Query Analyzer using servernameinstancename;(semicolon)portnumber. I've tried all sorts of combinations in my web.config -
servernameinstancename,(comma) portnumber
servernameinstancename;(semicolon - causes web.config to blowup) portnumber
ipaddressinstancename, (comma) portnumber
and i still get nothing. usually it's 'error 26' - but if i use this:
Data Source=ipaddressinstancename,portnumber;Network Library=DBMSSOCN;Initial Catalog=xxx;Trusted_Connection=Yes;
I get 'Login failed for user domainuser'
I've tried making an alias. I've tried using ODBC. I've even tried a freakin' DSN. No dice.
Nothing seems to work - I'm at the end of my rope. Can someone please help?
We're moving several Version 7 databases to a SQL Server
2000 box.
How can I grant SQL Server 2000 security for three
"Named Instances"?
Thanks
Benny
I have a 2012 AlwaysOn DB Mirroring environment set up with two nodes. Both have 5 installs of SQL named instances.
The issue we are having is when we patch one server and fail everything over, some of the applications will error. Some of the applications had to have their web.config files updated with hostinstance name because it seems to not work with DNS.
Hi All..
We are planning to build a cluster environment on MS SQL 2005. We want to install multiple named instances in a single database server and use cluster for that database server.
Is this option possible ? Any relevant documentation would be helpful
Thanks in advance
Regards
Madhav
Hi folks
I have deployed a package from file resources to sql server named instance. I did not receive any errors. msdb..dtspackages90 table have rows for my package.
But when I try to connect to integration services using ssms like 'serverinstance', I am getting an error saying that 'serverinstance' names are not supported by integrated services and I should use just a servername.
What am I doing wrong? Is that really integration services are not supported on named instances or i miss something?
Thank you, Gene.
We have 10+ MSDE 2000 installations on the same network. Each install has a named instance and the
machines connect to eachother via VB application. We have a couple SQL 2000 Standard boxes and a SQL 2005
box all running on the same network with no issues. The problem we have recently run into is with a SQL
Express box. When the box is on the network OSQL stops finding the MSDE 2000 named instances on the
network and only the SQL Express named instance appears in the list. The second the SQL Express box is removed
from the network the named instances are visible. I monitored the UDP traffic and suspect there is an issue with the response from SQL Express to OSQL. Can't find any issues for this problem only report I found is if MSDE and Express are on the same machine.
SQL Express Response
.Q.ServerName;DDK7P4D1;InstanceName;SQLEXPRESS;IsClustered;No;Version;9.00.1399.06;;
MSDE Response
...ServerName;RED;InstanceName;SECOND;IsClustered;No;Version;8.00.194;tcp;4307;;Ser
verName;RED;InstanceName;THIRD;IsClustered;No;Version;8.00.194;tcp;0;;Server
Name;RED;InstanceName;MSSQLSERVER;IsClustered;No;Version;8.00.194;tcp;4303;;
Any ideas?
Thanks, Kevin.
We have 10+ MSDE 2000 installations on the same network. Each install has a named instance and the
machines connect to eachother via VB application. We have a couple SQL 2000 Standard boxes and a SQL 2005
box all running on the same network with no issues. The problem we have recently run into is with a SQL
Express box. When the box is on the network OSQL stops finding the MSDE 2000 named instances on the
network and only the SQL Express named instance appears in the list. The second the SQL Express box is removed
from the network the named instances are visible. I monitored the UDP traffic and suspect there is an issue with the response from SQL Express to OSQL. Can't find any issues for this problem only report I found is if MSDE and Express are on the same machine.
SQL Express Response
.Q.ServerName;DDK7P4D1;InstanceName;SQLEXPRESS;IsClustered;No;Version;9.00.1399.06;;
MSDE Response
...ServerName;RED;InstanceName;SECOND;IsClustered;No;Version;8.00.194;tcp;4307;;Ser
verName;RED;InstanceName;THIRD;IsClustered;No;Version;8.00.194;tcp;0;;Server
Name;RED;InstanceName;MSSQLSERVER;IsClustered;No;Version;8.00.194;tcp;4303;;
Any ideas?
Thanks, Kevin.
My issue is that: here have two datamirror servers, and when the first fail, mirror needs to assume with the same name.
I'll try explain: SD01 - Primary, SD02 - mirror. If SD01 fails, SD02 assumes called SD01. By the way, the instances are in same domain, but not in same computer.
I am in the process of creating a VM server which serves as a backup for a production SQL server. the SQL server has SQL directory installed on C: and F: partitions. Sql installation files reside on C: while MSSQL directory (include backup, logs, data, etc.. ) reside on F:. The VM was created by cloning of the original (production) server but only C: partition. I need to use robocopy to copy the MSSQL directory from original sql (F:) server to the VM (F:). My questions are:
1. will doing it this way mess up database integrity on the VM?
2. What should I check to ensure installed filed registered to the new VM server instead of the original sql server?
3. what switches of robocopy should be used to produce exact copy of the MSSQL folder from original server to the VM
4. the current database is about 1GB, how long the robocopy may take?
If you have experienced this before, please share your thoughts, suggestions, ideas. Very much appreciate your feedback.
Thank you
Is it possible for a clustered instance of SQL2012 to have 2 network names ?
Reason: I need to segregate admin access to a clustered instance so that the admins and SSMS connect via a different IP address than the application. I know I can block SSMS access via application-level firewalls, but ideally the application would connect to CLUSTER1INSTANCE01 on , say, 10.192.5.5, and the admins would connect to CLUSTER1ADMININSTANCE01 on 172.168.2.2, and they'd be the same instance, just using different names and IPs
I am using sql2012 with partitiondb custom installation over 6 dbs on 4 servers, ~200GB per db. I am looking for the perfect graphic tool (similar to Perfmon+Activity Monitor) where I can monitor a wide-scope SQL environment. I am interested in data / log / table / index growth, buffer cache hit ratio, average wait time, physical/logical reads/writes and such. I am interested in real-time / time-range metrics. I know I can issue immediate queries against dm_os_performance_counters / dm_os_buffer_descriptors / dm_exec_query_stats and get some of the relevant data, but is this the only way?Also, for your opinnion, what are the most crucial metrics to monitor when dealing with multiple dbs?
View 1 Replies View RelatedNative backups to NAS do not complete.We have been experiencing an issue whereby our native backups are hanging with status': SUSPENDED/ RUNNABLE...I ran select * from sys.sysprocesses. All of the backup SPIDs processes show BACKUPTHREAD/PREEMPTIVE_OS_FILEOPS
This first occurred last Wednesday evening. When I discovered this on Thursday, I attempted to kill the backup jobs. This also hung with 0% completed/0% time remaining. Backups hung on more than one instance.That evening, I attempted to restart the instance which also failed with something along the lines of: could not start MASTER file in use.
I then restarted the server--which I really did not want to do--and this cleared it. I was also able to manually kick off maintenance plans (DBCC CHECKDB and full backup) without issue.I was off Friday and the weekend. I came in this morning and found the maintenance plans (diff/tlog backups) did not complete in some of the instances--in one case, the instance affected now was not affected before. They appeared to have hung on their next scheduled kickoff which was later that night after I went home
Remembering the "file in use" error, I have run process monitor to see if anything unusual had a lock on any files. I saw only SQL Server and Double-Take processes accessing log files.Being a relatively new DBA, I am user where to go next in trying to track down the cause of this issue. This is fairly urgent as one of the instances that has had this problem both times is our production SharePoint environment.
ENVIRONMENT:
SQL version:
Microsoft SQL Server 2012 (SP1) - 11.0.3368.0 (X64)
May 22 2013 17:10:44
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
My customer asks to install three instances of SSAS on a single physical windows server. Is it possible?
View 1 Replies View RelatedWe have 5 instances on our clients old machine with SQL 2005, now they want us to move on cluster environment with SQL 2012.
Once I have installed and configured SQL cluster with single instance, but how to install SQL Cluster environment for Multiple instances.
Should I install all the instances first and than have to configure cluster ?
OR
Is there any way that first I will install cluster and than we can add the instances ?
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.