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:).
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.
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.
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?
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!
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?
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.)
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
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
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.
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.
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.
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?
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.
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?
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'
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. :)
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.
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?
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.
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?
