SQL 2012 :: Swapping Drive Letters In Cluster
Jul 3, 2015
As part of a migration of data to a new SAN I have hit a bit of a snag in the migration. In summary what will happen is user database data files will be moved from one LUN (say drive F:) to a new LUN (say drive G:). Once all the data is migrated, plan is to remove dependency of that drive from SQL server and remove the drive and delete the LUN. So far, so good.
However one of the LUNs (drive D:) destined to be deleted also hosts the instance default directories, i.e. everything under MSSQL11.MSSQLSERVER (Data, Backups, FTData, JOBS, etc). BOL has articles on how to migrate system databases, including tempdb. But there is no guidance that I could find on how to relocate other folders. There are forums where users have listed registry changes, etc that can achieve this but these are steps I am unwilling to take on a production server.
So my plan is:
1) Add new drive to cluster (drive E:), sufficiently large enough to host instance default folders
2) Shutdown SQL server
3) Copy all default folders to new drive
4) Swap drive letters so that new drive is now D:
5) Start SQL server and if everything works, delete the original drive (which is now drive E:).
View 3 Replies
ADVERTISEMENT
Apr 11, 2007
Hello - I have a SQL 2000 server which has a D: drive that contains all of my databases (system and user). I am running out of space on this volume and need to migrate the contents of this volume to a larger one. My initial plan was to introduce a new volume to the server (say a K: drive). Backup all databases (of course), and then stop all SQL services. Copy all data from D: to K:. Once data is copied, swap drive letter names (D: to I: and then K: to D. Then restart SQL services. SQL should not know any better since everything was on the D: drive when it went down, and everythiing is still on the D: drive when it came back up, correct?
The other option mentioned is to detatch the databases, copy the data and then reattach them in their new locations. I understand this method, but it seems more involved (and riskier) than just renaming the drives. Does anyone have an opinion regarding these two migration methods? Thanks for your help.
Chris
View 3 Replies
View Related
Apr 18, 2007
All,
I'm writing to ask if anyone knows whether or not MS SQL server stores in any system tables the association between a database and the drive letter/directory path where its corresponding MDF/LDF files are located.
Thanks,
Isaac
View 4 Replies
View Related
Oct 9, 2007
All,
I have a test server that needs the drive letters changed for the data, log and backup drives. Is there a way to make this change without reinstalling SQL Server?
Future guru in the making.
View 13 Replies
View Related
Jul 20, 2005
Can you restore the master database, running on windows server 2000, sqlserver 2000 from a backup that was taken from c: to another serverrestoring to d:?*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Jan 28, 2015
How to parse a string to equal length substrings in SQL
I am getting a long concatenated string from a query (CTVALUE1) and have to use the string in where clause by parsing every 6 characters..
CREATE TABLE [dbo].[PTEMP](
[ID] [char](10) NULL,
[name] [char](10) NULL,
[CTVALUE1] [char](80) NULL
)
INSERT INTO PTEMP
VALUES('11','ABC','0000010T00010L0001000T010C0001')
select * from ptemp
After parsing I have to use these values in a where clause like this
IN('000001','0T0001','0L0001','000T01','0C0001')
Now ,the values can change I mean the string may give 5 values(6 character) today and 10 tomorrow.. So the parsing should be dynamic.
View 2 Replies
View Related
Dec 19, 2014
I need to build a query, or use a function to removing alfabetic Character from a string.
My string is
WI14000323-0003
The value i want to keep is : 14000323-0003.
I try using this SELECT:
SELECT STUFF(Upper(z.lote), PATINDEX('%[A-Z]%', Upper(z.lote)), 2, '') from mytable
but in this case i have only 2 letters WI, but in the future i dont know if is only 2 letters, 1 letter or more, then is not what i pretend.
View 2 Replies
View Related
Oct 3, 2007
I've added a new drive to a clustered instance of sql 2000. Go to the node the instance is active on, the drive shows up. But...try to add a backup device that lives on the new drive (which was, after all, the whole point of the exercise) and SQL Server is blind to that drive. I have vague recollections of it recognizing the clustered drives when i set up all this a couple years ago. Could it be that it recognizes all the drives it'll ever recognize at the moment of installation? Anybody know?
thanks,
Garth
:beer:
:D :D :D
:beer:
View 6 Replies
View Related
Jul 8, 2005
HI guys, long time since I've been here ...
Anyway, I have a question and hope to receive some suggestions.
We have a cluster installation of SQL 2000 Ent SP3a and we had a logical drive Y: on a second incative node: N2. SQL Right now is running of the active node: N1. We moved the Y: drive from Node N2 to the N1 one and it's fully functional and healthy. The only thing SQL doesn't see it. So ti has to be restarted.
Is there a way to refresh a list of logical drives on SQL without having the service to be restarted? (it's not an option for us meantime, it's our prod env.)
Any suggestions appreciated.
Thx
Dim
View 7 Replies
View Related
Jan 22, 2008
Hi
I have configured a cluster SQL 2005 (active/passive) and added 4 LUNS to that cluster the cluster works without problems.
However I am having issues backing and restoring on 2 drives. I can complete the operation ( a resotre of 100MB ) DB in less than a minute if i use the 2 drives but it takes about an hour if i use the other 2 drives. The wait type is ansynchronous_IOcompletion and backupthread in dm_exec_requests.
I have detached the drives and reformatted it but stil lsame issues any help
Anup
View 4 Replies
View Related
Aug 16, 2005
I'm trying to build a new cluster with SQL Server 2K Enterprise onWindows 2003 server. When I run the install, I tell it to put theprogram files on the D: (local) drive and the data/log files on shareddrives. However, after the installation is complete, almost all thefiles go in the C: drive in program filesMicrosoft SQL Server, ratherthan the D: drive (it does create a few files on D:). We've experiencedthis on more than one server build.Anyone know any way around this?Post answers publicly, please, or email tosteven.wechsler(at)mtvstaff.com (the posting email address is hosed).Thanks,Steve
View 1 Replies
View Related
Apr 10, 2014
I ran the Advanced cluster preparation for a new sql instance on an existing cluster.
Slq Server 2012.
After is completed, it was successful, I realized I specified the wrong Instance Root directory.
Is it possible to remove what the preparation installed? Or is it possible to change the root directory?
View 3 Replies
View Related
Aug 31, 2015
Is there a way to get access to the files on the shared drive from either of the nodes when the SQL Cluster is shut down?
It doesn't look like there is, but hoping there is some trick. I see how it can be done with a Windows Cluster but not a SQL Cluster.
View 4 Replies
View Related
Jan 16, 2015
How to backup half of dbs from a server on C drive and the other half on D drive and vice versa, first half on D drive and other half On C drive using only one job and one stored procedure??
Using scheduling from job add 2 schedules to the job so first schedule backup first half to C and second half to D , the second schedule backup first half to D and second half to D.
View 1 Replies
View Related
Feb 18, 2014
I´ve been reading that SQL Server 2012 Always On is dependent on having a Windows Failover Cluster setup. Is that correct ?
View 6 Replies
View Related
Nov 5, 2014
We have a cluster with two nodes and two instances of SQL Server 2012 Standard Edition running on them. Volume W: is a Fusion-IO card.
On one of these nodes a lot of database names are showing up in the resource monitor as *.mdf files (W:0MSSQL1…).
How and why SQL Server is using these files? They only show up on one of the nodes having more load.
Volume I: is the volume where the transaction log is written so we can explain these files.
View 3 Replies
View Related
Oct 22, 2015
How do I change the file path from C: drive to E:
See attached
View 2 Replies
View Related
Sep 11, 2014
We are seeing very high Average Disk Queue Length numbers in one of our clusters (both nodes of the cluster are Virtual, but have their own dedicated virtual environments). Our main data drive also houses TempDB, which I would like to move.
Each node in the Active/Passive cluster are running Windows Server 2012 Standard 64bit and SQL Server 2012 Enterprise 64bit. There is a separate drive for Log files and data files.
The data files also have TempDB on them as previously mentioned. I am reading that you can set up a local disk on each node of the cluster, with the same drive letter and path and then move tempdb as you would with a stand alone SQL Server.
View 4 Replies
View Related
Mar 20, 2015
Although clearly not best practice for a produciton DB, is it supported to attach MDF/LDF files that are hosted on a network drive?
I have been exploring options to host a read only archive database that we need to access on a very short term adhoc basis.
View 2 Replies
View Related
May 20, 2015
I am trying to run XMLA script to save .trc file to remote drive.
Anytime I try to save file it save it to the local drive where SSAS instance is installed.
Here what I am using it within XMLA script
<LogFileName>c:TraceTraceFileName.trc</LogFileName>
How can I save it to my local (c drive) or remote server
View 0 Replies
View Related
Mar 19, 2008
I am converting a legacy ASP.NET 1.x site to an ASP.NET 2 one. In the former site, sets of data connections are stored in an web.config file allowing swapping between testing and production servers based on a global variable value at the start of the app. As I now try to use sqldatasource, I find the control has its connection string embedded in the html page and stored as a unique variable in the web.config. How can I dynamically swap the new sqldatasource's connectionstring the same way data connections are made in the former site? Please advise. Thanks.
View 6 Replies
View Related
Apr 5, 2001
I have a table that has a column for each month and I want to use a view to convert each row into 12 rows with a one month column. The month column will have 1 - 12 in it depending on the month. I need to convert it this way to push it into an Essbase cube. I know I can use the union operator to do this, but I would rather not read the table 12 times. Is there a way to do this just reading through the table once? My Hyperion Essbase book gives an Oracle example for swapping rows and columns by using a decode function. Is there a similar function in SQL Server?
View 2 Replies
View Related
Jul 23, 2005
I need to build a *.sql script that will remove a database (let's callit "DB1") and replace it with a brand new empty database (let's call it"DB2").Caveat: I don't want to be left with database "DB1" having it's filesconfusingly named "DB2.mdf" and "DB2_log.ldf". These two files shouldalso be renamed to "DB1.mdf" and "DB1_log.ldf" so that outsidecustomers are not left confused. In addition, I need to be able torestore the original DB1 if anything goes wrong during, or even after,the entire process.Let's assume every customer's *.mdf's and *.ldf's will always reside inC:Program FilesMicrosoft SQL ServerMSSQLdata folder.I've researched sp_attach_db, but this looks more appropriate formoving databases. This isn't what I want to do.Thank you in advance.
View 1 Replies
View Related
Dec 21, 2007
Hi,
Is it possible on a matrix report for the rows and columns to be swapped around after the report has been built? E.g, can the rows and columns be swapped around by the user in the preview page?
thanks,
Al
View 6 Replies
View Related
May 16, 2008
I've got a field that might have spurious values in it (say, an admin adds a new row but doesn't have an entry for this field).
I'm trying to swap in the string no_image_EN.jpg if the value in the db does NOT end in .jpg. That way, any value rreturned is either a valid filename or no_image
I'm having trouble with the CASE statement, particularly testing just the last few cahracters of the string:
select product_code,
CASE can_image_en
?? When (can_image_en LIKE '%.jpg') then can_image_en
Else 'no_image_EN.jpg'
End as can_image_en,
none of these do the trick either (some are bad syntax obviously):
? When (can_image_en LIKE '%.jpg') then can_image_en
? When LIKE '.jpg' then can_image_en
? When '%.jpg' then can_image_en
? When right(can_image_en,4) = '%.jpg' then can_image_en This is the one that has correct syntax, though it seems to return false in ALL cases CASE can_image_en
When '%.jpg%' then can_image_en
Else 'no_image_EN.jpg'
View 5 Replies
View Related
Jul 20, 2005
All,To make a long story short, we are swapping out the "knock-off" drivesthat the NA purchased on E-Bay in one of our production SQL Servers(SQL Server 2000 Enterprise) this weekend for brand new ones (Compaq15K RPM 32GB drives). We are currently experiencing ASR almost on adaily basis and it is really causing a disription in service. SO, TheNetwork Admin ahs made this decison to replace these drives in attemptto solve this. These new drives will be imaged with ALL of the currentdata on the "knock-off' drives and will be plugged back in to thisdatabase server and brought back up .This Server also happens tocurrently be a subscriber in Merge Replication as well. Besidesstopping replication to this subscriber is there any other tasks thatI need to do or concerns that I need to be knowledgable about or lookfor when we bring this database server backup on line this weekend?Thanks Travis. :)
View 1 Replies
View Related
Feb 27, 2007
HI ,
Need some quick fix Help
I have been
trying to load data from AS400 to DB2 (windows) using ADO.NET connection in
Data reader source and OLEDB Destination (IBM Oledb provider )
The files, I€™m trying to load, have
number of rows more then 15 million.
On execution of the package I get
Out of Memory Error (see below)
My Destination Box is 4GB+ RAM and 4
CPU Box.
There seems to be some Buffer and
Swapping related issue which I€™m not able to figure out. It says that System is
unable to allocate memory
Please help me on the same.
Thanks in Advance
Amit S
SSIS package "ABCDE
1.dtsx" starting.
Information: 0x4004300A at ABCDE
2003 to 2004, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at ABCDE
2003 to 2004, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at ABCDE
2003 to 2004, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at ABCDE
2003 to 2004, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at ABCDE
2003 to 2004, DTS.Pipeline: Execute phase is beginning.
Error: 0xC0202009 at ABCDE
2003 to 2004, OLE DB Destination [12]: An OLE DB error has occurred. Error
code: 0x8007000E.
An OLE DB record is available.
Source: "Microsoft Cursor Engine" Hresult: 0x8007000E Description:
"Out of memory.".
Error: 0xC0047022
at ABCDE 2003 to 2004, DTS.Pipeline: The ProcessInput method on component
"OLE DB Destination" (12) failed with error code 0xC0202009. The
identified component returned an error from the ProcessInput method. The error
is specific to the component, but the error is fatal and will cause the Data
Flow task to stop running.
Error: 0xC0047021 at ABCDE
2003 to 2004, DTS.Pipeline: Thread "WorkThread0" has exited with
error code 0xC0202009.
Error: 0xC02090F5
at ABCDE 2003 to 2004, DataReader Source [61]: The component "DataReader
Source" (61) was unable to process the data.
Error: 0xC0047038 at ABCDE
2003 to 2004, DTS.Pipeline: The PrimeOutput method on component
"DataReader Source" (61) returned error code 0xC02090F5. The
component returned a failure code when the pipeline engine called
PrimeOutput(). The meaning of the failure code is defined by the component, but
the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at ABCDE
2003 to 2004, DTS.Pipeline: Thread "SourceThread0" has exited with
error code 0xC0047038.
Information: 0x40043008 at ABCDE
2003 to 2004, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at ABCDE
2003 to 2004, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at ABCDE
2003 to 2004, DTS.Pipeline: "component "OLE DB Destination"
(12)" wrote 289188 rows.
Task failed: ABCDE 2003 to
2004
Warning: 0x80019002 at ABCDE
1: The Execution method succeeded, but the number of errors raised (6) reached
the maximum allowed (1); resulting in failure. This occurs when the number of
errors reaches the number specified in MaximumErrorCount. Change the
MaximumErrorCount or fix the errors.
Executing ExecutePackageTask:
C:Documents and SettingsAdministratorMy DocumentsVisual Studio
2005ProjectsIntegration Services Project1Integration Services Project1ABCDE
2.dtsx
Information: 0x4004300A at ABCDE
2005_04 to 2005_11, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at ABCDE
2005_04 to 2005_11, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at ABCDE
2005_04 to 2005_11, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at ABCDE
2005_04 to 2005_11, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at ABCDE
2005_04 to 2005_11, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at ABCDE
2005_04 to 2005_11, DTS.Pipeline: Execute phase is beginning.
Information:
0x4004800D at ABCDE 2005_04 to 2005_11, DTS.Pipeline: The buffer manager failed
a memory allocation call for 10484320 bytes, but was unable to swap out any
buffers to relieve memory pressure. 3 buffers were considered and 3 were
locked. Either not enough memory is available to the pipeline because not
enough are installed, other processes were using it, or too many buffers are
locked.
Error: 0xC0047012
at ABCDE 2005_04 to 2005_11, DTS.Pipeline: A buffer failed while allocating
10484320 bytes.
Error: 0xC0047011
at ABCDE 2005_04 to 2005_11, DTS.Pipeline: The system reports 63 percent memory
load. There are 4294660096 bytes of physical memory with 1548783616 bytes free.
There are 2147352576 bytes of virtual memory with 227577856 bytes free. The
paging file has 6268805120 bytes with 3607072768 bytes free.
Error: 0xC02090F5 at ABCDE
2005_04 to 2005_11, DataReader Source [61]: The component "DataReader
Source" (61) was unable to process the data.
Error: 0xC0047038 at ABCDE
2005_04 to 2005_11, DTS.Pipeline: The PrimeOutput method on component
"DataReader Source" (61) returned error code 0xC02090F5. The
component returned a failure code when the pipeline engine called
PrimeOutput(). The meaning of the failure code is defined by the component, but
the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at ABCDE
2005_04 to 2005_11, DTS.Pipeline: Thread "SourceThread0" has exited
with error code 0xC0047038.
Error: 0xC0047039 at ABCDE 2005_04
to 2005_11, DTS.Pipeline: Thread "WorkThread0" received a shutdown
signal and is terminating. The user requested a shutdown, or an error in
another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at ABCDE
2005_04 to 2005_11, DTS.Pipeline: Thread "WorkThread0" has exited
with error code 0xC0047039.
Information: 0x40043008 at ABCDE
2005_04 to 2005_11, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at ABCDE
2005_04 to 2005_11, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at ABCDE
2005_04 to 2005_11, DTS.Pipeline: "component "OLE DB
Destination" (12)" wrote 0 rows.
Task failed: ABCDE 2005_04 to
2005_11
Warning: 0x80019002 at ABCDE:
The Execution method succeeded, but the number of errors raised (7) reached the
maximum allowed (1); resulting in failure. This occurs when the number of
errors reaches the number specified in MaximumErrorCount. Change the
MaximumErrorCount or fix the errors.
Executing ExecutePackageTask:
C:Documents and SettingsAdministratorMy DocumentsVisual Studio
2005ProjectsIntegration Services Project1Integration Services Project1ABCDE
3.dtsx
Information: 0x4004300A at ABCDE
2005_11 to 2006_04, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at ABCDE
2005_11 to 2006_04, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at ABCDE
2005_11 to 2006_04, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at ABCDE
2005_11 to 2006_04, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at ABCDE
2005_11 to 2006_04, DTS.Pipeline: Pre-Execute phase is beginning.
€¦€¦.
€¦€¦€¦€¦
View 11 Replies
View Related
Mar 29, 2014
I have virtualbox installed on my notebook. For testing purpose, is it possible to setup SQL Server Fail-Over cluster using virtual box? I don't have a shared storage. How can I simulate that?
View 1 Replies
View Related
May 5, 2014
How to create a SQL 2012 Failover cluster using VMs?
View 9 Replies
View Related
Jun 2, 2014
We have few tables where we do truncate load or only do insert activities , why do the cluster index get fragmented very often to > 80%?
View 9 Replies
View Related
Oct 8, 2014
We have an active/active two-node QA cluster with four instances of sql. I need to add a fifth. In the past I always moved existing instances to the other node while installing a new instance.
Other than impacts to users from install reboots, is that a hard requirement?
QA is reluctant to have me put the four existing instances on one node for most of a day, fearing big performance slowdowns. Each node has 250GB of memory and each instance is allowed 50GB. Netapp disk backend.
View 3 Replies
View Related
May 13, 2015
So, today we have the following:
Dallas: A 2 node Windows 2008 Cluster running SQL 2012 ENT cluster
Wash: A 2 node Windows 2008 Cluster running SQL 2012 ENT cluster
and I'm mirroring (synchronous, no witness) a database from Dallas to Wash.Crappy set up. I know.Now customer wants to have the database mirror to another server in Dallas.What are the high level steps to transform my two clusters to use Availability Groups and Always On?Do I need to basically start over and build a new environment?or can I transform my two disconnected cluster?
View 4 Replies
View Related
Oct 3, 2015
Can I add a new node to an existing sql server 2012 cluster witthout stopping all the current resources?
Currently, there are 2 nodes and 3 sql server instances, which can not be shutdown. Is the process of adding nodes a "online" process?
View 1 Replies
View Related
