Hello, we are trying to find a way to send all SQL traffic from multiple machines in a DMZ trough only one point. This way the firewall doesn't need to be opened to each and every machine.
This may be a simple question but I have never done it before. Any recommendations?
We have a Customized share point application with Very minimal data usage and we have used only 5 to 6 lists and libraries only in the share point.
Configuration is
Clients -- fire wall --- Load Balancer ---- WF1 and WF2 --- SQL DB
ROUTING IS VIA FIRE WALL.
SUDDENLY THE SITE GOT DEAD SLOW AND UNABLE TO TRACE THE PROBLEM AS EVERY THING LOOKS FINE.
Checked with the firewall Team and they stated its fine from their end & even we have verified the counters, CPU, Memory & Page life expectancy, buffer counters all looks good and even we do not have huge data in the database. We have only 50 concurrent users are working...
I set up DB mirror between a primary (SQL1) and a mirror (SQL2); no witness. I have a problem when I issue command:
alter database DBmirrorTest Set Partner = N'TCP://SQL2.mycom.com:5022'; go
The error message is:
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.
I have the steps below prior to the command. (Note that both servers' service accounts use the same domain account. The domain account I login to do db mirror setup is a member of the local admin group.)
1. backup database DBmirrorTest on SQL1
2. backup database log
3. copy db and log backup files to SQL2
4. restore db with norecovery
5. restore log with norecovery
6. create endpoints on both SQL1 and SQL2
CREATE ENDPOINT [Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER)
7. enable mirror on mirror server SQL2
:connect SQL2
alter database DBmirrorTest
Set Partner = N'TCP://SQL1.mycom.com:5022';
go
8. Enable mirror on primary server SQL1
:connect SQL1
alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go
This is where I got the error.
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy
Assuming all windows servers belonging to the WSFC are on the same subnet, will the AG listener become a single failure point in system if all Application servers connect to the AG through the listener? If the answer is yes, what are the options to resolve this issue?
First off, I appreciate the time that those of you reading and responding to this request are offering. My quesiton is a theoretical and hopefully simple one, and yet I have been unable to find an answer to it on other searches or sources.
Here's the situation. I am working with SQL Server 2005 on a Windows Server 2003 machine. I have a series of databases, all of which are in Full recovery mode, using a backup device for the full database backups and a separate device for the log backups. The full backups are run every four days during non-business hours. The log backups are run every half hour.
Last week, one of my coworkers found that some rarely-used data was unavailable, and wanted to restore a database to a point in time where the data was available. He told me that point in time was some time back in November.
To accomplish this, I restored the database (in a separate database, as to not overwrite my production database) using the Point in Time Recovery option. I selected November from the "To a point in time" window (I should note that this window is always grey, never white like most active windows, it seems), and the full database backup and the subsequent logs all became available in the "Select the backup sets to restore" window.
I then tried a bevy of different options from the "Options" screen. However, every restore succeeds (ie: it doesn't error out), but seems to be bringing the database back to a current point in time. It's never actually going back to the point in time I specify.
My questions are as follows:
a) Is it possible to do a point in time recovery to a point in time BEFORE the last full database backup?
b) If so, what options would you recommend I use? (ie: "Overwrite the existing database", restore with recovery, etc etc).
I again appreciate any and all advice I receive, and I look forward to hearing from anyone and everyone on this topic. Thank you.
What sort of traffic would be going on between the machine I am on Enterprise Manager and have a server registered (my local machine) to the actual SQL server? I haven't logged onto the actual SQL machine I only have it listed as a registered server and it's sitting there in the list with it's little green "started" icon.
I'm asking because i'm getting accused for a server going down. I opened my EM around the same time as this SQL server went down and some network people see my machine talking to the SQL server at the same time.
Hi,The application setup is: Access frontend + ODBC (TCP/IP) + MSSQL7Few days ago i was informed that the app generates high networktraffic. Thusi started to look for some savings.I found out (sniffer) that even if the client is idle every 30sek. aTCP packet comes from client to server, then answer goes back. Thepacket seems to be empty (TCP data is 1 byte: 0x00), the answer isempty as well. Seems like kind of watchdog. And it does not seem to berelated to ODBC refresh and any other option possible for setup inAccess client (I set those settings to different that 30sek. values,and 30 sek. traffic still exists).Does anybody know what those packets are for, what module isresponsible for them and how to change interval?My best regards for anybody reading this post,Mirek
I am having a problem and this may be more of a network issue bandwidth issue: When I ping from one of my dataservers to another dataserver it takes 10ms. However, if I ping from a fileserver to a dataserver it takes 1ms. Also, if I ping from a Citrix server to another Citrix server or one of the data servers, it also takes 10ms. What are my options?
Can anyone HELP!! I have a system set up with 2 servers, the master server replicating the data to the backup server. Each server has two network cards, 1 which links directly to the other server. The problem is the replication is being routed round the network making the direct link useless. How can this be ammended!! They are both logging into the same primary controler.
OK, I'm troubleshooting a performance issue on one of our production servers, but I really can't t-shoot and tweak in real-time, so I took a snapshot of "a day in the life of" my production server using the profiler.
I want to run the sql commands I collected on another server I set up identical to production, and play around there.
Does anyone know of a good tool to "replay" those captured commands onot another server?
Each have two Gig Network cards configured with different IP addresses.
Each running multiple instances of SQL Server 64.
I am trying to set up a mirror where mirroring traffic between servers will be dedicated to a secondary IP address on the second NIC.
I am also trying to avoid Windows authentication.
Interesting enough: Security Configuration screen suggesting you to use fully qualified TCP addresses and, at the same time, does not give you such an option...
Would someone please point me in the right direction?
I have a database of details of users that visit my site. Among other things, it saves the ipaddress and the date/time of the visit. I would like to be able to determine the unique visitors (by ipaddress) that i have on any given day. The problem though is that the data is saved as a date and a time, thus when I try and determine the unique visitors, since they are all a different time, MSSQL thinks they are all unique visitors. Any ideas how I can get around this without acctually modifying the data in the underlying table?
I can sucessfully encrypt/ decrypt 1 column, but Im under the impression there is also a way to encrypt the data being sent over the network by using a certificate? I can find lots of info, but no starting point or clear cut instructions. Could someone please assist?
I am attempting to duplicate a nifty feature that one of my colleagues used:
We have parameters where a user can select a client or an individual account. There are available values for each parameter from two queries. I tried to have the available values in the account list dependent on the user's selection in the client list by passing the client parameter's value to the query for the account data set and using it in a where clause, but I get a "forward dependency" error.
The weird thing is that my colleague tried the same strategy and it worked. We together tried to set my report up the same as his, but cannot find why his works and mine doesn't. Any ideas?
His report works when deployed from my machine, so there must be something in the actual report that is different...
Hello all - I am trying to come up with a reasonable solution to an intermittent network. Specifically, I have a SQL Server 2005 installation that receives data from a client across a wireless network via OLE-DB. There are times when the network connection may not be available, and I would like to buffer the transaction (somehow!) locally, and then have it passed to the database upon re-establishing connectivity.
Any suggestions or recommendations would be greatly appreciated!
I looking for a way of measuring how many MB of outbound SQL data is being transferred during the course of a day from an OLTP instance.  Data is pulled from this server by a DW in the same domain and adhoc via a linked server. I'm looking at perfmon but nit sure if this is the correct tool and if so which counters to use.  I'm looking for the total MB/GB over the period not the MB per sec.Â
Would anyone have a suggestion on how to setup a partner to partner NIC configuration for heartbeats/mirroring traffic? I've been told this is the recommended setup but have not found much on how to do it. We currently have a teamed NIC config for redundancy, but would like to have a separate set of NICs on each partner so that mirroring traffic is not interrupted by any regular network traffic.
We also have a witness running in full safety mode. Does this mean partnerA and partner B both need NICs with a crossover cable between them AND is it recommended for the witness to also have extra NICs to both partnerA and partnerB (w/ crossover cables)?
Any suggestions/help/links on properly configuring this would be appreciated.
Hello All,DDL Statements:CREATE TABLE [dbo].[Table1] ([MyDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOI have a varchar column which represents dates in YYYYMMDD and MM/DD/YYformats. If I query using:SELECT MyDate FROM Table1WHERE DOB LIKE '________'Why aren't the dates in MM/DD/YY returned ? Is the / a specialcharacter in T-SQL ?Thanks in advance
I assume that MS has a directive never to change the format of SSIS raw files...
However, what I'd like to know is that when I'm planning long-term systems where I've got backups of data (staging, logging, whatever) using raw files, can I be assured that future versions of SSIS will be able to read those raw files?
I assume a certain level of backwards compatibility, however, I'm just curious if I should think about building processes into my projects that would factor that in and rebuild raw files everytime a new/major release of SSIS comes out.
Ive read a few docs and i have some doubts about the implementation i ll make.
I will setup a FCI between ServerA and ServerB. So my databases will be running on server A or on ServerB. they will be replicated using AllwaysON to ServerC.
Because ill use a diferent vlan for AllwaysON replication iam having some doubts on how can i set the endpoints...
For example.
I ll have the following ips configured for AllwaysON replication on Server A, Server B and Server C 10.10.240.1/10.10.240.2/10.10.240.3
Database teste will be running on serverA. the endpoint configured will be 10.10.240.1 but when database fails over to Server B (using FCI) the ip configured there will be 10.10.240.2... so AO replication will stop working.
I tough on associating a virtual ip to the AllwaysON cluster resource but Microsoft doesn't recommend to alter AllwaysON cluster resource.
I have been told that I need to setup a crossover cable between a Server that has an application to have a dedicated NIC (using a crossover cable) to a server that has SQL 2005 Sp2 for fast access. How is htis done as far as SQL Properties, and NIC Settings, How do I make sure SQL will transmit using that 2nd NIC?
alter proc [ProGeneral_College_Structure] @Year nvarchar(4) as begin DECLARE @SQLStatement nvarchar(1000)
Set @SQLStatement = 'SELECT School AS Level1Code, DIVISIONS.Div AS Level2Code, DIVISIONS.ProgArea AS Level3Code, DIVISIONS.progName AS LevelName , ' + SUBSTRING(@Year,1,2) + '/' + SUBSTRING(@Year,3,2) + ' AS AcademicYearID FROM DIVISIONS WHERE (((DIVISIONS.[' + @Year + '])=1)) ORDER BY DIVISIONS.School, DIVISIONS.Div, DIVISIONS.ProgArea'
EXEC(@SQLStatement) end
It's something to do with the / concatenation I think, is it an escape character or something tried // obviously and CHAR(47). before I get comments I know it's dynamic sql and it's not great but I can't edit the divisions table so have to use a dynamic column.
When you restore a backup from a point in time, how do you then knowwhich transaction ID to start with when you want to roll forward fromthat point in time to another point in time?
I want to set a Report Parameter on a field. The Report Parameter is called 'filter'. In the statement I put the Report Parameter in the WHERE-part: WHERE ([DatabaseName$TableName].[FieldName] = @filter). After this I set the 'Available values' on the Report Parameter in the lay-out to Non-queried. When the report is running, no problems.
But.....
Now I want to set 'Available values' on 'From Query' and refer to the data set, so the user can choose on which value he want to filter. But now, after running the preview the following error displays: Error 1 [rsInvalidReportParameterDependency] The report parameter €˜filter€™ has a DefaultValue or a ValidValue that depends on the report parameter €œfilter€?. Forward dependencies are not valid.
Why can't I set the Report Parameter to 'From Query'? Anyone any suggestions???
(you can see the rest of my statement here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1098540&SiteID=1)
I am trying to get Event Forwarding to work between two SQL 2000 servers. Both are running SQL 2000 Enterprise SP4 and Windows 2003 Enterprise SP2. I am managing them from my local desktop running XP. I have both servers registered in Enterprise Mgr.
<Server XInstance Z> (named instance) is the server on which I have a MAPI client installed and configured along with SQL Operators and SQL Alerts. <Server Y> (default instance) is the server from which I want to forward events > Sev 17.
<Server XInstance Z> is set up to receive error msgs 9002 and 1105 and email operators that either a log is out of space or a data file is out of space. It has been verified on <Server XInstance Z> that it will send locally generated errors to the operators correctly.
<Server Y> is set to forward to <Server XInstance Z> any event that is sev 17 or greater. However, when I simulate an 1105 on <Server Y> I get this error msg in its Application Event log:
Event Type: Error Event Source: SQLSERVERAGENT Event Category: Alert Engine Event ID: 316 Date: 6/4/2007 Time: 11:37:55 AM User: N/A Computer: <Server Y> Description: Unable to open the eventlog on forwarding server '<Server XInstance Z>' (reason: Sockets error 11004).
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
I figured this may be due to the fact that SQLAgent is trying to forward a Windows event to another Windows server running SQL but there is no default instance of SQL Server. So I thought I would add a bogus entry to my Enterprise Manager that was just <Server X> with no instance name. Obviously, EM failed to connect but asked if I wanted to register anyway. I chose Yes. So I was then able to change the server in Forward To on <Server Y> to <Server X>.
That actually worked and it began forwarding error messages to to <Server X>. But the error messages are going to the System error log as opposed to the Application error log where I need for them to go. This is the message from the System error log on <Server X>:
Event Type: Error Event Source: MSSQLSERVER Event Category: Disk Event ID: 17052 Date: 6/4/2007 Time: 11:32:40 AM User: N/A Computer: <Server X> Description: The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Error: 1105, Severity: 17, State: 2 Could not allocate space for object 'MyTestTable' in database 'Northwind' because the 'PRIMARY' filegroup is full.. Data: 0000: 51 04 00 00 11 00 00 00 Q....... 0008: 0c 00 00 00 43 00 48 00 ....C.H. 0010: 41 00 52 00 4c 00 45 00 A.R.L.E. 0018: 53 00 53 00 2d 00 58 00 S.S.-.X. 0020: 50 00 00 00 0a 00 00 00 P....... 0028: 48 00 4f 00 55 00 34 00 H.O.U.4. 0030: 51 00 31 00 31 00 30 00 Q.1.1.0. 0038: 35 00 00 00 5...
I'm stuck here now. Anyone else run into this problem?
I'm running SQL Server 2005 on a Server 2003 machine serving both our home network as well as a remote site through a point-to-point T1. While file transfer speeds are up to par, the remote site's interaction with SQL Server (Point of sale system) is very slow. After testing I am certain that it has nothing to do with the actual physical machine in place neither is it an issue with the program itself since speeds are as they are supposed to be over the home network lan. It seems that there might be a packet size issue or something of the sort. Has anyone dealt with this before or have any thoughts?
I have a client that I provide financial modelling services to (using Excel). They have a requirement to start capturing subscriber movements in their SQL DB. how the table should be set-up and how to extract the necessary movements report. This is largely so that I may include these components in some of the financial models that I am working on.
Subscribers are reported as follows:
Opening subs (the prior periods closing balance; or the sum of new sales at point of 1st entry)
+ New Sales (new subs)
+ Upgrades (movement from a lower product package to the associated package)
- Downgrades (movement to a lower product package from the associated package)
- Churn (subscriber losses)
Closing Balance
All transactions are captured against a specific product package, on a specific date (ymd), and for an associated platform (e.g. digital TV, broadband TV, cable TV).
I believe we only need to capture new sales, upgrades, downgrades and churn. And then used a SP to compile the movements behaviour as described above.
So perhaps the table would appear as follows:
Platform Package Date Movement Value
DTV PROD 1 2014-11-02 New Sales 8
DTV PROD 1 2014-11-02 Upgrades 1
[code]....
So I am assuming that given a table such as the above, we could write a SP to produce an output such as (note, below looks at monthly total so will not agree back to sample above which contains only 2 days):
Platform Package Movement September October November
DTV PROD 1 OPEN 600 676 776
DTV PROD 1 New Sales 92 106 88
[code]....
how one is best to accumulate the balances given that the open date for any given reporting period is in fact an accumulation of all balances since day 1.
How does one typically capture this type of thing in SQL?
I am working on a rewards program and I have a table whenever customer completes a trip, his total fare,business points earned for that particular trip and respective Promotional points gets inserted.
Now I have a scenario whenever customer business points accumulates to 10 then need to award 3 promotional points.
If Business Points=14 for a single trip then for the first 10 points respective Promo points will be awarded and the remaining 4 points should get carry forward for the next trip and this 4 points should get accumulated with the next trip Business Points and so on.
Basically need to check for every 10 Business points accumulated award some Promo points and carry forward remaining points.
Here is the sample table structure and data :
CREATE TABLE [dbo].[tblRedeems] ( [Mobileno] [varchar](50) NOT NULL, [TripNo] [int] NOT NULL, [CustomerName] [varchar](50) NULL, [TripEndTime] DATETIME NOT NULL,
A fast_forward cursor is read only by definition, meaning the rows can't be updated, but I'm not sure if they are insensitive or not. Do they reflect the changes in the database after the cursor is opened?