I've stepped into a new environment and have never dealt with multiple data files on user databases only with Temp db.What would be the best way to get all my data files in sync. I have done this on databases that aren't that big in size or off in size by a lot. Here is what I have
Mdf -- 69 GB
ndf -- 3
ndf -- 3
ndf -- 3
ndf -- 3
ndf -- 4
ndf --4
ndf -- 2
(SQL 2012 Standard)
I have a database with one 320 .mdf file and one 200GB .ndf file.
I would like to reconfigure my database to have four 200GB .mdf files. How do I get from here to there?
I'm working on an application that will be running on two or more machines. Each machine is logging data to a local SQL Compact database. I would like to make these two computers sync the data with eachother, but I am unsure what the best method is to transfer data between machines in a table format. I think XML would work well, but there may be something easier, so I thought I'd ask. Do I want to use sockets to do this, or is there another type of connection that would work better?
All PCs will know the IP addresses of their peers. I forsee each PC having a server thread that is listening all the time. Each PC also has a client that checks to see if it can connect to the server on the other side. It then requests certain data, which the server part sends. Does this sound right?
The rest of the app is in Visual Basic 2008 Express.
Thanks,
Lance
hello everyone,
i am developing a application using C#and .NEt compact framework.
for that i need to sync pda data (in xml format) with the remote database server(SQL) and vice versa.
please help me out
thanks in advance.
I'm having an argument with our infrastructure architect who has just gone and bought lots of SSD drives to use for our tempdb data and log files, sounds great doesn't it? There is a catch though, his plan is to add the disks to the two available slots in each blade in a RAID0+1 configuration, effectively giving you one usable drive, and adding both data and log files on to one disk.
I then pointed out that SQL Server best practice is to host tempdb data and log files on two separate drive to reduce contention. The architect then basically said that because this isn't spinning disk the issue of drive, r/w contention isn't an issue I don't agree with this and wanted to get some opinions from the community, I'm still advising that two separate disks should be used but someone just went and spent £80k ($150k) on SSDs and doesn't want to back down...
I can detach/attach SSAS database.But I have a software that protects(backs up) the files of the SSAS Database.
What the customer needs is to be able to take these backed up files and port it to a different server and attach it there.But the new server complains that these files have no corresponding detach-log files.
The customer doesn't want to backup and restore the SSAS databases.
I have 2 server PROD n DR server all the PROD server DB is synched to DR server through log shipping. Now to chk whether the DR server is working or not in case of any disaster we shut down the PROD server now all the app request is handled by DR server,it is working fine. For 1 week we work in DR server now its time to move back to PROD server so that all the 1 week data in DR server should be replicate back to PROD server. Log shipping is deleted once the DR Server is up. What r the options other than Backup? It is SQL 2012
View 4 Replies View RelatedI have a small project to be done in which I need to fetch the pdf file from a my system and save it in database and also fetch the name of it and save it in the database.
View 9 Replies View RelatedI am using SSIS to load raw files into database. In my files I have columns Date which has format
1/1/2010 12:00:00 PM.
I want to load this column in format 1/1/2010 24:00:00. I mean in 24 hour format.
Been practicing DR strategies with a test SQL instance by following the scenarios listed here: [URL] ....
> Took a backup of the Model database
> Stopped SQL Server
> Deleted model database data & log file
> Started SQL Server and it obviously wouldn't start because TempDB needs a model database present.
> Started SQL instance with trace flags 3608 & 3609
> Connected to SQL instance using command prompt.
> Issued restore command but was met with this error:
Shared Memory Provider: The pipe has been ended.
Communication link failure
And found this in the SQL log..
2015-08-12 16:21:32.83 spid51 Starting up database 'tempdb'.
2015-08-12 16:21:36.88 spid51 Error: 3456, Severity: 21, State: 1.
2015-08-12 16:21:36.88 spid51 Could not redo log record (59:136:21), for transaction ID (0:0), on page (1:20), allocation unit 458752, database 'tempdb' (database ID 2). Page: LSN = (30:165:3), allocation unit = 458752, type = 1.
[Code] .....
sync values from table to calender. I got SQL Server 2012 and Outlook 2013.
I want to know is there any way all appointments booked in table should sync with exchange server automatically.
I recently set up a SQL 2012 FCI with a NetApp fileshare to store the data files. The install worked just fine, but I can't run an integrity check for any of my databases. Whenever I try, I get these error messages:
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 8, Line 1
A database snapshot cannot be created because it failed to start.
Msg 5120, Level 16, State 104, Line 1
Unable to open the physical file "path-to-fileshareMSSQL11.MSSQLSERVERMSSQLDATAmodel.mdf:MSSQL_DBCC12". Operating system error 1: "1(Incorrect function.)".
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.The error message suggests SQL had a problem creating the snapshot, but I checked through some NetApp documentation for configuring SMB 3.0 for SQL.
I understand that we shouldn't shrink data files as it might cause heavy fragmentation along with log usage, high IO/CPU etc.
In a DB in which lot of DML transaction occur, there will be empty spaces whenever deletions occur.
Will SQL Server fill that part with data when new insertions occur ?.
When opening .sql files, I get a connect to database engine prompt every single time. how to stop this from prompting vs. just using my current active connection?
View 4 Replies View RelatedI am currently investigating aa high avg write time ms issue (145ms) which seems to be only occuring on the tempdb data files.I have followed the recommended setup of TEMPDB in that
1. Data files = number of physical cores
2. Data files and logfiles are on separate partitions away from the other databases.
3. Tempdb is presized and no incremental file increases look like they are happening with frequency.
We have sharepoint 2012 setup on other sql servers and with TEMPDB setup following the same guidelines, with far more Sharepoint activity on a similary specified hardware which is why its confusing.FileIO auditing on the partitions themselves shows that the FileIO is very fast on the partitions that the tempdb data file which leads me to beleive that Sharepoint may be the culprit perhaps due to excess use of tempdb with operations taking a long time to resolve.
LocalDb cannot restore a backup whose original data and log files are in different folders
[URL]
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?
Got following query:
SELECT
event_data.value('(event/data/value)[4]', 'bigint') AS cpu_time,
--database name
event_data.value('(event/data/value)[5]', 'bigint') AS duration,
--estimated cost
--estimated rows
--nest level
[code]...
Basically, is a simple T-SQL query that reads the local file for my already setup extended event sessions. But I can't find the way to retrieve the following attributes as part as the T-SQL query:
--database name
--estimated cost
--estimated rows
--nest level
--object name
I am trying to find a BOL or some MS link with the full list of possible values for event_data.value but can't find one.
I am testing out a blank database created over two physical files on two separate disks with one table called data which has one column called values nvarchar(max).
I filled the table up with a whole load of data and ran a select * against it. If I run Permon at the same time I can see that the read load has been spread over multiple disks as each of these disks is getting read from in parallel. If I create the same database on a single file and run the same select * again it takes much longer, proving that the read load has been distributed across multiple disks.
Now moving onto writes, this is where the confusion lies. I understand that SQL server fills files evenly until they need growing, after which it will then fill files individually until they are full in a round robin fashion unless you have trace 1117 turned on. What I don't understand is why the writes aren't distributed out whilst it is filling these file groups.
I ran an continual insert into my table with go 1000000 to monitor how the files are being filled up. I monitored where SQL server was physically placing the files as they were being inserted by running the following query:
;WITH CTE AS
(SELECT
sys.fn_PhysLocFormatter (%%physloc%%) col1,
RIGHT(LEFT(sys.fn_PhysLocFormatter (%%physloc%%),2),1) AS [Physical RID],
DATAID
[Code] ....
I could see that it would a thousand or so records into file 1, then a thousand or so into file 2, then a thousand or so into file 1 etc etc. In another words it would hit one disk, then another disk, then back to disk one to fill the file evenly. Is there any way to make SQL Server distribute the writes out in parallel so that both disks are writing in tandem?
By the looks of it, multiple disks only scale reads, as with writes only one disk is ever written to at once which is annoying. Any way to harness the write power of multiple disks?
After failing over to the DR replica. All databases are out of sync. DR replicas were setup as async the other 2 are set up as sync. Is this by design. No data has been updated to any of these as they are test dbs. So all dbs should be the same, no data loss.
View 0 Replies View RelatedI checked the server and found that LS restore job failing and Backup and copy jobs running fine without any issue. and also observed that Copy folder the trn file existing on secondary server. i try to restore trn file im getting the error. and observed that last log backup file that it restored at the secondary database on May2nd,2015.
2015-06-02 12:25:00.72*** Error: The log in this backup set begins at LSN 761571000000022500001, which is too recent to apply to the database. An earlier log backup that includes LSN 721381000002384200001 can be restored.
From Restore job histort details below.
Message
2015-06-02 12:25:00.72*** Error: The file 'xxxx\_20150530104503.trn' is too recent to apply to the secondary database 'database'.(Microsoft.SqlServer.Management.LogShipping) ***
2015-06-02 12:25:00.72*** Error: The log in this backup set begins at LSN 761571000000022500001, which is too recent to apply to the database. An earlier log backup that includes LSN 721381000002384200001 can be restored.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
2015-06-02 12:25:00.73Searching for an older log backup file. Secondary Database: 'database'
2015-06-02 12:25:00.73*** Error: Could not find a log backup file that could be applied to secondary database 'database'.(Microsoft.SqlServer.Management.LogShipping) ***
2015-06-02 12:25:00.74Deleting old log backup files. Primary Database: 'database'
I 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?
We have 1 TB database and we recently got space so
1) can i add data files and put in different disk in production hours
2) what are the effects of doing this.
JUst want to get expert advise
Hi,
Here's my problem, i need to create a task that reads a folder looking for new files, if the folder contains new files, the task should read the files, extract the data and insert it into a database. I read something about the Bulk Insert Task, but i didn't understand it very well.
If someone can provide me code example or a more detail explanation i would be very grateful.
hi,
we currently have a web server/db on-site and a similar backup machine across the country. we're using a pretty bad backup strategy. we zip up a backup file, thats done on the main server every night, then ftp over to the back up server then run a restore.
lately ive been having problems with the main server. i would like to have the machines sync'd up as close as possible.
ive searched the threads and noticed replication and log shipping to be the most practical, but im assuming most of the machines are on the same domain.
what would be my best bet for machines connected over the internet, no vpn?
also the db on the main machine get queried/inserts all day from 7am - 6:30pm
thoughts?
thx
I want to synchronize two databases. Is there a query or a tool to synchronize them?
View 3 Replies View Related
I have two databases data1(Express 2005) and data2( Enterprise 2005). Data1 is a subset of Data2. Now beginning from that state any changes to either database should be reflected on both of them.
Now what's my best solution to accomplish this considering one is the subset of other??
Thanks
Hi,
i am working with windows mobile Cf 2.0 and desktop. i got problem when i were trying to synd mobile database and ms-access database.
my requirement is online i want to transfar data records from mobile to desktop vice versa.
but how? i dont knwo any body would like to guide me.
i am ready to accepting yours valuable views.
waiting
bye
Rajat.
Hi
Scenario: In general user will take database backup from database server to local system. User perform some changes to his local database based on his requirement. Again user connects to database server and click on update, local database should update to server database by informing each record status to the user. ex: record 1 updated / call closed do you want to update?
Here local database is going to be Pocket pc database and server database is on SQLDatabase. Can any one suggest best way to implement this feature in Windows Pocket pc, c#. I would appreciate your reply.
Thanks,
I am learning SQLServer Integration Services.
I created a file People.txt containing firstName, LastName seperated by a pipe.
------------------content-----------
John | Doe
Mike | James
Adam | Smith
-----------------------------------------
and another one called gender.txt
------------------content-----------
M
---------------------------------------
I will would like to create integration services package that compines each record of the first file with the record of the second file and inserts the result into table.
--------------Result table content------------------
John
Doe
M
Mike
James
M
Adam
Smith
M
-----------------------------------------------------------
Thanks
I have a requirement to
a. Read data from Different CSV files.
b. insert and update data to Data base in multiple table using joins.
This execution runs for 1-2 hours.I can use C# with Ado.net, but only concern I see is if in between execution fails due to some connection or other error. All insert data has to be cleaned up again.I feel writing and Store procedure inside transaction, which will take path's for CSV file as input and insert data in database. using transaction we will have flexibility rollback to original state.
hi.
I have two database( one on local system and another on my host ) there are same in table and stored procedure. i just want to sync data between there in my application ( ...on button on click event ! ). please help me to do it. tnx
SQL Database Schema Sync
We have development and production databases, when we change database schema in development database how can I synchronize it to production without creating data problems in production, is this possible in SQL2005? Or any external tool I can use?