SQL 2012 :: Disaster Recovery Options For Multi-Database Multi-Instance Environment

Sep 23, 2014

Disaster Recovery Options based on the following criteria.

--Currently running SQL 2012 standard edition
--We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately
--Recovery needs to happen within 1 hour (Not sure that this is realistic
-- We are building a new data center and building dr from the ground up.

What I have looked into is:

1. Transactional Replication: Too Much Data Not viable
2. AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances
3. Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options with be a nightmare.

View 1 Replies


ADVERTISEMENT

SQL 2012 :: MSDTC In Multi-node / Multi-instanced Cluster

Aug 17, 2015

More often than not, I typically don't touch DTC on clusters anymore; however on a project where the vendor states that it's required. So a couple things here.

1) Do you really need DTC per instance or one for all?
2) Should DTC be in its own resource group or within the instance's group?
2a) If in it's own resource group, how do you tie an instance to an outside resource group? tmMappingSet right?

View 9 Replies View Related

SQL Tools :: Possible To Get Multi-database Functionality On Same Instance Option Available In SSMS

Sep 27, 2015

Would it be possible to get a multi-database functionality on the same instance option available in SSMS anytime soon?

View 6 Replies View Related

SQL 2012 :: Copying A Database To Another Server Instance Options

Jun 25, 2014

We have SQL Server 2012 running on Windows 2008 Server. We need to copy five databases from our 'sandbox' to our test server and then to our production server. The database backup file sizes are 3 MB, 20 MB, 344 MB, 645 MB and 17 GB. We are planning on using the backup and restore method since we already have full backups and the scripts to recreate the logins/users/permissions. We believe this method provides more flexibility and control over the process. However, we have a few jobs, maintenance plans and ssis packages.

To get the jobs to the new server instance, the plan is to script out the jobs on the 'sandbox' and execute the scripts on the test server instance. Is this the best or only way to handle the jobs?How to get these maintenance plans to the new server instance? (There is no 'script out' maintenance plan option.) We may have to just recreate them on the new server instance. Is this the best or only way to handle the maintenance plans?

We have a few ssis packages. How to handle getting the ssis packages over to the new server instance (using the backup restore method)? These packages use the Project Deployment Model. Therefore, should we restore the SSISDB or open up the package file using VSS on the new server instance and change the connection information to point to the new server instance.

Just wondering if there is any reason or advantage to use the Detach and Attach method or Copy Database Wizard method? I have read where the Copy Database Wizard method handles the database's dependent objects like logins, jobs, maintenance plans, user-defined error messages and shared objects from the master database. Are there any other move/copy database methods to consider? Just trying to make sure we have thought out everything and are using the best method to copy our databases over to another server instance.

View 3 Replies View Related

Multi-server Environment

Oct 1, 2007

We are currently managing about 75 SQL Server instances. Each instance contains jobs (backups, index work, admin, etc) which report back to a central instance for monitoring. This is working well, but each time I need to change one of the jobs, I am having to log into each instance to do so.

I have recently played with Multi-server Environment, using a master and target, to see if this might help. There is relatively little written about Multi-server in books-online, and I am left with a question.

All jobs appear to be exact duplicates. But I don't want exact duplicates. For example, on the various target servers, I would like for the backup jobs to run at different times, and write to different directories. How are the rest of you working around this situation, if you are using Multi-server Environment?

View 1 Replies View Related

Multi-database Multi-server

Mar 27, 2007

I am new to Reporting Services and hope that what I am looking to do is within capabilities :-)



I have many identical schema databases residing on a number of data servers. These support individual clients accessing them via a web interface. What I need to be able to do is run reports across all of the databases. So the layout is:



Dataserver A

Database A1

Database A2

Database A3



Dataserver B

Database B1

Database B2



Dataserver C

Database C1

Database C2

Database C3



I would like to run a report that pulls table data from A1, A2, A3, B1, B2, C1, C2, C3



Now the actual number of servers is 7 and the number of databases is close to 1000. All servers are running SQL2005.



Is this something that Reporting Services is able to handle or do I need to look at some other solution?



Thanks,



Michael

View 5 Replies View Related

SQL Licenses In Multi-server ETL Environment

Jan 6, 2004

My company uses a quad processor server connected to a SAN to load and summarize detail sales information from 2000 stores on a nightly basis. We poll and load around 5,000,000 rows of data each night. THis information is summarized up to various levels, then replicated to one or more secondary datamart servers for end user access via web reporting, BI tools like Proclarity/Analysis Services etc...

