Trying to find out if this is the best way to move log files in databases that are in an availability group.
remove the DB from the AG
Run alter database commands like you would normally to take offline ,move file,bring online ,etc
drop the db from secondary node
then rejoin the DB to the AG
Is that the only option for moving them when its in an avail group? cant find any other info on moving files in mirrors or HA groups
When the database is configured for mirroring and you want to do partitioning on that database, How can we do? Is this similar process or any variation there while adding file groups and files? The partition will reflect in the mirroring database also?
View 1 Replies View RelatedI'm trying to move a log file of a database that is part of an availability group. I have been following steps from the article: [URL]
At first this worked fine for me in a test environment. When I tried it in a production environment the database on the secondary went into "Recovery Pending" state and I can't get it out.
I checked to ensure that the dB is looking in the right place for the log file, and it is. It just doesn't seem to actually use the new file. If I start and stop SQL service, the dB comes back up and is fine.
Here are the steps I'm going through and what is happening at each step:
--------------------------------------
:Connect DEVSQL --This is currently PRIMARY
USE[master]
GO
ALTER AVAILABILITY GROUP [DP-AG-DEV] MODIFY REPLICA ON N'DEVSQL' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = NO))
[Code] ....
All is good so far. Both the Primary and the Secondard have had their logical files changed, which has not taken affect yet because there has been no failover.
--Make SQL10 the PRIMARY
:Connect SQL10
ALTER AVAILABILITY GROUP [DP-AG-DEV] FAILOVER;
GO
SQL10 is now the Primary for this AG. And, as expected, the database [AG-Test] is in "Recovery Pending" because it is now looking for the log file in the new location. I need to move the file to the new location.
:Connect DEVSQL
--Enable XP_CMDSHELL
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'xp_cmdshell',1
[code].....
This is where the script is failing, returning the error:
Msg 1468, Level 16, State 5, Line 5
The operation cannot be performed on database "AG-Test" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
Msg 5069, Level 16, State 1, Line 5
ALTER DATABASE statement failed.
I can not get the dB to recognize the log file at it's new location.
If I restart the SQL Service, it comes back fine, which seems to indicate to me that it is not a permission problem and confirms that the file is in the right place.
How do I force SQL to look for the log file again without restarting the service?
We have recently added a new file group and file on a new drive. We have tested it by moving a small table to the new file group. We would like to relocate a new table to this file group but the table is about (we estimate) 75GB. My question is this: How long can we expect the transfer of data from the current file group to the new one for this table? I understand that depending on our hardware the answer may vary but does anyone have a rough estimate?
The current current (primary) file is located on a DELL SAN and the new secondary group is on a EMC 4700 both are connected via fiber channel.
Also a bonus question would be: Does a "normal" database backup created as a maintenance plan backup the seconary data as well into the BAK file?
Like I said a rough estimate is fine.
Many thanks,
Scott
Hi,
I'm using SQL 2005 express edition and my database files are in the MSSQL.1MSSQLData folder. When I'm trying to copy them I get "Files already in Use" error. How do I stop the server from running (services or command line). Or is there a better why to export
them to a diffrent location.
Thanks
If I use attach/detach to move my log file to a different drive, willit break any of my permissions for the sql logins. If so, how can I fixthis.This all stemmed from needing to setup log shipping to another serverfor redundancy. Step 1 says to create a share where the log filesreside. Well the sql server was installed by a previous employee andthe log files are in the same directory as the data files. I would feelmore comfortable (security wise) if I only shared out the directorywhere the log files lived.If anyone thinks that I am traveling down the wrong path or has anysuggestions, please let me know.Thanks
View 3 Replies View RelatedHow can i move files from one folder to other using script task
pls specify if its clear n can i ask this query in this forum
Hi, I'm fairly new to SQL, so I hope someone can help.
I'm trying to move our SQl data files to a new drive on a SAN. The MDF files are currently located in the E;mssql directory.
I want to keep the same drive letter, so I am attempting to do the following
1: stop SQL
2: xcopy data and ntfs permissions from E: to G:
3: rename e: to h:
4: rename g: to e:
5: reboot Server
when I do this, I get the following errors and SQl fails to start
udopen: Operating system error 3(The system cannot find the path specified.) during the creation/opening of physical device e:datamssqlDATAmodel.mdf.
FCB:pen failed: Could not open device e:datamssqlDATAmodel.mdf for virtual device number (VDN) 1.
Device activation error. The physical file name 'e:datamssqlDATAmodel.mdf' may be incorrect.
is anyone able to tell me what I'm doing wrong?
thanks!
Hi,
I need to move backup files from the production server to another server. This would be regularly scheduled file move only for security reasons. The target server does not have SQL Server installed. Which is the best way to do this?
Thanks
I been trying to learn availability groups since I have not implemented it.
From my understanding you can have more than one group.
Lets pretend we have two groups in one instance:
1. Accounting
2. Engineering
From my understanding you can't make a database in two AG because it wouldn't make sense.
But lets pretending there is one database that both are used by accounting and engineering.
Would you have to make a third AG for future fail overs so that other databases in the other two group don't failover when not needed because when you fail over an AG all the databases inside it fail over.
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?
I need to move the errorlog files from the d: drive to the e: drive on my NT servers. Does anyone know a way to accomplish this without having to re-install?
Thanks
tcb
I like to move my database from a directory to another (on different drives foir exemple).
Is there an easy way to do this kind of job ?
Sorry for the ultra-beginner question, but I've just recently started playing with SQL 2005 Express Edition for a task I've been assigned to at work.
I'm building an ASP.NET 2.0 web site that needs to connect to a SQL database to pull information. I installed SQL Express 2005 and installed the SQL Server Management Studio and I've managed to build a small database with one table.
I noticed that by default, the mdf and ldf files are located in C:Program FilesMS SQL ServerMSSQLDATA (or something along those lines). The website I'm working on is temporarily stored in C:WebSite. I need to move the database files over to C:WebSiteApp_Data so I can access them easily with ASP.NET and VS 2005 Express.
I can copy and paste the mdf and ldf files, but then I can't figure out how to point the SQL Server Management Studio to the new location.
I am obviously just a complete newbie at all of this. It's pretty sad that I can't figure out how to simply move the database from one directory to another on the same server... it makes me worry about when I'll need to move it to the new webserver!
Could anyone point me in the right direction at least?
Thanks in advance!
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?
I have a dataset that contains an EmployeeID, StartDate, EndDate, and Location. Each record tells me at which location and employee was during a payroll period (fortnightly). So the data looks like:
EMP_KEYSTART_DTEND_DTLOCATION
120130117201301318103
120130117201301318103
120130131201302143354
120130131201302148103
220130117201301311234
220130131201302144567
120130214201302283354
220130214201302281234
Employees can be at multiple locations during the two weeks. They can work at one location, stop working there, start working somewhere else, and then maybe go back to their old location. There are duplicate records here as each employee can be at the same location multiple times during the two week period. What I need to capture is the actual start and end date of an employee at each location for each 'assignment'. An assignment is defined as a continual period of employment at a location with gaps of no less than 4 days - if there is a gap of more than four days then that is classed as a new assignment.
I have four instances and each instance have its own Availability Group with its own listener.
Would like to know if you can have one listener for multiple Availability groups?
I am looking for the easiest way of rebalancing data across multiple files.
Instead of creating a secondary filegroup and then dropping and recreating all indexes in the database which is going to take ages (we have a lot of tables and indexes), I am trying to just add more files to the primary file group and then rebalance data evenly between these.
I guessed that adding the new files to the primary file group and then rebuilding all indexes on a table should redistribute the table over these multiple file groups evenly. This is not the case though. It does rebalance data a bit but I still end up with the majority on the first file that existed.
I have attached the script I am running, maybe it is something in the create database/file statements that is the issue.
Basically what I am seeing is to start off with the table is 160MB. I then add the file groups and rebuild all indexes on the table. The first file is then about 100MB and each of the three other files are about 20MB. I would expect them all to be the same size.
We had 3 Availability Groups set up in SQL 2012 last year but they were poorly named so I am just looking to rename them but there doesn't seem to be any command for it that I can find.Can they not be renamed once created? I guess I could just create new ones and move the DB's into them but just thought I would check!
View 1 Replies View RelatedWe have a large Datawarehouse and the size is 50TB.. The tables are placed in filegroups based on the schema like fact, dimensions, raw data each sit on seperate filegroups. I am thinking will it make sense to seperate the large facts which are having billions of rows so that they reside on filegroups on their own..
View 9 Replies View RelatedI have several 2012 availability groups running on a cluster. I have one database that is bulk loaded every 30 minutes. The DB is about 1 GB in size. To be on the availability group it has to be set to full recovery mode, but simple or even bulk would obviously be better. Is there a better way to handle the transaction log size other than to run a backup after each bulk load causing extra overhead? With mirrors you could use simple, but since those are going away . . .
View 2 Replies View RelatedI would like synchronizing all the missing objects (logins, agent jobs, SSIS, and anything else that I've missed) across SQL 2012 Availability Groups.
I would like to be able to able to automate this process....
We have a sorted data that looks like the first 3 columns below, and fourth colum is what I want to create)
Shift_start meal_break shift_endShift ID
1 0 0 1
0 0 0 1
0 0 1 1
1 0 0 2
0 0 0 2
0 0 1 2
I need to find a method to assign unique Shift IDS to rows that correspond to a single shift. For instance, the first shift would begin on the first row when shift_start flag is turned on, and end on the third row when shift_end flag is turned on.
Can I do this in SQL ?...some kind of grouping ?
We have some tables that are bulk-loaded every day and they do not have RI to the other tables in the database.
To ease pressure on the logs, I had the idea of spinning them off to another database on the same AG in simple or bulk-load recovery model and using synonyms to point to them so the code base would not need changing.
I know an earlier bug in 2005 existed that basically made the query analyzer ignore indexes if a table was accessed via a synonym.
We have multiple SQL 2012 SQL servers setup in an alwaysOn availability groups. Where should we schedule the re-index? We have Server1 as the primary and 2 secondaries Server2 and Server3. Are their any tricks to have it run on which ever one is the primary?
View 1 Replies View RelatedI have the need to delete old backup files via TSQL job. Found this solution online:
PushD "
emoteservershareDIFF" &&(
forfiles -m *DIFF*.sqb -d -1 -c "cmd /c del /q @path"
) & PopD
It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?
Is this possible? If so, how is it done?
View 1 Replies View RelatedHow do you load existing DTS Stored Structured files on to a new SQL 2000 Server?
View 1 Replies View RelatedHowdy y'all! :)
I have been instructed to move a large database we have on one of our servers off the current drive (local RAID-5 driveset in the server) to a EMC "drive" (logical drive, off-server).
I know one option is to back up the database, delete the database, re-create the db using the new drive for data/log files, then restore the database.
However, I was wondering if it would be better to just detach the DB, move the data/log files, then reattach to them?
Is it half-doz of one, and 6 of the other?
How should I go about this dastardly deed?
Off to poke around in BOL, but thought I would post first in case it's an incredibly easy answer for y'all
Thanks!
after moving the resource database files with this command:
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'new_path_of_mastermssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= 'new_path_of_mastermssqlsystemresource.ldf');
GO
I am getting this error trying to do anything in the instance while I have it started in minimal mode (/f /t3608)
(the instance WILL NOT COME UP UNLESS I USE THE /F /T3608 from a command promt.. please .. any suggestions??????????????????????
Also.. I checked and the primary file is NOT READ-ONLY.
File activation failure. The physical file name "E:MSSQLKOCSQLDEV01Datafilesmssqlsystemresource .ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
File activation failure. The physical file name "E:MSSQLKOCSQLDEV01Datafilesmssqlsystemresource .ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
Msg 945, Level 14, State 2, Line 1
Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
I have a number of XLS reports in template form. I want to move these to a new location on the File Server and after they have been populated move them to another location on the File Server.
I have seen some proposed solutions but I haven€™t found any that work. This should not be difficult and I envisage using a File System Task and a Foreach Loop Container. However passing the multiple file names to the File System Task errors repeatedly.
Any help would be greatly appreciated!!
Paul Boynton
I'm reading up on Always on and I am confused what is the difference between AlwaysOn Availability Groups and AlwaysOn Failover Cluster Instances.
View 3 Replies View RelatedI have a two node SQL 2012 AlwaysOn HADR cluster (v11.0.3412) with 4 availability groups configured. The AG groups are set to synchronous mode and the secondary is not readable (we do not want the synchronous replica readable so we do not risk any reads causing contention so we maintain fast performance).
On the secondary we are getting a persistent failure with the Data Collector job called Collection_Set_3_Upload. The failure occurs within the second job step. That job step is executing the following command:
dcexec -u -s 3 -i "$(ESCAPE_DQUOTE(MACH))$(ESCAPE_DQUOTE(INST))"
The error message is as follows:
Log Job History (collection_set_3_upload) Step ID 2 Server CLUSTERNODE2
Job Name collection_set_3_upload
Step Name collection_set_3_upload_upload
Duration 00:00:07
[Code] ....
I know I can prevent this error message by enabling readable secondaries, but we do not want this.
I have tried stopping the data collection jobs and purging the cache directory but to no avail. It will succeed the first time then persistently fail again with the same message every time after that.
In addition, if I set the one failing AG group to readable secondary the job succeeds. So that means that 3/4 work fine, only this one is having an issue.