I 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
I 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
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
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
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
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 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
I am currently having publisher(database A), subscriber (database B) and distributor on the same instance for a test environment that is using a uni directional transactional replication. Now I need to setup another unidirectional transactional replication in the same test instance but for a different database. Publisher database is D and subcsirber database is E.
View 4 Replies View Related
We have an archive table which keeps each instance of a sales order that was archived under a "Verion No" field. Each time the sales order is archived it is entered into the archive tables (Sales Header Archive, Sales Line Archive). What I am trying to do is write a query to return all sales orders but only the most recent archived version.
For example this table layout is similar to what I am working with. Version No, Order No and Customer No. are the keys between the Header and Line tables, Customer Name column in the output is from only the Sales Header Archive table
SALES LINE ARCHIVE TABLE
Version No - Order No. - Customer No -----> (other columns)
1 s-5 1000
2 s-5 1000
1 s-6 2000
1 s-7 3000
2 s-7 3000
3 s-7 3000
1 s-8 4000
1 s-9 2000
2 s-9 2000
Here is what I need to output to show:
RESULTS OF JOINED TABLES
Version No - Order No - Customer No - Customer Name ---> (other columns)
2 s-5 1000 Something, Inc.
1 s-6 2000 Acme
3 s-7 3000 Company, LLC
1 s-8 4000 Blah & Associates
2 s-9 2000 Acme
It should return the last Version No of each Sales order.
Does that make sense? It is something probably easy... But, I've spent two days using multiples and multiples of different ways, that just aren't working: I'm about to dropkick my server cabinet...
We have a SQL enterprise server 2014 with two installed instance.
First instance for SharePoint 2013 DBs and second instance Dynamics AX 2012 R3 DBs.
The server has 32 GB of RAM, currently the process usage for first instance is 26 GB and second instance is 700 MB.
Which mean that SharePoint DBs in the first instance is consuming most of the RAM.
My question is there any way to assign a fixed RAM usage for a specific instance without affecting the other instance?
for example 16 GB only first instance and 16 GB to second instance.
I am working on a C# stored procedure for SQL 2005, and i've uncovered a couple questions.
First a description of the procedure:
I have a series of equations taking place to calculate a score based on activities in which the user participated in, that will give them an over all grade or rating. The calculations currently take place in the database, and I am moving this from T-SQL to C# CRL.
1. In order to connect the stored procedure to the database I use a SqlConnection and a SqlCommand to execute either dynamic sql or a stored procedure to return data to a data reader. Is there an easier way to connect to the database? In SSMS if i open up the query it knows what database i am connected to. Do I have to make a sql connection in C# stored Procedures?
2. I have multiple functions within the main C# Stored Procedure that I'm working on. This ends up requiring Multiple Active Recordsets. I must set this withing the connection string. Seeing as I'm using a named instance of SQL 2005, I now must put the userid, password, and server name into the code. Is there a more secure way to connect to SQL Server in a C# Stored procedure that allows MARS?
3. I encryped the connection string, and put it into the assembly, I wrote a decryption class, and in the procedure itself everytime I need to refrence the connection string, I call it, decrypt it, and pass it along. But my code to decrypt the connection string is in the compiled DLL, if the server was ever compromised the encrypted connection string and the key to decrypt it are sitting in the DLL. Is there a config file that I can use for C# Stored Procedures?
4. If I have to keep the connection string in the file, then I need to change that per environment. Example I have 3 test environments before production. So I would need to change the connection string for each file. That may be fine for one procedure, but what if I have 20, that will quickly get of hand?
5. Along the security lines, can the assembly for a C# Stored Procedure be called from outside the assembly? From a command prompt, or by a maliceous program? Or could it be called directly by a .NET application instead of going through a T-SQL Stored Procedure that is using
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [PROJECTNAME].[CLASSNAME].[METHODNAME]
Thanks
i want to run a transaction across mulitpule instances of sqlserver with out linked server.distributed trnasaction can do it with link server , can it do it with out linked server.
View 4 Replies View RelatedI've two instances(Default, Named[dynamicsFINANCE]) running on SQL server 2014. However, when I try to connect to named instance say (dynamicsFINANCE) using SQL authentication from local SSMS, I get below error message:
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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
I assigned a static port number to the named instance [dynamicsFINANCE] 1450. I also setup the firewall rule to allow access to Port 1450.
Hi fellows,
I have to migrate all objects alogwith all constraints,SP,Triggers, indexes etc from Development instance to Production instance of a DB, all those things are created through wizard ie. Sql server 2000 Enterprise Manager. if i use DTS it only mirates data along with tables and views but constraints,SP,Triggers, indexes etc not yet copied.
can any body help me how can I solve this problem by copying all objects alogwith all constraints,SP,Triggers etc from Development instance to Production instance.
This is Sql server 2000 Cluster environment.
thanks in advance for any help
rahman
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 ?
Hi, I have a task in hand to migrate (upgrade) from SQL2K named instance to SQL2K5 default instance. There are many intranet applications touching current SQL2K. I would like to perform this upgrade such that I don't have to touch any application code - meaning I don't have to change the connectionstring to point to new Default instance. How can I achieve this?
So, in otherwords, here is what I want to achieve:
Current Server: SQL2K: SERVER_AINSTANCE_A (named instance)
Upgraded Server: SQL2K5: SERVERB (default instance)
If I have both default, I could achive this by setting up DNS alias after migration done so that any call for SERVER_A would point to SERVER_B. But in my case, I don't have SERVER_A, I have named instance. Is there any solution?
Regards,
Vipul
Hi,
I want to move one database from the source SQL Server 2000 instance to a new SQL 2000 instance in another machine. I have five user databases in this source SQL instance. How should be my approach to move this single database out of this ? My understanding is restoring this database in the new instance, copying all logins to the new instance and then copying the jobs, DTS packages, alerts, operators only specific to this database will do it. Please let me know if this is exactly what I should do ..
Thanks in advance..
Regards,
Himansu
Hi,
I want to move one database from the source SQL Server 2000 instance to a new SQL 2000 instance in another machine. I have five user databases in this source SQL instance. How should be my approach to move this single database out of this ? My understanding is restoring this database in the new instance, copying all logins to the new instance and then copying the jobs, DTS packages, alerts, operators only specific to this database will do it. Please let me know if this is exactly what I should do ..
Thanks in advance..
Regards,
Himansu
I am trying to set up a Named Instance of SQL 2000 on the same machine that has a default instance of SQL 7.0. The setup always completes and I am able to register the Named Instance of the SQL Server with which it was installed on. However, when I try to connect the users to the database, with both windows and SQL authentication, I receive a SQL server not found error. I have tried an alias setup as well as physically specifying the port number in settup up an ODBC connection.
Has anyone ran into similar problems?
Also, has anyone been able to successfully complete the process as mentioned above?
I have a server with sql server 2005 installed as the default instance -- I have a piece of software that needs SQL2000 to be the default instance. Is there a way other than install new sql2005 named instance and move databases to rename my SQL2005 instance from <machinename> to <machinename>sql05 for example?
Bryan
Hi all,
i have installed SQL client instance on my Desktop "Machine-A", and i used to connect to SQLServer2000 running on another "Machine-B" by using valid username and password.
i created a DTS package and i used to run it. im very confused how the communication happens between the two machines and which process run where and which initiates which and which machine allocates waht resources. im really interested to know all these things. if any one know plz help me out.
Thanks in advance.
I have SQL Server 2000 Standard Edition (sp3a) running on a windows 2003 (sp4) Server.
It is a Production Server with 3 NAMED Instances and NO Default Instance.
Does anyone know if I can rerun the SQL Server Install and add a DEFAULT Instance to this box without disrupting the other Named Instances???
I installed SQL Server 2005 recently on a cluster. I didn't go for the default instance and instead I named the instance option. Now I would like to migrate everything from the named instance to the default instance, which I haven't yet installed.
Is this an easy process? What about the logins and the maintenance plans and jobs? Is there anything else I need to be aware of?
Hi
I've never had to do this, but when I downloaded the Web Workflow Approvals Starter Kit, it requested that I install the database into a User Instance of .SQLEXPRESS.
Now the problem is, I've installed it onto a default instance, so I was wondering whether you can create a named instance on top of a default instance... and if so, how would you do that?
Cheers
Chris
Got windows Vista Business edition, installed Visual Web Developer Express, SQL Server 2005 Express SP2, SQL Server Management Studio SP1 in that order. I was developing in VWD express without problems using WinXP but now on Vista Business, the local connection to SQL server appears to be a problem.
I know there's nothing wrong with my web.config or anything like that as it was working perfectly prior to running under Vista. If i specify the database on the live website, it works fine. If I specify the test database in app_data folder, I get the following exception:-
Server Error in '/UKSD' Application.
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +736211
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1959
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +237
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +374
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +192
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84
System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197
System.Web.Security.SqlRoleProvider.GetRolesForUser(String username) +771
System.Web.Security.RolePrincipal.IsInRole(String role) +272
System.Web.Configuration.AuthorizationRule.IsTheUserInAnyRole(StringCollection roles, IPrincipal principal) +120
System.Web.Configuration.AuthorizationRule.IsUserAllowed(IPrincipal user, String verb) +300
System.Web.Configuration.AuthorizationRuleCollection.IsUserAllowed(IPrincipal user, String verb) +191
System.Web.Security.UrlAuthorizationModule.OnEnter(Object source, EventArgs eventArgs) +3509206
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64
Version Information: Microsoft .NET Framework Version:2.0.50727.312; ASP.NET Version:2.0.50727.312
I am taking a guess it's somerthing to do with access issues, but can't find anything out on searching the forums etc..
I am setup as an administrator, running VWD as an administrator and for the record, SQL Server Management Studio express works fine.
Another small issue, when I run the application from VWD, I always have to minimise the application and restore it as the graphics get corrupt.
Please help!! I cannot debug when running of the live website.