The initial data polling server is only touched by the development staff supporting the process (1-5 programmers) and is licensed for SQL server Enterprise using a CAL model. Each datamart server is licensed with MS SQL server processor licenses.

The question: We were told that the quad processor polling machine, which has no end user access allowed, must be licensed with processor licenses since it touchs the data ultimately consumed by end users. This makes no sense to me.

The Microsoft white papers discussing multi_tier environments don't seem to address this type of issue. They focus on applications that ultimately pass thru a data request to the SQL server machine. In this situation, user requests are handled by the datamart servers, which are licensed with processor SQL licenses.

Can anyone clarify?

Thanks, Mike

View 1 Replies View Related

SqlTransactions In A Multi-Threaded Environment

Nov 27, 2006

I am currently working on a project in which I am using SqlTransaction objects with their IsolationLevel set to Serializable. I am wondering what would be the effect if multiple threads were to call this code at the same time? Would the second transaction (created by the second thread on the same connection), for example, be queued until the first one (created by the first thread on the same connection) finishes? Or would an exception be thrown for the second transaction? In case an exception is thrown, would using the synchronization (locks) around critical sections solve the issue?
I guess it is easier to understand the scope of transactions in terms of multiple users, but it is confusing (for me) when I start thinking in terms of multiple threads. The underlying data is held in a single table and the scope of the transaction spans multiple SELECTs and an UPDATE or an INSERT.

I have been trying to find a feasible answer, and haven't found anything credible yet. Help!

View 10 Replies View Related

Reporting Services :: Multi-value Parameters - How To Not Select One Of Options

May 14, 2015

I have a multivalue parameter that has X options. Is it possible to implement a solution that when I go into the report through an action, to have one option (in this case is the first) not selected all all the others are checked?

View 4 Replies View Related

Anyone Know Table Limits In Multi-schema Environment?

Mar 27, 2007

My product is growing rapidly and currently I have a db for each client with identical schema. Of course maintenance is pretty hard. I was thinking of using a shared db but having a schema for each client (sql 2005) - I have almost 100 tables in the schema which means with just 10 clients the db would pass 1000 tables. My gut is telling me this ain't going to fly!any ideas? and if it does work ... any thoughts on updating the internal schemas for each client?thanks-c 

View 2 Replies View Related

Temporary Tables In Multi User Environment

Feb 6, 2004

Hi,

I am developing reporting application (access project) which will be used in multi user environment.

Here is what I have:

1 SQLServer database for many users

Each report will be based on:
stored procedure which creates a table filtered for specific dates predefined views will use the newly generated table to show results to the client. However, if more than 1 person runs reports results will not be accurate if each person specified different dates because they will look at the same table and results will match only for a user who called the stored procedure last.

what can you recommend - how to report in multi user environment?


Many thanks

View 2 Replies View Related

How Use Stored Procedures In Multi-user Environment?

Apr 19, 2007

Hi!



In my database I have all business logic in stored procedures. For example there are procedures: ReadBike and UpdateBike. Bike is business object stored in 4 tables.



On my system work 100 employees and we have one problem with this. The stroy is;

1. User A reads data about Bike1

2. User B reads data about Bike1

3. User A updates data about Bike1 (user B have old data)

4. User B updates data about Bike1



So user B don't know about chnages made by user A. How to solve that ptroblem?



It's probably solved in ADO, but I want use business login in procedures.



Regards,

Walter

View 1 Replies View Related

Control Number Of Options Selected In A Multi Select Parameter

Feb 13, 2007

Hi All

I have a report which has a multi-value parameter. Problem is, it can contain up to 100 options.Is there a way to limit the number of options that is passed to the SQL statement?. EG list has 100 options, user selects 10 but only the first 4 selected options are passed to the SQL statement.
Many Thanks
Delli

View 4 Replies View Related

Web Based Sql Server Record Manipulation In Multi-user Environment

Jan 20, 2007

