i have Two SQL 2012EE Node , it is installed in VCE Vblook 100 , now i want to move it to another hardware my question is " How i can i achive this task by move Windows 2012 R2 Cluster and SQL 2012 EE Cluster to another hardware".
View 4 RepliesI want to move index file(69 gb) to different location.Right now data and index files are on the same drive(e), I am trying to move index files from e drive to f drive, so I will get 69 gb free on e drive . Can anyone please advise me what precautions I should take and advise best practice.
Thanks in advance
Dear gurus,
I have the reports in https://reports.yyyy.com/reportserver
Now I want to point out to https://reports.yyyy.com
How can I do that?
I dont have any rdl files. Is it possible to move the reports?
I am very new to SSRS
Thanks
Krishna
I would like to move my AG databases to new location using C#.I found article on MSDN which uses SQLCmd utility to do that, but I cant run that utility from my application.I am looking for general steps OR T-SQL if possible so that I can follow the same from my C# application.
View 6 Replies View Related
Hello there,
I've been told that it is good practice to keep mdf and ldf files in another location... We have it in place for all our user databases, however mdf and ldf files for our system dbs are still at the same location. I was wondering what is the right way of splitting those should be?
I have a job which copies .txt files 24 hours 7 days a week to c:TempSource
What I am planning to do is copy the files from one location to another say c:TempTarget
So I have written the Powell shell script and when i put that in the sql agent job i get below error ;
A job step received an error at line 5 in a PowerShell script. The corresponding line is '$filesToMove = $files | Where -Property "Name" -NotLike -Value $newestFile.Name'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'A parameter cannot be found that matches parameter name 'Property'. '. Process Exit Code -1. The step failed.
$sourceFiles = "c:TempSource*.txt"
$targetFolder = "c:TempTarget"
$files = Get-ChildItem $sourceFiles
$newestFile = ($files | sort LastWriteTime -Descending)[0]
$filesToMove = $files | Where -Property "Name" -NotLike -Value $newestFile.Name
$filesToMove | ForEach { Move-Item $_ $targetFolder }
I have a warm standby (secondary server) receiving log shipping files.
The database has 5 files all in the primary filegroup. Two of the files need to be moved from one hard drive to another. Whats the best way / process to accomplish the move and re-establish the log shipping recovery status?
Thanks
Hi,
Sorry for the wide distribution.
I'm trying to find any useful whitepapers about how to effectively build and operate a disaster recovery site at a remote location for SQL Server 2000. Does anyone know where to find such information?
I also know that one good option for my customer is using the Mirroring feature of SQL Server 2005. What are the other options? Is Replication an effective one for a mission-critical database (online banking)?
Thanks in advance
I need to move files for a lot of databases that are all part of an AG. I've used the method at the bottom of this link with success on a small test DB.
View 2 Replies View RelatedSQL Server service accounts; the default (and recommended) service account is NT ServiceMSSQLServer virtual service account - which is shown as MSSQLSERVER locally - if you want to assign permissions to a local location. Fine, that's OK, works.
For network resources, as far as I understand it (or think I do, anyway), a virtual service on a box is made visible to the network using the <Domain><Computer>$ credential. Eg for box Bimble on Domain Wimble - if I want to give access to a directory on backupServer1, I simply assign rights to WimbleBimble$ and SQL Server should get those rights.
This, or so I thought, should allow SQL Server access to that location. The thing is, when trying to access that location, it's throwing an access denied error. But it works fine with LocalSystem.
Do I need to do some additional configuration to allow the Virtual Service account to be shown via <Domain><Computer>$ ?
SQL Server 2012 running under a domain Managed Service Account. (Server A)
File located on a Windows 2012 server in a directory which has been shared to user A. (Server B).
User A is a domain account and is using his laptop, (laptop C) which is using SSMS to run a bulk insert command.
User A (Bulk Insert from laptop SSMS Client) --- > SQL Server (server A) --- > File Server (Server B)
The command fails and is returning Access denied to the file/folder share on Server B.
Running the same command on the SQL Server (Server A), the command works fine, so this is a double hop kerberos issue.
If I use a SQL Login from Laptop C, then the command works fine as the SQL Server will use the SQL's Managed service account to connect to the file share, which is set up for delegation and impersonation.
I am struggling to work out why a domain user cannot bulk insert a file from a remote location. I have checked that the user is connected with Kerberos authentication and they are. All articles seem to talk about setting up SPN's for the SQL Server so that SQL Login authentication can work over remote bulk insert, and just say to set up the file share properties properly if using a domain account.
What I am missing to allow domain accounts to bulk insert remotely, from a remote file share?
How to load files with similar format , from two different locations into same database with same ssis.
Lets say
Location 1: C:LoadFilesCust1APP_123445.txt
Location 2: D:LoadFilescust2VDD_543121.txt
Currently we have one ssis which loads and process files from C:LoadFilesCust1 only. we have to modify the existing package it to load files from Location 2 (D:LoadFilescust2) as well. Also while loading, the ssis should assign a value to existing column CustID depending upon the file name. File names always start with APP_ in first location. VDD_ in second location
Assign CUSTID as 100 if file name starts with APP_
Assign CUSTID as 200if file name starts with VDD_
When adding a node to a SQL Server 2012 Standard edition cluster, how I do I identify the location for SQL server shared components and the rest of the SQL Server installation binaries?
When adding a node to a SQL Server 2012 Standard edition cluster all the binaries went to the C: drive default location. We put those files on a different drive when installing the first node. What needs to be done so both nodes have the binaries on the same drives and folders?
I've configured log shipping to use for DR purposes. I'm concerned that the physical location of the secondary is mis-reported by SQL Server Management Studio.
Viewing the secondary location (with Studio DB_name Properties Files) shows the path of the primary DB (I expected it to show the path of the secondary).
This SQL command shows the correct/actual paths of both primary and secondary DB's when run on their host servers.
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files
Is this just cosmetic?
Here is an Example of how the Studio shows the incorrect path for the secondary.
Example:
(Primary) servername=prodSrv, DBname=aquaDB, Actual_Path=G:aquaDB, SQL-studio-Properties-Path=G:AquaDB, sys.master_files Path=G:AquaDB,
Log shipped to
(Secondary / Read Only) servername=DRSrv, DBname=aquaDB, Actual Path=F:aquaDR, SQL-studio-Properties-Path=G:AquaDB(WRONG), sys.master_files Path=F:aquaDR
is there a way to change SSMS 2012 default query save location?
Scenario:
1. Open SSMS
2. Create new query
3. Click Save
I see "DocumentsSQL Server Management Studio" folder, but I want to change it to be "d:". How do I do this?
I tried:
1. [URL]- in folder "DocumentsSQL Server Management StudioSettingsSQL Server Management Studio" there is a file NewSettings.vssettings, setting it to "d:" or "d:" didn't work.
2. Changing HKEY_CURRENT_USERSoftwareMicrosoftMicrosoft SQL Server100ToolsShell VisualStudioProjectsLocation didn't work too. There is no "Shell" under "110Tools"
Did I do something wrong, or is there another way?
I have a table attendance_details in both database DB1 and DB2, i need to move 01/7/14 and 02/7/14 records from db1 to db2, My table contains
employee_no INT,
date_of_attendance datetime,
present varchar(20),
shift_type VARCHAR(20),
marked_by VARCHAR(50)
Is there a way to move the Distribution database (that is currently on the publisher server) to a different server without having to reync the subscriptions.
View 0 Replies View RelatedI would like to know that how can I move 70 plus tables that are on sql 7.0 to sql 2012 via SSIS.I know its a two step process but what is the best route and how I can process.
View 0 Replies View Related3rd party company set up SQL 2008 R2 SP1(awhile ago, years), they disabled SA no other logon was created with Admin rights, Database is Mixed Mode, never had a DBA. the database is from a Transactional system. Now comes me. I need to set up backups, maintenance etc..
I have been provided System AdminLocal Admin rights on the Server. The Server has been set up like a Domain Controller. I have tried all of the command line options that I know and I can find. I have not been able to get any to work, they all fail login to SQL. Before I take the route of reinstalling SQL on the server I want to make sure I haven't missed any thing, especially since this is a 100% production environment with 3rd party processes and applications attached that are not documented or supported. (nightmare)
I would prefer a cmdline or PowerShell script (ps2).I figure that the holiday is coming up so whatever I will end up needing to do I will be able to complete over the holiday weekend when it is down.
One set I tried as "run as Admin"
SQLServr.Exe –m
SQLCMD –S <Server_NameInstance_Name>
CREATE LOGIN ‘<Login_Name>’ with PASSWORD=’<Password>’
GO
SP_ADDSRVROLEMEMBER '<Login_Name>','SYSADMIN'
I should point out the rest of our environments are SQL 2012
One of our DB is in recovery state due to insufficient space in drive where the log files resides. Is there anyway we can come out of this situation?
View 9 Replies View RelatedI have set up an FTP connection that tests successfully. I can log on to the FTP site with the same credentials and see my root folder, and within that, two more folders. In my FTP task, I want to receive files to my local machine. The problem is that the only remote path available is not at the root level, and the only thing I can see are files from one of the child folders, but not the child folder I want.
Is it possible in the remote path to change folders? The remote path just shows a "/", which I thought would be the root level, but it is somehow linked to a child folder.I've tried various combinations to get to the folder I want, /root folder/child folder, but that gives the error that the folder does not exist.
If I open the FTP Task Editor, click on File Transfer, and click the ellipses for the Remote Path, the Browse For File box opens, with a "/" in the Location section, and a list of files in the child folder that I do not want to be in. If I click the "Up Directory" button, I get the message "Already at top level directory".
how I can get the files from one particular folder on the FTP site?
How do I recover point in time if the database is participating in transactional replication and is a publisher.
View 0 Replies View RelatedI have a database In Recovery.
Where do I check the completion status of the recovery?
Discovered that a geo-spatial AlwaysOn HA database (1 of 4) was not synchronizing as at a point in time earlier in the day. Suspend Data Movement appears to be working perpetually without finishing. The SQL Server services is in a Pending Changes state after an attempt to restart it from SQL Configuration Manager. The Cluster Dashboard says it is in a Not Synchronizing state, with only the one database in question having a yellow triangle, all 3 others show green.
The warning for the cluster is:At least one availability database on this availability replica has an unhealthy data synchronization state. If this is an asynchronous-commit availability replica, all availability databases should be in the SYNCHRONIZING state. If this is a synchronous-commit availability replica, all availability databases should be in the SYNCHRONIZED state. There is no abnormal data movement from the primary to the seconday.The warnings for the unhealthy database are:
The data synchronization state of this availability database is unhealthy. On an asynchronous-commit availability replica, every availability database should be in the SYNCHRONIZING state. On a synchronous-commit replica, every availability database should be in the SYNCHRONIZED state.Either a database administrator or the system has suspended data synchronization on this availability database.So how to get this database back to synchronizing state?
Trying to add a second replica to my SQL 2012 AG. for some reason i cant click the next button.
View 3 Replies View RelatedI have a windows 2012 cluster environment that consists of two SQL servers nodes with Quorum disk configured as witness.
Manual failover between nodes is working fine, however the sql instance virtual is not seeing the Quorum disk.
Moreover the Quorum disk has the same number as another cluster storage disk, is that considered a problem?
When I move the SQL instance from a node to anohter, should the Quorum Disk change ownership as well to that destination node ? if it is not changing ownership what would be the problem??
I am using SQL Server 2012 SE.I am trying to delete rows from a couple of tables (GetPersonValue has 250 million rows and I am trying to delete 50Million rows and GetPerson has 35 Million rows and I am trying to delete 20 million rows). These tables are in TX replication.The plan is to delete data older than 400 days old.
I tried to move data to new tables from the last 400 days and it took me like 11 hours. If I delete data in chunks of 500000 then its taking a long time to rebuild indexes(delete plus rebuild indexes 13 hours). Since I am using standard edition partition wont work.
find ddl below:
GO
CREATE TABLE [dbo].[GetPerson](
[GetPersonId] [uniqueidentifier] NOT NULL,
[LinedActivityPersonId] [uniqueidentifier] NOT NULL,
[CTName] [nvarchar](100) NULL,
[SNum] [nvarchar](50) NULL,
[PHPrimary] [nvarchar](50) NULL,
[code]....
Today we were trying to use restore wizard (recovery advisor) to restore a database which is over 80 GB, but the wizard won't load or start. However the wizard dialog box opens without any problems with all other databases stored in the instance. Is there any problem or reason to why the restore wizard won't come up?
This is SQL 2012 enterprise version we are running.
I have a SQL cluster which ran out of disk the other day. I threw plenty more disk at the SAN. However, 1 of the databases came back with Recovery Pending and has been over 24 hours. I have tried to backup the database with no success. I don't have a clean backup from the day it occured so would have a good amount of work missing.
Running a dbcc check returns the following:
Msg 945, Level 14, State 2, Line 1
Database 'sqldb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
I have not tried a dbcc check with REPAIR_ALLOW_DATA_LOSS as there seems to be a high risk of losing whatever was going on. how I can get the db back online?
I'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 are trying to restore from a backup (.bak) of a sql 2005 database with full text catologs to sql 2012.
We are getting errors saying that the backup file context are different from what we are trying to restore to, even if we try to create a new database from the backup.The new database is just a test database. The source is a production database with full cataologs.
The normal restore from a bak file does not seem to work.
We dont need the full text catalogs in the new database, but cannot change settings in the source production database.
Is there a way to take a backup of the sql 2005 database without the full text cataologs being included ? , without changing setting in the source database beforehand ?
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 Relatedis bulk logged recovery model support point in time recovery
View 9 Replies View Related