Hello,
I am currently running sql 2005 express on a W2k3 server, 4G ram. I would also like to install a named instance of SQL 2005 Developer Edition on this machine. Is this ok to do? I am assuming that I can access all of the instances through the SQL Server Management Studio, is that correct?
Thanks
Leo
I have trying to access a 2nd SQL Server instances running on my WHS 2011 server from my Windows 8.1 Pro client.
The first SQL Server instance I installed is SQL Server 2014 Developer. This has been in place for many months and access from my client pc is not a problem. The Developer instance was installed using standard settings so uses TCP port 1433 and I have verified this by checking the firewall rules. This instance name is <SERVERNAME>
As I want to host a personal website on my server I installed SQL Server 2014 Express as a 2nd instance on my WHS 2011 box for website database purposes. After this installation both instances are accessible via Management Console on the WHS 2011 box.
The second instance is named as <SERVERNAME>SQLEXPRESS.
However, I am unable to access the SQLEXPRESS instance using Management Console on my client pc. I know it is a firewall issue as when I turn off the firewall on the WHS 2011 box I can connect successfully. The errors reported have resulted in my trying many things to resolve the problem but none have worked, e.g.
Use the default SQLEXPRESS instance to listen on all TCP ports (default installation option). I checked the necessary firewall rules were set up correctly and they were, i.e. UDP ANY and TCP ANY for the SQLEXPRESS sqlservr.exe file.
Change the default SQLEXPRESS instance to list on a specific TCP port, e.g. 19344
Create an ALIAS for the SQLEXPRESS installation
The instance is definitely set up for Remote Access and is not Hidden.
Are there any other firewall rules I need to apply?
I am trying to restore multiple .bak backup SQL database files onto a new server. However, I have found that it will not allow me to restore multiple databases at once. Is there a way to do this so that I do not have to manually upload one at a time? I tried adding all the .bak files at once to the backup device window but it only did the first one listed. It would be so much easier to restore them all at once so that I do not have to continue this manual process. I am restoring them via device.
View 13 Replies View RelatedI work for a mid size company with the usual collection of enterprise applications. We are trying to consolidate our SQL environment consisting of around 100 databases none of which are over 100GB with the average database size around 5GB. Recently a large server was purchased as the "Big Daddy" SQL box. Obviously not all the databases will be moved to this one box but within reason a number can.
My question: What is the best way to configure the box?
1 OS & 1 Default Instance - How do we isolate loops memory leaks etc...
1 OS & Multiple instances, ie:Dedicated instance for each application. - How do you allocate CPU & memory, allow for optimum performance without putting others at risk from a rouge process?
Virtualized OS for each application with a default instance - Too much overhead impacts performance?
Future plans include offsite replication, perhaps mirroring with failover.
I'd appreciate anyone else's thoughts ideas? How have you tackled this problem in your environment?
Thanks
Scott
Someone please help me before I loose all of my hair :mad: !!!
I have two machines, a Windows Server 2003 machine running an SQL Server Standard Edition database instance and client tools (Enterprize Manager etc.), and a workstation PC running Windows XP SP2 which has just the SQL Server client tools (not an actual DB instance).
I'm trying to use the Enterprize Manager client tool on my WinXP machine to remotely connect to the server instance on the server PC (via 'New SQL Server Registration' option). Everytime I do this I get the following error message:
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server Connection.
Now I've tried using different usernames and passwords. I have login details for my SQL Server set to use Windows Login rather then SQL Server login, so I tried remotely connecting with the username and password for the server PC, tried 'sa' with server password, even tried setting up a new user on the server and logging in from the client with that, no luck. I've opened up correct ports 1433 on both PCs, even disabled the windows firewalls algother. Both server and client have SP3a installed, WinServer2003 has SP1 and XP SP2.
So I'm at a loss to how to remotely connect to my SQL Server. FYI I know the server itself is working fine because I can connect to it using Enterprize Manager/Query Analyser on the local machine. Any ideas? Many thanks for your help!
I am trying to transfer all jobs from one instance to another by using data tools. However, once i tried to make smo connection i am getting this error;
A network-related or instance-specific error occurred while establishing a connection to SQL Server.
In order to solve this issue i have tried these solutions;
1. SQL Server should be up and running. (OK)
2. Enable TCP/IP in SQL Server Configuration (OK)
3. Open Port in Windows Firewall (FW ACCEPTS ALL LOCAL PORTS)
4. Enable Remote Connection (CHECK OUT THE sp_configure SETTINGS, i even right-click instance then see from properties )
5. Enable SQL Server Browser Service (sql server browser has been restarted)
6. Create exception of sqlbrowser.exe in Firewall (FW ACCEPTS ALL PROGRAMS)
7. I tried windows and sql authentication which has sysadmin role
8. INSTANCE name is also chekced millions of times
Hi,
The problem I have is this:
I have installed SQL Server 2005 Enterprise Edition on a W2K3 server that already has a SQL Server 2000 default instance installed. The installation appears to be a success. The problem is that having installed the client tools, and trying to connect, I get the following error message in a dialog:
"Cannot connect server SERVERNAMEINSTANCENAME
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified)(Microsoft SQL Server)"
The funny thing is that I can connect to the SQL Server 2000 default instance that is on that server.
So it's obviously not a connection problem per se at my end i.e. bad dns etc which might stop me resolving the server name.
Googling for this error text gives the following advice on various web sites:
1. Check that the remote server allows connections.
It does, as other colleagues in my team have successfully installed the tools and can connect with no problems.
2. Check that the SQL Browser service is running.
It is, as without this running, my colleagues would not be able to connect either.
3. Open SQL Server Configuration Manager. Select "SQL Server 2005 Network Configuration | Protocols for MSSQLSERVER" then enable the protocols you need.
The required protocol (TCP/IP) is enabled. Named pipes is also enabled, although I don't think that we're using that. And shared memory is also enabled by default.
4. Open the firewall and add exceptions for sqlbrowser.exe or UDP port
1434
There is no firewall running at either end of the connection and as mentioned I can connect to the SS2K default instance.
5. Make sure that the server machine is reachable.
I can connect to the SS2K default instance so it must be reachable. I can't see that I would need to change any configuration settings as my colleagues are using exactly the same default settings for their connections and they work fine.
The only thing that I have done differently is that I originally installed the client tools using someone else's privileged account as I didn't have admin rights on my box. The client tools didn't work, so I got admin rights to my box, uninstalled the client tools and then reinstalled them using my account. Still no luck. Not sure if something is still hanging around after the uninstallation that would cause me a problem.
To help, I've also filled out the following questionnaire:
[1] Client side:
> What is the connection string in you app or DSN?
There isn't one. I'm trying to connect from the SQL Server Management Studio | 'Connect to Server' dialog.
> If client fails to connect, what is the client error messages?
"Cannot connect server Y
An error has occurred while establishing a connection to the server. When connecting to SQL Server
2005, this failure may be caused by the fact that under the default settings SQL Server does not
allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified)(Microsoft SQL Server)"
Is the client remote or local to the SQL server machine? Remote
Can you ping your server? YES
Can you telnet to your SQL Server? YES
What is your client database provider? [SNAC | MDAC | ADO.NET1.0 | ADO.NET2.0| other (please specify] N/A
What is your client application? SQL Management Studio
Is your client computer in the same domain as the Server computer? Different domains
What protocol the client enabled? [Shared Memory | TCPIP | Named Pipes]. All 3 protocols are enabled
Do you have aliases configured that match the server name portion of your connection string? No
Do you select force encryption on server and/or client? No
[2] Server side:
What is the MS SQL version? SQL Server 2005
What is the SKU of MS SQL? Enterprise
What is the SQL Server Protocol enabled? [Shared Memory | TCPIP | Named Pipes ]. All 3
Does the server start successfully? YES. Other people can connect to the same instance.
If SQL Server is a named instance, is the SQL browser enabled? YES
What is the account that the SQL Server is running under? Domain Account
Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? N/A. Firewall not running.
Do you make firewall exception for SQL Browser UDP port 1434? N/A. Firewall not running.
What tool or Application are you using to connect to SQL Server SQL Server Management Studio
[3] Platform:
What is the OS version? Windows 2003 (Server side), Windows XP Pro Version 2002 SP 2
Do you have third party antivirus, anti-spareware software installed? Symantec AntiVirus
[4] Misc:
If you have certificate configuration issue: N/A
Has anybody else come across this problem, as I've run out of ideas.
Many thanks,
Would it be possible to get a multi-database functionality on the same instance option available in SSMS anytime soon?
View 6 Replies View RelatedI'm a relative newbie to SQL Server so pardon me if this is dumb. However....
Microsoft provides a visual database tool for designing a query. The only way I know of firing up this utility is from EM. To get to it you must drill down to the table level and say "Open Table...Query" to get to it. Is there an easier way to get to this? Or am I missing something? Doesn't it make sense that when you are in Query Analyzer that you should be able to easily go into the Visual Query Designer since that's all your doing in QA is designing queries??? A toolbar button would be nice. Maybe I haven't worked with it enough and there is a way.
Any help is appreciated.
-Mike
I got a big Access file (1 400 tables) to convert to SQL and I wouldlike to be provided with some automated tools, except upsizing wizardand DTS, to convert it on my own.I got a lot of forms and query to convert too.Can someday provide me with at least one tool name ?Thanks,Guy
View 12 Replies View Relatedis there a tool or vb.net code for monitoring who is accessing my sql 2005 express databases? username, computer name, etc.?
View 5 Replies View RelatedHi,We have several Access database that we would over time plan to convertto SQL. I am looking for a tool that works better than the upsizewizard in helping with the transition - I am looking for somethingreliable yet reasonably priced.Thanks in advance for all your helpKR
View 2 Replies View RelatedWhen I try to open Activity Monitor from SSMS I receive the message "Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (mscorlib)". - more details below.
I have a SQL Server 2012 Enterprise SP1 installed in an Active/Passive cluster configuration on Windows 2008 R2 Enterprise SP1. The problem happens using sa and a domain administrator.
------- more details -------
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (mscorlib)
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(Object o)
at System.Management.ManagementScope.Initialize()
[Code] .....
I would like to be able to view the complete SQL command that is sent from a Microsoft Access query which has linked SQL Server tables through ODBC, and the query also has some VBA functions manipulating and filtering data.I tried using the SQL Profiler to view the sql command send to SQL Server but all I see is the simple query format and some of the fields. I don't see the fields that have a VBA function such as trim(fieldname) etc in the column. And If I use a VBA fucntion in the Criteria then nothing shows up in SQL Profiled under the TextData column.How can I see the complete SQL command sent to SQL Server?
View 2 Replies View RelatedI am not able to launch the Kerberos Configuration Manager due to the above error.
View 4 Replies View RelatedI have a DTS package I created that is dadabase driven to process source data from multiple sources.
Can I run several instances of this package in parallel for each source or would I need to create a copy for each source ?
JavaWaba
Hi There
If i have a 2 node cluster running a single instance (default) and i want to add another instance to the cluster , can i install the second instance as a defualt instance ?
Since the cluster instances are referred to their virtual names/ip's and if i choose to intall the binaries of the second instance to a different location is this possible?
Since in theory you never have 2 default instances running on a single server but each within a virtual server?
Normally i install a named instance but do i have to ?
Thanx
Can multiple instances of SQL Server be installed on the same machine under the same license? For example, a named instanced called WAREHOUSE and another called CHGMGMTSYS.
View 1 Replies View RelatedHi,
I have a report that has a SSN input parameter based on which the report is generated.
So given a single row (one SSN), one report instance is generated.
Now, I have multiple rows 20,000 or so, rows from which I need 20,000 reports generated, (so they can be printed and mailed to the clients)
How do I design the report that can handle singe or multiple reports instances?
I did tried to use the List Report Item however I could not find a way to fix the page numbers so they restart for each instance of the reports instead of being 1 of 20,000 2 of 20,2000 and so on
Any help is appreciated.
Thank you,
Vance
I am trying to set up multiple instances of MSDE 2000 for use by Trend Micro virus scan products. I have the instance set up but I am trying to access svrnetcn.exe to set up named pipes and TCP/IP and I keep getting a 126 error "The Specified Module could not be found". Any ideas how to set this up.
thanks,
eric
Hi,
I have sql2005 sp1 active-active installation.
I've fixed the named instance port.
I can't reach it from clients with i'ts name (servernameinstancename) .
I manage to rech it only by using servername, ip-port.
The sql browser has the following error:
"The configuration of the AdminConnectionTCP protocol in the SQL instance GILBOACL is not valid."
your help is appreciated!
Shahar
I have several fileds in a sql table which holds some text and a hell of a lot of white spaces (char(32)) which is causing some problems when I show the data on the front-end
What I want to do is use sql replace function to replace the char(32). However, because there is text I can't do a simple replace as the text will become just one word !
Can anyone tell me how to loop through the field to find the char(32), where the char(32) is greater than say 5 sapces and then replace this with just one single space
E.g. a field in a table contains the following :
"my text field and loads of spaces then some more text with loads of spaces "
I want to replace the char(32) with just one space so it looks like this :
"my text filed and loads of spaces then some more text with loads of spaces"
Thanks for your help
I have a named instance of MSDE running on my windows 2k3 web server. I can connect just fine using ODBC to that DB whailst I am on the server.
I would like to be able to create an ODBC connection from my local machine (running Windows XP pro) to the MSDE database on my server but I can't get it going.
I have heard that I need to edit the registry but a Google search warns that this could be a vulnerability...
How do I do it without compromising the security of my web server?
TIA
Hi, I've installed SQL Server 2005 Express Edition, SQL Server Management Studio Express using Windows Authentification and IIS 5. The problem occurs when I try to access the default address of my local computer to test my installation. I've tried with both http://localhost and http://127.0.0.1. The system requires a password despite I don't use password for my Windows Authentification. Does IIS always demands a password regardless my settings for Windows Authentification or?
Best regards
From what I understand, one SQL Server 2012 instance can host multiple availability groups. That should allow, for example, the following situation - one SQL Server 2012 instance containing three primary replica databases - each one part of a different availability group - and each availability group's secondary replica located on a separate SQL Server 2012 instance.
Can you have three SQL Server 2012 instances, each with one primary replica database and have all three secondary replica databases on one SQL Server 2012 instance? So instead of, as above, going from 1 primary server to 3 secondary servers, this time we're going from 3 primary servers to 1 secondary server? The one secondary server would then contain all three secondary replicas for each of the 3 separate primary replicas.
This would mean that the single server (where all three secondary replicas reside) was part of three separate Windows Server Failover Clustering clusters.
Is this scenario possible?
Scenerio : To keep a very large system running optimally in a VM cluster, Take PR01 and make PR02, PR03, PR04. Distribute the 45 databases and 9T+ of disk across multiple VM guest.
Each PR## is a SQL Server 2012 Enterprise guest on a VM 5.1 cluster.
So instead of PR01 needed 16 core and 128g of memory, each one will have 4 core and 32g of memory. Making VM HA more manageable. (yes, DRS rules will apply). Also provides more HBA paths and distributes i/o over more physical disk on the SAN.
Instead of a connection string having to know PR01.dbo.UserDB01, PR02.dbo.UserDB03, ect the connection would be PRDB.dbo.UserDB01. That way if needed 1) UserDB can be moved to any of the PR## 2) new PR05, PR06 can be added as needed. The end user and processes are not allowed to touch system databases, no PR## will have a user DB called the same name.
There are seperate VM guests on other VM clusters and Citrix servers that need access to PRDB. As things expand and move around, none of the connection strings need to be changed.
I am looking into RadWare and modifing level 7 information, but that is iffy and $$$$$$.
Currently we have an AlwaysOn AG set up with a listener running on port 1433 (underlying instances are on a non default port). Great, no problems there.If we set up a second AG on the same instance with its own specific listener, can this new listener also be configured to use port 1433?
View 2 Replies View RelatedCan someone explain what does this mean? This is from http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
<quote>However, all these techniques for coordinating client redirection with a database mirroring have an important limitation. Database mirroring occurs only at the database level, not the server level. Be careful if your application relies on querying several databases on a server, or uses multi-part object names to query across several databases. When several databases reside on one server, and they are mirrored to a standby server, it is possible that one of several databases might fail over to the standby but the others remain on the original server. In that case, you might need one connection per database that you are querying, so that you do not attempt cross-database queries on a standby server where only one database is a principal and the remaining are mirrors.</quote>
Also I read somewhere that if one mirrored DB fails, all other mirrored DB should also be transferred to the mirror.
For example: Let's say Srv1 (principle) and Srv2 (mirror) and Srv3(witness) are SQL servers with only default instances. The SQL instance has 4 DBs (DB1, DB2, DB3, DB4) and all of them are mirrored to Srv2.
An ASP.NET application has four seperate connection strings like:
objConn1 --> Data Source=Srv1;Failover Partner=Srv2;Initial Catalog=DB1;Integrated Security=True;
objConn1.connect();
objConn2 --> Data Source=Srv1;Failover Partner=Srv2;Initial Catalog=DB2;Integrated Security=True;
objConn2.connect();
objConn3 --> Data Source=Srv1;Failover Partner=Srv2;Initial Catalog=DB3;Integrated Security=True;
objConn3.connect();
objConn4 --> Data Source=Srv1;Failover Partner=Srv2;Initial Catalog=DB4;Integrated Security=True;
objConn4.connect();
If DB2 failsover to Srv2 (mirror), why should all other DBs be failedover?
Thanks
I installed Named instance in SQL 2005 Server (TESTAVTESTNETAV), now i am not able to acces this instance in SQL Query Ana.throug IP Address.
i am able to access this instance through name
How can i resolve this issue please help me out ??
situation
we intalled sqlexpress on a server 2003 ...work well... named server <deval-sqlexpr>
instance name of sql is <sqlexpress>
we changed the server name to <apps-ii>
after that we cannot access the sql instance <deval-sqlexprsqlexpress>
we cannot remove (with add and remove program) that unsuable instance.
we have an instance name <apss-iisqlexpress1> and its works well
we cannot change the server name back to <deval-sqlexpr> ...the server is now the DC....
QUESTION:
is it possible to remove and clean out that instance from the server.
Thank you
I have to mirror 3 DBs on my sql server instance . I have restored there full backups and log backups on mirror.I have also created endpoints on principal and mirror using default ports 5022 and 5023.
know if i can use the same endpoint for the databases on principal instance ?
We are currently looking at consolidating 10 servers into one cluster server.
Some servers may be busier than others. Is there any reason to split them up and give the busy databases specific CPUs or is it always better to have them on one instance?
Is there a way for multiple SQL 2005 instances in a cluster to share drives for user databases.
Once we install our instances in the cluster using seperate disk resources. Can we then have multiple instances share the same physical disk for user databases only. This is for a test environment.
Thanks