I was wondering if you guys might give me some advice on how best to handle a particular scenario i'm struggling with.
I have a client that basically wants web-based-update access to their sql server database.  Specifically, for a group of users to be able to access a page where they select a record for editing.  the caveat is that no two users should be able to pull up the same record at the same time.  Originally I would have thought there was some easier record-locking-mechanism I could exploit within sql server or ado.net itself, but I haven't been able to come up with anything..  so this is my current approach:
The page they use starts-out with basically a blank form.  there are custom-built paging controls at the bottom of the screen.  they click page-forward to begin and a stored procedure is ran to select a record and update a field on that record to indicate "in-process".  when they finish editing the record - or page on to the next record without updating - another stored procedure is ran - updating/resetting the status field on the record appropriately.
The entire page is encapsulated within an ajax.net updatepanel.
The entire page has caching disabled.  This works well in conjunction with the first page being blank.  if they get out of the app and try to get back in by clicking the back button - all they can do is get to the first (blank) page.
A piece of javascript window.onunload clicks a button on the page that releases the record they currently have selected in the event of a re-direct, clicking back, etc.. it appears to work with everything except a window close.  in that case, i have a stored procedure running periodically on the server that checks how long a record has been selected - and if it exceeds the time indicated - resets the record as to allow it to be re-selected later.
In the event of session timeout, they are redirected to another page that tells them their session has timed-out (and since the window.onunload fires - it takes care of releasing the record if they have one on the screen).
The concept seemed to be working well until I started multi-user testing.  Now it seems as if two users time it perfectly - they are actually able to both select the same record.  it happens pretty rarely, but it does seem to happen.  I'm guessing this has to do with how my stored procedure is structured - possibly allowing a tiny-enough window between the record being selected for editing - and the update running to actually status the record as in-process (2 separate sql statements within the one stored procedure).
I believe I also have a found a second quirk in my approach where something is causing the window.onunload event to fire twice in some strange situations..  but that's more annoying/confusing from a logging standpoint than anything..
I've read where people say to ensure you dont update a record that's already been updated - that you should compare the fields before you actually perform the update and ensure they haven't changed since you selected it..  but to me that doesn't solve anything.. if two people select the same record and both spend time working on it - the person that tries to update last has just wasted their time.
I've also toyed with the idea of maintaining a separate table in the database to hold the keys to the currently selected records and use that to keep multiple people from selecting the same record - but honestly i dont know if that approach is any better than what i'm doing now.   
anyway, I was just curious if you guys had any advice in regards to how you'd handle a request like this..  or if you see any obvious problems/fixes with my current approach..
I would greatly appreciate any info you could provide-
thanks-

View 3 Replies View Related

Relational DB/Analysis Services Configuration For Multi-proc Environment

Oct 19, 2006

Hello--

Is it possible to install/configure SQL-Server 2005 on a multi-processor machine so that the relational DB utilizes a given subset of processors while Analysis Services utilizes another subset?

Thanks,

- Paul

View 4 Replies View Related

Reporting Services :: Where To Install SSRS SharePoint Mode In A Multi-server Environment

Aug 17, 2015

I am setting up sharepoint and sql server integration environment. I am considering the following topology: PowerPivot for SharePoint 2013 and Reporting Services in SharePoint mode Two Server Deployment

[URL]

I am looking to follow the topology example by the letter, which involves installing PowerPivot for SharePoint (aka SSAS in SharePoint mode) in the same server as my SQL, and installing SSRS in SharePoint (SP) integrated mode in the same server as SharePoint.

I understand, however, that if I wanted to install SSRS in SP mode in the same server as SQL, I could but only if the server contains the SP Object Model.

My first question is, what would involve having the SP Object Model in the SQL Server?

Would only installing SP binaries be enough; or Do I need to do a minimal install of SP in the SQL server enough for it to joing the SP farm? And most importantly, what would be the licensing implications for SP in case I want proceed down this route and have SSRS in SharePoint mode installed in the same server as the SQL?

View 4 Replies View Related

Multi-Instance

Jun 28, 2005

Using Management Studio (April CTP), I tried to connect to integration services. There was no server listed, so I added my computer jaargeroxpctp and this went fine.

View 3 Replies View Related

Recovery :: Multi Site Clustering Failing Over

Nov 16, 2015

I have setup a WSFC that contains 3 nodes ( node 1,2 belongs to subnet A and  node3 to subnet B), lets say node 1 is the primary host in the wsfc.

Each node has their own sql server instance, that i configure an Availability Group that will replicate between node1 and node 3), created an Availability Group Listener that will contain 2 vips for each subnet.

My question here and I am not sure if i missed something when setting up the Availability Group. If i failover an Availability group from sql server from node 1 subnet a to nde 3 subnet b, then the listener will be on the vip that belongs to subnet b and the primary instance will be the one in node 3, but this wont cause the WSFC to move the primary host from node 1 to node 3.

So at this point dont see what is the dependency between setting up the AG with WSFC. when fail over an AG doesn't cause the primary host in wsfc to fail over as well.

Why availability group relies on WSFC and how can i make it to depend so the primary host moves as well?

View 5 Replies View Related

The Multi Delete && Multi Update - Stored Procedure Not Work Ok

Feb 4, 2008

the stored procedure don't delete all the records
need help



Code Snippet
DECLARE @empid varchar(500)
set @empid ='55329429,58830803,309128726,55696314'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0




TNX

View 2 Replies View Related

Multi-Server Instance

Aug 22, 2007

What is the effect of deploying Multi-Server instance on performance?

View 3 Replies View Related

SSIS And Multi-Instance

Nov 1, 2006

I have figured out that SSIS installs once for as many instances of SQL Server 2005 as there are on the machine. I also figured out that even if there is only a named instance on the machine, it will always install under the "Default" machine named instance.

I also figured out that you can change the instance viewed by modifying C:Program FilesMicrosoft SQL Server90DTSBinnMsDtsSrvr.ini.xml

What I am unclear about is that in SQL Server 2000, each Instance had an MSDB db which stored all of the jobs. In SQL Server 2005, each instance still has an MSDB DB but theSSIS also has a MSDB DB ?? or is this pointing to the MSDB for the instance specified in the xml file???

There is not much info out on this at all and would be good if the MSDB help explained this install once so it would all be clear!

THanks for any help... thank goodness for newsgroups!

View 6 Replies View Related

Recovery :: Multi Site AlwaysOn Setup With Different Subnet?

Nov 17, 2015

OS - Windows 2012 R2 Standard Edition.
DB - SQL 2012 Enterprise edition

Total 3 nodes participates for AO setup, 2 Nodes for Local HA and 1 Node for another datacenter for DR. All the 3 Nodes are same domain name and member.

1. Local First 2 Nodes are same subnet XXX.XX.44.XX
2. DR Node another subnet XXX.XX.128.XX

Does it require to add two different IP address while creating cluster name? Not using shared disk SAN storage etc.. I am using Node majority quorum witness setting for failover. 

View 10 Replies View Related

Recovery :: How To Achieve Multi-site Cluster With 2 Node

Apr 27, 2015

We have two locations in US, I am thinking of having 2 node SQL cluster for Lync 2010, I alardy have One DB server running in one location, now we got new site where we are planning to have one more DB for redundancy.

View 5 Replies View Related

SQL7 Job - Single/Multi-Instance

Jul 7, 2001

SQL7

If I have a job that runs every minute, and that job happens to take more than one minute to run on occassion, will SQL Server start a second instance of the job or will it be serial - one after another ?

Thanks,

Craig

View 2 Replies View Related

SQL 2005 Multi-Instance Cluster Question

Mar 19, 2008



I understand most of the concepts for the Active/active setup. I am setting up a 2 node cluster. My question is can I use the default instance name (one default instance name per group) when setting up each instance or do I have to use named instances . I am assuming that since each group is a virtual machine that I can get away with using the default instance, but I cant find any documentation to confirm my frequently wrong perceptions.

I want to set it up like this

Instance1 Group

Group resources
Default SQL Instance Name Installation
Preferred Owner Node1

Instance2 Group

Group resources
Default SQL Instance Name Installation
Preferred Owner Node2

Thanks

View 4 Replies View Related

Help With Multi Join Or Multi Tier Select.

Jul 20, 2005

Hello,I am trying to construct a query across 5 tables but primarily 3tables. Plan, Provider, ProviderLocation are the three primary tablesthe other tables are lookup tables for values the other tables.PlanID is the primary in Plan andPlanProviderProviderLocationLookups---------------------------------------------PlanIDProviderIDProviderIDLookupTypePlanNamePlanIDProviderStatusLookupKeyRegionIDLastName...LookupValue....FirstName...Given a PlanID I want all the Providers with a ProviderStatus = 0I can get the query to work just fine if there are records but what Iwant is if there are no records then I at least want one record withthe Plan information. Here is a sample of the Query:SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,pl.InvalidDataFROM Plans plnINNER JOIN Lookups l3 ON l3.LookupType = 'REGN'AND pln.RegionID = l3.Lookupkeyleft outer JOIN Provider p ON pln.PlanID = p.PlanIDleft outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderIDleft outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'AND pl.ReasonMain = l1.LookupKeyleft outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'AND pl.ReasonSub = l2.LookupkeyWHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNumI know the problew the ProviderStatus on the Where clause is keepingany records from being returned but I'm not good enough at this toanother select.Can anybody give me some suggestions?ThanksDavid

View 5 Replies View Related

SQL Server 2008 :: Comparing Two Multi Instance R2 Installations

Apr 26, 2015

I have 2 SQL server installs for 2008 R2 configured as multi instances. I have a product called Esri ArcMap 10.3 that can be used to generate a database. When I run the wizard against one installation, the wizard successfully creates the database. When I then run the same against the other installation it fails with the following error [Microsoft][SQL Server Native Client 10.0]Invalid cursor state

I've attempted to look at the configuration of each using
select *
from master.sys.configurations

From this I found several differences

Successful Mulit instance
Optimize for Ad hoc Workloads – False
Max Degree of Parallelism - 0

UnSuccessful Multi instance
Optimize for Ad hoc Workloads – True
Max Degree of Parallelism - 4

I attempted to co-ordinate the differences running the wizard for each iteration but it always failed with the same error above. The error always seems to occur when a particular store procedure is run. There are quite a number of scripts run prior to this and are technically under the covers and only discovered via tracing, in this case using SQL Profiler. I don't have access to individual scripts that I can run incrementally to replicate the issue. I have to rely only on the Esri Wizard.

Reviewing the error against several forums suggests that this is an ODBC error but the trace I ran using SQL Profiler finds that the driver used is Native.

My question then is "What are the conditions that would cause this error above (Invalid Cursor)?" "Is there other configuration settings that are not captured via the SQL identified above?" "Could this be caused by mapped drives for data, Logs and Temp?"

View 2 Replies View Related

Multi Instance Sql Server 2000, Coexistence SP3-SP4, Replication Issue?

Feb 1, 2007

Hi,
I've went throught the SP4 documentation but I didn't find a clear answer about how to deal with this situation:
In the situation of a multi instances server in which all instances are SP3a, can I just upgrade a couple of instances to SP4 and leave the others in SP3a ? .
One of the instances I may have to leave in SP3a belongs to a merge replication environnement (Distributor and Publisher), while the Subscriber is also in SP3 on a remote server (which I don't administer..).(the point is that in this situation I have to upgrade the 2 servers simultaneously)
In other words, when I 'll apply the SP4 to just one instance, I understood that the all the tools will be upgraded to SP4.

Will the merge replication involving a SP3 instance on the same server still work?


Thanks for your comments/replies/ideas.

Regards,
jalal

View 3 Replies View Related

Remove Select All Options From Multi Select Parameter Dropdown

Jun 8, 2007

Hi All



I am using SQL Server 2005 with SP2. I have multi select parameter in the report. In SP2 reporting services gives Select All option in the drop down.



Is there any way I can remove that option from the list?



Thanks

View 4 Replies View Related

Analysis :: SSAS Tabular And Multi-dimensional Instances Using Same Server Instance?

Aug 27, 2015

Is it possible to install 2 SSAS instance (one default and one named) and have them access a single sql server instance (default) all on the same server?

View 3 Replies View Related

How To Upgrade One Of A Multi-instance SQL2K5 DEVELOPER Versions To Standard Version With SP1 Applied?

Oct 11, 2006

hello we want to evaluate TS foundation server but it says it won't support developer edition sql, only sql2k5 stanard. But now we have DEVELOPER version on our development sql server, DEVDATA1, i.e. instances DEVDATA1DEV, and DEVDATA1QA are both installed on the same sql server box with both SP1 applied.

How can we update DEVDATA1QA to Standard version while still have SP1 applied? we just install Standard CD and then apply SP1 again?

Because this instance is our QA version, before we do something silly, if any of you did that before, any advice would be really helpful.



thanks in advance.

View 1 Replies View Related

Recovery :: Server Local High Availability Using Failover Cluster And Disaster Recovery Using AlwaysOn

Aug 17, 2015

We have a requirement to build SQL environment which will give us local high availability and disaster recovery to second site. We have two sites- Site A & Site B. We are planning to have two nodes at Site A and 2 nodes at Site B. All four nodes will be part of same Windows failover cluster. We will build two SQL Cluster, InstanceA will be clustered between the nodes at Site A Server and InstanceB will be clustered between the nodes at Site B, we will enable Always On Between the InstanceA and InstanceB and will be primary owner where data will be written on InstanceA and will be replicated to InstaceB. URL....Now we want we will have instanceC on the Site B and data will be writen from the application available on Site B, will be replicated to the instance on the Site A as replica.

View 6 Replies View Related

SQL 2012 :: Two Node WSFC On A Multi Subnet

Feb 10, 2015

We are running SQL Server 2012 Enterprise on Windows 2012 R2. We have set up A WSFC with the primary Node in our East Coast Data Center and the Secondary Node in our West Coast Data Center each node is in a dfferent subnet. Since we have high latency between the sites we run in Asynchronous mode with manual failover only. My quesion concerns Quorum, everything I read would indicate we need a third node (odd number) (e.g. a fileshare Witness) with only the file share and the primary node having a vote...What I don't see is any need for a file share if we can only do manual failover.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved